SQL returns certain coded values when errors occur (i.e., no record was found in a Query) or as a report on the result of certain actions (i.e., how many records were changed by an Update). These results appear on the Result Text tab in xCommerce Designer as special variables labeled: SQLSTATE, SQLCODE, UPDATECOUNT.  These variables are global ECMAScript variables created by the execution of SQL statements. 

The SQLCODE variable contains a status code generated by the database engine.  The SQLSTATE variable contains information generated by the database engine.  The UPDATECOUNT variable contains a count of the number of rows changed by the database engine.

These variables are available to ECMAScript functions you may write and can be used for error handling within your JDBC component. For instance, you can create a Decision action to process after an SQL statement. Based on the value returned in the UPDATECOUNT variable, you can choose one or the other set of actions in the two branches of the Decision action.  Likewise, error information contained in SQLSTATE or SQLCODE (which are standard SQL status variables) can be used to branch to appropriate recovery logic in case of error.  

The LASTSQL variable is an xCommerce-defined string variable which contains the last SQL statement to actually execute in the component in question. Logging the value of this variable can be useful for troubleshooting.  

In a JDBC component you can check the success or failure of SQL statements using the SQLCODE and SQLSTATE variables.  Below is an example:

You can also create a custom function to return the SQL variables.  This custom function can be called from a component. 

1) Use the following function in the Database Custom Script: 

function getJDBCErrors(SQLSTATE, SQLCODE, UPDATECOUNT) 

        { 

var ErrorMessage; 

ErrorMessage = "\r\n" + "!!! JDBC Error !!!" + "\r\n" + "SQLSTATE= " + SQLSTATE + "\r\n" + "SQLCODE= " + SQLCODE + "\r\n" + "UPDATECOUNT=" + UPDATECOUNT + "\r\n" + "!!! End JDBC Error !!!" + "\r\n" ;

 return(ErrorMessage); 

} 

 

Below is a sample of how to call the custom function and write the message returned by the function to the log.

Below is an example of log message received when the above component is executed when it has an error.