SQL Statement Considerations
Identify SQL statements to avoid when using ODBC with IBM® i Access functions.
There are several SQL statements that are not recommended to be prepared and executed. Examples of these are:
- SET TRANSACTION
- SET SCHEMA
- SET PATH
- COMMIT
- ROLLBACK
- CONNECT TO
- DISCONNECT ALL
For these statements, you can accomplish the same behavior in other ways through ODBC. For example, if you turn off autocommit for the ODBC connection, you can use the SQLEndTran option instead of attempting to execute a COMMIT or ROLLBACK statement.
Note that the SET SESSION AUTHORIZATION SQL statement changes the user that is in control of that connection which leads to unpredictable behavior when used in combination with ODBC connection pooling. The recommended way to use the SET SESSION AUTHORIZATION statement, through ODBC, is to free all open statement handles except for the SET SESSION AUTHORIZATION on which is it to run. Once SET SESSION AUTHORIZATION is run, you should free the statement handle.