Problem with TUniScript

[ael]

Hello,

I'm having a problem with TUniScript on Firebird embedded 2.5 after the upgrade to Delphi XE5 + UniDAC 5.1.4.

I'm creating an empty database, and calling the same procedure twice. This procedure will load an SQL script from an external file and execute it. The first script contains CREATE TABLE statements (multiple tables), and the second INSERT statements to fill the database.

The Connection is defined as follows:

Code: Select all

Connection.Username := 'sysdba';
Connection.Password := 'masterkey';
Connection.Server := '';
Connection.Database := DatabaseFile;
Connection.ProviderName := 'InterBase';

Connection.SpecificOptions.Values['Charset'] := 'UTF8';
Connection.SpecificOptions.Values['UseUnicode'] := 'True';
Connection.SpecificOptions.Values['ClientLibrary'] :=
  IncludeTrailingPathDelimiter(ExtractFilePath(Application.ExeName))+'fbclient.dll';

The procedure is quite simple:

Code: Select all

if Connection.Connected then begin
  SQLScript := TUniScript.Create(nil);
  SQLScript.Connection := Connection;

  SQLScript.SQL.LoadFromFile(ScriptFile);
  SQLScript.OnError := ScriptFileError;
  SQLScript.Execute;

  SQLScript.Free;
end;

At the end the structure is Ok, but the database is empty. Am I missing something or do I need to change something due to the autocommit changes in 5.1?

[ael]

Follow up: Ok, this is very weird, at this point I wish I could use 5.0 on XE5.

- After the TUniScript.Execute, data are here
- If I disconnect, data vanish (whether Connection.AutoCommit was set to true or false, doesn't matter)
- If I call Connection.Commit manually data are ok after a disconnect/reconnect
- However, even after calling Connection.Commit if Connection.AutoCommit was set to true, data are not committed.

The same problem occurs (at least) with TUniSQL. I can prepare a test case if you have trouble reproducing this (I'm not sure if it's an error or if I'm dense and should have changed something after the 5.1 update).

[AndreyZ]

The point is that TUniScript automatically commits DDL statements, but not DML statements (when you are working with the InterBase provider). You can solve the problem in two ways:
1. add the COMMIT statement to the script;
2. set the TUniScript.AutoCommit property to True. In this case TUniScript will commit all statements. Unfortunately, now there is a bug related to using TUniScript.AutoCommit, it does not work properly. We have fixed this problem. This fix will be included in the next UniDAC build.

[ael]

Thank you for the explanation.

I'll wait for the next release then, I implemented a workaround for now.

One last question though, regarding TUniSQL with InterBase/Firebird, do I also now have to manually commit (after an INSERT statement) or is the non persistence a side effect of the TUniScript bug?

[AndreyZ]

To make TUniSQL commit statements automatically, you should set the AutoCommit specific option to True. Here is a code example:

Code: Select all

UniSQL.SpecificOptions.Values['AutoCommit'] := 'True';

[ael]

Thank you.

[AndreyZ]

You are welcome. Feel free to contact us if you have any further questions about UniDAC.