The log_execute_sql utility executes Oracle SQL statements and looks in the log for any errors. If errors are found they are displayed in a message box. There is a debug flag that, when turned on, will display the SQL statement before execution. For JMP 11 and above needs the log window open, settable via a preference (see comments below).
Function Name: log_execute_sql
Description: Execute SQL and trap Oracle ODBC errors found in the log file. If any are found, display a warning message to the user. If the global variable ::g_log_execute_sql_debug flag is set to 1, display the SQL before executing it.
Arguments:
_caller Calling program. This will be included in the error message
_dbc database connection created with create database connection call
_sql sql statement to run
_invisible If 1, set the invisible flag. If 0, don't set it.
_tbl_name Optional table name to give the returned results table. If no table name desired use ""
Subprograms: execute sql
clear log
get log
Returns: pointer to dataset
Comments: Specific to Oracle, but could be adapted for other databases
Need the following preferences set:
Preferences(Log Open Strategy( "When text is added" )); // Open Log window - needed for JMP 11
This preference is optional but good to use:
pref(ODBC Hide Connection String(1)); // Don't show connection string in dataset
Example calls:
1. Retrieve data from an Oracle table into an invisible dataset
// Name of calling program
pname = "Program1";
// Your database connection string
db_dsn = "Driver={Oracle in OraClient11g64_home};Dbq=MYDBNAME;UID=MYUSER;PWD=MYPASSWORD;"
dbc = Create Database Connection(db_dsn);
// SQL statement to use
sql_statement =
"SELECT rp.parameter_name, rp.parameter_value
FROM myschema.my_parameters rp";
// Get values from PARAMETERS table
param_dataset = log_execute_sql(pname, dbc, sql_statement, 1, "My Parameters");
close database connection(dbc);
2. Execute an INSERT statement:
// Name of calling program
pname = "Program2";
// Your database connection string
db_dsn = "Driver={Oracle in OraClient11g64_home};Dbq=MYDBNAME;UID=MYUSER;PWD=MYPASSWORD;"
dbc = Create Database Connection(db_dsn);
sql_statement = evalinsert(
"INSERT INTO myschema.my_activity_log
(user_created, date_created, my_section, activity, parameter1, value1, parameter2, value2,
parameter3, value3, parameter4, value4, parameter5, value5, parameter6, value6, parameter7, value7,
parameter8, value8)
VALUES ('^one_current_user_id^', SYSDATE, 'DASHBOARD', 'REFRESH', 'Product Name', '^product_name_txt^',
'Owner', '^owner_txt^', 'Search Event Text', '^search_event_txt^', 'Date From', '^date_from_txt^',
'Date To', '^date_to_txt^', 'Alert Status', '^alert_status_txt^', 'Event Types',
'^concat_event_types_list^', 'Alert Types', '^concat_alert_types_list^')");
// Run this SQL. No table is retrieved as it's a simple insert
log_execute_sql(pname, dbc, sql_statement, 0, "");
// Close the database connection
Close Database Connection(dbc);
