FreeTDS Development Group

["Thompson, Bill D (London)" <bill_d_thompson AT ml.com>]

Hi,

I don't want to stick my oar in where it's not wanted, but I thought I'd
tell you about something I'm working on at the moment in dblibrary, as it
looks analagous somewhat to the issue in ODBC.

I've realised there is a problem in dbsqlok() both in my out of date version
and in the current code.

Our problem is that dbsqlok() returns when it finds either a result set or
ANY kind of end token (including DONEINPROC)

I've done some tests with Sybase open client and realised that our behaviour
differs. 
Sybase open client version of dbsqlok IGNORES DONEINPROC tokens and reads
the returned data until it finds either a result set or a DONE or DONEPROC
token.

The following extract from the sybase manual page (kind of) describes this
behaviour, but I've proven it now.

Return values 
SUCCEED or FAIL.
The most common reason for failing is a SQL syntax error. 
dbsqlok will also fail if there are semantic errors, such as incorrect
column or table names. 
Failure occurs if any of the commands in the batch contains a semantic or
syntax error.

In addition, a runtime error, such as a database protection violation, will
cause dbsqlok to fail if the command buffer contains only a single command. 
If the command buffer contains multiple commands, a runtime error will not
cause dbsqlok to fail. 
Instead, failure will occur with the dbresults call that processes the
command causing the runtime error.
The situation is a bit more complicated for runtime errors and stored
procedures. 
A runtime error on an execute command may cause dbsqlok to fail, in
accordance with the rule given in the previous paragraph. 
A runtime error on a statement inside a stored procedure will not cause
dbsqlok to fail, however. 
For example, if the stored procedure contains an insert statement and the
user does not have insert permission on the database table, the insert
statement will fail, but dbsqlok will still return SUCCEED. 
To check for runtime errors inside stored procedures, use the dbretstatus
routine to look at the procedure's return status and trap relevant server
messages inside your message handler.


For reasons that are too complicated to go into, I've discovered that in my
application at least it is VERY important to change the behaviour of
dbsqlok, as in some circumstances we are missing error conditions within the
database.
SQL server can in some cases completely abort a stored procedure, completely
bypassing any error handling in that procedure and what is more NO return
status is returned in the TDS stream.
The only indication of failure is the status on the final DONEPROC or DONE
token. It's also clear to me that dbresults() does not always (and maybe is
not meant to) interpret this correctly.

I don't want to take the debate on this off on a tangent so please ignore
this mail if you want. I just thought this insight might help a little.


Bill



==============================================================================

If you are not an intended recipient of this e-mail, please notify
the sender, delete it and do not read, act upon, print, disclose,
copy, retain or redistribute it.

Click here for important additional terms relating to this e-mail.
     <http://www.ml.com/email_terms/>

==============================================================================