FWH: MySql/MariaDB: RowSet object

User avatar
Marc Venken
Posts: 727
Joined: Tue Jun 14, 2016 7:51 am

Re: FWH: MySql/MariaDB: RowSet object

Post by Marc Venken »

Very nice !

Thank you for the changes. Learning a lot with this. Have become a nice sample!
Marc Venken
Using: FWH 20.08 with Harbour
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: FWH: MySql/MariaDB: RowSet object

Post by nageswaragunupudi »

Adolfo wrote: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?
Regards

G. N. Rao.
Hyderabad, India
nnicanor
Posts: 296
Joined: Fri Apr 23, 2010 4:30 am
Location: Colombia

Re: FWH: MySql/MariaDB: RowSet object

Post by 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,
Nicanor Martinez M.
Auditoria y Sistemas Ltda.
MicroExpress Ltda.
FW + FWH + XHARBOUR + HARBOUR + PELLES C + XDEVSTUDIO + XEDIT + BCC + VC_X86 + VCC_X64 + MINGW + R&R Reports + FastReport + Tdolphin + ADO + MYSQL + MARIADB + ORACLE
nnicanor@yahoo.com
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: FWH: MySql/MariaDB: RowSet object

Post by 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'"
 
Regards

G. N. Rao.
Hyderabad, India
User avatar
Adolfo
Posts: 815
Joined: Tue Oct 11, 2005 11:57 am
Location: Chile
Contact:

Re: FWH: MySql/MariaDB: RowSet object

Post by 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
;-) Ji,ji,ji... buena la cosa... "all you need is code"

http://www.xdata.cl - Desarrollo Inteligente
----------
Lenovo Legion Y520, 16GB Ram, 1 TB NVME M.2, 1 TB SSD, GTX 1050
vinhesoft
Posts: 20
Joined: Sat Sep 03, 2016 3:11 pm
Location: Campinas/SP-Brasil
Contact:

Re: FWH: MySql/MariaDB: RowSet object

Post by 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
João Carlos
VinheSoft Informatica Ltda
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: FWH: MySql/MariaDB: RowSet object

Post by nageswaragunupudi »

oRs:SetOrder( "age,date,id,..." )
// or
oRs:Sort := "age,date,id,..."
//
Multiple column sorting is provided in FWH 17.04.
Regards

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

Re: FWH: MySql/MariaDB: RowSet object

Post by 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?
Regards

G. N. Rao.
Hyderabad, India
User avatar
Adolfo
Posts: 815
Joined: Tue Oct 11, 2005 11:57 am
Location: Chile
Contact:

Re: FWH: MySql/MariaDB: RowSet object

Post by 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
;-) Ji,ji,ji... buena la cosa... "all you need is code"

http://www.xdata.cl - Desarrollo Inteligente
----------
Lenovo Legion Y520, 16GB Ram, 1 TB NVME M.2, 1 TB SSD, GTX 1050
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: FWH: MySql/MariaDB: RowSet object

Post by 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?
Regards

G. N. Rao.
Hyderabad, India
User avatar
Adolfo
Posts: 815
Joined: Tue Oct 11, 2005 11:57 am
Location: Chile
Contact:

Re: FWH: MySql/MariaDB: RowSet object

Post by 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.
;-) Ji,ji,ji... buena la cosa... "all you need is code"

http://www.xdata.cl - Desarrollo Inteligente
----------
Lenovo Legion Y520, 16GB Ram, 1 TB NVME M.2, 1 TB SSD, GTX 1050
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: FWH: MySql/MariaDB: RowSet object

Post by 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.
Regards

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

Re: FWH: MySql/MariaDB: RowSet object

Post by nageswaragunupudi »

Mr Adolfo

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

G. N. Rao.
Hyderabad, India
luiz53
Posts: 43
Joined: Fri Jun 01, 2007 12:41 pm
Contact:

Re: FWH: MySql/MariaDB: RowSet object

Post by 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 ????
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: FWH: MySql/MariaDB: RowSet object

Post by 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" } )
Regards

G. N. Rao.
Hyderabad, India
Post Reply