topic Re: Using Create Database Connection for a ODBC Database in JMP 17 in Discussions

Using Create Database Connection for a ODBC Database in JMP 17

Austin — Fri, 09 Jun 2023 16:10:49 GMT

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!

Re: Using Create Database Connection for a ODBC Database in JMP 17

pmroz — Thu, 25 May 2023 18:06:40 GMT

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.

Re: Using Create Database Connection for a ODBC Database in JMP 17

tbidwell — Fri, 26 May 2023 14:24:18 GMT

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.

Re: Using Create Database Connection for a ODBC Database in JMP 17

bryan_boone — Thu, 22 Jun 2023 19:46:38 GMT

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.