[SOLVED] SQLite query not providing data lazarus / 63-Database ---------------------------------------------------------------------- Slyde · 2024-02-02 19:56 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE TABLE "MC_Skipped" (        "nid"   INTEGER,        "qid"   INTEGER,      "skip_date"     TEXT); I have one record inside it: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i nid     qid     skip_date103     23      2024-01-30 07:03:01 The pertinent code using String: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i var  elapsedTime: String;    with ZQ1 do      begin        Close;        SQL.Text := 'SELECT (strftime(''%s'', ''now'', ''localtime'') - ' +        'strftime(''%s'', ''skip_date'')) / 3600 AS elapsed FROM MC_Skipped ' +          'WHERE nid = :nid'; { gives the number of hours between the two dates. this query works fine in DB Browser }      Params.ParamByName('nid').AsString := nameID;        Open; {returns nothing }        elapsedTime := ZQ1.FieldByName('elapsed').AsString;       ShowMessage(elapsedTime); {shows nothing }      end;I get no data return and no error. Using integer: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i begin      with ZQ1 do      begin        Close;        SQL.Text := 'SELECT (strftime(''%s'', ''now'', ''localtime'') - ' +          'strftime(''%s'', ''skip_date'')) / 3600 AS elapsed FROM MC_Skipped ' +          'WHERE nid = :nid';        Params.ParamByName('nid').AsString := nameID;        Open;        elapsedTime := ZQ1.FieldByName('elapsed').AsInteger;        ShowMessage(FloatToStr(elapsedTime));      end;I get an error:  Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Invalid type conversion to Integer in field elapsed I've also tried it like this: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i var  elapsedTime: Real;       with ZQ1 do      begin        Close;        SQL.Text := 'SELECT (strftime(''%s'', ''now'', ''localtime'') - ' +        'strftime(''%s'', ''skip_date'')) / 3600 AS elapsed FROM MC_Skipped ' +          'WHERE nid = :nid';        Params.ParamByName('nid').AsString := nameID;        Open;        elapsedTime := ZQ1.FieldByName('elapsed').AsFloat;        ShowMessage(FloatToStr(elapsedTime));        if elapsedTime < 24.0 then          ShowMessage('It hasn''t been 24 hours yet')        else          ShowMessage('It''s been more than 24 hours');      end;I get the same error:  Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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 · 2024-02-02 20:22 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 · 2024-02-02 20:52 Quote from: Slyde on February 02, 2024, 08:56:45 pmOS: 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE TABLE "MC_Skipped" (        "nid"   INTEGER,        "qid"   INTEGER,      "skip_date"     TEXT); I have one record inside it: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i nid     qid     skip_date103     23      2024-01-30 07:03:01 The pertinent code using String: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i var  elapsedTime: String;    with ZQ1 do      begin        Close;        SQL.Text := 'SELECT (strftime(''%s'', ''now'', ''localtime'') - ' +        'strftime(''%s'', ''skip_date'')) / 3600 AS elapsed FROM MC_Skipped ' +          'WHERE nid = :nid'; { gives the number of hours between the two dates. this query works fine in DB Browser }      Params.ParamByName('nid').AsString := nameID;        Open; {returns nothing }        elapsedTime := ZQ1.FieldByName('elapsed').AsString;       ShowMessage(elapsedTime); {shows nothing }      end;I get no data return and no error. Using integer: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i begin      with ZQ1 do      begin        Close;        SQL.Text := 'SELECT (strftime(''%s'', ''now'', ''localtime'') - ' +          'strftime(''%s'', ''skip_date'')) / 3600 AS elapsed FROM MC_Skipped ' +          'WHERE nid = :nid';        Params.ParamByName('nid').AsString := nameID;        Open;        elapsedTime := ZQ1.FieldByName('elapsed').AsInteger;        ShowMessage(FloatToStr(elapsedTime));      end;I get an error:  Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Invalid type conversion to Integer in field elapsed I've also tried it like this: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i var  elapsedTime: Real;       with ZQ1 do      begin        Close;        SQL.Text := 'SELECT (strftime(''%s'', ''now'', ''localtime'') - ' +        'strftime(''%s'', ''skip_date'')) / 3600 AS elapsed FROM MC_Skipped ' +          'WHERE nid = :nid';        Params.ParamByName('nid').AsString := nameID;        Open;        elapsedTime := ZQ1.FieldByName('elapsed').AsFloat;        ShowMessage(FloatToStr(elapsedTime));        if elapsedTime < 24.0 then          ShowMessage('It hasn''t been 24 hours yet')        else          ShowMessage('It''s been more than 24 hours');      end;I get the same error:  Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT (strftime('%s', 'now', 'localtime') - strftime('%s', 'skip_date')) / 3600 AS elapsed FROM MC_Skipped; Returns nothing. Code: MySQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT (strftime('%s', 'now', 'localtime') - strftime('%s', skip_date)) / 3600 AS elapsed FROM MC_Skipped; Works as expected. ---------------------------------------------------------------------- rvk · 2024-02-02 21:10 Quote from: dsiders on February 02, 2024, 09:52:08 pmCheck your SQL statement. Good spot  I knew it had something to do with all those quotes. Made me dizzy just looking at them ---------------------------------------------------------------------- Slyde · 2024-02-02 21:29 Quote from: rvk on February 02, 2024, 09:22:04 pmAre you sure nameID is correct?It is Quote from: rvkDo a showmessage(nameID).It works Quote from: rvkTry 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: rvkShow 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i '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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT skip_date FROM MC_Skipped/code]and it worked. But I still can't make the time query work. ---------------------------------------------------------------------- Slyde · 2024-02-02 21:52 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i main_unit.pas(1649,40) Error: Syntax error, ";" expected but "ordinal const" found I appreciate you trying.  Thanks. ---------------------------------------------------------------------- rvk · 2024-02-02 21:54 Quote from: Slyde on February 02, 2024, 10:29:27 pmThis is what I get from Showmessage(SQL.Text). Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i '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 · 2024-02-02 22:01 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 · 2024-02-05 08:32 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