RowSet works more like DBF and RecordSet and differences from oQuery of TMySql and Dolphin are discussed here.
This documentation includes improvements made after release of 16.05.
Creating RowSet:
Options
1) oRs := oCn:RowSet( cTable(or)cSql, [lShowError] ) // simple and normal
2) oRs := oCn:RowSet( cTable(or)cSql, NumRows, [lShowError] )
3) oRs := oCn:RowSet( cSql, aParams, [lShowEror] ) //where oCn is the connection object.
Alias name oCn:Query(...) also is availble.
4) oRs := oCn:Call( cStoredProc, params, ... )
5) oRs:Clone() --> Clone of a RowSet
In case of failure, the return value is nil. After calling this method it is necessary to the check the success before using the rowset object. Example:
Code: Select all
oRs := oCn:RowSet( params )
if oRs == nil
oCn:ShowError()
return nil
endif
// proceed with using the rowset
(Future plans: To spool the edits and insertions when connection is lost and to upload the spooled edits after connection is restored.)
Option (1):
oRs := oCn:RowSet( cTable(or)cSql, [lShowError] )
This is similar to other libs. The parameter can also be a table name without prefixing "select * from". If the last optional parameter is .T., error message is displayed in case of failure.
Option (2):
oRs := oCn:RowSet( cSql/table, NumRows, [lShowError] )
This method works only for either (a) simple table name or (b) if the sql statements ends with "ORDER BY <x>". This method internally uses LIMIT/OFFSET clauses to retrieve the first NumRows records.
Later we can use
oRs:ReadNext( n ) --> lMoreRecsRemaining
to read next n records or
oRs:ReadNext() to read all remaining records.
DATA oRs:lMore indicates if there are some more records to be read. When this data is false it indicates that all records are read.
Note: It is expected that the users are well acquinted with the issues, side-effects and possible inaccuracies while using LIMIT clause. Desirable to be thorough with MySql documentation. By far using Limit/Offset is safer when ordered by primary key.
Option (3):
oCn:RowSet( cSql, aParams, [lShowError] )
cSql should be a parameterised statement
aParams is array of values to be used in the place of parameters.
Example:
Code: Select all
cState := "NY"
nMinAge := 41
nMaxAge := 50
oRs := oCn:RowSet( "select * from `customer` where `state` = ? and 'age' between ? and ?", { cState, nMinAge, nMaxAge } )
Later we can use :
oRs:Requery( aNewParams )
In the above example we can use
Code: Select all
cState := "WA"
nMinAge := 51
nMaxAge := 60
oRs:Requery( { cState, nMinAge, nMaxAge } )
Parameters:
Place-holders for the parameters can be specified in any manner like:
? ? ? : like in sql
&1, &2, ... : compatible with other libs
Or named paramters:
:param1, :param2, ... : standard sql
¶m1, ¶m2, .. : compatible with other libs
In the case of named paratmers the array needs to be a multi-dim { { value1, value2, ..} ... }
Option - 4:
oRs := oCn:Call( cStoredProc, params, ... )
A RowSet can also be created by calling a Stored Procedure returning a result set. In case the stored procedure is called with parameters, subsequenlty oRs:Requery( params ) can be called with same parameters with different values.
Though in such cases, the real SQL statement executed by the stored procedure is not visible, the rowset object makesw its best efforts to identify the base table and prepares appropriately to Requery, Refresh and Resync.
Option-5:
oRs2 := oRs:Clone()
This works similar to the ADO method Clone().
All the Clones work on the same data but can be sorted / filtered independantly.
All changes in one RowSet are automatically seen in all the Clones.
The internal linkage of the Clones does not work / broken in the following cases.
a) Deletions and Inserts are not seen by other clones. (differs from ADO)
b) ReQuery() of a Clone totally de-links it from other clones. (like ADO)