JSL- Open Database for an Oracle db, need assistance please

[#1]

Hello everyone,

I'm trying to use the Open Database() function to create a data table from an Oracle db. I'm at a loss as to what I should do. If you could help with any advice then I'd be very much appreciative.

Here is what I've tried,

1) I started by creating the DSN,

File: DataWarehouse_RegEntry.reg

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\DWHDB]
"Description"="Data Warehouse Database"
"Driver"="C:\\WINDOWS\\system32\\sqlsrv32.dll"
"Server"="scooby1.mydomain.com"
"Trusted_Connection"="Yes"

2) then in JMP as JSL Script I made the following function call,

dt_test = Open Database(

"DSN=DWHDB;

PWD=myP@ssword;

UID=myUsername;

Description=Data Warehouse Database;

APP=JMP;

DATABASE=Shipment_QA;

Trusted_Connection=Yes"

,

"--my sql command to pull the dt");

and when I run this I get an error.

[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

I've even tried,

opening admin tools\ODBC, going to System DSN tab and trying to add/test a connection to scdobi01.cree.com with the UID and PWD provided. It says it can't connect.

----------------------------------------------------------------

The information that I've been given to make this connection is,

UserID(UID) = myUsername,

Password(PWD) = myP@ssword,

Hostname = scooby1.mydomain.com,

Port = 1521,

SID = prodooby,

Database Name = Shipment_QA

I appreciate any help or direction given. Thank you.

Sincerely,

Loysius

[#2 · Accepted solution]

The best way to get the JSL for most anything in JMP is to do it interactively and then get the script from the result.

For dealing with ODBC databases, choose File->Database->Open Table... and work through the dialogs to open the table you're interested in and then look at the Source script in the resulting data table. It will have the JSL to open that table.

-Jeff

[#3]

Thank you for the response. This has helped me find out that I don't even have data source drivers that work with Oracle. Now I have a great lead and work to do .

Thanks Jeff.

Sincerely,

Loysius

[#4]

I've never been able to get the Microsoft Oracle ODBC driver to work.  Get the free Oracle client from Oracle instead.

Jeff's answer is spot on.  If you have further trouble let us know.  I have been connecting JMP to Oracle for years now.

One thing to be sure of is to "match the hatch".  That is if you have 32-bit JMP, you need 32-bit Oracle.  If you have 64-bit JMP you need 64-bit Oracle.

[#5]

Hi,

To setup the 64bit ODBC driver in windows 10, in order for JMP connection.

What are packages need to download, as there are a few of packages  .

Would greatly appreciate if anyone  could help to list the exact packages need, and setup the procedures here!

[#6]

You can just install the Oracle client - that comes with the ODBC driver.