SQL Interface

CachéRDD provides yet another powerful feature to execute SQL Statements. CachéRDD's implementation of SQL interface returns true server-side forward-cursors. This is important because of the fact that there is no memory overhead whatsoever on the client. For simplicity let us call it Query Interface, though this feature is not restricted to generate query only. Almost all of the SQL can be submitted for executuion.

Query interface necessarily involves these steps:

1.  Defining a SQL statement.
2.  Building a parameters array, if any.
3.  Preparing the defined statement.
4.  Executing thus prepared statement.
5.  Retreiving results, if any.

If everything goes ok and query executes successfully, application receives a cursor handle to traverse and retrieve the results.

A typical SQL statement could look like this...

 
SELECT ip.item_id, es.loc, el.item_name, el.cost, el.price, el.ordered,
               el.inv_no, el.inv_date, el.cust_id, el.sub_acct, es.int_rate, es.no_of_pmt
FROM itemprom ip
LEFT OUTER JOIN estlines el ON ip.item_id=el.item_id
LEFT OUTER JOIN estimate es ON el.inv_no=es.inv_no '
WHERE el.inv_date >= ? AND el.cust_id > ? AND es.posted <> ?
ORDER BY el.inv_no

 
The character "?" represent the parameter which will be passed when executing the ststement. The parameters must be passed exactly in the same order appearing in the statement and number of paarmeters will be exactly the same. Otherwise query will return nothing. In example above there are 3 occurances of character "?" denoting that there must be three parameters to be passed with the execution method. Not only exact number and order is important but type of parameter must also be the same what query expects it to be.

CachéRDD exposes two approaches to exploit this powerful feature:

1. Functions based.
2. Class based.

I will focus on Class based approach as it provides a clean environment hiding the complexity of functions always expecting correct number of parameters. This can be best illustrated with an example as follows.

//-------------------------------------------------------------------//

Function TestComplexQuery()
Local nNumParam , aTypeParam, aTypeSqlParam
Local nNumCols, aTypeCols, aTypeSqlCols, aColNames
Local cSql := ''
Local aParams := {}
Local aResult := {}
Local hQry, oSql
/* The parameters to Process the SQL Statement */
Local cCustid := '500-0000'
Local dDate := ctod( '01/01/2006' )
Local cEmpty := ''
Local nConxn := CachéAddConnectionEx( "127.0.0.1", 1972, "_system", "SYS", 30, "USER" )

/*
Build the SQL Statement
*/
cSql += 'SELECT ip.item_id, es.loc, el.item_name, el.cost, el.price, '
cSql += 'el.ordered, el.inv_no, el.inv_date, el.cust_id, el.sub_acct, '
cSql += 'es.int_rate, es.no_of_pmt '
cSql += 'FROM itemprom ip LEFT OUTER JOIN estlines el ON ip.item_id=el.item_id '
cSql += 'LEFT OUTER JOIN estimate es ON el.inv_no=es.inv_no '
cSql += 'WHERE el.inv_date >= ? AND el.cust_id > ? AND es.posted <> ? '
cSql += 'ORDER BY el.inv_no'

aParams := { dDate, cCustID, cEmpty }

/*
Create an SQL Query Object
*/
oSql := CachéSqlQuery():New( nConxn, cSql )
 
/*
Prepare the query
*/
oSql:Create()
 
/*
Test if query is allocated
*/
if oSql:hQuery > 0
  /*
  Retrieve info about parameters to be passed to the Query
  Based on info below you can build an array of possible values to be passed as parameters
  */
  nNumParam := oSql:GetNumParam()
  aTypeParam := oSql:GetParTypes()
 
  /*
  Retrieve info about Columns returned by the Query
  Based on the above info you can populate tables, arrays, etc
  Possibilities are endless
  */
  nNumCols := oSql:GetNumParam()
  aTypeCols := oSql:GetColTypes()
  aColNames := oSql:GetColNames()
 
  /*
  Both methods of parameters passing are supported
  if oSql:Execute( aParams ) // Array - Unlimited number of parameters
  OR
  */
  if oSql:Execute( dDate, cCustId, cEmpty ) // Direct - Limited to 15 parameters

     /*
     It is time to retrieve the results
     */
     do while oSql:Skip()
        aadd( aResult, oSql:GetAllFields() )
     enddo

     /*
     There is another way to do the same
     This way you can retrieve only those fileds which interest you most
     But I do not see the logic to retrieve partial fields when we can
     optimize the query itself to include only needed fields.
     .
     do while oSql:Skip()
        aadd( aResult, { oSql:GetField( 1 ),oSql:GetField( 2 ),oSql:GetField( 3 ) } )
     enddo
     .
     OR
     .
     do while oSql:Skip()
        aadd( aResult, { oSql:GetField( aColNames[ 1 ] ),;
        oSql:GetField( aColNames[ 2 ] ),;
        oSql:GetField( aColNames[ 3 ] ) } )
     enddo
     .
     If you prefer to retrieve fields data by name.
     When retrieving data by name be careful to use column names off
     returned by the oSql:GetColNames() only as you never know about
     column names what SQL engine has assigned to them.
     */

     /*
     There is yet another way to retrieve records, viz.,

     Fetch fixed number of Records
     .
     aResult := oSql:FetchRecords( 10 )
     .
     OR For all remaining records
     .
     oSql:FetchRecords()

     The above two methods are provided for simplicity only.
     When these methods are called data rows are retrieved and stored in
     aRecords instance variable of the class and so later

     aResults := aclone( oSql:aRecords )
     */
 
  endif
endif

/*
Close the Query
Note that Query can still be EXECUTed with different parameters
DESTROYing a query will purge it permanently.
*/
oSql:Close()

/*
Must Destroy the Query to Free Resourses at the Server
Only if you need not to EXECUTE it again with different parameters
*/
oSql:Destroy()

Return aResult

//----------------------------------------------------------------------//