As to the docs, the option has the following effect:
What does it mean?
IMHO, auto_commit should use the first approach.
For example, the difference would be notable (as in this question) when calling a stored procedure that uses a local temporary table (which does delete its contents on a commit by default). Obviously, the latter approach would cause the deletion of the contents - making the default behaviour of temporary tables quite pointless when used with auto_commit.
Furthermore, would the typical autocommit mode for different APIs show the same behaviour?
Looking into more docs, I'm starting to get puzzled more and more:
As to the docs, the APIs autocommit mode will use one of both variants I have listed. ODBC and others (which I use mostly) will use the first approach (named "Client-side autocommit") whereas ESQL and JDBC will use the second approach ("Server-side autocommit ").
But as dbisqlc is an ESQL application and DBISQL uses JDBC, I do not understand why they seem to behave differently in the cited question - they should behave identically as in both cases
returns 'ON' (as SET does, though I have never set that option explicitly).
What am I doing wrong here?
You have sadly tripped across a few different gotchas that probably need to be documented better.
First, the doc link that you pointed to above is out of date. The note about JDBC is incorrect. The specific doc note that you highlighted incorrectly equates JDBC with jConnect. I will make sure the documentation is corrected so that the SQL Anywhere JDBC driver is properly listed under the client-side autocommit section while jConnect is listed under the server-side autocommit section. The generic reference to JDBC will then be deleted from this doc note.
Now with respect to autocommit behaviour in DBISQL, as you pointed out, DBISQL uses the SQL Anywhere JDBC driver and should therefore fall under the client-side autocommit section. In fact, that is exactly what happens and as a result, DBISQL does behave as you expect (i.e. it falls under your category "1) It automatically adds a COMMIT statement after each statement it sents to the engine."). The problem is that when DBISQL encounters the first result set in the procedure, it turns around and makes a metadata call to try and describe the result set and underlying temporary table. This metadata call translates into a query against the catalog tables and hence the SQL Anywhere JDBC driver properly issues a commit once the catalog query completes. Sadly, that commit has the side effect of deleting all the rows in the table.
To demonstrate what I mean, try writing a stored procedure that performs 5 inserts prior to performing a select and then call that stored procedure from DBISQL with request level logging turned on. You will see that there is no commit following each insert but as soon as execution hits the select statement within the procedure, DBISQL will turn around and issue a catalog query to describe the result set and that in turn will force an explicit COMMIT to be issued from the client.
answered 11 May '11, 13:24