cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
loysius
Level II

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

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
Jeff_Perkinson
Community Manager Community Manager

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

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

View solution in original post

5 REPLIES 5
Jeff_Perkinson
Community Manager Community Manager

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

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
Level II

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

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

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

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.

wu
wu
Level III

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

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!

 

pmroz
Super User

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

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