Getting data from NetSuite using ODBC

Difficulty: E-Z

For this post, I will show you how to connect to your NetSuite datasource using ODBC. But before we start, here are some pre-requisites for this lesson:

  1. NetSuite account — (of course, but let us point this one out so we are 100% in line)
  2. Database fishing tool — like SqlDbx or DBVisualizer

If you already have those tools, then let’s start:

STEP 1:

Go to your settings portlet on your dashboard and click the “Set up SuiteAnalytics Connect” link:

connect netsuite odbc

upon clicking, download the NetSuite ODBC driver suited to your machine (32 or 64 bit)

netsuite odbc

Once you downloaded the driver, go ahead and install it. You can skip the configuration part of the installation since I will explain it on the next step. Also, take note of the part that is boxed in blue, because that would be the values needed for the ODBC configuration.

Step 2:

(If you followed my instructions above and skipped the config part of the ODBC driver installation then you must do this part.)

After the setup of the driver, go to your ODBC data sources. On your start menu, search “ODBC Data Sources” then click on it.

connect netsuite database

And you will see this window:

odbc netsuite database

The ODBC Data Source Administrator is the place where you can add different connection source given by your 3rd party applications provided their drivers. In our case, NetSuite Drivers.

Once you are here on this part, click on the ‘System DSN’ tab then click ‘Add’ :

database netsuite

Upon clicking Add, a new window will appear where you must select the right driver, which in our case is “NetSuite Drivers XX – bit” once you selected it, click “Finish”.

Then the Driver Setup window will appear where it will ask for the credentials it needs for you to connect to NetSuite. In there, input your NetSuite account details, (remember Step 1 part that is boxed in blue? Yep, that’s it.)

odbc setup netsuite

Some pointers to consider:

  1. On the Name field, that would be the name of your Data Source, you have to remember it.
  2. On the Service Host field, there are various hosts you can connect (Sandbox, Production.)
    • Sandbox: odbcserver.sandbox.netsuite.com
    • Production: odbcserver.netsuite.com
  3. On the Service Port field, if your port 1708 is not available then you can try 1706. Or else, ask your Network Administrator to free up either one of the specified ports as NetSuite is configured to run on these ports.

Click “Test Connect” once you are done inputting the needed requirements. If you are successfully connected to ODBC server of NetSuite a new window should appear asking for your account username and password.

Step 3:

Once you are able to add your NetSuite datasource successfully, then it’s the time to connect to it!

Open your database fishing tool of choice (in my case, SqlDbx)

odbc database netsuite

On the DBMS type, select ODBC  the on the DSN part, select the Data Source you created.

After clicking OK then we’re done! That’s it you are now connected to your NetSuite account database.

Here are some final pointers before we end:

  1. You can only use SELECT queries on NetSuite database.
  2. The tables you can see there are just Views in NetSuite’s end, that is why the statement #1 is the only possible command you can do.

Imagine what you can do with this, you can now create your own reports, desktop applications, and other stuff that is feeding on your NetSuite data.

Happy coding!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s