SQLFetch - cSQLStatement

Fetch the next row of the statement's result set.

Type: Function
Return Data Type: Integer

Syntax

Function SQLFetch Returns Integer

Call Example

Get SQLFetch to IntegerVariable

Description

The SQLFetch function is used to retrieve the next row from the statement result set (if any). The SQLFetch function returns zero if there are no more rows, a non-zero value otherwise. The Connectivity Kit will store the column values of the retrieved row in its column buffers. These values can be retrieved individually by using the SQLColumnValue message.

Sample

Handle hoSQLManager hTableConnect hStatement
Integer iFetchResult

Object oSQLHandler is a cSQLHandleManager
    Move Self to hoSQLManager
End_Object

Open Customer
Get SQLFileConnect of hoSQLManager (RefTable(Customer)) to hTableConnect
Get SQLOpen of hTableConnect to hStatement
Send SQLExecDirect of hStatement "select * from customer"
Repeat
    Get SQLFetch of hStatement to iFetchResult
    If (iFetchResult <> 0) Send ProcessRow hStatement
Until (iFetchResult = 0)

Send SQLClose of hStatement
Send SQLDisconnect of hTableConnect

SQLFetch will only retrieve data from one result set at a time.

Col 1	Col 2
Note:	When using batch statements, i.e. SQL statements built from two or more individual SQL statements, use SQLNextResultSet to traverse between result sets.

Procedure RunQuery
    Handle hoSQLManager hConn1 hStatement1
    Integer iResult1      
    String sCol1 sCol2 sCol3      

    Object oSQLHandler is a cSQLHandleManager
        Move Self to hoSQLManager
    End_Object  

    Open Customer  

    Get SQLFileConnect of hoSQLManager Customer.File_number to hConn1
    Get SQLOpen of hConn1 to hStatement1
    If (hStatement1 <> 0) Begin
        Send SQLExecDirect of hStatement1 "select * from #support where customer_number > 20; select * from #support"              
       // fetch rows from the first result set
        Repeat    
            Get SQLFetch of hStatement1 to iResult1
            If (iResult1 <> 0) Begin
                Get SQLColumnValue of hStatement1 1 to sCol1
                Get SQLColumnValue of hStatement1 2 to sCol2
                Get SQLColumnValue of hStatement1 3 to sCol3
                Showln sCol1 ",  " sCol2 ",   " sCol3
                Showln
            End
        Until (iResult1 = 0)  

        // change to second result set
        Get SQLNextResultSet of hStatement1 to iResult1
        If (iResult1 <> 0) Begin
            // fetch rows from second result set
            Repeat    
                Get SQLFetch of hStatement1 to iResult1
                If (iResult1 <> 0) Begin
                    Get SQLColumnValue of hStatement1 1 to sCol1
                    Get SQLColumnValue of hStatement1 2 to sCol2
                    Get SQLColumnValue of hStatement1 3 to sCol3
                    Showln sCol1 ",  " sCol2 ",   " sCol3
                    Showln
                End
            Until (iResult1 = 0)                
        End
        Send SQLCLose of hStatement1

        Send SQLDisconnect of hConn1
    End
End_Procedure

Return Value

Returns the next row of the statement's result set.