Using SQL Connectivity
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