[SOLVED] SQLite query not providing data

[Slyde]

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?

[rvk]

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]

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.

[rvk]

Check your SQL statement.

Good spot 

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

[Slyde]

Are you sure nameID is correct?

It is

Do a showmessage(nameID).

It works

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.

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.

[Slyde]

    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.

[rvk]

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.

[Slyde]

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.

[Zvoni]

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