# 22. Virtuoso Functions Guide - sql_transact

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
Virtuoso Functions Guide
Administration
Aggregate Functions
Array Manipulation
BPEL APIs
Backup
Compression
Cursor
Date & Time Manipulation
Debug
Dictionary Manipulation
Encoding & Decoding
File Manipulation
Free Text
Hashing / Cryptographic
LDAP
Locale
Mail
Miscellaneous
Number
Remote SQL Data Source
att_local_name
quote_dotted
rclose
rexecute
rmoreresults
rnext
rstmtexec
sql_columns
sql_config_data_sour...
sql_data_sources
sql_driver_connect
sql_get_installed_dr...
sql_get_private_prof...
sql_gettypeinfo
sql_primary_keys
sql_procedures
sql_remove_dsn_from_...
sql_special_columns
sql_statistics
sql_tables
sql_transact
sql_write_file_dsn
sql_write_private_pr...
vd_remote_data_sourc...
vd_remote_proc_wrapp...
vd_remote_table
vd_statistics
vdd_disconnect_data_...
vdd_measure_rpc_time
Replication
SOAP
SQL
String
Transaction
Type Mapping
UDDI
User Defined Types & The CLR
Virtuoso Java PL API
Virtuoso Server Extension Interface (VSEI)
Web Server & Internet
XML
XPATH & XQUERY
Functions Index

## sql_transact

get list of available DSNs
sql_transact (in dsn_name varchar, [in rollback integer]);
Description

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.

Parameters
dsn_name – The name of the data source being used on the remote.
rollback – Activate the desired behavior. Set to 1 for rollback, ignore to commit.
Return Types

None.

Examples
Control remote rollback/commit behavior

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

See Also

sql_columns sql_primary_keys, sql_gettypeinfo, sql_statistics, sql_tables.