But there is a catch. The code we need to write to open ADO connection, open recordset with the query and then closing recordset and connection itself is a lot of code and is more than what we save by using SQL. But FWH provides functions that make all this very simple.
For example, to ascertain the count of unique values in STATE field in CUSTOMER.DBF, we can use this SQL Query, "SELECT COUNT(*) FROM ( SELECT DISTINCT STATE FROM CUSTOMER )"
( Note: SELECT COUNT( DISTINCT STATE ) does not work in this implementation )
We can use this function in the following manner:
- Code: Select all Expand view
nStates := FW_DbfSqlQuery( "c:\fwh\samples\", "SELECT COUNT(*) FROM ( SELECT DISTINCT STATE FROM CUSTOMER )" )
? nStates
// for versions 13.06 and earlier use
// ? nStates[1,1]
Another example. A bit more advanced:
- Code: Select all Expand view
#include "fivewin.ch"
#include "xbrowse.ch"
#include "hbcompat.ch"
function Main()
local nAvgAge, aData, cSql
local cFolder := "c:\fwh\samples\"
TEXT INTO cSql
SELECT A.STATE AS CODE,S.NAME AS STATENAME,A.NUM,A.AVGAGE,A.SALARY FROM
( SELECT STATE, COUNT(*) AS NUM, AVG(AGE) AS AVGAGE, SUM(SALARY) AS SALARY
FROM CUSTOMER GROUP BY STATE ) A
LEFT OUTER JOIN STATES S ON A.STATE = S.CODE
ENDTEXT
aData := FW_DbfSqlQuery( cFolder, cSql )
nAvgAge := FW_DbfSqlQuery( cFolder, "SELECT AVG(AGE) FROM CUSTOMER" )
// nAvgAge := nAvgAge[1,1] // for FWH13.06 and earlier
XBROWSER aData TITLE "Statewise Employees" ;
SETUP ( oBrw:cHeaders := { "Code", "StateName", "NumEmp", "AvgAge", "TotalSalary" }, ;
oBrw:cEditPictures := { nil, nil, "999", "99.99", "99,999,999" }, ;
oBrw:lFooter := .t., ;
oBrw:NumEmp:nFooterType := oBrw:TotalSalary:nFooterType := AGGR_SUM, ;
oBrw:AvgAge:bFooter := { || nAvgAge }, ;
oBrw:Code:cFooter := cValToChar( Len( aData ) ), ;
oBrw:MakeTotals() )
return nil
