Using DataGrip with NetSuite

#database #datagrip #jdbc #netsuite #odbc

JetBrains DataGrip is a great for managing the database portion of your web development projects. It allows you to write queries and design new objects against a wide variety of databases. This video will show you how to add the necessary driver and connect to the NetSuite ERP system to query live data.

Video link until I can get the video embed working correctly.

Steps:

  • Download and install the JDBC drivers from NetSuite for your operating system. If you have access, there is a link to them in the Settings portlet on your dashboard.
    • On Windows, download and run the executable to install
    • Mac, download and extract the NQjc.jar file to a location of your choice.
  • In DataGrip, click File -> Data Sources...
  • In the Data "Sources and Drivers" window, click Add -> Driver
  • In the Name box, enter: NetSuite
  • In the Additional files box, click Add
    • Find and select the NQjc.jar file that you downloaded in the first step.
  • In the Class box, select com.netsuite.jdbc.openaccess.OpenAccessDriver
  • In the Dialect box, select Oracle.
  • Click Apply. This should add the driver to the list of drivers on the left-hand side.
  • Click Add -> NetSuite. Complete the following fields:
    • Name: Give this connection a Name
    • User: enter the email address that you use to log into NetSuite
    • Password: enter the password that you use to log into NetSuite
    • URL: jdbc:ns://URL:1708;ServerDataSource=NetSuite.com;encrypted=1;Ciphersuites=TLS_RSA_WITH_AES_128_CBC_SHA;CustomProperties=(AccountID=ACCOUNT_ID;RoleID=ROLE_ID).
      • URL – You can get this from within your NetSuite account by navigating to: Setup -> Company -> Company Information -> Company URLs -> SuiteAnalytics Connect
      • ACCOUNT_ID – You can get this from within your NetSuite account by navigating to: Setup -> Company -> Company Information -> Account ID
      • ROLE_ID – The internal ID of any role with the SuiteAnalytics Connect permission. The Administrator role id is 3.
  • Click Test Connection. You should receive a green Successful message.
  • On the schema’s tab, check the box next to the company that you wish to connect to.
  • Click OK. You should now see the connection listed in your Database window.

UPDATED to reflect the new cipher suite needed in the JDBC URL.