Re: How do I browse a database ?

On 8/12/2019 4:08 AM, R.Wieser wrote:
> Jerry,
>

>> You'll have to look at the table itself.  No table *requires* a primary
>> key, but it's always a good idea to have one.

>>> Than again, I just ran into this:

>> Sorry I can't help you here.

[Quoted] Yes, LOWER() is a string function (converts the string to lower case) so I guess it would return a string if used on a numeric field. But I've never seen a use for that.

[Quoted] >> You may be thinking of the ORDER BY clause, which can use comma separated
>> column names.

That's an extension to SQL then. I guess they just use the same parser as for other expressions in the SQL statement.

>> And there is no default sort order in SQL.  If you don't specify ORDER BY
>> the sort order is undetermined.

As I said, if it does it is an extension to the SQL standard.

>> That is the definition of an expression, not sort criteria.

>> On 8/11/2019 3:04 PM, R.Wieser wrote:
>>> Jerry,
>>>
>>>> You don't use a temporary database.  Just use the existing database.  I
>>>> hope you have a primary key on the table.
>>>
>>> :-)  Thats part of the problem: my program is ment to be a generic
>>> viewer,
>>> which should be able to handle /any/ sqlite database.    And currently
>>> I'm
>>> not even sure if a sqlite database must actually have a primary key.
>>>
>>
>> You'll have to look at the table itself.  No table *requires* a primary
>> key, but it's always a good idea to have one.
>>
>> But I also wouldn't worry too much about using a lot of resources - SQLite
>> is good for quick data but not very good at handling large databases.
>> Other databases are much more capable.
>>
>>
>>>> You simply add the primary key as the last sort parameter and search for
>>>> the next row based on the previous one.
>>>
>>> Yep, that should work.
>>>
>>> Than again, I just ran into this:
>>>
>>> SELECT * FROM foo ORDER BY LOWER(id) LIMIT 7;
>>>
>>> "id" is the primary key column, an 8 byte integer.
>>>
>>> Resulting "id"s: 1, 10, 100, 1000, 100000, 10001, 10002
>>>
>>> I'm rather sure that all the other key values between 1 and 100002 are in
>>> the table too.
>>>
>>> And when doing this:
>>>
>>> SELECT * FROM foo WHERE id<='100032' ORDER BY LOWER(id) DESC LIMIT 7;
>>> (with or without the quotes around 100032)
>>>
>>> it thinks that the first number is 99999 (not even 100032 itself!) ...
>>>
>>> Yep, a string comparision where there should be an integer one.  :-(
>>>
>>> At this moment I really could use a sqlite specific newgroup ...
>>>
>>
>> Sorry I can't help you here.  As I said - it's been a LONG time since I've
>> used SQLite.  Well over a decade at the least.
>>
>>>
>>>> Now the next SELECT gets a little more complicated, but:
>>>
>>>>      WHERE (col1>val1) OR (col1= val1 AND col2>VAL2) OR
>>>
>>> Whut ?  I thought the WHERE comparisions could be seperated by commas,
>>> and
>>> that the DB software would be smart enough to do the sorting that way by
>>> default.
>>>
>>
>> So, if the WHERE clause has multiple conditions they must always use
>> boolean operators between the conditions.  You may be thinking of the
>> ORDER BY clause, which can use comma separated column names.
>>
>> And there is no default sort order in SQL.  If you don't specify ORDER BY
>> the sort order is undetermined.
>>
>>> Looking .... Yup, it looks that I'm right about the expression:
>>> https://www.sqlite.org/syntaxdiagrams.html#expr , seventh branch,
>>> starting
>>> with "(".
>>>
>>
>> That is the definition of an expression, not sort criteria.
>>
>>> The only thing I have to find out is if the DB software is "smart"
>>> enough.
>>>
>>>
>>> ... but first the non-numeric sorting of a numeric column.
>>>
>>> Regards,
>>> Rudy Wieser
>>>
>>>
>>
>>
>>
>> -- 
>> ==================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> jstucklex_at_attglobal.net
>> ==================

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================