Multiple result sets in v12

Problem

A customer reported a problem with the following statement after upgrading to v12.

BACKUP DATABASE [framework] TO DISK = N'FRAMEWORK.BAK' WITH NOFORMAT, INIT, NAME = N'FRAMEWORK',

SKIP, NOREWIND, NOUNLOAD, STATS = 10

Use of the STATS option, used to provide progress reports for a running job, effectively makes this a multi-result set statement. The driver waits for a progress report from the server and the job hangs.   This was not a problem in v11 because the driver did not support multiple result sets. This statement fails in v12 because the driver now knows that a result set is waiting to be fetched.

Any ESQL command that returns multiple result sets (and does not iterate through them) may experience a problem after upgrading to v12.

Suggested Fixes for the Above Problem

Skip progress reporting

If progress reporting is not required, simply changing the STATS value solves the problem:

BACKUP DATABASE [framework] TO DISK = N'FRAMEWORK.BAK' WITH NOFORMAT, INIT, NAME = N'FRAMEWORK',

SKIP, NOREWIND, NOUNLOAD, STATS = 100

Or, you can change your code to add the SQL_CANCEL_QUERY_STMT:

SQL_SET_STMT to ("BACKUP DATABASE [" + sMainDataBase + "] TO DISK = N'FRAMEWORK.BAK' " + "

WITH NOFORMAT, INIT, NAME = N'FRAMEWORK', SKIP, NOREWIND, NOUNLOAD, STATS = 10")

SQL_PREPARE_STMT

SQL_EXECUTE_STMT

Repeat

  SQL_CANCEL_QUERY_STMT

  Sql_Next_Result_Set

Until (Not(Found))

Fetch the progress message from the server

Another suggestion is to fetch the progress report from the server and display your own messages as shown below:

Move 0 to nBkpDone

SQL_SET_STMT to ("BACKUP DATABASE [" + sMainDataBase + "] TO DISK = N' FRAMEWORK.BAK' " + "

WITH NOFORMAT, INIT, NAME = N'FRAMEWORK', SKIP, NOREWIND, NOUNLOAD, STATS = 10")

SQL_PREPARE_STMT

SQL_EXECUTE_STMT

Repeat

  SQL_NEXT_RESULT_SET <<< FETCH NEXT RESULT SET

  If (Found) Begin

    Move (nBkpDone + 10) to nBkpDone

    Showln ' Backup: ' (Trim(nBkpDone)) '% Done ...'

  End

Until (Not(Found))

SQL_CANCEL_QUERY_STMT

Example Retrieving Four Result Sets in one ESQL call

Sql_Set_Stmt    to 'SELECT name, city, state FROM customer; '

Sql_Append_Stmt to 'SELECT id FROM salesp; '
Sql_Append_Stmt to 'SELECT o.order_number,'
Sql_Append_Stmt to '       o.salesperson_id as "SalesManID",'
Sql_Append_Stmt to '       s.name as "Sales Name",'
Sql_Append_Stmt to '       o.customer_number as "CustID",'
Sql_Append_Stmt to '       c.name as "Customer Name",'
Sql_Append_Stmt to '       c.state,'
Sql_Append_Stmt to '       o.order_total as "Total",'
Sql_Append_Stmt to '       o.order_date as "Ordered In"'
Sql_Append_Stmt to ' FROM orderhea o'
Sql_Append_Stmt to '       left outer join salesp   s on (s.id              = o.salesperson_id) '
Sql_Append_Stmt to '       left outer join customer c on (c.customer_number = o.customer_number) '
Sql_Append_Stmt to " WHERE c.state like '%A%'"
Sql_Append_Stmt to ' ORDER BY o.order_total desc;'

Sql_Append_Stmt to "SELECT customer_number,name,comments FROM customel WHERE comments IS NOT NULL;"

Sql_Prepare_Stmt
Sql_Execute_Stmt

Move 1 to iRow
Move 1 to iRS 

Repeat
   Sql_fetch_next_row
   Sql_Get_Num_Cols TO iNumCols
   Increment iNumRset

   Sql_Get_Num_Rows TO iNumRows
   Showln 'This RS has ' iNumRows ' Rows' 

    Repeat
       For iColumn From 1 to iNumCols
           Sql_Fetch_Column         iColumn INTO           sColumnValue
           Sql_Get_Col_Attribute OF iColumn COLUMN_NAME TO sColumnName
           // Your Code Here
       Loop
       Sql_Fetch_Next_Row
   Until (Not(Found))

   Sql_Next_Result_Set
Until (Not(Found))