FWH: MySql/MariaDB: RowSet object
- Marc Venken
- Posts: 727
- Joined: Tue Jun 14, 2016 7:51 am
Re: FWH: MySql/MariaDB: RowSet object
Very nice !
Thank you for the changes. Learning a lot with this. Have become a nice sample!
Thank you for the changes. Learning a lot with this. Have become a nice sample!
Marc Venken
Using: FWH 20.08 with Harbour
Using: FWH 20.08 with Harbour
- nageswaragunupudi
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: FWH: MySql/MariaDB: RowSet object
May we know what version of FWH are you using?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
Regards
G. N. Rao.
Hyderabad, India
G. N. Rao.
Hyderabad, India
Re: FWH: MySql/MariaDB: RowSet object
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,
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
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
- nageswaragunupudi
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: FWH: MySql/MariaDB: RowSet object
To Read all customers
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:
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:
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 := oCn:RowSet( "select * from customer" )
// or
oRs := oCn:RowSet( "customer" )
// or
oRs := oCn:Customer // fwh16.12
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
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
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
G. N. Rao.
Hyderabad, India
Re: FWH: MySql/MariaDB: RowSet object
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
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
http://www.xdata.cl - Desarrollo Inteligente
----------
Lenovo Legion Y520, 16GB Ram, 1 TB NVME M.2, 1 TB SSD, GTX 1050
Re: FWH: MySql/MariaDB: RowSet object
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
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 Informatica Ltda
- nageswaragunupudi
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: FWH: MySql/MariaDB: RowSet object
Multiple column sorting is provided in FWH 17.04.oRs:SetOrder( "age,date,id,..." )
// or
oRs:Sort := "age,date,id,..."
//
Regards
G. N. Rao.
Hyderabad, India
G. N. Rao.
Hyderabad, India
- nageswaragunupudi
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: FWH: MySql/MariaDB: RowSet object
Mr Adolfo
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?I have a large clients table, but in a xbrowse I just show 4 fields out of 20+,
Regards
G. N. Rao.
Hyderabad, India
G. N. Rao.
Hyderabad, India
Re: FWH: MySql/MariaDB: RowSet object
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
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
http://www.xdata.cl - Desarrollo Inteligente
----------
Lenovo Legion Y520, 16GB Ram, 1 TB NVME M.2, 1 TB SSD, GTX 1050
- nageswaragunupudi
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: FWH: MySql/MariaDB: RowSet object
My question is in the "SELECT" queryI only show 4 out of 20+ fields,
do you SELECT * FROM table
or
do you SELECT fld1,fld2,fld3,fld4 FROM table?
Regards
G. N. Rao.
Hyderabad, India
G. N. Rao.
Hyderabad, India
Re: FWH: MySql/MariaDB: RowSet object
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.
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
http://www.xdata.cl - Desarrollo Inteligente
----------
Lenovo Legion Y520, 16GB Ram, 1 TB NVME M.2, 1 TB SSD, GTX 1050
- nageswaragunupudi
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: FWH: MySql/MariaDB: RowSet object
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
Suggested with RowSet: Ensure that the modified record is the selected record in the browse and call
After inserting a new record.
With Dolphin
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().
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.
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()
Code: Select all
oRs:Resync()
oBrw:RefreshCurrent() // same as oBrw:DrawLine()
With Dolphin
Code: Select all
oDbCli:Refresh(.F.)
oBrw:DrawLine()
Code: Select all
oRs:ReQuery()
// position the record on the just appended record
oRs:Refresh()
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
G. N. Rao.
Hyderabad, India
- nageswaragunupudi
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: FWH: MySql/MariaDB: RowSet object
Mr Adolfo
Please also see the new sample posted about oBrw:EditBaseRecord()
You may consider if it can be useful to you.
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
G. N. Rao.
Hyderabad, India
Re: FWH: MySql/MariaDB: RowSet object
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 ????
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
- Posts: 8017
- Joined: Sun Nov 19, 2006 5:22 am
- Location: India
- Contact:
Re: FWH: MySql/MariaDB: RowSet object
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" } )
--------
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
G. N. Rao.
Hyderabad, India