Forum > Database
[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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---CREATE TABLE "MC_Skipped" ( "nid" INTEGER, "qid" INTEGER, "skip_date" TEXT);
I have one record inside it:
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---nid qid skip_date103 23 2024-01-30 07:03:01
The pertinent code using String:
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---Invalid type conversion to Integer in field elapsed
I've also tried it like this:
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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:
--- Quote from: Slyde 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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---CREATE TABLE "MC_Skipped" ( "nid" INTEGER, "qid" INTEGER, "skip_date" TEXT);
I have one record inside it:
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---nid qid skip_date103 23 2024-01-30 07:03:01
The pertinent code using String:
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---Invalid type conversion to Integer in field elapsed
I've also tried it like this:
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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?
--- End quote ---
Check your SQL statement.
--- Code: MySQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT (strftime('%s', 'now', 'localtime') - strftime('%s', 'skip_date')) / 3600 AS elapsed FROM MC_Skipped;
Returns nothing.
--- Code: MySQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT (strftime('%s', 'now', 'localtime') - strftime('%s', skip_date)) / 3600 AS elapsed FROM MC_Skipped;
Works as expected.
rvk:
--- Quote from: dsiders on February 02, 2024, 09:52:08 pm ---Check your SQL statement.
--- End quote ---
Good spot 8-)
I knew it had something to do with all those quotes.
Made me dizzy just looking at them %)
Slyde:
--- Quote from: rvk on February 02, 2024, 09:22:04 pm ---Are you sure nameID is correct?
--- End quote ---
It is
--- Quote from: rvk ---Do a showmessage(nameID).
--- End quote ---
It works
--- Quote from: rvk ---Try to make nameID an integer and use asInteger instead of asstring for the param.
--- End quote ---
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.
--- End quote ---
This is what I get from Showmessage(SQL.Text).
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---'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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT skip_date FROM MC_Skipped/code]and it worked. But I still can't make the time query work.
Navigation
[0] Message Index
[#] Next page