SQLQuery1 listing wrong table
lazarus / 16-Databases

----------------------------------------------------------------------
cov · 2015-12-12 09:40

I have a number of tables in a (SQLite) database.

drawing table: id  path  filesize  drawing
============================= 12 1      94528     plan.dwg 13 1     
16287     elev.dwg 14 1      59414     side.dwg 15 1        261   
sect.dwg 16 1      94528      end.dwgtext table:
 id   dwg   text                 ================================= 183
12     plan view on site     184 12     plan view on building 185 14 
  side elevation        186 16     end elevation         187 15   
section a-a           My code:
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
SQLQuery1.SQL.Text:='SELECT dwg FROM text WHERE text LIKE '+query;   
SQLQuery1.Open;     while not SQLQuery1.EOF do    begin     
drawingIDs.Add(SQLQuery1.FieldByName('dwg').AsString);     
SQLQuery1.Next;    end;   
I'm expecting to get a list of the drawing wherein the text 'query'
appears.

Instead I get an error message "Field not found 'dwg'"

When I look at my SQL query:
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    
SQLQuery1.SQL.Text:='SELECT dwg FROM text WHERE text LIKE '+query;   
SQLQuery1.Open;    for I := 0 to SQLQuery1.FieldCount - 1 do    begin 
    Showmessage(SQLQuery1.Fields[I].FieldName);    end;I see that
instead of the SQL running on the 'text' table, it's running on the
'drawings' table.

This is not what I'm expecting.

PS, sorry for the cock up in formatting the tables: hope it's clear.

----------------------------------------------------------------------
Jurassic Pork · 2015-12-12 10:20

hello,
try to close the sqlquery before to use 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
SQLQuery1.Close;SQLQuery1.SQL.Text:='SELECT dwg FROM text WHERE text
LIKE '+query;    SQLQuery1.Open;

----------------------------------------------------------------------
balazsszekely · 2015-12-12 10:32

Always use parameterized querys. 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  Query:
String;begin  Query := 'AAAA'; //change this 
SQLQuery1.SQL.Text:='SELECT dwg FROM text WHERE text LIKE :PTEXT'; 
SQLQuery1.ParamByName('PTEXT').AsString := Query;  SQLQuery1.Open; 
SQLQuery1.First;  while not SQLQuery1.EOF do  begin   
drawingIDs.Add(SQLQuery1.FieldByName('dwg').AsString);   
SQLQuery1.Next;  end;end;

----------------------------------------------------------------------
cov · 2015-12-12 11:38

Thank you both!

Much appreciated.