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
Mauro_Gerber
Level IV

catch Sql Query error

Hello

I want to update a database via the New SQL Query function:

Names Default To Here( 1 );
obj = New SQL Query(
	Connection("DB connection string"),
	Custom SQL "
		UPDATE table
			SET Column_1 = 1
		where Column_2 = '2'
	")
);
Error_Msg = Log capture( obj << Run;);

I get the following error message:

""
Error running SQL Query:
[Server] Invalid object name table

Log Capture only get the empty "" string. So i don't know if the query was successful since it returns an empty string when no error occurs.

 

simmilar topic but with open database: https://community.jmp.com/t5/Discussions/Catching-database-error-message/m-p/6749#M6743

"I thought about our dilemma, and I came up with a solution that I honestly think works out best for one of both of us"
- GLaDOS
3 ACCEPTED SOLUTIONS

Accepted Solutions
pmroz
Super User

Re: catch Sql Query error

You might have better luck with the EXECUTE SQL function.  I wrote a function on top of EXECUTE SQL called LOG_EXECUTE_SQL, that looks for errors, and if found, displays a text box with the error and the SQL that caused it.  Also you can display the SQL for any calls by setting a global variable.

//------------------------------------------------------------------------------
/*
Function Name: log_execute_sql

Description:    Execute SQL and trap 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

Subprograms:	execute sql
				clear log
				get log

Returns: pointer to dataset 

Comments:
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 a table into an invisible dataset

	pname = "Main Program";
	dbc = Create Database Connection(::db_dsn);		// ::db_dsn contains your database connection string
													// The two colons means this is a global variable btw
	// Get values from PARAMETERS table
	sql_statement = 
	"SELECT rp.parameter_name, rp.parameter_value
		FROM myschema.my_parameters rp";

	param_dataset = log_execute_sql(pname, dbc, sql_statement, 1, "My Parameters");

	close database connection(dbc);


2. Execute an INSERT statement:

		dbc = Create Database Connection(::db_dsn);		// ::db_dsn contains your database connection string

		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^', CURRENT_DATE, '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 - end of initialization queries
			Close Database Connection(dbc);
*/
log_execute_SQL = function({_caller, _dbc, _sql, _invisible, _tbl_name},
		{Default Local},

	if (isempty(_dbc),
		_nw = new window("log_execute_sql: Empty Value", << modal,
			textbox("Error in log_execute_sql: The database connection is empty."),
			);
			throw("Empty database connection string");
	);

	if (isempty(_sql) | _sql == "",
		_nw = new window("log_execute_sql: Empty Value", << modal,
			textbox("Error in log_execute_sql: The SQL statement is empty."),
			);
			throw("Empty SQL string");
	);

// Check the debug flag
	_debug = 0;
	if (! is empty(global:g_log_execute_sql_debug),
		_debug = global:g_log_execute_sql_debug;
	);
	
	if (_debug,
		_nw = new window("log_execute_sql DEBUG", << modal,
			lineup box(ncol(2),
				text box("_caller: "),    text box(_caller),
				text box("_dbc: "),       text box(char(_dbc)),
				text box("_invisible: "), text box(char(_invisible)),
				text box("_tbl_name: "),  text box(_tbl_name),
			),
			panel box("SQL",
				sql_teb = text edit box(_sql)
			),
			hlistbox(
				_cb = check box("Turn off subsequent debug output"),
				_tb = text box(" "),
				_bb = button box("OK",
					is_checked = _cb << get(1);
					if (is_checked,
						global:g_log_execute_sql_debug = 0;
					);
				),
			),
			sql_teb << set nlines(40) << set width(500);
		);
	);

// OK we're good to go now.
	batch interactive(1);	// New 5-17-2018 force all errors to go to the log
	_log_contents = log capture(
		if (_invisible,
			_dt = execute sql(_dbc, _sql, invisible, _tbl_name);
			,
			_dt = execute sql(_dbc, _sql, _tbl_name);
		);
	);
	batch interactive(0);	// New 5-17-2018 errors will not be forced to go to the log

// Check the log window for ODBC errors
//	_log_contents = get log();
	_found_error  = 0;
	crlf = hex to char("0D0A");
	if (contains(_log_contents, "[Oracle][ODBC]") | contains(uppercase(_log_contents), "ERROR"),	// Looking for errors
	// then
		_found_error = 1;
		_log_list = words(_log_contents, crlf);
		_error_msg = _log_list[1];
	);

	if (_found_error,
	// then
//		print(_sql);										// Write the SQL out to the log window
		_nw = new window("log_execute_sql: Oracle ODBC Error", << modal,
			textbox("An Oracle error occurred in " || _caller || ": "),
			panel box("Please email to technical support:",
				sql_teb = text edit box(evalinsert(
"Calling Program: ^_caller^
Error Message:   ^_error_msg^
SQL:
^_sql^"))),
			sql_teb << set nlines(30) << set width(500),
		);
	);

// Return the table pointer
	_dt;
);	// end log_execute_sql

View solution in original post

Mauro_Gerber
Level IV

Re: catch Sql Query error

THX

Short version:

E_Msg = Log capture(
	EXECUTE SQL(
		create database connection("DB_String");
	,
		"SQL string"
	);
);
"I thought about our dilemma, and I came up with a solution that I honestly think works out best for one of both of us"
- GLaDOS

View solution in original post

pmroz
Super User

Re: catch Sql Query error

You might want to surround your code with these statements to force all error messages to the log:

 

batch interactive(1);

<your code>

batch interactive(0);

View solution in original post

11 REPLIES 11

Re: catch Sql Query error

I looks like you are missing the opening parenthesis after Custom SQL argument.

 

I think the error message is referring "table" after Update. Is that the actual name or is it a variable in which the name is stored? Also, should the name of the table be quoted in SQL?

Mauro_Gerber
Level IV

Re: catch Sql Query error

I made up the error message from the database since I cannot provide the acctual connection string and message.
"I thought about our dilemma, and I came up with a solution that I honestly think works out best for one of both of us"
- GLaDOS
Mauro_Gerber
Level IV

Re: catch Sql Query error

I found the tailcontents = Get Log( -5 ); code to acces the Log, but the message ist still not accessible:

 

{""}
Error running SQL Query: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '*table name*'. [SQLSTATE=42S02]

I only get the {""} from the log file as with capture log. even when reading out the whole via Get Log();

"I thought about our dilemma, and I came up with a solution that I honestly think works out best for one of both of us"
- GLaDOS
stan_koprowski
Community Manager Community Manager

Re: catch Sql Query error

Hi @Mauro_Gerber ,

did you specify a default database in the connection string with "Database=myDBname"

or via the ODBC driver UI?

 

It appears that the table is not found in the “default” database that you are querying against. 

cheers,

Stan

jara95
Level III

Re: catch Sql Query error

Add the schema name in front of your table like schema.table_name

Mauro_Gerber
Level IV

Re: catch Sql Query error

Hello @jara95

Thank you for your reply

I deliberately make a false SQL statement to force an error message.
I update/extend an existing data base at high speed and need to verify that the new entry is actually present. If an error occurs, the user should be alerted with an error message (best case the one I got from SQL). The workaround would be to make a second SQL statement to ask for the new entry which opens an new data table to verify and then close again.
This would need a lot more time and put more “stress” on the server and doesn't give a clue what went wrong.

"I thought about our dilemma, and I came up with a solution that I honestly think works out best for one of both of us"
- GLaDOS
jara95
Level III

Re: catch Sql Query error

Sorry, I misread your question.

 

Could you try Run Foreground command?

 

Error_Msg = Log Capture( obj << run	foreground );
pmroz
Super User

Re: catch Sql Query error

You might have better luck with the EXECUTE SQL function.  I wrote a function on top of EXECUTE SQL called LOG_EXECUTE_SQL, that looks for errors, and if found, displays a text box with the error and the SQL that caused it.  Also you can display the SQL for any calls by setting a global variable.

//------------------------------------------------------------------------------
/*
Function Name: log_execute_sql

Description:    Execute SQL and trap 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

Subprograms:	execute sql
				clear log
				get log

Returns: pointer to dataset 

Comments:
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 a table into an invisible dataset

	pname = "Main Program";
	dbc = Create Database Connection(::db_dsn);		// ::db_dsn contains your database connection string
													// The two colons means this is a global variable btw
	// Get values from PARAMETERS table
	sql_statement = 
	"SELECT rp.parameter_name, rp.parameter_value
		FROM myschema.my_parameters rp";

	param_dataset = log_execute_sql(pname, dbc, sql_statement, 1, "My Parameters");

	close database connection(dbc);


2. Execute an INSERT statement:

		dbc = Create Database Connection(::db_dsn);		// ::db_dsn contains your database connection string

		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^', CURRENT_DATE, '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 - end of initialization queries
			Close Database Connection(dbc);
*/
log_execute_SQL = function({_caller, _dbc, _sql, _invisible, _tbl_name},
		{Default Local},

	if (isempty(_dbc),
		_nw = new window("log_execute_sql: Empty Value", << modal,
			textbox("Error in log_execute_sql: The database connection is empty."),
			);
			throw("Empty database connection string");
	);

	if (isempty(_sql) | _sql == "",
		_nw = new window("log_execute_sql: Empty Value", << modal,
			textbox("Error in log_execute_sql: The SQL statement is empty."),
			);
			throw("Empty SQL string");
	);

// Check the debug flag
	_debug = 0;
	if (! is empty(global:g_log_execute_sql_debug),
		_debug = global:g_log_execute_sql_debug;
	);
	
	if (_debug,
		_nw = new window("log_execute_sql DEBUG", << modal,
			lineup box(ncol(2),
				text box("_caller: "),    text box(_caller),
				text box("_dbc: "),       text box(char(_dbc)),
				text box("_invisible: "), text box(char(_invisible)),
				text box("_tbl_name: "),  text box(_tbl_name),
			),
			panel box("SQL",
				sql_teb = text edit box(_sql)
			),
			hlistbox(
				_cb = check box("Turn off subsequent debug output"),
				_tb = text box(" "),
				_bb = button box("OK",
					is_checked = _cb << get(1);
					if (is_checked,
						global:g_log_execute_sql_debug = 0;
					);
				),
			),
			sql_teb << set nlines(40) << set width(500);
		);
	);

// OK we're good to go now.
	batch interactive(1);	// New 5-17-2018 force all errors to go to the log
	_log_contents = log capture(
		if (_invisible,
			_dt = execute sql(_dbc, _sql, invisible, _tbl_name);
			,
			_dt = execute sql(_dbc, _sql, _tbl_name);
		);
	);
	batch interactive(0);	// New 5-17-2018 errors will not be forced to go to the log

// Check the log window for ODBC errors
//	_log_contents = get log();
	_found_error  = 0;
	crlf = hex to char("0D0A");
	if (contains(_log_contents, "[Oracle][ODBC]") | contains(uppercase(_log_contents), "ERROR"),	// Looking for errors
	// then
		_found_error = 1;
		_log_list = words(_log_contents, crlf);
		_error_msg = _log_list[1];
	);

	if (_found_error,
	// then
//		print(_sql);										// Write the SQL out to the log window
		_nw = new window("log_execute_sql: Oracle ODBC Error", << modal,
			textbox("An Oracle error occurred in " || _caller || ": "),
			panel box("Please email to technical support:",
				sql_teb = text edit box(evalinsert(
"Calling Program: ^_caller^
Error Message:   ^_error_msg^
SQL:
^_sql^"))),
			sql_teb << set nlines(30) << set width(500),
		);
	);

// Return the table pointer
	_dt;
);	// end log_execute_sql
Mauro_Gerber
Level IV

Re: catch Sql Query error

THX

Short version:

E_Msg = Log capture(
	EXECUTE SQL(
		create database connection("DB_String");
	,
		"SQL string"
	);
);
"I thought about our dilemma, and I came up with a solution that I honestly think works out best for one of both of us"
- GLaDOS