SQL Interface

  Previous topic Next topic  

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( "", 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
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
  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() )

     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 ) } )
     do while oSql:Skip()
        aadd( aResult, { oSql:GetField( aColNames[ 1 ] ),;
        oSql:GetField( aColNames[ 2 ] ),;
        oSql:GetField( aColNames[ 3 ] ) } )
     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

     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 )

Close the Query
Note that Query can still be EXECUTed with different parameters
DESTROYing a query will purge it permanently.

Must Destroy the Query to Free Resourses at the Server
Only if you need not to EXECUTE it again with different parameters

Return aResult