Subject Re: sql where statement not working correctly
From Akshat Kapoor <akshat.kapoor@kapoorsons.in>
Date Sun, 15 Jul 2018 18:54:54 +0530
Newsgroups dbase.getting-started

On 14/07/2018 23:45, Charlie wrote:
> Hi Mervyn.. Thanks for the help.
 >
> I tried this:
 >
> sql = "select sum(qty) as qqty from SPDDATAARCH.DBF where namedate=:spname"
 >                 params['spdname'] = trim('spdname')
 >
> But I get an error parameter not defined: spdname
 >
> So not sure what is going on.  I'm curious, why didn't it work the way I had it?  I've never worked with parameters before so have no clue on this!!
 >
> I have spddataarch1.rowset filtered to namedate="spdname"
 >
> I tried the same thing with spddatarch2.rowset and it will not allow me to filter.
 >
> Thanks again for your help...
I know your problem has been solved but in this response I am just
trying to clear your doubts about the use of params (If any are still left).

More often then not we would like to filter our rowset based on certain
criteria. There are multiple ways, the three most used are.
1.Filter
2.canGetRow
3.Where clause in sql. We can easily type a query with where clause

sql = "select sum(qty) as qqty from SPDDATAARCH.DBF where namedate='ABC'"

but the problem arises when we want to change the value in the where
clause. The tough solution is to change the sql statement. The easier
solution is to use params.

When we use
sql = "select sum(qty) as qqty from SPDDATAARCH.DBF where namedate=:spname"
By using : we are telling the database engine that spname is a parameter.
When we use
params['spdname'] = trim('spdname')
we are assigning value to the parameter.

Value to params have to be assigned before the query is set active.

Changing the value afterwards does not cause the rowset already in
memory to change.

For changing the rowset in memory you have to issue a requery() command.

But this also makes our work as a programmer very easy
we can easily use
form.query.params['spdname'] = trim(form.entryfield.value)
form.query.requery()

And the rowset is updated.

I hope I have been able to explain the use to params correctly.

Regards
Akshat