Subscribe Bookmark RSS Feed
pmroz

Super User

Joined:

Jun 23, 2011

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