cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Error trapping for Oracle ODBC SQL
pmroz
Super User

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