FW_DbfSqlQuery(...): Less known features of FWH-2

FW_DbfSqlQuery(...): Less known features of FWH-2

Postby nageswaragunupudi » Tue Jul 30, 2013 11:23 pm

While there is nothing we can not do with DBF tables using RDD, there are times when using SQL query simplifies coding to a great extent.

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


Image
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10208
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: Google [Bot] and 26 guests