Hello all,

I'm trying to browse a random sqlite3 database and would like to sort on an also random collumn. I've got two problems:

1) the text collumns contents may be longer than I care to handle. Meaning that I want to do a string compare only upto a certain number of characters.

2) the selected "select on" column may be numeric.

Some googeling gave me SUBSTRING. Alas, that doesn't go well if the target column is numeric (lots of entries get skipped).

Some more googeling gave me LIKE. But for some reason I think that adding a
"%" to the end of a value (in string form) will also turn the value into an actual string, causing the same problem.

So, the question: How do I do a WHERE and SORT BY which will work with
limited length provided strings as well as values. Both retrieved as
strings and provided parameterized.

Example of a query :

SELECT * FROM %s
WHERE (SUBSTR(%s,1,63), %s) >= (?1, ?9)
ORDER BY %s , %s LIMIT %d;

And yes, that is a "sprintf" string :-)

"?1" is the "sort on" collumn, which may be string or nmeric, and "?9" the first, key collumn - as a fallback if the "sort on" column contains (more
than) a screenfull of the same data.

Regards,
Rudy Wieser

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Mon, 15 Feb 2021 22:44:52 +0100, R.Wieser wrote:

Hello all,

I'm trying to browse a random sqlite3 database and would like to sort on an also random collumn. I've got two problems:

1) the text collumns contents may be longer than I care to handle. Meaning that I want to do a string compare only upto a certain number of characters.

2) the selected "select on" column may be numeric.

Some googeling gave me SUBSTRING. Alas, that doesn't go well if the target column is numeric (lots of entries get skipped).

Some more googeling gave me LIKE. But for some reason I think that adding a "%" to the end of a value (in string form) will also turn the value into an actual string, causing the same problem.

So, the question: How do I do a WHERE and SORT BY which will work with limited length provided strings as well as values. Both retrieved as
strings and provided parameterized.

Example of a query :

SELECT * FROM %s
WHERE (SUBSTR(%s,1,63), %s) >= (?1, ?9)
ORDER BY %s , %s LIMIT %d;

And yes, that is a "sprintf" string :-)

"?1" is the "sort on" collumn, which may be string or nmeric, and "?9" the first, key collumn - as a fallback if the "sort on" column contains (more than) a screenfull of the same data.

Regards,
Rudy Wieser

If the column value is numeric, why not just use math calculation?

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

JJ,

If the column value is numeric, why not just use math calculation?

Thats the whole problem : *I don't know* what the (random, user-selectable) "sort on" column will contain. If I would know for a fact that that column contains just numbers I would (not need to) bother with the
string-comparision length at all ...

IOW: Fine-tuning the WHERE for numeric use will (probably?) clash with a columns string contents, and fine-tuning it for string comparision will (as experienced) clash with the columns numeric contents. :-|

One possibile solution would be if I could tell the DB that the comparision should be done in accordance with the columns type specification, instead of letting the WHERE clause dictate it (like the use of SUBSTRING forcing a
string comparision). The problem is that I simply do not know enough about sqlite3 to even be aware of possible roads to investigate.

Regards,
Rudy Wieser

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

I /think/ I've solved my previous problem by modifying the query depending
on the type of the targetted column (in the DB) (wrapping a SUBSTRING around requests towards non-numeric colums - not shown in the below), but now I've
got another problem :

When a SELECTed column contains many of the same entries (in my case: empty string fields) those rows are, when doing a "give me the next batch"
request, skipped.

I /thought/ had solved that one by appending the ID column to each query,
but as it appears it doesn't seem to.

The used query (to get the next screen-full of results) is this one :

SELECT * FROM moz_places WHERE (title, id) >= (?1, ?9) ORDER BY title, id
LIMIT 34;

"?1" and "?9" contain the last "title" and "id" colum results. In the case
of "?1" that is an empty string.

My question is : what am I missing here ? Why are those entries skipped
and what can I do against it ?

Regards,
Rudy Wieser

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

When a SELECTed column contains many of the same entries
(in my case: empty string fields) those rows are, when doing a "give me
the next batch" request, skipped.

I was a bit too quick - the above described skipping behaviour seems to
*only* happen on empty (string?) fields (I've not yet found empty numeric fields). The moment there is /anything/ in there, even just a single
dot, all works well again.

Did I just stumble over a bug ?? If not, what is happening / the reason
for it ?

Regards,
Rudy Wieser

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)