0

I am working on one issue where I need to insert multiple records using TSQLQuery at once in my Mysql Database.

Below is Testing code:

    var
  LSQLQuery : TSQLQuery;
begin
  LSQLQuery:=TSQLQuery.Create(nil);
  try
    LSQLQuery.SQLConnection:=SQLConnection1;
    LSQLQuery.SQL.Add('Insert into TestTable(P_ID, F_Name, L_Name) values (2,"Vijay", "Chauhan");');
    LSQLQuery.SQL.Add('Insert into TestTable(P_ID, F_Name, L_Name) values (4,"raj", "Chauhan");');
    LSQLQuery.SQL.Add('Insert into TestTable(P_ID, F_Name, L_Name) values (5,"XXX", "Chauhan");');
    LSQLQuery.ExecSQL;//Execute the query
      finally
        LSQLQuery.Free;
      end;
    end;

I dont know whats wrong I am doing but everytime I execute this I get the error as follows:

--------------------------- Debugger Exception Notification ---------------------------

Project Test.exe raised exception class TDBXError with message 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Insert into TestTable(P_ID, F_Name, L_Name) values (2,'Vijay', 'Chauhan'); insert into Te' at line 2'.

Can anyone help me to get it done? I am not sure where I am wrong.

Thank you in advance.

A B
  • 1,461
  • 2
  • 19
  • 54
  • You need to terminate each statement with `;`, including the final one. This is standard SQL behavior. Voting to close as a simple typo/non-reproduceable error. – Ken White Jun 28 '18 at 12:13
  • I tried with the same also but no luck – A B Jun 28 '18 at 12:14
  • 2
    no need to repeat the statement, just append values: `Insert into TestTable(P_ID, F_Name, L_Name) values (2,"Vijay", "Chauhan"), (4,"raj", "Chauhan"), (5,"XXX", "Chauhan")` – whosrdaddy Jun 28 '18 at 12:16
  • You also have embedded double-quotes, which should be single. You really should find a basic SQL tutorial. Also, stop concatenating SQL and use parameters, which removes the issue of quotes and data conversions entirely, as the database driver will do that for you. – Ken White Jun 28 '18 at 12:17
  • @whosrdaddy Thanks for the help. I was doing wrong by assigning the values to `commatext` of `SQLQuery.SQL` which was giving me error. – A B Jun 28 '18 at 15:21
  • @whosrdaddy I have one Question that how many records we can add like comma separated records in single insert Query for `MySQL`. – A B Jun 28 '18 at 15:23
  • Depends on the size of the query, check `SHOW VARIABLES LIKE 'max_allowed_packet';` on your MySQL server... – whosrdaddy Jun 28 '18 at 17:20
  • Thank you @whosrdaddy. It helps me alot – A B Jul 02 '18 at 06:57

0 Answers0