INF: How to Process Results in an ESQL Application

• Microsoft Embedded SQL for C PTK version 6.0

SUMMARY

When you process results from an ESQL application, always take into account that ESQL only processes a single result set. This is most likely to become an issue when you execute a stored procedure via the EXECUTE IMMEDIATE statement or use a CURSOR implementation.

For example:

Create procedure spTest as

   select au_lname from authors
   select au_fname from authors

In a DB-Library application, this stored procedure is treated and retrieved as two result sets: au_lname and au_fname. The DB-Library application calls dbresults multiple times in conjunction with dbcmdrow to determine the result set information.

 By design, ESQL is platform independent, allowing you to use the same

If you execute the spTest stored procedure, it really produces two result sets from SQL Server, but the ESQL application will only see the first result set.

The following code sample contains examples of executing a stored procedure:

EXEC SQL BEGIN DECLARE SECTION;

   char  strTest[15]    =  "spTest";
   char  strName[51]    =  "";

//
// Install the error handlers
//

. . .

//
// Using a cursor
//

while(SQLCODE == 0) {

   EXEC SQL FETCH C_2 INTO :strName;
   .
   .
   .


//
// Using EXECUTE IMMEDIATE
//

Depending on how you attempt to process the results, you may get different SQLCODE settings. When you are processing the results as a cursor, the OPEN returns (0) and the FETCH will continue to return (0) until it reaches the end of the first result set, and then it returns (100) as expected.

The EXECUTE IMMEDIATE returns a SQLCODE of (1). As documented, EXECUTE IMMEDIATE cannot return result rows.

We can complicate the issue a bit by adding a RAISERROR command to the stored procedure.

Create procedure spTest as

   RAISERROR(50001, 1, 1)
   select au_lname from authors

The RAISERROR statement will be treated as the result set that does not return any result rows. In the case of the OPEN, the SQLCODE is set to -50001, and in the EXECUTE IMMEDIATE, the SQLCODE is set to -50001. The RAISERROR is considered to be the result set and you cannot access the result rows returned from the select.

Reversing the stored procedure changes the behavior:

Create procedure spTest as

   select au_lname from authors
   RAISERROR(50001, 1, 1)

You again get the select result set but you do not see the RAISERROR of -50001 from the OPEN. However, the EXECUTE IMMEDIATE sets the SQLCODE to 1 because result rows were returned.

In all of the above examples, you can change the behavior by not returning results, meaning that if you take the third variation of the stored procedure and add a where clause--such as "where au_lname = '12'"--the computer no longer returns result rows but an empty result set.

In this case, the SQLCODE is set to -50001 for both the OPEN and the EXECUTE IMMEDIATE statements, because the first result set did not return rows.

Finally, if you attempt to ignore the SQLCODE, you get unpredictable results. If you do a RAISERROR and then a select, you can modify the cursor code.

While((SQLCODE == 0) || (SQLCODE == -50001)) {

   EXEC SQL FETCH C_2 INTO :strName;
   .
   .
   .

This is not a recommended or supported method of ESQL result set processing. You should always guarantee that only one result set can be returned from any given statement.