SQL syntax error?
lazarus / 16-Databases

----------------------------------------------------------------------
bobonwhidbey · 2014-12-05 23:27

Near "Notes" I'm getting a sql syntax error. My Events and NewEvents
tables have only two text fields, Day and Notes. I'm trying to create
a 2nd table with dates formatted y/m/d instead of y-m-d. The Select
statement works fine. The error occurs with the ExecSQL statement. I'm
using a SQLite db.

Here's the first insert statement
'INSERT INTO NewEvents (Day,Notes) VALUES("2004/11/26","4:00
Turkey");'

Code: [Select]  with Query do begin       
    SQL.Clear;
    q := 'SELECT * FROM Events';
    SQL.Add(q);
    Open;
    while not EOF do begin
      r := FieldValues['Day'];
      while pos('-', r) > 0 do begin
        i := pos('-', r);
        r[i] := '/';
      end;
      n := FieldValues['Notes'];
      q := 'INSERT INTO NewEvents (Day,Notes) VALUES("' + r + '",' + n
+ ');';
      SQL.commatext := q;
      ExecSQL;
      Next;
    end; // while

    Conn.Transaction.Commit;
  end; // with Query

----------------------------------------------------------------------
bobonwhidbey · 2014-12-06 02:59

This problem was caused by using
SQL.commatext := q;
Commas within the value of "q" resulted in more than one record being
added.

A better technique is to use:
SQL.Add(q);

----------------------------------------------------------------------
JZS · 2014-12-06 06:19

If I were you I would have changed and rethought about the entire
code.

As per the case of your code, use FieldByName instead of FieldValues.
Also use SQL.Add or SQL.Text instead of CommaText:

Code: [Select]  with Query do begin       
    SQL.Clear;
    //q := 'SELECT * FROM Events';
    //SQL.Add(q);
    SQL.Add('SELECT * FROM Events');
    Open;
    while not EOF do begin
      r := FieldByName('Day').AsString; //FieldValues['Day']; This
might return multiple value if the query result in multiple records
      //while pos('-', r) > 0 do begin
      //  i := pos('-', r);
      //  r[i] := '/';
      //end;
      r:= StringReplace(r, '-', '/', [rfReplaceAll]); //no need to
loop. Even better, use FormatDateTime
      //n := FieldValues['Notes'];
      q := 'INSERT INTO NewEvents (Day,Notes) VALUES("' + r + '",' +
{n}FieldByName('Notes').AsString + ');';
      SQL.text := q; //why Commatext? Also could use SQL.Text:=
'INSERT INTO...' to reduce using variables unnecessarily
      ExecSQL;
      Next;
    end; // while

    Conn.Transaction.Commit;
  end; // with Query

----------------------------------------------------------------------
mangakissa · 2014-12-06 11:18

You can't use TSQLQuery twice to select and insert the same time.
Also use parameters for values parsing the insert / update query.
Code: [Select]procedure DoSomeThing;
var SelectQuery, InsertQuery : TSQLQuery;
    TransAction : TSQLTransaction;
begin
  SelectQuery := TSQLQuery.create(nil);
  InsertQuery := TSQLQuery.create(nil);
  TransAction := TSQLTransAction.create(nil);
  try
    try
      SelectQuery.database := Conn;
      SelectQuery.Transaction := Transaction;
      SelectQuery.SQL.text := 'SELECT * FROM Events';
      InsertQuery.Transaction := Transaction;
      InsertQuery.database := Conn;
      InsertQuery.SQL.text := 'INSERT INTO NewEvents (Day,Notes)
VALUES(:day, :notes)';
      Transaction.database := Conn;
      SelectQuery.Open;
      while not SelectQuery.EOF do
      begin
        r := FieldByName('Day').AsString; //FieldValues['Day']; This
might return multiple value if the query result in multiple records
        r:= StringReplace(r, '-', '/', [rfReplaceAll]); //no need to
loop. Even better, use FormatDateTime
        InsertQuery.params[0].value := r;
        InsertQuery.params[1].value := FieldByName('Notes').AsString; 
        InsertQuery.ExecSQL;
        SelectQuery.Next;
      end; // while
      Transaction.Commit;
    except
      Transaction.Rollback;
     end;
   finally
     SelectQuery.free;
     InsertQuery.free;
     TransAction.free;
   end;
  end
Read the DBTutorial on Wiki. It clears a lot on finding the right way
to use TSQLQuery.[/code]