Issue with TUniScript

ael · Post by **ael** » Tue 13 Sep 2011 08:49

Hello,

I'm having an issue with TUniScript (using a pro license with source v3.70.0.19) and I'm not sure what I'm doing wrong.

Basically I run a script which create a table and add a record. The script execute with no error. If I query the database the table is here and the record is present. So far, no problem.

However, if I disconnect/reconnect and retry the query the table is still here but is empty.

The database is a Firebird 2.5 embedded DB.

Any idea what I'm overlooking?

ael · Post by **ael** » Tue 13 Sep 2011 10:12

Addendum:

I'm not sure if it's related, but I'm having a lot of issues related to "Lock conflict on no wait transaction". The problem is, as far as I know it's an embedded database (so no multi user), and I didn't manually start any transaction, so there should be only one transaction.

The error message always occurs when trying to create a table with a foreign key, so I suppose it's related to the database metadata update.

Is it possible to set the transaction to waiting mode or is it a whole another problem? I might add that these scripts used to work with no problem with ZEOS.

#EDIT: The same script works fine on PostgreSQL (same application, using UniDAC), and Firebird when using flamerobin to execute the script.

AndreyZ · Post by **AndreyZ** » Tue 13 Sep 2011 15:41

The point is that TUniScript automatically commits DDL statements, but not DML statements (when you are working with InterBase provider). You can solve the problem with table filling by using a script like the following:

Code: Select all

create table test(
id integer not null primary key,
txt varchar(20));

insert into test(id, txt) values(1, 'test1');
insert into test(id, txt) values(2, 'test2');
insert into test(id, txt) values(3, 'test3');
commit;

Also you can set the TUniScript.AutoCommit property to True. In this case TUniScript will commit all statements.
I cannot reproduce the problem with locking conflicts. Please specify the exact SQL code that you are using in TUniScript that causes the "Lock conflict on no wait transaction" error.

ael · Post by **ael** » Wed 14 Sep 2011 05:18

Thanks for the clarification, AutoCommit=True helps a lot. I'm curious, is there is a reason why DML statements are not auto commited with InterBase/Firebird?

I cannot reproduce the problem with locking conflicts. Please specify the exact SQL code that you are using in TUniScript that causes the "Lock conflict on no wait transaction" error.

I tried to extract a small testcase, but so far so such luck. It seems a transaction (related to metadata) is kept open even after TUniScript.ExecuteFile and further script trying to create an FK fails.

I will let you know if I can pinpoint the problem. Meanwhile, is there a way to ensure no other transaction are active before running a script?

AndreyZ · Post by **AndreyZ** » Wed 14 Sep 2011 12:04

TUniScript automatically commits DDL statements but not DML statements, because if some DDL statement wasn't commited, the following DML statement cannot be executed. This kind of behaviour is common for most tools and components for InterBase and Firebird. For example, you can check this behaviour with IBExpert.
You can check whether the transaction of TUniScript is active using the following code:

Code: Select all

if UniScript.Transaction.Active then
  ShowMessage('There is an active transaction');

, and the following code to check if there is any transaction active for a connection:

Code: Select all

for i := 0 to UniConnection.TransactionCount - 1 do
  if UniConnection.Transactions[i].Active then begin
    ShowMessage('There is an active transaction');
    break;
  end;

ael · Post by **ael** » Wed 14 Sep 2011 13:32

Thanks for the help, it's much clearer now.