FWH: MySql/MariaDB: RowSet object

Re: FWH: MySql/MariaDB: RowSet object

Postby Marc Venken » Sat Dec 17, 2016 6:41 pm

Very nice !

Thank you for the changes. Learning a lot with this. Have become a nice sample!
Marc Venken
Using: FWH 23.04 with Harbour
User avatar
Marc Venken
 
Posts: 1338
Joined: Tue Jun 14, 2016 7:51 am
Location: Belgium

Re: FWH: MySql/MariaDB: RowSet object

Postby nageswaragunupudi » Sun Dec 18, 2016 2:51 am

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

Re: FWH: MySql/MariaDB: RowSet object

Postby nnicanor » Fri Dec 23, 2016 5:03 am

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
nnicanor
 
Posts: 295
Joined: Fri Apr 23, 2010 4:30 am
Location: Colombia

Re: FWH: MySql/MariaDB: RowSet object

Postby nageswaragunupudi » Fri Dec 23, 2016 8:19 am

To Read all customers
Code: Select all  Expand view

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  Expand view

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  Expand view

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  Expand view

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
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWH: MySql/MariaDB: RowSet object

Postby Adolfo » Mon Apr 24, 2017 2:28 pm

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
----------
Asus TUF F15, 32GB Ram, 1 TB NVME M.2, 1 TB SSD, GTX 1650
User avatar
Adolfo
 
Posts: 846
Joined: Tue Oct 11, 2005 11:57 am
Location: Chile

Re: FWH: MySql/MariaDB: RowSet object

Postby vinhesoft » Mon Apr 24, 2017 8:01 pm

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
vinhesoft
 
Posts: 23
Joined: Sat Sep 03, 2016 3:11 pm
Location: Campinas/SP-Brasil

Re: FWH: MySql/MariaDB: RowSet object

Postby nageswaragunupudi » Tue Apr 25, 2017 2:59 pm

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: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWH: MySql/MariaDB: RowSet object

Postby nageswaragunupudi » Wed Apr 26, 2017 7:21 am

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
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWH: MySql/MariaDB: RowSet object

Postby Adolfo » Wed Apr 26, 2017 11:58 am

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
----------
Asus TUF F15, 32GB Ram, 1 TB NVME M.2, 1 TB SSD, GTX 1650
User avatar
Adolfo
 
Posts: 846
Joined: Tue Oct 11, 2005 11:57 am
Location: Chile

Re: FWH: MySql/MariaDB: RowSet object

Postby nageswaragunupudi » Wed Apr 26, 2017 12:01 pm

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
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWH: MySql/MariaDB: RowSet object

Postby Adolfo » Wed Apr 26, 2017 12:37 pm

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
----------
Asus TUF F15, 32GB Ram, 1 TB NVME M.2, 1 TB SSD, GTX 1650
User avatar
Adolfo
 
Posts: 846
Joined: Tue Oct 11, 2005 11:57 am
Location: Chile

Re: FWH: MySql/MariaDB: RowSet object

Postby nageswaragunupudi » Wed Apr 26, 2017 4:57 pm

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  Expand view

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  Expand view

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


After inserting a new record.
With Dolphin
Code: Select all  Expand view

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  Expand view

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: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: FWH: MySql/MariaDB: RowSet object

Postby nageswaragunupudi » Thu Apr 27, 2017 1:35 pm

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

Re: FWH: MySql/MariaDB: RowSet object

Postby luiz53 » Fri May 12, 2017 2:54 pm

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 ????
luiz53
 
Posts: 43
Joined: Fri Jun 01, 2007 12:41 pm

Re: FWH: MySql/MariaDB: RowSet object

Postby nageswaragunupudi » Fri May 12, 2017 3:24 pm

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

PreviousNext

Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: No registered users and 11 guests