Is this wrong?

There is also a technical note regarding table classes and bind vars: www.omnis.net/technotes/tnsq0001.jsp <www.omnis.net/technotes/tnsq0001.jsp>

Best,

Andreas

> Am 01.03.2018 um 15:25 schrieb Doug Easterbrook <doug@artsman.com>:
>
> not sure if I agree with the bind variable notion. We certainly use it in the table classes for insert and update
>
> but we never use it for where clauses (ever). it limits the power of what you can do with sub-selects, corelated subqueries, in clauses, etc where you want to use the full power of sql.
>
>
> as a result of a much earlier effort to try to be backend agnostic, we built features like
>
> sqlChar(text)
> sqlBOOL(BOOLEAN)
> sqlLike(text)
> sqlTimestamp(timestamp)
> sqlBETWEEN(textString1,textString2)
> sqOverlap(date1,date2)
> sqlBLOB()
>
> etc.
>
> That concept deals with things like O’Reilly since the sqlChar() adds the escape characters that are needed depending on the back end.
>
> in other words, the where clause does
>
> calc theName as “O’Reilly”
> calc where as con(‘surname =‘,sqlChar(theName))
>
> or
> calc where as con(‘surname ilike ‘,sqlLike(theName))
>
>
> not only does that centralize the particular functions for a database, but lets lets you add the requisite additional chars for line searches.
>
>
> we also use the ‘text block’ capability for readability.
>
> eg
>
> begin text block
> text: where xx_name ilike [sqlLike(var1)]
> text: and xx_yy_seq in (select yy_seq from othertable where something = [var2])
> text: and xx_date [sqlbetween(date1,date2)]
> text: and someSqlFunction([var1],[var2],[var3],[var4]) is [sqlBoolean(ktrue)]
> end text block
> get text as whereClause
>
>
>
> now, I know each to their own…. however the mantra of must use bind variables all the time doesn’t apply when doing a number of more complex things, simply be cause bind variables were never designed for the full power of sql.
>
> eg… its near impossible to use bind variables
> – to do a multi line insert
> – or an upsert to the database
> – or if trying to stuff something into a background worker that might require multiple statements to be run
> – or when using functions and stored procedures at times
> – or when trying to optimize sql calls into multi record updates to avoid being chatty over long distances.
> – etc
>
>
> just an opinion. I’ve found bind variables limiting more often than not.
>
>
>
>
> Doug Easterbrook
> Arts Management Systems Ltd.
> mailto:doug@artsman.com
> www.artsman.com
> Phone (403) 650-1978
>
>
>
>
> see you at the third annual users conference
> tickets.proctors.org/TheatreManager/95/online?performance=29086 <tickets.proctors.org/TheatreManager/95/online?performance=29086>
>
>> On Mar 1, 2018, at 2:40 AM, Graham Stevens <graham.stevens@gmail.com> wrote:
>>
>> Hi Das,
>>
>> I know this isn’t what you were asking and Kelly dealt with that.
>>
>> But let me just throw in my 2¢ here. It is wrong because you are not using a bind variable for your data. As a DBA, my constant mantra to developers is “Always use bind variables”. Your application may be working now but there will come a time when something will go wrong.
>>
>> As an example, if you are searching for clients with a statement like “where surname = [iLineItemsList.line_surname]”, what happens when the client’s name is O’Reilly?
>>
>> And depending on your application’s audience, you may need to worry about SQL injection. There are plenty of references online to explain this one.
>>
>> But on a more positive note, using bind variables allows you to reuse a prepared SQL statement. So when running the same query, such as your example, if you are looking for multiple products one after another, you would prepare your statement once and then execute it as often as needed, just changing the value each time. This will speed up execution as you don’t need to make 2 round trips to the DB server for each execution. This may not be an issue for you but in a high transaction system it can make a difference.
>> So, for instance, within your Products table class instantiated as a row variable you can do something like this:
>>
>> Begin statement
>> Sta: {select [$cinst.$selectnames()]}
>> Sta: {from [$cinst.$servertablenames]}
>> Sta: {where prod_id = @[lvProdId]} ;; note the @ symbol before the [
>> End statement
>>
>> Do $cinst.$statementobject.$prepare() Returns lbOK
>> If not(lbOK)
>> ; handle the $prepare error
>> Quit method
>> End If
>>
>> For iLineItemsList.$line from 1 to iLineItemsList.$linecount step 1
>> Calculate lvProdId as iLineItemsList.line_prod_id ;; populate your bind variable
>> Do $cinst.$statementobject.$execute() Returns lbOK
>>
>> If lbOK
>> Do $cinst.$statementobject.$fetch($cinst)
>> ; do whatever you need to with the product record
>> Else
>> ; handle the $execute error
>> Break to end of loop
>> End If
>> End For
>>
>> As I said, just my 2¢ worth. But you will find it easier to put bind variables in now rather than having to modify your code later.
>>
>> Best regards,
>> Graham
>>
>> On 01/03/2018 08:47, Das Goravani wrote:
>>> Do iProdRow.$select(“WHERE prod_id='[iLineItemsList.line_prod_id]'”) Returns #F
>>>
>>> Is there something wrong with my usage of row/column inside square brackets as shown?
>>>
>>> If I calculate that value into #1 and stick that there, then it works
>>>
>>> With a reduced variable, just [#1] it works, but with the row and column, nope.
>>>
>>> I did an awful lot of programming with row and column I hope I don’t have to change it all
>>>
>>
>> _____________________________________________________________
>> Manage your list subscriptions at lists.omnis-dev.com
>> Start a new message -> mailto:omnisdev-en@lists.omnis-dev.com
>
> _____________________________________________________________
> Manage your list subscriptions at lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en@lists.omnis-dev.com

_____________________________________________________________
Manage your list subscriptions at lists.omnis-dev.com
Start a new message -> mailto:omnisdev-en@lists.omnis-dev.com