Subscribe Bookmark RSS Feed

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

loysius

Community Trekker

Joined:

Jun 5, 2015

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

1 ACCEPTED SOLUTION

Accepted Solutions
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.

9046_Parallels DesktopScreenSnapz001.png9072_Pasted_Image_6_20_15__5_08_PM.png9071_Pasted_Image_6_20_15__5_04_PM.png

9073_Pasted_Image_6_20_15__5_10_PM.png

-Jeff

-Jeff
3 REPLIES
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.

9046_Parallels DesktopScreenSnapz001.png9072_Pasted_Image_6_20_15__5_08_PM.png9071_Pasted_Image_6_20_15__5_04_PM.png

9073_Pasted_Image_6_20_15__5_10_PM.png

-Jeff

-Jeff
loysius

Community Trekker

Joined:

Jun 5, 2015

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

pmroz

Super User

Joined:

Jun 23, 2011

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.