[Nic]

I have followed all the steps in the documentation to set up SuiteAnalytics connect. I can verify that the driver and configuration is correct by going to the ‘System DNS’ tab in the ODBC Data Source.

Now what? The documentation completely falls off here. I use MySQL and I was thinking of setting up a new location and connecting through SSH. But that does not work. I guess that makes sense because those are to connect to a MySQL server, which NetSuite is not.

I imagine running some sort of script is going to be necessary. I have experience running PHP and Perl scripts to connect to a database using ODBC. Is that what I am going to need to do to successfully run queries off of NetSuite?
This is a cached copy. Click here to see the original post.

[al3xicon]

Nic – you will need a tool that allows you to connect via creating a “Generic ODBC” connection. I have had luck with a trial of Toad Data Point (free). There are others as well (though I haven’t really tried many others), and these provide a GUI for interacting with and querying NetSuite via ODBC. You can also code your own app to connect to it of course, but I’d suggest not reinventing the wheel.

I hope this helps!

[michoel]

Another option for a GUI querying program is SQuirrel SQL, which is open source and pretty fully featured.

[Nic]

michoel, I am looking into using SQuirrel SQL, but I do not have any Java experience, but it looks like I can query data using pure SQL through GUI. So I am would like to give it a try.

I have installed SQuirrel SQL and the JDBC driver. I added NetSuite’s JDBC driver (NQjc.jar) into squirrel’s path (/program files/squirrel/lib).

Now I am bit lost with creating the driver. I don’t suppose you know the ‘Example URL’ and the ‘WebSite URL’ and the ‘Class Name’ to create the driver?

[michoel]

Nic –

I am looking into using SQuirrel SQL, but I do not have any Java experience, but it looks like I can query data using pure SQL through GUI. So I am would like to give it a try.

Correct, no need to write any Java code.

Now I am bit lost with creating the driver. I don’t suppose you know the ‘Example URL’ and the ‘WebSite URL’ and the ‘Class Name’ to create the driver?

Try the following:

Example URL: jdbc:ns://odbcserver.netsuite.com:1708;ServerDataSource=nets uite.com;Encrypted=1;CustomProperties=(AccountID=1 2345;RoleID=3)

Website URL:

Class Name: com.netsuite.jdbc.openaccess.OpenAccessDriver

[Remove the spaces in the url, I don’t know why the forum keeps adding them].

Let me know if you need more help.

[Nic]

I was able to successfully create the driver, I received this message after creating. “Driver class com.netsuite.jdbc.openaccess.OpenAccessDriver successfully registered for driver definition: NetSuite”

But was not able to create an alias. Below is the error message I received, not sure if it means anything to you.

Again I do not have any experience with Java, but was wondering if I needed to add/edit environmental variables? Also when I create the driver inside of SQuirrell is does not seem like there is anything actually connecting the driver to the NCjc.jar file (NetSuites JDBC driver that I downloaded). Lastly I am running Java version 7. Please let me know if you require any addition information.

HTML Code:
java.util.concurrent.ExecutionException: java.lang.RuntimeException: java.sql.SQLException: [NetSuite][NetSuite JDBC Driver][OpenAccess Service]The specified data source is not defined.
at java.util.concurrent.FutureTask$Sync.innerGet(FutureTask.java:232)
at java.util.concurrent.FutureTask.get(FutureTask.java:91)
at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.awaitConnection(OpenConnectionCommand.java:132)
at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.access$100(OpenConnectionCommand.java:45)
at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand$2.run(OpenConnectionCommand.java:115)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
at java.util.concurrent.FutureTask.run(FutureTask.java:138)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:662)
Caused by: java.lang.RuntimeException: java.sql.SQLException: [NetSuite][NetSuite JDBC Driver][OpenAccess Service]The specified data source is not defined.
at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.executeConnect(OpenConnectionCommand.java:171)
at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.access$000(OpenConnectionCommand.java:45)
at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand$1.run(OpenConnectionCommand.java:104)
… 6 more
Caused by: java.sql.SQLException: [NetSuite][NetSuite JDBC Driver][OpenAccess Service]The specified data source is not defined.
at com.netsuite.openaccess.ssp.Diagnostic.toSQLException(Unknown Source)
at com.netsuite.openaccess.ssp.Chain.cnvDiagnostics(Unknown Source)
at com.netsuite.openaccess.ssp.Chain.decodeDiagnostic(Unknown Source)
at com.netsuite.openaccess.ssp.Chain.decodeBody(Unknown Source)
at com.netsuite.openaccess.ssp.Chain.decode(Unknown Source)
at com.netsuite.openaccess.ssp.Chain.send(Unknown Source)
at com.netsuite.openaccess.ctxt.conn.ConnectionContext.connect(Unknown Source)
at com.netsuite.jdbc.openaccess.OpenAccessImplConnection.open(Unknown Source)
at com.netsuite.jdbc.oabase.BaseConnection.connect(Unknown Source)
at com.netsuite.jdbc.oabase.BaseConnectionStartup.run(Unknown Source)

[michoel]

Sorry, I did not come across that error, and am not sure what that means. Perhaps Netsuite support could assist you?

As far as I can recall I did not need to touch any environmental variables.

You could try using the JDBC ODBC bridge instead, I have also managed to get that working in SQuirrel SQL.

[charoldson]

QweryBuilder has been my tool of choice.

[michoel]

QweryBuilder has been my tool of choice.

Looks as though it’s now discontinued.

http://werysoft.com/qwerybuilder.aspx

[Author]

Posts