Recent

Author Topic: [SOLVED] SQLite query not providing data  (Read 1146 times)

Slyde

  • Full Member
  • ***
  • Posts: 152
[SOLVED] SQLite query not providing data
« on: February 02, 2024, 08:56:45 pm »
OS: Linux Mint 21.3
Lazarus 3.0
SQLite3: 3.37.2
----------------------
I have a query that works fine in DB Browser and fails in the Pascal environment.

Table:
Code: Pascal  [Select][+][-]
  1. CREATE TABLE "MC_Skipped" (
  2.         "nid"   INTEGER,
  3.         "qid"   INTEGER,
  4.         "skip_date"     TEXT
  5. );

I have one record inside it:
Code: Pascal  [Select][+][-]
  1. nid     qid     skip_date
  2. 103     23      2024-01-30 07:03:01

The pertinent code using String:
Code: Pascal  [Select][+][-]
  1. var
  2.   elapsedTime: String;  
  3.  
  4. with ZQ1 do
  5.       begin
  6.         Close;
  7.         SQL.Text := 'SELECT (strftime(''%s'', ''now'', ''localtime'') - ' +
  8.           'strftime(''%s'', ''skip_date'')) / 3600 AS elapsed FROM MC_Skipped ' +
  9.           'WHERE nid = :nid'; { gives the number of hours between the two dates. this query works fine in DB Browser }
  10.         Params.ParamByName('nid').AsString := nameID;
  11.         Open; {returns nothing }
  12.         elapsedTime := ZQ1.FieldByName('elapsed').AsString;
  13.         ShowMessage(elapsedTime); {shows nothing }
  14.       end;
I get no data return and no error.

Using integer:
Code: Pascal  [Select][+][-]
  1. begin
  2.       with ZQ1 do
  3.       begin
  4.         Close;
  5.         SQL.Text := 'SELECT (strftime(''%s'', ''now'', ''localtime'') - ' +
  6.           'strftime(''%s'', ''skip_date'')) / 3600 AS elapsed FROM MC_Skipped ' +
  7.           'WHERE nid = :nid';
  8.         Params.ParamByName('nid').AsString := nameID;
  9.         Open;
  10.         elapsedTime := ZQ1.FieldByName('elapsed').AsInteger;
  11.         ShowMessage(FloatToStr(elapsedTime));
  12.       end;
I get an error: 
Code: Pascal  [Select][+][-]
  1. Invalid type conversion to Integer in field elapsed

I've also tried it like this:
Code: Pascal  [Select][+][-]
  1. var
  2.   elapsedTime: Real;  
  3.  
  4.     with ZQ1 do
  5.       begin
  6.         Close;
  7.         SQL.Text := 'SELECT (strftime(''%s'', ''now'', ''localtime'') - ' +
  8.           'strftime(''%s'', ''skip_date'')) / 3600 AS elapsed FROM MC_Skipped ' +
  9.           'WHERE nid = :nid';
  10.         Params.ParamByName('nid').AsString := nameID;
  11.         Open;
  12.         elapsedTime := ZQ1.FieldByName('elapsed').AsFloat;
  13.         ShowMessage(FloatToStr(elapsedTime));
  14.         if elapsedTime < 24.0 then
  15.           ShowMessage('It hasn''t been 24 hours yet')
  16.         else
  17.           ShowMessage('It''s been more than 24 hours');
  18.       end;
I get the same error: 
Code: Pascal  [Select][+][-]
  1. Invalid type conversion to Integer in field elapsed

So it appears to me that the first query is correct.  And again, the query works fine in DB Browser.  At the time of writing this, it returns 78.  But I can't get it to work inside the Lazarus IDE.  Whatever the issue is, I can't use it.

Can anyone help me on this?
« Last Edit: February 02, 2024, 11:02:24 pm by Slyde »
Linux Mint 21.3
Lazarus 3.0

rvk

  • Hero Member
  • *****
  • Posts: 5978
Re: SQLite query not providing data
« Reply #1 on: February 02, 2024, 09:22:04 pm »
A few options.

Are you sure nameID is correct?
Do a showmessage(nameID).

Try to make nameID an integer and use asInteger instead of asstring for the param.

