topic Re: Basic Questions using oracle SQL and JSL in Discussions

Basic Questions using oracle SQL and JSL

beastwood — Wed, 14 Dec 2016 17:40:46 GMT

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?

Re: Basic Questions using oracle SQL and JSL

Eric_Hill — Wed, 14 Dec 2016 20:22:14 GMT

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

Re: Basic Questions using oracle SQL and JSL

beastwood — Wed, 14 Dec 2016 21:24:08 GMT

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.

Re: Basic Questions using oracle SQL and JSL

briancorcoran — Thu, 15 Dec 2016 13:55:14 GMT

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

Re: Basic Questions using oracle SQL and JSL

briancorcoran — Thu, 15 Dec 2016 13:55:56 GMT

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

Re: Basic Questions using oracle SQL and JSL

Eric_Hill — Thu, 15 Dec 2016 14:18:28 GMT

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

Re: Basic Questions using oracle SQL and JSL

beastwood — Thu, 15 Dec 2016 16:17:51 GMT

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.)

Re: Basic Questions using oracle SQL and JSL

briancorcoran — Thu, 15 Dec 2016 19:02:32 GMT

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

Re: Basic Questions using oracle SQL and JSL

beastwood — Fri, 16 Dec 2016 17:27:06 GMT

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!

Re: Basic Questions using oracle SQL and JSL

pmroz — Fri, 16 Dec 2016 19:37:15 GMT

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;

Re: Basic Questions using oracle SQL and JSL

beastwood — Fri, 16 Dec 2016 20:02:30 GMT

Hmm, interesting. I can actually create the tables by logging into oracle, but I'd love to get this working and the DBA hasn't been extremely helpful so far. If I just try inserting the records how do I set it up to iterate through the JMP table records to insert them? For example, let's say that I have a table in JMP with 100 records and 3 columns (ID, zip code, order amount). How do I take what you just wrote and insert those records back into the table in oracle?

Re: Basic Questions using oracle SQL and JSL

pmroz — Fri, 16 Dec 2016 20:31:14 GMT

OK here's some code I cobbled together from a production system. Names have been changed to protect the innocent and all that.

dt = New Table( "Example", Add Rows( 3 ), 
		New Column( "ID", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [1, 2, 3] )	),
	New Column( "Zip Code", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [12345, 19117, 10111] )	),
	New Column( "Order Amount", Numeric, "Continuous", Format( "Currency", "USD", 17, 2 ),
		Set Values( [56.25, 34.11, 14.72] )	)
);


// Come up with a unique temporary table name
unique_table_name = "TMP_" || right(char(today()), 5) || "_" || 
			substitute(left(char(tick seconds()), 9), ".", "_");

dbc = create database connection(<insert your parameters here>);
max_inserts = 200;
nr = nrows(dt);

// Use a series of INSERT ALL INTO statements, which have good performance
sql_statement = evalinsert(
"CREATE TABLE ^unique_table_name^ (
	id           NUMBER,
	zipcode      NUMBER,
	ORDER_AMOUNT NUMBER)");

//show(sql_statement);
	execute sql(dbc, sql_statement);
		
sql_statement = "INSERT ALL ";

for (i = 1, i <= nr, i++,
	one_id = dt:id[i];
	one_zip = dt:zip code[i];
	one_amt = dt:Order Amount[i];

	sql_statement = sql_statement || evalinsert(
" INTO ^unique_table_name^ (ID, ZIPCODE, ORDER_AMOUNT)
  VALUES(^one_id^, ^one_zip^, ^one_amt^) ");

	if (mod(i, max_inserts) == 0,
// Weird - INSERT ALL INTO requires a SELECT statement at the end - use a dummy one
		sql_statement = sql_statement || " SELECT 1 FROM DUAL";
//		show(sql_statement);
		execute sql(dbc, sql_statement);
		sql_statement = "INSERT ALL ";
	);
);

// See if there's anything left to do
if (sql_statement != "INSERT ALL ",
	sql_statement = sql_statement || " SELECT 1 FROM DUAL";
//	show(sql_statement);
	execute sql(dbc, sql_statement);
);

close database connection(dbc);