Genero db Sql Error management

Genero db has some specific rules that need to be highlighted

1. To handle SQL errors, you can use the SQLCA.SQLCODE register, which gives the Informix^® error code converted from the native Genero db error. Additional SQL error information can be checked with following registers:
   • The native Genero db error code is stored in SQLCA.SQLERRD[2] register
   • The native Genero db error message is stored in SQLERRMESSAGE operator

   This table shows the native Genero db errors and the corresponding Informix SQL error returned in SQLCA.SQLCODE:

   Table 1. Native Genero db errors and the corresponding Informix SQL error returned in SQLCA.SQLCODE

   Genero db SQLCA.SQLERRD[2]	Informix SQLCA.SQLCODE	Error description (SQLERRMESSAGE)
   -1	-201	syntax error
   -3	-206	table not found
   -4	-201	syntax error
   -6	-217	column not found
   -17	-743	object exists
   -24	-201	syntax error
   -35	-236	cols/vals mismatch
   -10014	-213	SQL interrupted
   -30004	-263	Cannot wait on another session.
   -30005	-213	SQL interrupted
   -60001	-268	Uniqueness constraint violation.
   -80002	-387	no connect permission

2. Many different formerly Informix errors can be returned by the Genero db database driver; For example, fetch on open cursor, commit on unopened transaction, and so on.

   Here is the list of known errors the Genero db database driver can return:

   Table 2. List of known errors the Genero db database driver can return

   SQLCA.SQLERRD[2]	SQLERRMESSAGE	Reason
   -213	Query canceled	Long running query interrupted by user
   -254	Too many or too few host variables given.	PREPARE s FROM "insert into t values (?,?)" EXECUTE s USING x,y,z (z is too many)
   -255	Not in transaction.	OPEN insert cursor without BEGIN WORK
   -284	A subquery has not returned exactly one row.	SELECT * INTO ... FROM tab, returns more than one row
   -400	Fetch attempted on unopened cursor.	FETCH on cursor not opened
   -404	The cursor or statement is not available.	OPEN cursor after a FREE
   -410	Prepare statement failed or was not executed.	EXECUTE a statement where PREPARE has failed
   -413	Insert attempted on unopened cursor.	PUT on insert cursor not opened
   -481	Invalid statement name or statement was not prepared.	EXECUTE a statement without PREPARE
   -482	Invalid operation on a non-SCROLL cursor.	FETCH LAST/PREV/... on non SCROLL cursor
   -526	Updates are not allowed on a scroll cursor.	SELECT FOR UPDATE with SCROLL cursor
   -535	Already in transaction.	BEGIN WORK x2
   -6370	Unsupported SQL feature.	CREATE DATABASE, SET CONNECTION DORMANT, CREATE PROCEDURE FROM, DATABASE IN EXCLUSIVE MODE, CONNECT TO @server, ...

3. If an unknown error comes from the DB Server and therefore is not mapped as an Informix error, you'll get: -6372 General SQL error, check SQLCA.SQLERRD[2]

   When this SQL error occurs, you can check the native SQL error in the SQLCA.SQLERRD[2] register.

4. If an unexpected problem happens within the database driver, the driver will return: -6319 Internal error in the database library. Set FGLSQLDEBUG to get more details

   When this SQL error occurs, you should set the FGLSQLDEBUG environment variable to get more details about the internal error.

Error handling example

MAIN
 WHENEVER ERROR CONTINUE
 CONNECT TO dsn_connectstring  IF STATUS <> 0 THEN
    DISPLAY "ERROR: Connection to the database failed."
    DISPLAY SQLCA.SQLCODE, ": ",
            SQLCA.SQLERRD[2], "-", 
            SQLERRMESSAGE
    EXIT PROGRAM 1
 END IF

 WHENEVER ERROR STOP

END MAIN