Using Create Database Connection for a ODBC Database in JMP 17

Question

Hello,

I have a JSL script that uses the "Create Database Connection" function to connect to an ODBC database and executes an SQL query (see below). This script was working on JMP 16, however I received a new computer and upgraded to JMP 17 and now I am receiving the attached error ("Send Expects Scriptable Object in access or evaluation of 'Send', dt1 << Show WIndow(0)"). I've performed some debugging and it looks like the Execute SQL isn't producing any data, so the Show Window function is trying to hide a table that doesn't exist. I've verified directly in the database that the query does generate data.

database = "DB";
server = "server";
user = "user";
pass = "pass";

conn = Create Database Connection ("ODBC:Description=" || database || ";DRIVER=SQL Server Native Client 11.0;SERVER=" || server || ";UID=" || user || ";PWD=" || pass || ";APP=JMP;Database=" || database || ";");
strg = "query string";
dt1 = Execute SQL(conn,strg,"Table Title");
dt1 << Show Window(0);

Thanks for your help!

Screenshot 2023-05-25 102654.png

bryan_boone · Answer

Remove

ODBC:

From your connection string.

It looks like you copied a connection string from the QueryBuilder to

Create Database Connection

ODBC: is a directive in a QueryBuilder connection string that tells it to use, in this case, ODBC.

For completeness, QueryBuilder can have

JMP:

SAS:

ODBC:

prefixes in a Query Builder script that specify the type of connection string that is represented.

tbidwell · Answer

As @pmroz points out, I think the problem might be in your definition of 'conn'.  According to the Scripting Index, the Create Database Connection uses a different syntax that what you've shown.  Instead of "ODBC:Description=", it looks like it should be "DSN=".  See the picture I've attached here.

I ran into a similar problem when I started using the Create Database Connection function because I was also including the "ODBC:Description=" syntax which didn't seem to work.

pmroz · Answer

Some possibilities:

* You don't have the correct ODBC driver on the new computer.  Make sure to "match the hatch".  I.e. if you have 32-bit JMP use a 32-bit driver.  Similarly if you have 64-bit JMP you'll need the 64-bit driver.
* Check the value of conn after the call to Create Database Connection.
* Create a connection manually with File > Database > Open Table

I assume "query string" isn't your actual query.

If that doesn't help try tech support.