SQL SELECT Statement problem

[Bob Sneidar]

I will have to go back and check. I seem to remember having a problem with sqLite using just '%:1%' and reading that the solution for sqLite was LIKE ':1%' or LIKE '%:1%' or LIKE '%:1' or LIKE ':1' whereas I did not have the problem with mySQL. 

Bob S


> On Sep 15, 2015, at 09:31 , Peter Haworth <pete at lcsql.com> wrote:
> 
> I found that LIKE :1 where the :1 variable contains %whatever% with no
> quotes around it works, at least ion sqlite.
> 
> I'm not sure I understand your comment about this not finding rows that
> begin with, end with, or contain the string in sqlite.  It's always worked
> for me.
> 
> 
> On Tue, Sep 15, 2015 at 9:15 AM Bob Sneidar <bobsneidar at iotecdigital.com>
> wrote:
> 
>> Not sure if it matters, but I use LIKE '%:1%'. Also remember that with
>> sqLite, and unlike mySQL, this form will not find records beginning with,
>> ending with or explicitly is whatever replaces :1. Why the implementations
>> of the different SQL engines behave differently escapes me, other than to
>> think that the people who develop these engines don't have a lot of respect
>> for standards and why we need them.
>> 
>> Bob S
>> 
>> 
>>> On Sep 2, 2015, at 12:52 , Peter Haworth <pete at lcsql.com> wrote:
>>> 
>>> Having an issue with the following statement in SQLite.
>>> 
>>> SELECT col1,col2 FROM TableA WHERE colid IN (:1)
>>> 
>>> This is executed with
>>> 
>>> put revQueryDatabase(gdbid,tsql,"tArray") into tCursor
>>> 
>>> If tArray[1] contains a single integer, the SELECT works, if tArray[1]
>>> contains a comma separated list of integers, no records are returned,
>> even
>>> though I know there are qualifying entries.
>>> 
>>> If I replace ":1" in the SELECT with 1,2 the rows are correctly returned,
>>> but if tArray[1] contains 1,2 then no rows are returned.
>>> 
>>> On another related issue, I remember a discussion about the correct
>> syntax
>>> for using a parameter list variable with a LIKE statement but can't find
>>> it.  I have tried:
>>> 
>>> LIKE ':1' with :1 containing %abc%
>>> LIKE '%:1%' with :1 containing abc
>>> LIKE :1 with :1 containing '%abc%'
>>> 
>>> None of these return the correct data.  Anyone remember how to get this
>> to
>>> work?
>>> _______________________________________________
>>> use-livecode mailing list
>>> use-livecode at lists.runrev.com
>>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>> 
>> 
>> _______________________________________________
>> use-livecode mailing list
>> use-livecode at lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>> 
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode