Can you search SQL this way?

Fri Oct 22 16:21:34 UTC 2021

agreed with andy.

you CANNOT search omnis lists that are in a database .  thats binary stuff.

you can search omnis lists if you decompose them into strings in some way.    They two ways that we've done:

using TMobjs.$listToString or kelly’s StringJockey tools.   

meaning before you store the list in the database, you turn it into a string.    Only then can you search it .. because you can do STRING searches eg ‘like'

the other way is to decompose the omnis list into JSON and then use JSON search tools (these are great in postgres ….).  in SQL lite there is the JSON1 extension that looks to provide similar features
https://www.sqlite.org/json1.html <https://www.sqlite.org/json1.html>

that give you the searching of lists.    you now have all the character searching tools 
- like
- =
- between
- substring,
etc

and if you use JSON… then the additional json tools.

To retrieve the data out of the record, you use TMObjs.$stringtoList or kelly’s stringjockey external equivalent, or convert it from JSON back to a list.

OR.. (alternative implementation)..

MAKE A SECOND COLUMN….    and each time you store the list in the database as binary, decompose to a string — and save the string in a second column.

that way, your old code will always work and you only change the part of the code that saves records to populate the second column.

My omnis life goes way way back.   and since omnis 5, we have never stored lists in the database - we have always converted them to string equivalents because the omnis data file was not always friendly to the binary format of lists.   in other words, they could easily become corrupted in the native DF1 at that time.

since then, our general premise has been to store data only in natively supported data formats.   eg,  if you want a time stamp with time zone, then if the database supports that data type, use it — if not, don’t, or convert the data to a format that the database understands.

hence,  convert lists to string or json to save it.

just my thoughts.

recap:
- you’ll need to convert the list to string or json format, perhaps in a second column
- then you can use string or json searches on that ‘duplicated’ column of data

Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978

> On October 22, 2021, at 8:19 AM, Andy Hilton <andyh at totallybrilliant.com> wrote:
> 
> You can’t do a sql query to achieve that, in short…..
> 
> As you had mentioned, you would either have to return your list into Omnis and use traditional code to go through the list data line by line,
> 
> Or do a wholly new sql search as I had mentioned for whatever generated that list to start with (if you know that !) with added additional sql search parameters….
> 
> Andy Hilton
> Totally Brilliant Software Inc
> Phone (US) : (863) 409 4870 
> Phone (UK) : 0207 193 8582
> Web : www.totallybrilliant.com <http://www.totallybrilliant.com/> 
> Helpdesk : http://totallybrilliant.kayako.com
> Email : andyh at totallybrilliant.com
> 
>> On Oct 22, 2021, at 10:45 AM, Das Goravani <goravanis at gmail.com> wrote:
>> 
>> 
>> I still don’t get it then.
>> 
>> I have Omnis lists stored in SQLite and would like to query them the way we do in Omnis somehow.
>> 
>> For example, does row 3 column 2 contain 4835 or does row 5 column 10 equal “Saturn”
>> 
>> 
>> 
>>> On Oct 22, 2021, at 10:42 AM, Andy Hilton <andyh at totallybrilliant.com <mailto:andyh at totallybrilliant.com>> wrote:
>>> 
>>> Das
>>> 
>>> I think what Phil is meaning is :
>>> 
>>> You would simply do a new sql search for whatever got you your list to start with, plus the extra addition of a like (note that depending on the database, ‘ilike’ can also be a case insensitive search) for your column search within that…..
>>> 
>>> Andy Hilton
>>> Totally Brilliant Software Inc
>>> Phone (US) : (863) 409 4870 
>>> Phone (UK) : 0207 193 8582
>>> Web : www.totallybrilliant.com <http://www.totallybrilliant.com/> <http://www.totallybrilliant.com/ <http://www.totallybrilliant.com/>> 
>>> Helpdesk : http://totallybrilliant.kayako.com
>>> Email : andyh at totallybrilliant.com
>>> 
>>>> On Oct 22, 2021, at 10:36 AM, Phil (OmnisList) via omnisdev-en <omnisdev-en at lists.omnis-dev.com> wrote:
>>>> 
>>>> Hi Das,
>>>> 
>>>> the string needs quotes...
>>>> field like '%string%'
>>>> 
>>>> A list?
>>>> That is not a SQL object.
>>>> What is that stored as? is it binary? so basically no chance of looking up on that.
>>>> 
>>>> regards
>>>> Phil Potter
>>>> Based in Chester in the UK.
>>>> 
>>>> On 22/10/2021 15:18, Das Goravani wrote:
>>>>> Hello Listers,
>>>>> 
>>>>> I’m wondering about two SQL searches or selects.
>>>>> 
>>>>> One is if you have a large field full of text, and you want to search for a string inside it. Do you use LIKE %sring% ?
>>>>> 
>>>>> And if you have a list, and you want to select on a value in a column, can you do that?
>>>>> 
>>>>> That’s it.
>>>>> 
>>>>> Thanks
>>>>> 
>>>>> Das Goravani
>>>>> _____________________________________________________________
>>>>> Manage your list subscriptions athttp://lists.omnis-dev.com
>>>>> Start a new message ->mailto:omnisdev-en at lists.omnis-dev.com  
>>>> _____________________________________________________________
>>>> Manage your list subscriptions at http://lists.omnis-dev.com
>>>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 
>>> 
>>> _____________________________________________________________
>>> Manage your list subscriptions at http://lists.omnis-dev.com <http://lists.omnis-dev.com/>
>>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com <mailto:omnisdev-en at lists.omnis-dev.com> 
>> 
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com <http://lists.omnis-dev.com/>
>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com <mailto:omnisdev-en at lists.omnis-dev.com>
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 