Re: How do I browse a database ?

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
==================