Can't find what you are looking for? Try these pages!

Blog

ESQL: Fetching from Batch Statements

October 14,2010
By Marcia Booth

When using the database Connectivity Kits, developers may choose to use embedded SQL (ESQL) in their applications to take advantage of the database server capabilities and set processing. In ESQL you can execute many different statements either one by one or in batches. So, how can batch statements be used in DataFlex?

What are Batch Statements
A batch of SQL statements is a group of two or more SQL statements or a single SQL statement that has the same effect as a group of two or more SQL statements.

Examples of batch statements:


-- Batch1
    delete weekly ;
    delete monthly ;
    update company set cbal_amt = 0 ;
    update contact set county = ’’
-- Batch2
    select * into #support from customer;
    select * from #support
-- Batch3
    select * from #support where customer_number > 20;
    select * from #support where balance > 0

Results from Batch Statements
In some implementations, the entire batch statement is executed before any results are available.

A result is something returned by the data source after a statement is executed. ODBC has two types of results: result sets and row counts. Row counts are the number of rows affected by an update, delete, or insert statement. Result Sets are rows of data returned as a result from the SQL statement that was executed. Batches can generate multiple results.

Why use Batch Statements
The way that statements are processed at the server and how resut sets are sent back to the clients may vary, but here is an example on how it is done in a Microsoft SQL Server 2005 server:

  1. SQL Server receives a network packet from the client containing the Transact-SQL statement or batch of Transact-SQL statements to be executed.
  2. SQL Server compiles and executes the statement or batch.
  3. SQL Server begins putting the rows of the result set, or multiple result sets from a batch or stored procedure, in network packets and sending them to the client. SQL Server puts as many result set rows as possible in each packet.
  4. The packets containing the result set rows are cached in the network buffers of the client. As the client application fetches the rows, the ODBC driver or the OLE DB provider pulls the rows from the network buffers and transfers the data to the client application. The client retrieves the results one row at a time in a forward direction.

Using Batch Statements in DataFlex
Batch statements can be executed as any other ESQL statement in DataFlex -- using SQLExecDirect or SQLPrepare/SQLExecute, but you need to be careful when trying to fetch results from those because only result sets can be fetched.

If the batch statements you execute do not generate a result set, you will get an error when trying to fetch from them, in other words, you may execute any statement, but you may only fetch from the ones that generate result sets.

So, in order to fetch without errors, you will need to:

  1. fetch only from statements in the batch that would generate a result set;
  2. use SQLNextResultSet to traverse through the result sets generated by the batch statements.

Examples of using Batch Statements in DataFlex:


// Batch2
Procedure RunQuery
    Handle hoSQLMngr
    Handle hConn1
    Handle hStmt1
    Integer iResult1
    String sCol1 sCol2 sCol3
    Object oSQLHandler is a cSQLHandleManager
        Move Self to hoSQLMngr
     End_Object
     Open Customer
     Get SQLFileConnect of hoSQLMngr Customer.File_number to hConn1
     Get SQLOpen of hConn1 to hStmt1
     If (hStmt1 <> 0) Begin
         Send SQLExecDirect of hStmt1 "select * into #support from customer;select * from #support "
         Get SQLNextResultSet of hStmt1 to iResult1
         If (iResult1 <> 0) Begin
             Repeat
                 Get SQLFetch of hStmt1 to iResult1
                 If (iResult1 <> 0) Begin
                     Get SQLColumnValue of hStmt1 1 to sCol1
                     Get SQLColumnValue of hStmt1 2 to sCol2
                     Get SQLColumnValue of hStmt1 3 to sCol3
                     Showln sCol1 ", " sCol2 ", " sCol3
                     Showln
                 End
             Until (iResult1 = 0)
         End
         Send SQLCLose of hStmt1
         Send SQLDisconnect of hConn1
     End
End_Procedure

Note that if you do not use SQLNextResultSet before SQLFetch in this example, you will get an error because the first statement (select * into #support from customer) does not generate a result set.


// From Batch3
Procedure RunQuery
    Handle hoSQLMngr
    Handle hConn1
    Handle hStmt1
    Integer iResult1
    String sCol1 sCol2 sCol3
    Object oSQLHandler is a cSQLHandleManager
        Move Self to hoSQLMngr
    End_Object
    Open Customer
    Get SQLFileConnect of hoSQLMngr Customer.File_number to hConn1
    Get SQLOpen of hConn1 to hStmt1
    If (hStmt1 <> 0) Begin
    Send SQLExecDirect of hStmt1 "select * from #support where customer_number > 20; select * from #support where balance > 0"
    Repeat
        Get SQLFetch of hStmt1 to iResult1
        If (iResult1 <> 0) Begin
            Get SQLColumnValue of hStmt1 1 to sCol1
            Get SQLColumnValue of hStmt1 2 to sCol2
            Get SQLColumnValue of hStmt1 3 to sCol3
            Showln sCol1 ", " sCol2 ", " sCol3
            Showln
        End
    Until (iResult1 = 0)
    Get SQLNextResultSet of hStmt1 to iResult1
        If (iResult1 <> 0) Begin
            Repeat
                Get SQLFetch of hStmt1 to iResult1
                If (iResult1 <> 0) Begin
                    Get SQLColumnValue of hStmt1 1 to sCol1
                    Get SQLColumnValue of hStmt1 2 to sCol2
                    Get SQLColumnValue of hStmt1 3 to sCol3
                    Showln sCol1 ", " sCol2 ", " sCol3
                    Showln
                End
            Until (iResult1 = 0)
        End
        Send SQLCLose of hStmt1
        Send SQLDisconnect of hConn1
    End
End_Procedure

In the above example you should be able to fetch from both batch statements and use SQLNextResultSet to move from the first result set to the next.

References