View RSS Feed

Development Team Blog

ESQL: Fetching from Batch Statements

Rating: 2 votes, 5.00 average.
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 Visual 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.
We can see that one request might generate a lot of network traffic, so it is often more efficient to use batch statements than submit statements separately because network traffic can often be reduced that way and the database server may be able to optimize execution of the statements part of a batch.

Using Batch Statements in Visual DataFlex
Batch statements can be executed as any other ESQL statement in Visual 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 Visual DataFlex:
\\ Batch2
Code:
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
Code:
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

Comments

  1. A Paul Anthony's Avatar
    I see you're using SQLFileConnect in order to get a connection handle. If people really want to use embedded SQL seriously then you should probably use SQLConnect instead - you can read the login details from a file if you want. The key difference is that rather than having a second instance of that connection, you're actually using the SAME connection as VDF itself to talk to the table, complete with the session options required to make the CK work properly (ARITHABORT for example). Your embedded SQL might not want those option values, so rather than change them and potentially messing up the CK (the interaction between ESQL and the CK has caused us all sorts of problems, too many to go into now), it is much simpler and safer to create a connection specifically for your own purposes and leave the one opened by the CK well alone.
  2. Marco's Avatar
    Hi Marcia

    Please note that 'Batch statements' are called compound statements in DB2. The same thing though. Multiple statements in one UOW (Unit of work), with none, one or multiple result sets.

    Cheers,
    Marco