Uniloader With Transactions?

[tonymeadors]

I have read several places that uniloader can be used just like
any other "set of insertions", inside a transaction.
But I have not yet been able to get the expected ROLLBACK behavior.
Maybe there is a special step I am missing. Below is a simple example.
I expect the table to be empty but the data has been transferred despite the ROLLBACK.


procedure Teditfloat.Button10Click(Sender: TObject);
var
q1: txquery;
uniloader: tuniloader;
begin
uniloader:= tuniloader.create(nil);
q1:=txquery.create(nil);
try
q1.sqlconnection:=mconnection;
uniloader.connection:=mconnection;
uniloader.TableName:='dd_master';
//
q1.sql.add('delete from dd_master'); //CLEARS DD_MASTER
q1.execsql;
q1.sql.clear;
q1.sql.add('select * from md_master'); //GRABS DATA RECORDS
MCONNECTION.STARTTRANSACTION;

uniloader.loadfromdataset(q1); //FILLS DD_MASTER

IF MCONNECTION.INTRANSACTION THEN
MCONNECTION.ROLLBACK; //SHOULD RETURN DD_MASTER BACK TO ORIGINAL STATE - EMPTY

finally
q1.free;
uniloader.free;
end;

end;

[AndreyZ]

Please specify the database server you are working with (TUniConnection.ProviderName).

[tonymeadors]

Yes - I should have added,

Our original report on this involved SQL Server 2005 and
the tiny code example was developed hitting SQL Server 2005 also.
(we use uniloader with other DBs regularly too, IB, FB, Oracle.)

Also, we are using 4.02 Unidac (not authorized to upgrade at this time)

As always, I appreciate this forum and the effort put into it,

Sincerely,
tonyM

[AndreyZ]

When SQL Server is used, TUniLoader uses the IRowsetFastLoad interface ( http://technet.microsoft.com/en-us/libr ... 31708.aspx ). As IRowsetFastLoad does not take into account the active transaction, there is no way to control its behaviour.

[tonymeadors]

I certainly understand how in developing a software tool to interface with
a myriad of others you are necessarily constrained by the 3rd party design
choices and capabilities.

In the case of UniLoader - we had invested quite a bit of time embedding an
in memory table that would automatically flush every X records.
(we derived from tuniloader a class that would do it automatically)
Some of our tasks and phases went from hours to minutes - beta tests by clients
were very promising.

However, if MS MSQL Server (the primary DB clients use) simply ignores
the required transactions we will have to reverse course and remove all
this code. Data integrity cannot be sacrificed in the insurance/banking world.

For loading millions of records, UniLoader becomes close to useless - at the
very least dangerous for the unwary. Please consider adding this issue
to the Uniloader documentation.

Sincerely grateful for Unidac,
tonyM
ClearCycle

[AndreyZ]

When you use TUniLoader for SQL Server, it uses the IRowsetFastLoad.InsertRow method to add rows to the bulk copy rowset. When all records are inserted, TUniLoader calls the IRowsetFastLoad.Commit method to write the rows to the SQL Server table.
I have investigated this question and found out that TUniLoader called IRowsetFastLoad.Commit even if there were errors. I have fixed this bug, this fix will be included in the next UniDAC build.
In the next UniDAC build, if there is an error, all rows will be roll backed, otherwise committed. As you can see, there will be no data integrity problem.