|
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 |
|
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. 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 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. @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? @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. @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 @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. @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 :) @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. @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. More comments hidden
|
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.