auto_commit connection property changing at random

[former_SQLA_member1695014]

I've just noticed this odd behaviour on 17.0.11.6933

We have a database where the PUBLIC option for 'auto_commit' is set to 'Off'.

We have a Powerbuilder application that makes multiple connections to this database via ODBC. The properties of these connections are identical other than the name and there is no code that attempts to change the value of the 'auto_commit' connection property.

Historically this has worked as expected - UPDATE/INSERT/DELETE statements via our application were not committed unless we issued a COMMIT, or we could undo them via ROLLBACK.

In 17.0.11.6933 I noticed that in certain cases ROLLBACK wasn't working and investigation found that some of our connections, seemingly at random, have had their 'auto_commit' connection property set to 'On'.

i.e. this query was returning rows with a value of both 'On' and 'Off', with no obvious pattern

select ci.name, ci.number, cp.propname, cp.value
from sa_conn_info() ci
inner join sa_conn_properties( ) cp
on cp.number = ci.number
where propname = 'auto_commit'
order by cp.number, cp.propname

Has something changed recently that might cause this behaviour and/or could it be a bug? The previous version we had in production was 17.0.10.6057 and whilst I can't be 100% sure I think this worked as expected.

Second question, are the release notes for the latest version of SQL Anyhwhere publicly available? I tried logging into my SAP launchpad account to try and download them but got a permission denied message (possibly as a result of our maintenance agreement now being invalid for some reason, which I am trying to sort out).

[jack_schueler]

If your database server is version 17, then it supports server-side autocommit. There is an ODBC connection parameter ClientAutocommit that enables client-side autocommit (=YES) or disables client-side autocommit (=NO). The default is NO. So this option can be used to control where the autocommit is handled. ClientAutocommit=NO gets you pre-17 behavior which may be useful for ODBC-based client front-ends that set/reset autocommit before every transaction.

When ODBC makes a connection to the server, it uses your SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT) setting.

If you enabled it before connecting, then the default is AUTOCOMMIT=TRUE.

If you disabled it before connecting, then the default is AUTOCOMMIT=FALSE.

If you did not set it before connecting, then the default is AUTOCOMMIT=TRUE (this is standard ODBC).

If you enable it after connecting, then the default becomes AUTOCOMMIT=TRUE and all current transactions are committed.

If you disable it after connecting, then the default becomes AUTOCOMMIT=FALSE (no transactions are committed or rolled back). Then it is up to your application to manually COMMIT or ROLLBACK transactions. You can do this using SQLEndTran, or execute COMMIT/ROLLBACK statements. Note that SQLEndTran( SQL_ROLLBACK ) will only ROLLBACK if AUTOCOMMIT=FALSE. Otherwise, SQLEndTran() does an explicit client-side commit (regardless of what option you chose).

You can use SQLGetConnectAttr(SQL_ATTR_AUTOCOMMIT) at any time to determine the AUTOCOMMIT behavior.

Note that SQLSetConnectOption and SQLGetConnectOption are ODBC 1.0 versions of the SQLSetConnectAttr and SQLGetConnectAttr functions.

You should not see any difference in this autocommit behavior in ODBC ever. So you might enable an ODBC TRACE using the MS ODBC Administrator tool. If you need help with interpreting the trace, you can log a support case with SAP.

[former_SQLA_member1695014]

Thanks for the reply. I plan to log a support case with SAP once I can sort out the current problems with our maintenance contract.

[jack_schueler]

One of the things I omitted was that an ODBC-based application should never execute a SET [TEMPORARY] OPTION auto_commit = ['ON' | 'OFF'] statement because that would mess with the ODBC driver's notion of the autocommit state (i.e., the driver wouldn't know that you changed the state and all sorts of odd behaviour could ensue).