Show the exact sql via
Showmessage(SQL.Text)
With all this ' there could be some problems.
(I'm guessing the SQL is sightly different from what you execute in a db manager)

Do a showmessage(ZQ1.Recordcount.tostring) after the open to see if there are any records returned.

dsiders

  • Hero Member
  • *****
  • Posts: 1017
Re: SQLite query not providing data
« Reply #2 on: February 02, 2024, 09:52:08 pm »
OS: Linux Mint 21.3
Lazarus 3.0
SQLite3: 3.37.2
----------------------
I have a query that works fine in DB Browser and fails in the Pascal environment.

Table:
Code: Pascal  [Select][+][-]
  1. CREATE TABLE "MC_Skipped" (
  2.         "nid"   INTEGER,
  3.         "qid"   INTEGER,
  4.         "skip_date"     TEXT
  5. );

I have one record inside it:
Code: Pascal  [Select][+][-]
  1. nid     qid     skip_date
  2. 103     23      2024-01-30 07:03:01

The pertinent code using String:
Code: Pascal  [Select][+][-]
  1. var
  2.   elapsedTime: String;  
  3.  
  4. with ZQ1 do
  5.       begin
  6.         Close;
  7.         SQL.Text := 'SELECT (strftime(''%s'', ''now'', ''localtime'') - ' +
  8.           'strftime(''%s'', ''skip_date'')) / 3600 AS elapsed FROM MC_Skipped ' +
  9.           'WHERE nid = :nid'; { gives the number of hours between the two dates. this query works fine in DB Browser }
  10.         Params.ParamByName('nid').AsString := nameID;
  11.         Open; {returns nothing }
  12.         elapsedTime := ZQ1.FieldByName('elapsed').AsString;
  13.         ShowMessage(elapsedTime); {shows nothing }
  14.       end;
I get no data return and no error.

Using integer:
Code: Pascal  [Select][+][-]
  1. begin
  2.       with ZQ1 do
  3.       begin
  4.         Close;
  5.         SQL.Text := 'SELECT (strftime(''%s'', ''now'', ''localtime'') - ' +
  6.           'strftime(''%s'', ''skip_date'')) / 3600 AS elapsed FROM MC_Skipped ' +
  7.           'WHERE nid = :nid';
  8.         Params.ParamByName('nid').AsString := nameID;
  9.         Open;
  10.         elapsedTime := ZQ1.FieldByName('elapsed').AsInteger;
  11.         ShowMessage(FloatToStr(elapsedTime));
  12.       end;
I get an error: 
Code: Pascal  [Select][+][-]
  1. Invalid type conversion to Integer in field elapsed

I've also tried it like this:
Code: Pascal  [Select][+][-]
  1. var
  2.   elapsedTime: Real;  
  3.  
  4.     with ZQ1 do
  5.       begin
  6.         Close;
  7.         SQL.Text := 'SELECT (strftime(''%s'', ''now'', ''localtime'') - ' +
  8.           'strftime(''%s'', ''skip_date'')) / 3600 AS elapsed FROM MC_Skipped ' +
  9.           'WHERE nid = :nid';
  10.         Params.ParamByName('nid').AsString := nameID;
  11.         Open;
  12.         elapsedTime := ZQ1.FieldByName('elapsed').AsFloat;
  13.         ShowMessage(FloatToStr(elapsedTime));
  14.         if elapsedTime < 24.0 then
  15.           ShowMessage('It hasn''t been 24 hours yet')
  16.         else
  17.           ShowMessage('It''s been more than 24 hours');
  18.       end;
I get the same error: 
Code: Pascal  [Select][+][-]
  1. Invalid type conversion to Integer in field elapsed

So it appears to me that the first query is correct.  And again, the query works fine in DB Browser.  At the time of writing this, it returns 78.  But I can't get it to work inside the Lazarus IDE.  Whatever the issue is, I can't use it.

Can anyone help me on this?

Check your SQL statement.

Code: MySQL  [Select][+][-]
  1. SELECT (strftime('%s', 'now', 'localtime') - strftime('%s', 'skip_date')) / 3600 AS elapsed FROM MC_Skipped;

Returns nothing.

Code: MySQL  [Select][+][-]
  1. SELECT (strftime('%s', 'now', 'localtime') - strftime('%s', skip_date)) / 3600 AS elapsed FROM MC_Skipped;

Works as expected.

Preview Lazarus 3.99 documentation at: https://dsiders.gitlab.io/lazdocsnext

rvk

  • Hero Member
  • *****
  • Posts: 5978
Re: SQLite query not providing data
« Reply #3 on: February 02, 2024, 10:10:34 pm »
Check your SQL statement.
Good spot  8-)

I knew it had something to do with all those quotes.
Made me dizzy just looking at them  %)


Slyde

  • Full Member
  • ***
  • Posts: 152
