TLiteQuery Using Params Will Not Insert Records

TLiteQuery Using Params Will Not Insert Records

Postby Bob Boyd » 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.
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;

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.
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;

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?
Bob Boyd
 
Posts: 12
Joined: Thu 28 Jul 2016 19:26

Re: TLiteQuery Using Params Will Not Insert Records

Postby MaximG » Fri 10 Feb 2017 13:58

Please explain, whether we understand correctly that the problem is in passing a string values array (e.g. "Abc", "Xyz", "Mno") as a query parameter ?
MaximG
Devart Team
 
Posts: 404
Joined: Mon 06 Jul 2015 11:34

Re: TLiteQuery Using Params Will Not Insert Records

Postby Bob Boyd » Mon 13 Feb 2017 18:43

I was attempting to use a single parameter to pass a string that I built which contained multiple EIDs (ex: 'Cable1', 'Cable5', 'Cable 8').
I found a reference that indicates multiple values can not be assigned as a single Param and the recommended solution was to use a temporary table to hold the list of selections and add that table in the query.
Code: Select all
SELECT * FROM TABLE WHERE col IN (SELECT col FROM temporarytable);

http://stackoverflow.com/questions/4788724/sqlite-bind-list-of-values-to-where-col-in-prm
Bob Boyd
 
Posts: 12
Joined: Thu 28 Jul 2016 19:26

Re: TLiteQuery Using Params Will Not Insert Records

Postby MaximG » Tue 14 Feb 2017 08:48

We are glad that you could find a problem solution. Also to pass the list values into a query (e.g., 'Cable1', 'Cable5', 'cable 8' ) you can use macros :
Code: Select all
  Query.SQL.Text: = 'select * from table where col in (&list)';
  Query.MacroByName('list').AsString := 'Cable1'', ' + '''Cable5'', ' + '''Cable8';
MaximG
Devart Team
 
Posts: 404
Joined: Mon 06 Jul 2015 11:34

Re: TLiteQuery Using Params Will Not Insert Records

Postby Bob Boyd » Tue 14 Feb 2017 15:59

Thanks Maxim, I did not know about macros and that method will be an easy swap to the current implementation that is loading each EID into a string, I'll just switch to a macro!
Bob Boyd
 
Posts: 12
Joined: Thu 28 Jul 2016 19:26

Re: TLiteQuery Using Params Will Not Insert Records

Postby MaximG » Tue 14 Feb 2017 17:33

We were glad to help you. Please don't hesitate to contact us with questions concerning LiteDAC usage.
MaximG
Devart Team
 
Posts: 404
Joined: Mon 06 Jul 2015 11:34


Return to SQLite Data Access Components