Sv: $select in table

TBS andyh at totallybrilliant.com
Mon Mar 20 15:53:53 UTC 2023


Worth noting that using bind variables removes this need !! (Just to be clear)

Andy Hilton
Totally Brilliant Software Inc
Phone (US) : (863) 409 4870
Phone (UK) : 0207 193 8582
Web : www.totallybrilliant.com
Helpdesk : https://totallybrilliantsoftware.supportsystem.com/
Email : andyh at totallybrilliant.com
On Mar 20, 2023, 11:35 AM -0400, Daniel Sananes <daniel.s at kopparbergs.se>, wrote:
> Thanks
> I put this in my repertoire as well.
>
> Daniel
>
> -----Ursprungligt meddelande-----
> Från: omnisdev-en <omnisdev-en-bounces at lists.omnis-dev.com> För Rudolf Bargholz
> Skickat: den 20 mars 2023 15:52
> Till: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com>
> Ämne: AW: $select in table
>
> Hi Daniel,
>
> If you are using LIKE with a character value, e.g.
>
> Sta: select * from TABLE where FIELD LIKE '[MyVar]%'
>
> just remember to replace special chars appropriately, e.g.
>
> Calculate MyVar as replaceall(MyVar,"'","''")
> Sta: select * from TABLE where FIELD LIKE '[MyVar]%'
>
> Replace all single quotes with two single quotes.
>
> If you do not do this, at some stage you search for <O'Brian> and your SQL will fail. Searching for <O''Brian> (two single quotes) will work fine.
>
> Regards
>
> Rudolf Bargholz
>
> -----Ursprüngliche Nachricht-----
> Von: omnisdev-en <omnisdev-en-bounces at lists.omnis-dev.com> Im Auftrag von Daniel Sananes
> Gesendet: Montag, 20. März 2023 12:17
> An: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com>
> Betreff: Sv: $select in table
>
> Hi Mike
>
> I am now using ILIKE.
> Works like a brilliant diamond.
>
> Daniel
>
> -----Ursprungligt meddelande-----
> Från: omnisdev-en <omnisdev-en-bounces at lists.omnis-dev.com> För Mike Rowan
> Skickat: den 19 mars 2023 01:57
> Till: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com>
> Ämne: Re: $select in table
>
> Hi Daniel
> Also it can sometimes be useful to use ILIKE rather than LIKE to make the match ignore the case.
>
> Mike
>
> On Sun, Mar 19, 2023 at 10:47 AM Scotte Meredith <spomacguy at gmail.com>
> wrote:
>
> > Look for “bind variables” in the docs to see how they work. A little
> > tough to explain simply but very powerful.
> >
> > Sent from my iPad
> >
> > > On Mar 18, 2023, at 3:14 PM, Daniel Sananes
> > > <daniel.s at kopparbergs.se>
> > wrote:
> > >
> > > Thanks Scotte
> > >
> > > This works: where client_name like '%Amaz%'.
> > > But the @ is confusing and does not work: where client_name like
> > '@%Amaz%'.
> > >
> > > Daniel
> > >
> > > -----Ursprungligt meddelande-----
> > > Från: omnisdev-en <omnisdev-en-bounces at lists.omnis-dev.com> För
> > > Scotte
> > Meredith
> > > Skickat: den 18 mars 2023 20:18
> > > Till: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com>
> > > Ämne: Re: $select in table
> > >
> > > Always start with how the full SQL would look.
> > > In your case:
> > > SELECT * FROM client WHERE client_name LIKE '%somethinghere%'
> > > The $select is the WHERE clause
> > > So put this first:
> > > Calculate clientname = con('%',clientname,'%')
> > > Then:
> > > where client_name like @[clientname]
> > >
> > > Using the @ sign makes it a bind variable so the SQL DAM will put in
> > > the
> > quotes or whatever is needed for the particular SQL field type. Also
> > can make it more efficient and other things, but that's another story.
> > > Also remember that the $select just sets the data set to use. Then
> > > you
> > need to $fetch to get 1 at a time, or all at once or 100 at once or
> > whatever you decide.
> > >
> > > Scotte Meredith
> > > spomacguy at gmail.com
> > > 509/998-0991
> > >
> > > > On Mar 18, 2023, at 11:52 AM, Daniel Sananes
> > > > <daniel.s at kopparbergs.se>
> > wrote:
> > > >
> > > > Hi all
> > > > I am having problems understanding the $select.
> > > > As I am using tables now I would like to override the table $select.
> > > > I am constructing an order-window now and want to let user enter a
> > > > part
> > of a clients name to find the client.
> > > > Begin text block
> > > > Text: where client_name LIKE [clientname] End text block Get text
> > > > block lQuery Do iclientrow.$select(lquery)
> > > >
> > > > The only thing that works is if I explicitly send "where client_id=1".
> > > >
> > > > Are there a kind soul out there who could give me an example how an
> > overridden $select would look?
> > > > As I don't know how the inbuilt $select looks I feel lost somehow.
> > > >
> > > > I guess there are joins and other useful stuff you could have in a
> > > > $select-method
> > > _____________________________________________________________
> > > Manage your list subscriptions at https://lists.omnis-dev.com Start
> > > a
> > new message -> mailto:omnisdev-en at lists.omnis-dev.com
> > > VARNING: Det här e-postmeddelandet kommer från en extern avsändare.
> > KLICKA INTE på länkar eller bilagor om du inte känner igen avsändaren
> > och vet att innehållet är säkert.
> > > _____________________________________________________________
> > > Manage your list subscriptions at https://lists.omnis-dev.com Start
> > > a new message -> mailto:omnisdev-en at lists.omnis-dev.com
> > _____________________________________________________________
> > Manage your list subscriptions at https://lists.omnis-dev.com Start a
> > new message -> mailto:omnisdev-en at lists.omnis-dev.com
> >
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
> VARNING: Det här e-postmeddelandet kommer från en extern avsändare. KLICKA INTE på länkar eller bilagor om du inte känner igen avsändaren och vet att innehållet är säkert.
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
> VARNING: Det här e-postmeddelandet kommer från en extern avsändare. KLICKA INTE på länkar eller bilagor om du inte känner igen avsändaren och vet att innehållet är säkert.
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com


More information about the omnisdev-en mailing list