Using SQL Connectivity

Eddy Kleinjans presentation at EDUC2008

Below is some sample code that fills up a 2-column grid with data which is retrieved from a SQL Server backend using a SQL Statement that is dynamically created. Note that it uses the existing connection from an already opened table called Customer. This way you don't have to make the connection with the SQL Server yourself and it is more maintenance friendly in case the tables get move over to a different server or database.

Procedure ShowTurnover
    Handle hoSQLMngr
    Handle hdbc hstmt
    String sStatement 
    Integer iFetchResult iYear iTurnover iState
    
    // Get rid of direct repainting the grid while filling it up with data
    Get Dynamic_Update_State to iState
    Set Dynamic_Update_State to False
    
    // Delete the current data from the grid
    Send Delete_Data

    // Use the connection from the open file Customer (assuming this is
    // located on the database you want to connect to.
    Get SQLFileConnect Of oSQLHandler Customer.File_number To hdbc
    
    // Create a new statement on that connection
    Get SQLOpen Of hdbc To hstmt
    
    // Compose the SQL statement
    Move "SELECT YEAR(OrderDate), SUM(Quantity*Price) " to sStatement
    Add  " FROM ORDERDTL " to sStatement
    Add  " LEFT OUTER JOIN ORDERHDR ON ORDERHDR.OrderId = ORDERDTL.OrderId " to sStatement
    Add  " WHERE ORDERHDR.CustomerId = " to sStatement
    Add (String(customer.CustomerId)) to sStatement
    Add  " GROUP BY YEAR(OrderDate) " to sStatement
    Add  " ORDER BY YEAR(OrderDate) DESC" to sStatement
    
    // Execute the SQL Statement
    Send SQLExecDirect Of hstmt sStatement

    // Fetch the results
    Repeat
        // Get the next row
        Get SQLFetch Of hstmt To iFetchResult
        If (iFetchResult <> 0) Begin

            // Get the value from the columns we need
            Get SQLColumnValue Of hstmt 1 To iYear
            Get SQLColumnValue Of hstmt 2 To iTurnover

            // Put the column values in the grid
            Send Add_Item Msg_None iYear
            Send Add_Item Msg_None iTurnover

        End

    Until (iFetchResult = 0)

    // Let the grid repaint itself
    Set Dynamic_Update_State to iState
    
    // Release the allocated handles
    Send SQLClose Of hstmt
    Send SQLDisconnect Of hdbc

End_Procedure