FWH: MySql/MariaDB: RowSet object

[Marc Venken]

Very nice !

Thank you for the changes. Learning a lot with this. Have become a nice sample!

[nageswaragunupudi]

Mr Rao.

Is there something like oRowset:GetBlankRow() as in tDolphin, which I found quite easy to create a blank or default set of values for the current table to fiil with new values an insert it into the table.

Regards

From Chile
Adolfo

May we know what version of FWH are you using?

[nnicanor]

Hello,

Is there a method similar to dolphin SetWere to change where condition in query result ? i don't how Rowset and requery works.

Example in Tdolphin

oQry:= oServer:Query("Select * from customers") // All customers

oQry:SetWhere("sate='NY'") //Only NY State

oQry:SetWhere("") // All customers again reset where



Regards,

[nageswaragunupudi]

To Read all customers

Code: Select all

oRs := oCn:RowSet( "select * from customer" )
// or
oRs := oCn:RowSet( "customer" )
// or
oRs := oCn:Customer  // fwh16.12
 

Instead of oQry:SetWhere( "state='NY'" ):
Note: SetWhere() reads the data again from the server and this places burden on the server and network traffic.
You may use:

Code: Select all

oRs:SetFilter( "state='NY'" )
// or
oRs:SetFilter( "state='WA' .and. age > 30" )
? oRs:cFilter  // to know present filter
oRs:SetFilter( "" ) // Clear filter
 

Filter condition is similar to the usual DBF filter condtion.
SetFilter() does not read data again from the server. Works even when server is disconnected.

Changing Sort order:

Code: Select all

oRs:SetOrder( "age" )
// or
oRs:Sort := "age"
//
? oRs:Sort  // to know the present sort order
 

This sorts the data of the rowset in the client pc' memory. Data is not read again from the server.

Just like we can use parameters while reading rowset, we can also use paramters for setting filters.

Example:

Code: Select all

oRs:SetFilter( "state = ?", { "NY" } )  // same as "state = 'NY'"
// later change the filter to another state
oRs:ReFilter( { "WA" } ) // same effect as "state = 'WA'"
 

[Adolfo]

Nages.

I have a doubt, how can you do you the following in FWHMARIA.
I have a big xbrowse routine which captures key strokes, functions keys and a personalized popup menu that calls functions for appending, modifying, deleting, listing etc.
I have a large clients table, but in a xbrowse I just show 4 fields out of 20+, in tDolphin, I have an extra dialog, a big one with lot of validations items, after that I save modifications and do:

oDbCli:LoadQuery(.F.)
oBrw:DrawLine()

and only the current line in xBrowse is altered and updated.
When appending new records, I do the same, call another extra dialog, its different from the one used for modifying data, after saving I do:

oDbCli:Refresh(.F.)
oBrw:DrawLine()

and the new record is shown in the xbrowse in the place it is supposed to be (acording to key order)

You advice us to use oDbCli:bEdit, and oBrw:EditSource( .t. ), but I prefer a more (complex) controlled way because I have permissions on appending, modifying, which are different for every user.

How can I do the refreshing of the data shown in the xBrowsethe same way I do it with tDolphin.

Regards.
From Chile, Adolfo

[vinhesoft]

Mr.Rao


oRs:SetOrder( "age" )
// or
oRs:Sort := "age"
//
? oRs:Sort // to know the present sort order


Is it possible to sort by more than one column?

example :

oRs:SetOrder( "age,date,id,..." )
// or
oRs:Sort := "age,date,id,..."
//
? oRs:Sort // to know the present sort order

Att

Joao Carlos
VinheSoft

[nageswaragunupudi]

oRs:SetOrder( "age,date,id,..." )
// or
oRs:Sort := "age,date,id,..."
//

Multiple column sorting is provided in FWH 17.04.

[nageswaragunupudi]

Mr Adolfo

