Problem with MySQL and Delphi 1

[morfasie]

I have this statement in Delphi

Datamodule1.Query1.SQl.Add('Start Transaction;');
Datamodule1.Query1.SQl.Add('Insert into independantfamilyplan');
Datamodule1.Query1.SQl.Add('values(''1111111111'',''Martin'',''sssss'',''8006195076089'',''70'',''GA010001'',''2004/05/05'',''0'');');
Datamodule1.Query1.SQl.Add('Insert into production');
Datamodule1.Query1.SQl.Add('select a.`Policy Number`, a.`PolicyHolder ID`, a.`Agent AccreditationCode`, a.`Policy Premium`, ''0'',''0000-00-00'',''0'',''Independent Family Care Plan'',''2004/05/05'' from independantfamilyplan a where a.`Policy Number` = ''1111111111'';');
Datamodule1.Query1.SQl.Add('Commit;');

and then this

Datamodule1.Query1.ExecSQL;

but it gives me an error before Insert into independantfamilyplan'? It says the ; is wrong? I have tested it in MySQL and the query works but in delphi it doesn't want to.

Does anybody know why?

thanks

 

[hilfy]

You don't state which data connection technology you're using - BDE, ADO, etc. - but from the looks of it, I'll assume you're using BDE.

Try putting

Datamodule1.Query1.SQl.Add('Begin');

at the begining of this code block and

Datamodule1.Query1.SQl.Add('End;');

at the end. If that doesn't work, you'll need to handle the transactions in the TDatabase that your TQuery connects to. If you don't have a TDatabase and you're connecting just through a BDE alias, you'll need to use a TDatabase.

Your code would look something like this:

Datamodule1.Database1.StartTransaction;
Datamodule1.Query1.SQl.Add('Begin');
Datamodule1.Query1.SQl.Add('Insert into independantfamilyplan');
Datamodule1.Query1.SQl.Add('values(''1111111111'',''Martin'',''sssss'',''8006195076089'',''70'',''GA010001'',''2004/05/05'',''0'');');
Datamodule1.Query1.SQl.Add('Insert into production');
Datamodule1.Query1.SQl.Add('select a.`Policy Number`, a.`PolicyHolder ID`, a.`Agent AccreditationCode`, a.`Policy Premium`, ''0'',''0000-00-00'',''0'',''Independent Family Care Plan'',''2004/05/05'' from independantfamilyplan a where a.`Policy Number` = ''1111111111'';');
Datamodule1.Query1.SQl.Add('End;');
try
  Datamodule1.Query1.ExecSQL;
  Datamodule1.Database1.Commit;
except
  on e:Exception do
  begin
    Datamodule1.Database1.Rollback;
    ShowMessage(e.Message);
  end;
end;

Note that you still need the 'Begin...End;' in your SQL because you're performing multiple inserts.

-D

 

[lespaul]

I don't know that you can have the multiple INSERTS and use the Start Transaction and End Transaction (that looks more like a stored procedure)

I would think about concatenating your SQL string (I think it's easier to read this way) and execute after each query:

with Datamodule1.Query1 do
begin
  SQl.Add('Insert into independantfamilyplan ' +
  'values (''1111111111'',''Martin'',''sssss'',''8006195076089'',''70  '',''GA010001'',''2004/05/05'',''0'')');
  ExecSQL;
  SQl.Add('Insert into production select a.`Policy Number`, a.`PolicyHolder ID`, ' +
  'a.`Agent AccreditationCode`, a.`Policy Premium`, ''0'',''0000-00-00'',''0'', ' +
  '''Independent Family Care Plan'',''2004/05/05'' from independantfamilyplan a where ' +
  'a.`Policy Number` = ''1111111111'';');
  ExecSQL;
end;

now is the field in the table where the value 70 a string field or a number? If it's a number you should remove the '' from both sides.

I'm also going to assume that eventually you will want to put variables in place of your hardcoded examples. Using the QuotedStr function makes adding the quotes around string a little cleaner:

with Datamodule1.Query1 do
begin
  SQl.Add('Insert into independantfamilyplan ' +
  'values(' + QuotedStr(somevariablename) + ', ' + QuotedStr(strName) + ', ' + QuotedStr(strSSSS) + ',' + QuotedStr(somelongnumber) + ', ' + QuotedStr(numberwithspace) + ', ' + QuotedStr(policyID) + ', ' + QuotedStr(PolicyDate) + ', ' + QuotedStr(someothernumber) + ')');
  ExecSQL;
end;

HTH

Leslie

 

[lespaul]

thanks hilfy, learn something new everyday! I didn't know that you could do multiple SQL queries like that!

have a star!

leslie