MyDump / MyScript with Commit/Rollback

[CocaZero]

Hello.

I want to restore a dump made by MyDump : it's a big SQL file with only INSERT instructions (no Lock/unlock).

But before this, I want to execute :
* lock tables instructions
* delete instructions for tables in the database with MyScript component

And restore of the dump

And :
* unlock tables instruction

This is OK.

If an error occur in the restore, I do a Rollback, and this rollback not work.
I do this :
* MyConnection1.StartTransaction;
* MyScript1.AutoCommit := False;
* MyScript1.SQL :=
* MyScript1.Execute
* MyScript1.ExecuteFile()
* MyConnection1.Rollback;
* MyScript1.SQL :=
* MyScript1.Execute

And the restore is realised !
But I want no restore (Rollback instruction).

It's the same problem with MyDump component.

[swierzbicki]

Is their any "Commit" in your dumped file ?

[CocaZero]

No, the dump file contain only INSERT instructions

And I want to do :
* delete table (query or myscript component)
* insert into table (dump file)
in the same transaction.

[eduardosic]

Your table's are InnoDB?


please, paste a small block you script. a complete create table

[CocaZero]

Yes, all tables are InnoDB.

The script :

Code: Select all

begin
  MyConnection1.StartTransaction;
  MyScript1.AutoCommit := False;

  tstr := TStringList.Create;
  tstr.Add('lock tables tablename write;');
  tstr.Add('delete from tablename;');
  MyScript1.SQL := tstr;
  MyScript1.Execute;

  MyScript1.ExecuteFile('script.sql');

  tstr.Clear;
  tstr.Add('unlock tables;');
  MyScript1.SQL := tstr;
  MyScript1.Execute;

  MyConnection1.Rollback;
End;

The file "script.sql" contain only INSERT INTO instruction :

Code: Select all

  INSERT INTO tablename VALUES
  ('...','...',...),
  ('...','...',...);

The Rollback at End of script don't work :
the DELETE and INSERT is executed