There are four ways to execute a statement, depending on when they are compiled (prepared) by the database and who defines them:
Direct execution. The application defines the SQL statement. It is prepared and executed at run time in a single step. The method execute/3 of the query class directly executes a SQL statement.
Prepared execution. The application defines the SQL statement. It is prepared and executed at run time in separate steps. The statement can be prepared once and executed multiple times. To prepare a SQL statement use the method prepare/3, to execute a prepared SQL statement the method execute/2 of the query class can be called. Prepared execution is faster than direct execution if the same SQL statement must be executed more than once with different parameter values.
Procedures. The application can define and compile one or more SQL statements at development time and store these statements on the data source as a procedure. The procedure is executed one or more times at run time. The application can enumerate available stored procedures using queryProcedures/1. To execute a procedure direct the method execute/3 can be used. To prepare a procedure there is the method prepareProcedure/4.
Catalog functions. The database driver contains function(s) that return a predefined result set. The function is executed one or more times at run time, see Catalog methods.
After a SQL statement or procedure call have been prepared the parameters that it may contain can be assigned a value, use the method putParameter/3 for this.
A transaction is a number of SQL statements that is considered to a single unit of work, an atomic operation, the operation succeeds or fails as a whole. Transactions can be committed or rolled back, committing a transaction means making the changes caused by the statements permanent, rolling back means that the database is put in the same state again as when the transaction was started (all changes of the statements in the transaction are undone).
ODBC supports two modes of operation for transactions, the automatic commit mode (default) and the manual commit mode. In auto-commit mode every database operation is considered a transaction that is committed when performed. For databases that do not have transaction support is this the only supported mode. In manual commit mode applications must explicitly commit or rollback transactions by commit/1 or rollback/1. The transaction mode can be set by the method putAutoCommit/2 of the database class.
Transactions do not have to be explicitly started. A transaction begins implicitly when the application starts operating on a database or when a transaction is committed or rolled back. Applications should not commit or rollback transactions by executing COMMIT or ROLLBACK SQL statements, the effects of doing this are undefined.