This procedure can be used to control the commit/rollback behavior of a particular remote data source during a Virtuoso transaction such as in a stored procedure.
Under normal circumstances Virtuoso will correctly commit or rollback all associated work as expected, however it may be desirable intervene. When issued without the second parameter a commit will be forced upon the current transactions of the dsn_name above the call to sql_transact regardless of overall outcome. When rollback = 1 is set then a rollback will be forced likewise, hence this will not rollback work on the remote dsn_name prior to sql_transact.
None.
This code fragment can be used to demonstrate the effects of directly controlling the rollback/commit behavior of remote data sources connected to Virtuoso.
create procedure TEST_ROLLBACK ()
{
commit work;
insert into mydsn..rb_test values (1);
sql_transact('mydsn');
insert into mydsn..rb_test values (2);
rollback work;
};
create procedure TEST_ROLLBACK ()
{
commit work;
insert into mydsn..rb_test values (1);
sql_transact('mydsn', 1);
insert into mydsn..rb_test values (2);
commit work;
};
delete from mydsn..rb_test;
commit work;
TEST_COMMIT();
select MIN (ID) from mydsn..rb_test;
-- returns 1
delete from mydsn..rb_test;
commit work;
TEST_ROLLBACK();
select MIN (ID) from mydsn..rb_test;
-- returns 2
sql_columns sql_primary_keys, sql_gettypeinfo, sql_statistics, sql_tables.