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
gbu
gbu
Level III

Catching database error message

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.

1 ACCEPTED SOLUTION

Accepted Solutions
stan_koprowski
Community Manager Community Manager

Re: Catching database error message

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

View solution in original post

2 REPLIES 2
stan_koprowski
Community Manager Community Manager

Re: Catching database error message

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

gbu
gbu
Level III

Re: Catching database error message

Hi,

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