Skip to main content

Connecting DBeaver to Netsuite SuiteAnalytics Connect using JDBC

· 4 min read

An issue with DBeaver and the Netsuite Suite Analytics Connect ODBC Driver​

When using DBeaver to connect to Netsuite SuiteAnalytics Connect using the ODBC drivers some columns may return blank or NULL. If you are just starting to investigate the schema this may lead to a lot of confusion and thinking that you do not have full permissions on the table. One way around this is to use the JDBC driver instead of the ODBC driver for DBeaver.

Why DBeaver​

DBeaver Community is a free open source database tool and is available for Windows, Mac OS X, and Linux. As someone who lives in SQL Server Management Studio, I was not as familiar with other options to connect to various databases. Lately, I have been using DBeaver frequently as I connect to Netsuite through Netsuite SuiteAnalytics Connect using the various drivers provided by Netsuite. Two of the features I like are the ER Diagrams and the ability to generate the SQL for my SELECT statements.

DBeaver and the SuiteAnalytics Connect ODBC driver​

The issue is currently DBeaver will return NULL in fields with the character data types (char, varchar, etc) when using the ODBC driver from Netsuite. Data is returned for data types like TimeStamp and BigInt. It is not specific to the Netsuite ODBC driver but that is where I am seeing the issue. Some people have other options or workarounds depending on the driver they are using. There is an Open Issue with DBeaver regarding this along with potential workarounds.

This may be fixed in a future version of DBeaver or you may find some of the listed workarounds may work for you. This is not an issue when using the ODBC connection with tools like Excel Power Query or SQL Server Integration Services. The solution that I am using is switching to the Netsuite SuiteAnalytics Connect JDBC driver.

Setting up the JDBC driver​

I am making the assumption that your Netsuite account has been set up to use SuiteAnalytics Connect.

  1. Download the JDBC driver from the SuiteAnalytics Connect driver download page. Settings are available on the Home page after logging in to your account. Go to Settings and then click Set Up Suite Analytics Connect. This screen also lists all the configuration details you need to make the connection such as Service Host, Service Port, Account ID, and Role ID.

JDBC Netsuite Download

  1. I am using Windows to set this up. Mac and Linux instructions will vary. Once the driver is downloaded double click on the downloaded file to start the installation. The main thing during installation is to note where the driver is being installed so you can locate it later.

  2. Download DBeaver Community Edition and install it

  3. In DBeaver go to Database > Driver Manager > New

    • Give it a name and a description
    • You can enter the Class Name in the box provided which is: com.netsuite.jdbc.openaccess.OpenAccessDriver
      • More info on Class Name
      • You could also use the Find Class on the Library Tab to determine the class for you. But this may require clicking OK to save the driver settings and then editing it again to have it return the class. I prefer to copy and paste it in.
    • Click on the Libraries Tab and click Add File. You will need to locate the driver that was installed earlier. An example would be C:\Program Files (x86)\NetSuite\NetSuite JDBC Drivers\nQjc.jar
    • Then click OK and close the Driver Manager window.

  4. Now we are ready to use the driver to create new connections. Go to Database > New Database Connection. Find the driver you created in the previous step and click Next. For me this is Netsuite_JDBC.

  5. Specify the URL which is specific to your account. Note that if you are following older documentation the format has changed.

    • Format:
      jdbc:ns://{ServiceHost}:1708;ServerDataSource={serverDataSource};Encrypted=1;NegotiateSSLClose=false;CustomProperties=(AccountID={accountID};RoleID={roleID});
    • Example:
      jdbc:ns://1234567.connect.api.netsuite.com:1708;ServerDataSource=NetSuite2.com encrypted=1;CustomProperties=(AccountID=1234567;RoleID=1048)
    • More details on Specifying Connection Properties
    • You can add StaticSchema to CustomProperties to see all available tables and fields like this: CustomProperties=(AccountID=1234567;RoleID=1048;StaticSchema=1;). There is a blog post on the Static Data Model detailing why this is helpful.

Leave it to DBeaver​

Once the JDBC driver is set up I have no issue seeing all the data regardless of the data type. I create multiple connections including one for ODBC, JDBC, and variations of those with and without StaticSchema enabled. This allows me to switch connections if I think there may be an issue with how the driver is presenting the data.

DBeaver is a great complement to Netsuite SuiteAnalytic Connect.