Catching database error message

[#1]

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.

[#2 · Accepted solution]

Hi,

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

1. A failure error by not establishing a connection to the Access database prior to selecting the columns from the specified table
2. 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

[#3]

Hi,

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