Re: SQLite query not providing data
« Reply #4 on: February 02, 2024, 10:29:27 pm »
Are you sure nameID is correct?
It is

Quote from: rvk
Do a showmessage(nameID).
It works

Quote from: rvk
Try to make nameID an integer and use asInteger instead of asstring for the param.
It's used throughout my application (3020 lines) as a String.  It works in all other queries, too.

Quote from: rvk
Show the exact sql via
Showmessage(SQL.Text)
With all this ' there could be some problems.

This is what I get from Showmessage(SQL.Text).
Code: Pascal  [Select][+][-]
  1. SELECT (strftime('%s', 'now', 'localtime') - strftime('%s', 'skip_date')) / 3600 AS elapsed FROM MC_Skipped WHERE nid = :nid
(I'm guessing the SQL is sightly different from what you execute in a db manager)[/quote]

In DB Browser, I use the same:
Code: Pascal  [Select][+][-]
  1. 'SELECT (strftime("%s", "now", "localtime") - strftime("%s", "skip_date")) / 3600 FROM MC_Skipped WHERE nid = 103;'

Do a showmessage(ZQ1.Recordcount.tostring) after the open to see if there are any records returned.
[/quote]
I get '1', as it should be.

I use many queries in this app.  All of them work as expected.  But this is the first time I've ever worked with DateTime.

I pulled out the query and replaced it with:
Code: Pascal  [Select][+][-]
  1. SELECT skip_date FROM MC_Skipped/code]
  2. and it worked. But I still can't make the time query work.
« Last Edit: February 02, 2024, 10:31:33 pm by Slyde »
Linux Mint 21.3
Lazarus 3.0

Slyde

  • Full Member
  • ***
  • Posts: 152
Re: SQLite query not providing data
« Reply #5 on: February 02, 2024, 10:52:55 pm »
Quote from: dsiders
    SELECT (strftime('%s', 'now', 'localtime') - strftime('%s', skip_date)) / 3600 AS elapsed FROM MC_Skipped;

That doesn't work for me.  I'd tried it before, and I just tried it again with your query.  The compiler shuts this down when I try to run it with this message:
Code: Pascal  [Select][+][-]
  1. main_unit.pas(1649,40) Error: Syntax error, ";" expected but "ordinal const" found

I appreciate you trying.  Thanks.
Linux Mint 21.3
Lazarus 3.0

rvk

  • Hero Member
  • *****
  • Posts: 5978
Re: SQLite query not providing data
« Reply #6 on: February 02, 2024, 10:54:30 pm »
This is what I get from Showmessage(SQL.Text).
Code: Pascal  [Select][+][-]
  1. SELECT (strftime('%s', 'now', 'localtime') - strftime('%s', 'skip_date')) / 3600 AS elapsed FROM MC_Skipped WHERE nid = :nid

In DB Browser, I use the same:
Code: Pascal  [Select][+][-]
  1. 'SELECT (strftime("%s", "now", "localtime") - strftime("%s", "skip_date")) / 3600 FROM MC_Skipped WHERE nid = 103;'
How are those the same??.
The second uses double quotes. The first single quote.
And although sqlite tries to enable both methods, sometimes this goes horribly wrong.

Try the double quotes in your program and it should work.
In some fiddle website I see it makes a difference.
« Last Edit: February 02, 2024, 10:56:15 pm by rvk »

Slyde

  • Full Member
  • ***
  • Posts: 152
Re: SQLite query not providing data
« Reply #7 on: February 02, 2024, 11:01:57 pm »
Thanks, rvk.  I feel kind of stupid in doing that.  I used single quotes instead of double quotes because that's always what Lazarus always wants.  But you're 100% right.  In this instance, it's all about double quotes.  It works.

Really appreciate you.
« Last Edit: February 02, 2024, 11:09:00 pm by Slyde »
Linux Mint 21.3
Lazarus 3.0

Zvoni

  • Hero Member
  • *****
  • Posts: 2237
Re: [SOLVED] SQLite query not providing data
« Reply #8 on: February 05, 2024, 09:32:48 am »
Seeing such queries always gives me cramps.
Yes, you're using strftime to return the seconds, but why in blazes don't you use the unixepoch-function?
That said: Depending on your use-case: have you thought about storing your DateTime's as a Unixepoch from the get go?
Then you wouldn't have to jump through hoops to recalculate the seconds, just to get hours between two datetimes

Next: you use 'now' with modifier 'localtime'. Is your skip_date localtime, too?

Beware: SQLite stores Date/Times as UTC IF NO Modifier is passed
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018