topic Re: Catching database error message in Discussions

Catching database error message

gbu — Thu, 16 May 2013 07:47:47 GMT

Hello,

I would like to know if there is a way to catch database error message and put it into one jmp variable.

Example for an Oracle DB, if I try to insert one value already existing on ID primary key :

/* JSL */

sql = "INSERT INTO MY.TABLE (ID, DATA) VALUES ('A',150)";

OpenDatabase (dsnDB, sql);

/* LOG display */

[Oracle][ODBC][Ora]ORA-00001: unique constraint (MY.TABLE) violated

ORA-06512: at line 1

I would like to have in a jsl variable this sting : "[Oracle][ODBC][Ora]ORA-00001: unique constraint (MY.TABLE) violated".

I didn't find any solutions for the moment.

Thanks.

Re: Catching database error message

stan_koprowski — Fri, 17 May 2013 15:33:44 GMT

Hi,

One way to do this is with the Log Capture function. To test the JSL code I simulated two different types of errors:

A failure error by not establishing a connection to the Access database prior to selecting the columns from the specified table
A failure error by not having an appropriate ODBC driver installed

I have included an example of how you could accomplish what you want to do in the following JSL code and the resulting error messages:

Names Default To Here( 1 );
AccessError01 = "Access Error:" || Log Capture(Open Database(
"DSN=MS Access Database;DBQ=C:UsersstkoprDocumentsMovinOn.accdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;",
"SELECT * FROM tblVehicle",
"tblVehicle"
));

The following is written to the log and the error string is stored in the variable AccessError01 for scenario test 1.

"Access Error:
[Microsoft][ODBC Microsoft Access Driver] Could not find file '(unknown)'. 
"

The following is written to the log and the string is stored in the variable AccessError01 for scenario test 2.

"Access Error:
[iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded 
"

Yes, you can...connect to an ODBC database

Re: Catching database error message

gbu — Sat, 18 May 2013 18:59:02 GMT

Hi,

Thanks for this great tips. Log Capture function is the function that I was looking for.