Accessing Oracle tables via ODBC

[#1]

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

[#2]

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?

[#3]

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?

[#4]

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

[#5]

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?

[#6]

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.