Subscribe Bookmark
stan_koprowski

Community Manager

Joined:

Aug 8, 2012

Using JMP to connect to database, insert records

I've recently gotten a few questions from customers about how to perform database operations using JMP. One question is whether you can use JMP to connect to databases. With JMP, yes, you can connect to an ODBC-compliant database using JMP Scripting Language or the Open database dialog interface. If  you have the appropriate ODBC driver installed (and the Data Source Name or DSN has been defined), then using the File -> Database -> Open Database command will connect to the database and open the specified table within JMP. This video shows how to configure an ODBC DSN using a Microsoft Access database file.

In the example for this post, I connected to a Microsoft Access database using a machine data source name and selected the database table tblVehicle. I could also have connected to an ORACLE DBMS, MySQL database or even an SQL Server database.

If you prefer not to use the menu option, you could also do the same using JSL. In this example script provided for demonstration purposes, I connected to the Microsoft Access database and returned all the records in the table titled "tblVehicle" (see above).

//Connect to database and select table

Open Database(

"DSN=MS Access Database;DBQ=C:\Users\stkopr\Documents\MovinOn.accdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;",

"SELECT * FROM tblVehicle",

"tblVehicle"

);

Another question is I've gotten recently from customers is whether you can use JMP to insert records into a database. Again, the answer is yes, you can. You can perform some analysis with JMP and then insert an analysis results table or even a single individual record back to the database.

The demonstration script for inserting a single record into a database table is shown below.

// Connect to database and insert new record

Open Database( "DSN=MS Access Database;DBQ=C:\Users\stkopr\Documents\MovinOn.accdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;",

"INSERT INTO tblVehicle

(VehicleID , LicensePlateNum, Axle, Color)

VALUES ('TRK-199', 'JMP 429', 4, 'Blue');");

Using the same JSL submitted previously, I can display the table in JMP with the newly inserted record selected.

You could also order the records, subset the data with a where clause, submit a nested subquery or delete a single record -- all from within the comfort of JMP.

Once you have completed all your database actions, remember to close the database connection. Again, this can be done using the Disconnect button from the Open Database dialog window or using JSL.

More information on using the JMP ODBC interface can be found in the Help documentation under “Import Data from a Database.”

Let me know if you have used this feature to insert a record. Or do you typically write out the entire table back to the database?

3 Comments
Community Member

Guillaume wrote:

Hi Stan,

I use this INSERT statement as describe in your post for some jsl applications in order to save statisticals indicators by time period.

Like this I'm able to request these indicators later (this avoid the recalculations).

But I've one question. Do you know if JMP is able to catch database error and store the database error in one jsl variable ? If yes, how can I do ?

I describe more in detail the problem here : https://communities.sas.com/message/167531#167531

Thanks

Guillaume

Community Manager

Stan Koprowski wrote:

Hi Guillaume,

Thank you for your comments, and I am glad the example JSL was able to help you from having to perform any recalculations.

In response to your question, yes, you can capture the error message returned from a database to a JSL variable as a string.

One way to do this is with the Log Capture function. To test the JSL code, I simulated two different types of errors:

A failure error by not establishing a connection to the Access database prior to selecting the columns from the specified table.

A failure error by not having an appropriate ODBC driver installed.

I have included an example of how you could accomplish what you want to do in the following JSL code and the resulting error messages:

Names Default To Here( 1 );

AccessError01 = "Access Error:" || Log Capture(Open Database(

"DSN=MS Access Database;DBQ=C:UsersstkoprDocumentsMovinOn.accdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;",

"SELECT * FROM tblVehicle",

"tblVehicle"

));

The following is written to the log, and the error string is stored in the variable AccessError01 for scenario test 1.

"Access Error:

[Microsoft][ODBC Microsoft Access Driver] Could not find file '(unknown)'.

"

The following is written to the log, and the string is stored in the variable AccessError01 for scenario test 2:

"Access Error:

[iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded

"

Thank you again for your comments.

Stan

Community Member

Guillaume wrote:

Hi,

And many thanks for this tips. Log Capture function is the one that I was looking for.

Besrt regards,

Guillaume