Progress
Embedded SQL-92
Guide and Reference


Using an SQLCA to Check for Errors

Sometimes an attempted SQL operation does not succeed. You can check the SQLCA for the error code and the corresponding error message. Use the following components of the SQLCA structure to obtain the error codes and error messages:

The SQLCODE component indicates the return status after the execution of an SQL statement. SQLCODE is set to zero for a successful execution and is negative for a failure. Also, SQLCODE can report SQL_NOT_FOUND, which is set during a FETCH operation when there are no more rows to be fetched.

The component SQLERRM is a null-terminated character string, which is the diagnostic text corresponding to the SQLCODE.

The component SQLERRML contains the length of the error message in SQLERRM.

The component SQLERRD is an array of six INTEGER variables.

EXAMPLE

The following code fragment shows how to use the SQLCA components SQLCODE and SQLERRM:

/* 
** CONNECT TO database ESQL statements go here 
*/ 
  
EXEC SQL BEGIN DECLARE SECTION ; 
     long deptno_v ; 
     char dname_v [20] ; 
     char loc_v [10] ; 
EXEC SQL END DECLARE SECTION ; 
  
/* 
** Determine values for input host variables 
*/ 
deptno_v = 30 ; 
strcpy (dname_v, "ACCOUNTS") ; 
strcpy (loc_v, "BOSTON") ; 
  
EXEC SQL 
     INSERT INTO department (deptno, dname, loc) 
     VALUES (:deptno_v, :dname_v, :loc_v) ; 
  
if (sqlca.sqlcode < 0) 
{ 
     printf("Insert statement failed (%ld : %s)\n", 
             sqlca.sqlcode, sqlca.sqlerrm); 
     EXEC SQL ROLLBACK WORK ; 
  
     /* 
     ** DISCONNECT here 
     */ 
     exit (1); 
} 
  
/* 
** Successful INSERT; commit changes 
*/ 
EXEC SQL COMMIT WORK ; 
  
printf ("Inserted one row \n\n"); 
  
/* 
** DISCONNECT here 
*/ 

EXAMPLE

The following example shows how to check for the SQL_NOT_FOUND status code after a FETCH operation:

EXEC SQL CONNECT TO DEFAULT ; 
  
/* 
** Declare a cursor for retrieving customer information 
*/ 
EXEC SQL 
     DECLARE cust_cur CURSOR FOR 
     SELECT last_name, city, state 
     FROM customer 
     WHERE cust_no = :cust_no_v; 
if (sqlca.sqlcode < 0) 
{ 
     print ("Declare cursor statement failed (%ld : %s)\n", 
     sqlca.sqlcode, sqlca.sqlerrm); 
     EXEC SQL ROLLBACK WORK ; 
     EXEC SQL DISCONNECT DEFAULT ; 
     exit (1); 
} 
/* 
** Open the cursor 
*/ 
EXEC SQL OPEN cust_cur ; 
  
if (sqlca.sqlcode < 0) 
{ 
     printf ("Open cursor statement failed (%ld : %s)\n", 
          sqlca.sqlcode, sqlca.sqlerrm); 
     EXEC SQL ROLLBACK WORK ; 
     EXEC SQL DISCONNECT DEFAULT ; 
     exit (1); 
} 
  
/* 
** Fetch rows and return result values into host variables 
** Break out of for loop when no more rows 
*/ 
for (;;) 
{ 
     EXEC SQL FETCH cust_cur INTO :cust_no_v, :name_v, :city_v ; 
  
     if (sqlca.sqlcode == SQL_NOT_FOUND) break ; 
 if (sqlca.sqlcode < 0) 
     { 
          printf ("FETCH cursor statement failed (%ld : %s)\n", 
               sqlca.sqlcode, sqlca.sqlerrm); 
          EXEC SQL CLOSE cust_cur ; 
          EXEC SQL ROLLBACK WORK ; 
          EXEC SQL DISCONNECT DEFAULT ; 
          exit (1); 
     } 
/* 
** Successful fetch. Print results, continue the for loop. 
*/ 
printf ("cust_no : %d, last_name : %s, city : %s\n", 
     cust_no_v, name_v, city_v) ; 
} 
EXEC SQL CLOSE cust_cur ; 
EXEC SQL COMMIT WORK : 
EXEC SQL DISCONNECT DEFAULT ; 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095