1. Forum
  2. Usenet
  3. COMP.OS.MS-WINDOWS.PROGRA

  • An sqlite3 database browsing problem ...

    From R.Wieser@21:1/5 to All on Mon Feb 15 22:44:52 2021
    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)
  • From JJ@21:1/5 to R.Wieser on Tue Feb 16 14:24:49 2021
    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)
  • From R.Wieser@21:1/5 to All on Tue Feb 16 10:07:17 2021
    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)
  • From R.Wieser@21:1/5 to All on Tue Feb 16 21:08:41 2021
    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)
  • From R.Wieser@21:1/5 to All on Wed Feb 17 07:54:33 2021
    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)