Please confirm browser refresh can cause SQLCODE -299 in web service

[Breck_Carter]

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 :)...

http://dcx.sybase.com/index.html#1101en/saerrors_en11/errm299.html

[reimer_pods]

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.

[Former Member · Accepted solution]

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.

[VolkerBarth · Accepted solution]

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. http://sqlanywhere-forum.sap.com/questions/608#624

[Breck_Carter · Accepted solution]

@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.

[Breck_Carter · Accepted solution]

@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?

[Former Member · Accepted solution]

@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.

[Former Member · Accepted solution]

@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

[Breck_Carter · Accepted solution]

@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.

[Breck_Carter · Accepted solution]

@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 🙂

[Former Member · Accepted solution]

@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.

[VolkerBarth · Accepted solution]

@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.