filter or where statement in SQL

[Horizon]

Hi Mr. Rao,

I want to clarify my mind.

We can create rowset without any sql statement and next set filter.

Code: Select all

oRs:=oCn:RowSet('bigdata')
oRs:SetFilter('datano = 555')

In this situation, load all table to client.
To change condition, oRs:SetFilter('datano=666') again.

or

with sql statement.

Code: Select all

oRs:=oCn:RowSet('select * from bigdata where datano = ?', {555})

In this situation, load just my desired part of table to client.
To change condition, oRs:ReQuery({666})

My goal is change condition just two times. Which one is suitable for me. Which one is faster?

Thanks.

[nageswaragunupudi]

Both work.
Let us now see the pros and cons of the two approaches.

1st method:

- Reads the entire table in the beginning itself. If it is a big table and we do not need all the rows lot of time, server and network resources are wasted
- Once read, we can change filters any number of times and there is no strain on server or network resources.

2nd method.
- Only required records are retrieved from the server.
- But each time we change the filter/where condition, we need to retrieve the new data again. Everytime this places strain on server and network resources.

So, depending on the size of the table and how large part of the table we will be needing we need to choose one of these methods.
It is a case to case decision.
Normally it is a good idea to reduce strain on server and network resources.

For example:
Sorting at the client is better than sorting on the server.

[Horizon]

Hi Mr. Rao,

Code: Select all

oRs := oCn:RowSet( "select * from `customer` where `state` = ? and 'age' between ? and ?", { cState, nMinAge, nMaxAge } )

There is a data "Source" in FWMARIAROWSET. We can see sql statement like "select * from `customer` where `state` = ? and 'age' between ? and ?"

Is there any data to see current Query variables? (not names) like {"NY",51,60}

Edit : Is there any method to inspect FWMARIAROWSET as an object with xbrowser?

[nageswaragunupudi]

Yes

Code: Select all

oRs:aParams  // undocumented.
 

We request not to use any undocumented data and rely on it in your application program. We suggest you store the current values of these parameters in your application program and also do not change oRs:aParams yourself.

These classes are complex and there are many methods and data for internal use. We need to keep them for ourselves to retain enough flexibility for future developments.

We, therefore, request you to use only the datas/methods documented in WiKi and subsequent WhatsNew.Txt.

[Horizon]

Yes

Code: Select all

oRs:aParams  // undocumented.
 

We request not to use any undocumented data and rely on it in your application program. We suggest you store the current values of these parameters in your application program and also do not change oRs:aParams yourself.

These classes are complex and there are many methods and data for internal use. We need to keep them for ourselves to retain enough flexibility for future developments.

We, therefore, request you to use only the datas/methods documented in WiKi and subsequent WhatsNew.Txt.

Thank you Mr. Rao.

I want to ask something if it is possible.

We have a oRs object.

We create oBrw xbrowse using as a datasource oRs.

Is oRs and oBrw:oDbf objects are same object? or when you create xbrowse clone oRs to oDbf?

After creating xbrowse, if I change to Query of oRs, will oBrw:oDbf change?

[Horizon]

Hi,

I think oBrw:oDbf is clone and another object.

Another question. When I set to new params with ReQuery(), browse design is gone. Which methods should run after requery?

[nageswaragunupudi]

oBrw:oDbf is the same as (Rowset object). It is not a clone.
When building xbrowse, oBrw:oDbf is set to oRs by using

Code: Select all

oBrw:oDbf := oRs
 

After oRs:Requery( aNewParams ), present records are replaced by new records, but fields and columns are not changed. Therefore no changes in the browse are required. But you need to reposition the browse to the top.

Browse design is not gone.

Code: Select all

oRs:Requery( aNewParams )
oBrw:GoTop()
oBrw:Refresh()