Subscribe Bookmark RSS Feed

Saving jmp datatable to database

scott_87

Community Trekker

Joined:

Feb 22, 2013

Hi guys i have a jmp datatable that gets updated regularly on a shared network drive. I need to update a table on the database (oracle) from the table on the shared drive, both these tables have the same name and format. Here's what ive created so far but i cant get this working, any help will be much appreciated:

dt = Open("J:\Shared_folder\my_datatable.jmp");

dt = datatable("my_datatable");

dt << Save Database("DRIVER={Oracle in instantclient11_1};DBQ=My_Database;UID=My_username;PWD=My_Password;", "my_datatable");

1 REPLY
peterj

Community Trekker

Joined:

Jun 23, 2011

In my experience I've been unable to use a JMP table and execute an append or insert function on an existing database table. To get around this, I saved the JMP datatable to a new, temporary, table name. Once saved, issue an append command on the server and finally drop the temporary table.

Your workflow might look something like this:

dt << Save Database("DRIVER={Oracle in instantclient11_1};DBQ=My_Database;UID=My_username;PWD=My_Password;", "my_datatable_temp");

Open Database("DRIVER={Oracle in instantclient11_1};DBQ=My_Database;UID=My_username;PWD=My_Password;","INSERT INTO my_datatable name ( col1,col2,col3) SELECT col1,col2,col3 FROM my_datatable_temp");

Open Database(("DRIVER={Oracle in instantclient11_1};DBQ=My_Database;UID=My_username;PWD=My_Password;","DROP TABLE my_datatable_temp");