HOW TO: Process Results in an Embedded SQL Application (151599)

SUMMARY

When you process results from an Embedded SQL (ESQL) application, you must 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 by using the EXECUTE IMMEDIATE statement or when you use a CURSOR.

back to the top

Example

Here is an example stored procedure that is named spTest:

Create procedure spTest
as
   select au_lname from authors
   select au_fname from authors
go

In a DB-Library application, the spTest stored procedure is treated and retrieved as two result sets:

• au_lname -and-
  
  
• au_fname

The DB-Library application calls the dbresults function multiple times in conjunction with the dbcmdrow function 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 do 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; however, the ESQL application will only see the first result set.

The following code sample contains examples of how to execute 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;
.
.
.

back to the top

SQLCODE Settings

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

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

If you add a RAISERROR statement to the stored procedure, it may make it even more difficult. For example:

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 statement, the SQLCODE is set to -50001. In the EXECUTE IMMEDIATE statement, the SQLCODE is set to -50001. The RAISERROR statement is considered to be the result set, and you cannot access the result rows returned from the SELECT statement.

Reversing the stored procedure changes the behavior. For example:

Create procedure spTest
as
   select au_lname from authors
   RAISERROR(50001, 1, 1)
go

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

In all these examples, you can change the behavior by not returning results. This means that if you take the third variation of the stored procedure and you add a where clause, such as

where au_lname = '12'

the computer no longer returns result rows; it returns 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 try to ignore the SQLCODE, you get unpredictable results. If you run a RAISERROR statement, and then run a SELECT statement, you can modify the cursor code. For example:

While((SQLCODE == 0) || (SQLCODE == -50001))
{
   EXEC SQL FETCH C_2 INTO :strName;
   .
   .
   .

This specific case permits you to process the results from the SELECT statement.

Microsoft does not recommend or support this method of ESQL result set processing. You must always guarantee that only one result set can be returned from a statement.

back to the top