TLiteQuery Using Params Will Not Insert Records
Posted: Mon 06 Feb 2017 21:56
I need to use a parameter for a query because the values in our EquipmentID database field may contain a single quote which breaks the use of SQL.Add calls for each EID addition. I hope someone can identify what I am doing wrong with the Params property for the TLiteQuery component. I am attempting to export records to tables in an attached database and the insert query works correctly when I use a loop for the individual SQL.Add calls but when the EID contains a single quote and error is generated. The TLiteQuery object has the ParamCheck := True, Options.StrictUpdate := False; and Options.QuoteNames := True.
When I put the EIDs into a string and replace the SQL.Add with the :EIDList parameter in this function the records are never inserted into the tables. The initial query to check the source for EIDs using the parameter list returns True for the IsQuery call as expected and I added the StartTransaction - Commit calls for the export database that gets inserted with no change in the table.
I also verified the insert query with a list of EIDs using the DB Browser for SQLite but I could not use a parameter for the query in that utility. Is there something I'm missing in my use of the Params?
Code: Select all
procedure TV6ExportForm.QueryExportEIDs (stTable : String; stField : String);
var
stQ : String;
stList : String;
lgQ : Boolean;
begin
lgQ := False;
with ExportLiteQuery do
begin
Connection := WC6SourceLite;
SQL.Clear; // Query to check for source records
stQ := 'SELECT * FROM main.' + stTable;
SQL.Add(stQ);
stQ := 'WHERE "'+stField+'" IN (';
SQL.Add(stQ);
with SelectEidDS.DataSet do
begin
First;
while not Eof do
begin
stQ := '''' + FieldByName('EquipmentID').AsString;
Next;
if not Eof then
begin // Add comma and add to SQL string
stQ := stQ + ''',';
ExportLiteQuery.SQL.Add(stQ);
end;
end;
end;
stQ := stQ + ''');'; // Add paren for final EID and add to SQL string
SQL.Add(stQ);
try
stQ := FinalSQL;
Execute;
lgQ := IsQuery;
except
on E: Exception do
begin
end;
end;
if not lgQ then
MessageDlg ('No records returned from query:' + #13#10#13 + stQ, mtError,[mbOK],0);
Close;
if lgQ then
begin // Attach export database and insert records query
SQL.Clear; // Check source query
stQ := 'ATTACH DATABASE ''' + stExportSQLDBFileName+ ''' AS ''Export'';';
SQL.Add(stQ);
stQ := 'INSERT INTO Export.' + stTable;
SQL.Add(stQ);
stQ := 'SELECT * FROM main.' + stTable + ' AS ''Source''';
SQL.Add(stQ);
stQ := 'WHERE Source."'+stField+'" IN (';
SQL.Add(stQ);
with SelectEidDS.DataSet do
begin
First;
stQ := '';
while not Eof do
begin
stQ := '''' + FieldByName('EquipmentID').AsString;
Next;
if not Eof then
begin // Add comma and add to SQL string
stQ := stQ + ''',';
ExportLiteQuery.SQL.Add(stQ);
end;
end;
end;
stList := stQ;
stQ := stQ + ''');'; // Add paren for final EID and add to SQL string
SQL.Add(stQ);
stQ := 'DETACH DATABASE ''Export'';'; // Detach as final step of query
SQL.Add(stQ);
try
if not WC6ExportLite.InTransaction then
WC6ExportLite.StartTransaction;
stQ := FinalSQL;
Execute;
if WC6ExportLite.InTransaction then
WC6ExportLite.Commit;
except
on E: Exception do
begin
end;
end;
Close;
end;
end;
Code: Select all
procedure TV6ExportForm.QueryExportEIDsParam (stTable : String; stField : String);
var
stQ : String;
stList : String;
lgQ : Boolean;
begin
lgQ := False;
with ExportLiteQuery do
begin
Connection := WC6SourceLite;
SQL.Clear; // Check source query
stQ := 'SELECT * FROM main.' + stTable;
SQL.Add(stQ);
stQ := 'WHERE "'+stField+'" IN (:EIDList);';[
SQL.Add(stQ);
with SelectEidDS.DataSet do
begin
First;
stList := '';
while not Eof do
begin
stList := stList + '''' + FieldByName('EquipmentID').AsString;
Next;
if not Eof then // Add single quote and comma
stList := stList + ''','
else
stList := stList + ''''; // add last single quote
end;
end;
ParamByName('EIDList').AsString := stList;
try
stQ := FinalSQL;
Execute;
lgQ := IsQuery;
except
on E: Exception do
begin
end;
end;
if not lgQ then
MessageDlg ('No records returned from query:' + #13#10#13 + stQ, mtError,[mbOK],0);
Close;
if lgQ then
begin // Attach export database and insert records query
SQL.Clear; // Check source query
stQ := 'ATTACH DATABASE ''' + stExportSQLDBFileName+ ''' AS ''Export'';';
SQL.Add(stQ);
stQ := 'INSERT INTO Export.' + stTable;
SQL.Add(stQ);
stQ := 'SELECT * FROM main.' + stTable + ' AS ''Source''';
SQL.Add(stQ);
stQ := 'WHERE Source."'+stField+'" IN (:EIDList);';
SQL.Add(stQ);
stQ := 'DETACH DATABASE ''Export'';'; // Detach as final step of query
SQL.Add(stQ);
ParamByName('EIDList').AsString := stList;
try
if not WC6ExportLite.InTransaction then
WC6ExportLite.StartTransaction;
stQ := FinalSQL;
Execute;
if WC6ExportLite.InTransaction then
WC6ExportLite.Commit;
except
on E: Exception do
begin
end;
end;
Close;
end;
end;