World Statistics Day was yesterday, but we’re celebrating all week long! This celebration means acknowledging the impact statistics has on our world. Who is your favorite statistician? Share with us who they are and why they top your favorites list.
Choose Language Hide Translation Bar
pmroz
Super User

Error trapping for Oracle ODBC SQL

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

Article Labels
Article Tags