INF: How to Process Results in an ESQL Application

ID: Q151599

The information in this article applies to:

Microsoft Embedded SQL for C Programmer's Toolkit, version 6.O

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
go

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 source code and another precompiler to access a different DBMS environment. To accomplish this, ESQL handles each statement as a single result set.

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]    =  "";

EXEC SQL END DECLARE SECTION;
// 
// Install the error handlers
// 
EXEC SQL WHENEVER SQLERROR CALL ErrorHandler();
EXEC SQL WHENEVER SQLWARNING CALL ErrorHandler();
EXEC SQL WHENEVER NOT FOUND CALL ErrorHandler();
.
.
.

// 
// Using a cursor
// 
EXEC SQL DECLARE C_2 CURSOR FOR STMT1;
EXEC SQL PREPARE STMT1 FROM :strTest;
EXEC SQL OPEN C_2;
<BR/><BR/>
while(SQLCODE == 0)
{
   EXEC SQL FETCH C_2 INTO :strName;
   .
   .
   .

// 
// Using EXECUTE IMMEDIATE
// 
EXEC SQL EXECUTE IMMEDIATE :strTest;
.
.
.

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
go

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)
go

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 specific case allows you to process the results from the select statement.

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.

Additional query words: 4.22 esqlc 6.00

Keywords : ssvrprg
Version : 4.22
Platform : WINDOWS
Issue type : kbhowto