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

Accessing Oracle tables via ODBC

Hi All,

I'm new to JMP and need to go into an Oracle database and turn about 50 tables into JMP tables using JSL.

I've found and can use the OPEN DATABASE command, but have several questions:

1) Once you've opened the database, how do you disconnect from it at the end?

2) The OPEN DATABASE Command accepts an SQL statement and a JMP table name. How do you get data from 50 tables and put them into 50 JMP tables? Use 50 OPEn DATABASE commands or is there another way to execute SQL to get data?

Thanks for your help
5 REPLIES 5
afterword
Level IV

Re: Accessing Oracle tables via ODBC

I recently had success with question two using JSL. First, make a list of your table names, enclosed in curly braces.

TableList = {"003", "004", "005", "Table6", "Table 34", ...};

Then, use a for-loop to step through the table list, constructing a query that you can feed to the "Open Database" command that you've already found. I can't paste the code because this message board interprets some of it as HTML and make it look funny.

---------------------------------------------------------------------------------
For( -insert for loop conditions here- //step through the list and concatenate query
NewQuery = "SELECT DATE, FIELD from " || TableList || " where FIELD < 1;";

currentDT = Open Database( "DSN=*****;UID=*****;PWD=*****;DBQ=*****, etc;", NewQuery);
---------------------------------------------------------------------------------

Sorry that looks so messy, but hopefully you get the idea.

To do this within the database, you would need to use 50 JOIN statements in your SQL query. Do each of the tables share a common field?

Re: Accessing Oracle tables via ODBC

I have soemthing similar here. I need to select 6 weeks of data from sql server each week separately.
For Eg,
Week 1 wud be Jan 3 to Jan 9. Week 2 wud be Jan 10 to jan 16 and so on.

I have variables for startdate, enddate, monthvar and yearvar.
To start with let startdate = 3 and enddate = 9
monthvar = 1 and yearvar = 2010

my select statement wud be
select *
from table1
where (date > startdate and startdate < enddate) and
month = monthvar and
year = yearvar;

How do I pass these variables in Open database statement?
mfisher
Level III

Re: Accessing Oracle tables via ODBC

I use Sybase, not Oracle, but:

dt=Open Database(
"DSN=Sybase;SRVR=servername;DB=dbname;UID=userid; PASSWORD=password",
"select *
from table1
where (date > startdate and startdate < enddate) and
month = monthvar and
year = yearvar;" );

Message was edited by: mfisher

Re: Accessing Oracle tables via ODBC

Thanks.

So what you are saying is that the only way to issue SQL to a database is via OPEN DATABASE. Does this connect and disconnect each time from the database each time it is issued?
afterword
Level IV

Re: Accessing Oracle tables via ODBC

I could be wrong, but I think the Open Database command is more like an "Open Database Table" function. It queries the database and dumps the data to a table. It's not a live-view of the data, so I don't think it takes up database resources when its idle even if it maintains a connection.

Hopefully the developers can tell you more.