ESQL: Fetching from Batch Statements
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:
delete weekly ;
delete monthly ;
update company set cbal_amt = 0 ;
update contact set county = ’’
select * into #support from customer;
select * from #support
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:
- SQL Server receives a network packet from the client containing the Transact-SQL statement or batch of Transact-SQL statements to be executed.
- SQL Server compiles and executes the statement or batch.
- 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.
- 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:
- fetch only from statements in the batch that would generate a result set;
- use SQLNextResultSet to traverse through the result sets generated by the batch statements.
Examples of using Batch Statements in DataFlex:
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.
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
- DataFlex Help
- MSDN - http://msdn.microsoft.com/en-us/libr...(v=VS.85).aspx
- SQL Server 2005 Books Online - http://technet.microsoft.com/en-us/l...4(SQL.90).aspx