Please confirm: If a SQL Anywhere web service launched from a web browser has called a stored procedure which is executing a SQL statement when the end user gets impatient and presses browser refresh, the SQL statement will be terminated with the following exception:

SQLCODE = -299, SQLSTATE = 57014, ERRORMSG() = Statement interrupted by user

The Help says "Probable cause: You canceled a statement..." which is less than helpful since almost NO user interfaces give the user ANY facility to "cancel a statement"... even at the programming level, canceling a statement is usually a dream unfulfilled... killing a connection, maybe, but canceling a statement? Tell me how :)...

asked 28 Sep '10, 11:04

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

Comment Text Removed
Comment Text Removed

PowerBuilder has a method DBCancel, so something is already there. I suppose it is available in DBLib or the like. But: cancelling a connection (which in many causes the application to get blocked until the query finishes) might require another thread to execute the cancel request.

(28 Sep '10, 12:19) Reimer Pods

In JDBC, you can call Statement.execute()/executeQuery() to have the server start executing the statement and fetching the first row (if the statement returns a result set). If you then call Statement.cancel() on the same statement handle from a different thread, you effectively cancel the statement and the original thread that executed the statement will regain control with a -299 error message being returned from the server. That is basically what DBISQL does when you hit the stop button. So canceling a statement does make sense.

Now to answer your specific question. hitting the refresh button effectively calls cancel on the underlying database connection that the browser is making use of. Canceling the connection causes the statement that was executing to be canceled thereby returning the "interrupted" message. So yes, hitting refresh will/should result in the -299 error message.

permanent link

answered 28 Sep '10, 15:13

Karim%20Khamis's gravatar image

Karim Khamis
accept rate: 40%

Just to sum up: Canceling a statement is possible with ODBC, too, with the help of a second thread and the SQLCancel() ODBC API, cf.

(28 Sep '10, 15:23) Volker Barth
Comment Text Removed

@Volker: Ah, yes, the magic "second thread"... and how many applications are written to have multiple threads on the client side for a single user transaction? I claim almost none.

(28 Sep '10, 16:46) Breck Carter

@Karim: So the message IS misleading: The connection was dropped, which is wholly different from a statement being canceled. In this case, isn't passing control to an EXCEPTION handler profoundly dangerous since the connection on which the web service is executing no longer exists?

(28 Sep '10, 16:48) Breck Carter

@Breck: yes and no. The connection is not dropped until after the interrupted message is successfully returned to the client; so I'm not sure there is much danger involved here. Also, keep in mind that the user of the browser is not aware that there is a connection under the covers (since you don't actually "establish" one); but, since some SQL is executing, the user of the browser probably realizes that a statement is being executed, so the statement interrupted error does sort of make sense. Nevertheless, I will bring your concern up with the developer and see what he thinks.

(28 Sep '10, 17:00) Karim Khamis

@Breck and Volker: shut down hooks and global exception handlers are quite common in JAVA land. In both cases we are talking about secondary threads that are used to perform clean up or other similar work. So, I would think that having multiple threads in a JDBC application is quite common and in many cases necessary. I cannot say the same is necessarily true for ODBC applications, but I would not say that they are entirely uncommon in ODBC land either. JMO

(28 Sep '10, 17:09) Karim Khamis

@Karim: IMO there just needs to be a bit more in the Help topic on this SQLCode. I must confess, this exception (which NEVER goes back to the browser user, just shows up in application logs) has had me befuddled for a long time. I never connected it to browser refresh, even though it was me pressing refresh... possibly because it was always much later when I would look through the application exception logs for problems. Note that in the case of a web service the "client" is not a human being, it is an internal connection, so the "user" never sees this exception.

(28 Sep '10, 21:08) Breck Carter

@Karim: Stay tuned for a fat bounty... I have to wait for the software to let me set a bounty, and then I can click the big green arrow :)

(28 Sep '10, 21:10) Breck Carter

@Breck: it does not look like getting the error changed for this particular scenario is easily doable. I therefore agree that the help topic for this SQLCode needs to be improved. I will take the matter up with the doc team.

(29 Sep '10, 13:41) Karim Khamis

@Breck: As to ODBC with SQLCancel(), I know at least one app that does so... (as I'm the developer). But I agree, it's not common usage.

(30 Sep '10, 07:29) Volker Barth
More comments hidden
showing 4 of 9 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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 28 Sep '10, 11:04

question was seen: 1,081 times

last updated: 28 Sep '10, 15:13