an open source technology and e-commerce related site

NetSuite & ODBC under Linux to generate reports via SQL

Written by Matthew Cooper on July 07, 2013

I have been dabbling in NetSuite for the last year and a half and by far the most annoying thing for me as a developer has been that I couldn't access the NetSuite database via ODBC from Linux.

When I wanted to automate sales and inventory reports from the command line I used to execute a NetSuite saved search via web service (suite talk) in Java or PHP. That way of getting information from NetSuite wasn't really stable though. Firstly I found speed was a big problem because of the overhead a SOAP request/response generates and secondly if you I were to export a large amount of data (like an entire catalog of products) the result set would get truncated.

The read only NetSuite ODBC driver for Windows seemed to be a viable solution at first but it did mean running Windows as a second operating system under Virtual Box and executing my SQL queries there. While not perfect it was working OK until I needed to start generating reports on the weekend also. Unfortunately the office maintenance people often cut the power without warning and I would end up with missed days and angry colleagues on Monday. I needed to get my hands on a native Linux driver for NetSuite’s protected ODBC to delegate this work to a Linux production server with high uptime reliability.

I tried to apply a generic database driver wrapper under WINE to get it functional under Linux but that didn’t work. So, I decided to trace down exactly who created the Windows driver and contact them directly. It turned out to be the nice folks over @datadirect.com. They kindly asked me to bump the issue with the NetSuite Support team saying they had recently developed a native Linux driver based on the customizable DataDirect OpenAccess SDK. Sure enough my NetSuite support told me they had just released it, but that it may not be stable yet. Well, I can say that I have it running just fine so if you need to create reports from NetSuite I think that SQL is far more effective solution than exporting CSV files, and you should give it a try.

Below are the steps I used to get the driver, install it and connect it with PHP under Linux:

  1. cd /usr/src
  2. mkdir netsuite_odbc
  3. cd netsuite_odbc
  4. #This is a trial driver. If you want to use it properly you need to contact your NetSuite
  5. #sales rep for a license file. Meanwhile you can continue installing the trial using "EVAL" for
  6. #the serial number and key fields.
  7. wget http://www.datadirect.com/download/files/evals/openaccess\
  8. /sdk/603/client/odbc/linux/oaodbc64_linux_6_0_0_3.tar
  9. tar -xvf oaodbc64_linux_6_0_0_3.tar
  10. cd oaodbc64_linux_6_0_0_3
  11. #You might have to install ksh (http://linux.die.net/man/1/ksh) if you don't have it.
  12. ksh install.pi
  13.  

Once installed you need to configure the odbc.ini file in the NetSuite ODBC driver installation directory that you chose. The data source name (DSN) section of the ini file should look something like this:

  1. [NetSuite.com ODBC driver]
  2. Driver=/opt/netsuite_odbc/lib64/ivoa22.so
  3. Description=DataDirect OpenAccess SDK 6.0
  4. Host=odbcserver.netsuite.com
  5. Port=1708
  6. UseLDAP=0
  7. Encrypted=1

Great, we are almost done, we just need to declare some global variables and configure PHP.

  1. ODBC_HOME=/opt/netsuite_odbc; export ODBC_HOME
  2. ODBCINI=/opt/netsuite_odbc/odbc.ini;export ODBCINI
  3. ODBCINST=/opt/netsuite_odbc/odbcinst.ini;export ODBCINST
  4. CPPFLAGS="-I$ODBC_HOME/include"; export CPPFLAGS
  5. CFLAGS="-I$ODBC_HOME/include"; export CFLAGS
  6. CUSTOM_ODBC_LIBS="-L$ODBC_HOME/lib64 -lodbc -lodbcinst";
  7. export CUSTOM_ODBC_LIBS
  8.  

Include a C header file in /opt/netsuite_odbc/lib64/ called odbc.h

  1. #include <sql.h>
  2. #include <sqlext.h>
  3. #include <odbcinst.h>
  4.  

Get PHP and compile it!

  1. cd /usr/src
  2. mkdir php_src
  3. cd php_src
  4. wget http://www.php.net/get/php-5.4.17.tar.gz/from/au1.php.net/mirror
  5. tar -zxvf php-5.4.17.tar.gz
  6. cd php-5.4.17
  7. #I needed to configure PHP to work with this thing without writing over the production
  8. #PHP on the server which is why I use the --prefix flag. Remember to include any other
  9. #flags you might need here also like --with-pdo-mysql if you want to use MySQL for instance.
  10. ./configure --prefix=/opt/php-5.4.17 --with-custom-odbc=$ODBC_HOME
  11. make
  12. make install
  13.  

If everything compiled correctly you can try and execute a simple PHP ODBC connection script using the newly configured PHP executable from the CLI (/opt/php-5.4.17/bin/php /path/to/your/file.php). Such a script might look like this:

You must should include the putenv line so that PHP knows where the ODBC license resides if you do not include it, you will receive a non-critical PHP warning like this one:

“[DataDirect][ODBC OpenAccess SDK driver]You are attempting to use this DataDirect Technologies ODBC driver from an application that is not licensed to use it.[DataDirect][ODBC OpenAccess SDK driver]You are not licensed to use this DataDirect Technologies ODBC Driver under the license you have purchased. If you wish to purchase a license for use with this application, then you may use this driver for a period of 15 days, during which time you are required to obtain a license. [DataDirect][ODBC OpenAccess SDK driver]You can order a license for a DataDirect Technologies ODBC Driver for use with this application by calling DataDirect Technologies at 800-876-3101 in North America and +44 (0) 1753-218 930 elsewhere.“

  1. <?php
  2. putenv("ODBCINI=/opt/netsuite_odbc/odbc.ini");
  3. /**
  4. * The first param should be the same as what you called your DSN.
  5. */
  6. $connection = odbc_connect("NetSuite.com", "user@your_domain.com", 'your_password');
  7. $res = odbc_exec($connection, "SELECT * FROM some_netsuite_table");
  8. while( $row = odbc_fetch_array($res) ) {
  9. print_r($row);
  10. }
  11. odbc_close($connection);
  12.  

I hope you found this article interesting; if you did I would love to hear from you.
Follow me on Twitter @debugthat. Thanks!