I have a large clients table, but in a xbrowse I just show 4 fields out of 20+,

Can you please clarify whether (a) you read all 20+ fields in the query but display only 4 fields in the xbrowse or (b) you read only 4 fields in the query, display these 4 fields in the xbrowse, but you want to edit all of some of 20+ fields of the table in your edit dialogs?

[Adolfo]

Hi Nages.

Yes, I only show 4 out of 20+ fields, the others are loaded in a personalized dialog when you want to see, edit, modify the record, mainly because there are a lot of validations in some fields and not every user has privileges to edit some of them.

I use this approach with almost all the tables in a big ERP, just the auxiliary tables like cities, Tipe of taxes, discount % etc have 2 or 3 fields which can be easily modified inside the xbrowse.

Regards, from Chile
Adolfo

[nageswaragunupudi]

I only show 4 out of 20+ fields,

My question is in the "SELECT" query
do you SELECT * FROM table
or
do you SELECT fld1,fld2,fld3,fld4 FROM table?

[Adolfo]

Select f1,f2,f3,f4 from clients

f1 anf f2 are Keys (unique both)

When I load the whole Record, I also load relations with other tables, history sales or payments records etc.

Thanks in advance.

[nageswaragunupudi]

Mr Adolfo

Both LoadQuery() and Refresh() of TDolphin are equivalent to oRs:Requery() of RowSet. These methods read and load the entire table again from the server and we like to avoid it as far as possible.

RowSet:ReSync() reads only one record from the server and updates the rowset. This is fast.

After modifying the record
With Dolphin

Code: Select all

oDbCli:LoadQuery(.F.)
oBrw:DrawLine()
 

Suggested with RowSet: Ensure that the modified record is the selected record in the browse and call

Code: Select all

oRs:Resync()
oBrw:RefreshCurrent() // same as oBrw:DrawLine()
 

After inserting a new record.
With Dolphin

Code: Select all

oDbCli:Refresh(.F.)
oBrw:DrawLine()
 

Till FWH17.03, we can not use Resync to read external appends. Only way is to use oRs:Requery() which is same functionally as oQry:Refresh().

Code: Select all

oRs:ReQuery()
// position the record on the just appended record
oRs:Refresh()
 

From FWH17.04:

The new method oRs:ReSyncWhere( cWhere ) is ideal for refreshing external modifications/appends.

Call
oRs:ReSyncWhere( "<primary/Uniquekey> = <value>" )
or
oRs:ReSyncWhere( "<primary/uniquekey>", uValue )
and oBrw:RefreshCurrent()

If the primary/unique key with the value already exists in the rowset, the record pointer is moved to the record and values of the record are re-read from the server.

if the primary/unique key with the value is not found in the rowset, the values of that row are read from the server and appended to the rowset in memory and record pointer is moved to the newly appended record.

[nageswaragunupudi]

Mr Adolfo

Please also see the new sample posted about oBrw:EditBaseRecord()
You may consider if it can be useful to you.

[luiz53]

HOW TO MAKE ?

IN TDOLPHIN
oQry := TDolphinQry():New("select * from country ", oServer )
oQry:setWhere("name like '%REPLUBLICA%'",.T.) // QUERY RESULT -> select * from country where name like '%REPLUBLICA%

to clean
oQry:setWhere("",.T.) // QUERY RESULT -> select * from country


IN TMARIADB ????

[nageswaragunupudi]

Method 1
--------
oRs := oCn:RowSet( "SELECT * FROM country WHERE NAME LIKE ?", { "%REPLUBLICA%" } )
Later
oRs:Requery( { "%OTHERNAME%" } )

Method 2
--------

oRs := oCn:RowSet( "SELECT * FROM country ?", { "" } )
Later
oRs:ReQuery( { "WHERE name like '%REPLUBICA%' } )
Later
oRs:ReQuery( { "" } )
Later
oRs:ReQuery( { "WHERE age > 40" } )