You can use the following functions to handle more complex database operations:
db = Create Database Connection( "Connection String With Password" );
Execute SQL( db, "SQL statement", <invisible>, <"New Table Title"> );
Close Database Connection( db );
Using these three functions, you can open a connection, call Execute SQL several times, and then close the connection. Create Database Connection returns a handle for use in Execute SQL and Close Database Connection.
Depending on the SQL submitted, a table might or might not be returned. A SELECT statement typically returns a JMP table. INSERT INTO would not return a table, because it is modifying one in the database.
Open a connection to your database:
dbc = Create Database Connection(
"DSN=dBASE Files;DBQ=$SAMPLE_IMPORT_DATA/;"
);
Execute one or more SQL statements using this connection:
dt = Execute SQL( dbc,
"SELECT HEIGHT, WEIGHT FROM Bigclass", "NewTable"
);
When you are finished, close your connection.
Close Database Connection( dbc );
You might want to create a database connection without selecting a table. The script would open the Database Open window with the connection already showing. We strongly recommend that you encrypt this script. Here’s an example:
dbc = Create Database Connection(
"DSN=dBASE Files;DBQ=$SAMPLE_IMPORT_DATA/;UID=MyDatabase;pwd=MyPassword");
Main Menu( "Open Table" );
Note: Execute SQL() creates a connection string that includes the cleartext password in the resulting data table. We encourage you to create the SQL query in Query Builder and then run the query in JSL. Then the password is not included in the data table.