As to the docs, the option has the following effect:

If auto_commit is On, a database COMMIT is performed after each successful statement.

What does it mean?

  1. It automatically adds a COMMIT statement after each statement it sents to the engine.
  2. It causes EVERY statement to be committed automatically, both the sent statements and the statements executed interally as part of stored procedures and the like.

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

select connection_property('Chained')

returns 'ON' (as SET does, though I have never set that option explicitly).

What am I doing wrong here?

asked 11 May '11, 12:19

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 11 May '11, 12:20


Volker,

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.

HTH Karim

permanent link

answered 11 May '11, 13:24

Karim%20Khamis's gravatar image

Karim Khamis
5.7k53870
accept rate: 40%

Karim, thanks for the clarification. I guess the difference between jConnect (server-side autcommit) and the SA JDBC driver (client-side autocommit) is clear to me.

But I still don't understand why DBISQL does need this metadata call since the according proc (cf. the question) has an explicit result clause. - If I understand you correctly, that metadata call is just another statement sent to the engine and is therefore followed by an extra COMMIT (in autocommit mode), and that empties the temporary table.

But that would still not clear the questions from my other question, namely

  1. why the setting of sql_show_multiple_result_sets does make a difference and
  2. why dbisqlc (which as an ESQL application is said to use server-side commit) would return a non-empty result set.
(12 May '11, 03:53) Volker Barth
Replies hidden
1

Volker,

Keep in mind that the result set which gets returned by a procedure need not match the result set defined in the result clause. As a result, an explicit describe is quite often required to get the schema of the actual result set returned by the stored procedure call. The COMMIT that follows the metadata query is as you said a side effect that is out of DBISQL's control.

With respect to the show_multiple_result_sets option, turning that option on forces DBISQL to cache all the result sets up front. The describe requests still occur but they follow after the result sets have been cached and so the temp tables are still being cleared, but the first 500 or so rows have already been cached by the time the temp table gets emptied.

As far as dbisqlc is concerned, I believe that unlike DBISQL, dbisqlc drives the commit requests explicitly on its own rather than relying on the autocommit support within the underlying driver. Hence, dbisqlc has full control as to when the "auto-commit" occurs.

In general, it is best to avoid the use of auto-commit due to these possible unexpected and in many cases unavoidable side-effects. Also, handling commits explicitly within your own application and/or SQL will yield better performance in the long run.

Karim

(12 May '11, 07:43) Karim Khamis

Thanks for the further explanations. Now the image seems to get clearer...

As to the "auto_commit = 1" setting, that's my personal default option for dbisqlc (and DBISQL), and with dbisqlc, I had not had such issues so far. Just the typical ad-hoc queries...

As "real application programming" is concerned, I fully share your recommendation. We usually do use manual commit mode - or at least decide to use the commit mode deliberately:).

FWIW: For further development, I will make sure to use temp tables with NOT TRANSACTIONAL to avoid the related issues.

(12 May '11, 07:54) Volker Barth

@Karim, just another question:

In my tests, the procedure's result set was displayed correctly when using the "SELECT * FROM proc()" syntax.

So I would conclude that in this case, DBISQL does not have to query the system catalog in the interim for the result set schema (leading to the disturbing COMMIT) but queries the result set information specified in the system catalog before actually calling the proc. Is this assumption right?

(Otherwise I would not understand the different behaviour between CALL and SELECT FROM.)

(13 May '11, 06:39) Volker Barth
1

When you use a procedure in the FROM clause, the result set returned by the procedure MUST match the result set schema defined in the procedure RESULT clause (either explicitly or implicitly). So in this case, the procedure result set is predefined and all DBISQL needs to do is obtain the schema of the the actual "select" statement which is done well after the procedure has been executed. To provide additional clarification, consider the case where the FROM clause has a join between a base table and a procedure, as in:

SELECT * FROM mytab,proc()

In this case, the schema of the result set is not the schema of the procedure and so DBISQL will not be allowed to describe the result set until after the join has been done.

(13 May '11, 09:44) Karim Khamis

Thanks again for another important explanation! - And yes, I do understand the difference between my naive conclusion and the real (and apprently much more reasonable) behaviour:)

So I'm glad I've asked again...

(13 May '11, 09:53) Volker Barth

What does "Replies hidden" mean?

(13 May '11, 14:16) Breck Carter
Replies hidden
1

It means the nested levels of answers are getting a bit deeper than the forum wants to show right off the bat, so if you click on the little grey "show all" button below the last reply, it'll expand the display.

(13 May '11, 14:32) Margaret Kam...

d o h !

(13 May '11, 14:38) Breck Carter

...and it's all documented here as part of the 2011-04-30 update:)

(14 May '11, 12:00) Volker Barth
showing 2 of 10 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×438
×105
×59
×20
×15

question asked: 11 May '11, 12:19

question was seen: 4,721 times

last updated: 14 May '11, 12:00