Transaction Statements

In situations where the direct use of the JDBC connection is not possible, transaction statements can be used to control a local transaction:

START TRANSACTION start transaction (disable autocommit)
COMMIT commit (enable autocommit )
ROLLBACK rollback and return to autocommit mode

START TRANSACTION depends on how the query is executed. If the query is executed using the normal SQL editor, the script is sent to the CData Virtuality Server via JDBC. On the other side, if the same script is scheduled in an SQL job, the script is not sent via JDBC, but it parsed and processed directly by the CData Virtuality Server. Since START TRANSACTION is not a part of the grammar, the execution of the script fails. In this case, BEGIN ATOMIC should be used instead of START TRANSACTION:

SQL

BEGIN ATOMIC
SELECT 1;
END ;