Development Team Blog
ESQL: Fetching from Batch Statements
by , 14-Oct-2010 at 08:25 AM (9332 Views)
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:
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.
- 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 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:Examples of using Batch Statements in Visual DataFlex:
- 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.
\\ Batch2
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.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
\\ From Batch3
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.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
References
- Visual 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
Go to Profile
Mark as Read