Basic Questions using oracle SQL and JSL

[#1]

I'm currently doing some testing to see how well JMP 13 and Oracle (12c) can work together.  My end-game is to import oracle data using SQL queries into JMP, run analysis on the data, and then return the data back to the Oracle database while dropping the original table.  I've been able to sucessfully bring data into JMP, but I'm having issues with saving the data table back to oracle. Here's what I have so far and what I think the drop the table command should be (currently commented out):

constring = ("DSN=***; UID=****; PWD=****);

dt1 = Open Database(constring,"select * from test1","test1");

dt1 << save database(constring,"test2");

//open database (constring,"Drop table test1");

When I run this the "test1" table pops up with the correct data in it, but the "save database" errors out with the following error:

Send Expects Scriptable Object in access or evaluation of 'Send' , dt1 << /*###*/save database( constring, "test2" ) /*###*/

In the following script, error marked by /*###*/
dt1 << /*###*/save database( constring, "test2" ) /*###*/
{}

I've been using this document (https://www.jmp.com/content/dam/jmp/documents/en/white-papers/wp-jmp-odbc-102427.pdf) as my primary reference and I think it looks the same as the example, but something is clearly off.

What am I doing wrong with the "save database" function and will the drop table command work?

[#2]

Hey, beastwood,

Well, I'm puzzled, because I mimicked your script exactly on JMP 13 with Oracle and it worked fine, including the DROP TABLE:

connstr = "DSN=Oracle Striper Replacement;UID=***;PWD=***;DBQ=EXADAT12C ;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;";

dt1 = Open Database(
	connstr,
	"SELECT * FROM \!"SQBTEST\!".\!"BigTest3\!"",
);

dt1 << Save Database(connstr, "SQBTEST.BigTest4");

Open Database(
	connstr,
	"DROP TABLE \!"SQBTEST\!".\!"BigTest3\!""
);

The only time I got the "Send expects scriptable object" message was one time when I had the wrong table name in the Open Database function and so the open failed.

Maybe throw a:

show(dt1);

in between the Open Database and Save Database calls and make sure dt1 looks right. It should look something like this in the JMP Log:

dt1 = DataTable("SQL Results 3");

HTH,

Eric

[#3]

I changed it around a little bit, trying to dot my i's and cross my t's.  Here's my jsl code and the log output:

JSL Code

constring = ("DSN=***; UID=***; PWD=***");

dt1 = Open Database(constring,"SELECT * FROM UID.TEST", "TEST1");

show(dt1);

dt1 << save database(constring,"UID.TEST1");

Log output:

Database Connection Information:
DSN=***; UID=***;
dt1 = DataTable("TEST1");
Database Connection Information:
DSN=***; UID=***;
Scriptable[]

Nothing is too small at this point, if something looks fishy let me know. Otherwise, if you can run it, I'm stuck with wondering if it's my ODBC somehow.

[#4]

Did you specify the schema name with the name of the table to save?  An example:

dt << Save Database("DSN=MyConnectionInfo", "SchemaName.BigClass");

Brian Corcoran

JMP Development

[#5]

Whoops, somehow missed that above. You did. Are you sure you have write privileges to this database?

[#6 · Accepted solution]

You could try inserting a Wait(0) statement after Open Database:

dt1 = Open Database(...);
Wait(0);
dt1 << Save Database(...);

I have my doubts that this will help, because the show(dt1) shows that it knows it is a data table.

If you simply open a sample data table, say Big Class, interactively and then, from script, do:

Current Data Table() << Save Database(...);

Does that work?  That would establish that you have write permission.

Eric

[#7]

Sadly that didn't work.  I created the tables I'm trying to reference in my own schema.  Is it possible that the ODBC connection can have select access, but not write access? (Sorry I'm not really familiar with how the ODBC connection actually works.)

[#8]

It's actually very common to only be able to query the database, but not modify the contents.

[#9]

Thanks for the information and help everyone.  It looks like I'll need to talk to my DBA and see if there is some option will will give me write access over my ODBC connection.  Thanks again!

[#10]

You need the proper grants to be able to create tables in that particular schema.  If that's not possible perhaps this approach would work:

Have your DBA create the second table and give you read, write, delete privs on it.

Use DELETE FROM to remove records, UPDATE to modify existing records, or INSERT INTO to add new records.

If you're doing a lot of INSERTS that can be quite slow.  I stumbled across INSERT ALL which can greatly speed up the process.  I chunked a large number of inserts into 200 inserts per chunk and saw great decreases in time.  Here's an example.  Note that you need "SELECT * FROM DUAL" at the end for some reason.

INSERT ALL
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
SELECT * FROM dual;