FreeTDS Development Group

["James K. Lowden" <jklowden AT freetds.org>]

With Daniel's help, I tested bsqlodbc on OS X using Sybase's driver. The
results were very unsatisfactory, so much so that I think we shouldn't do
as Sybase does in this case.  

Sybase's RAISERROR syntax doesn't allow the user to set the severity
level; all user-generated error messages are severity 16.  The only other
choice is PRINT (severity 0).  So the test I did with Microsoft's server
had to be modified, and to learn anything interesting bsqlodbc needed
somewhat more flexible error handling.  

(The first change I had to make was:

        SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0)

        "An application must set this environment attribute before calling any
function that has an SQLHENV argument...."

The FreeTDS driver doesn't require this to be set; I guess there's a
compiled in default.  It should be required, though.)

What the Sybase driver does right

If the SQL batch does not begin with RAISERROR, 

$ head -4 raiserror.sybase.sql 
-- raiserror 50000 'ouch of severity 0' select 0 as 'value'
print  'ouch of severity 0' select 0 as 'value'
raiserror 50000 'ouch of severity 1' select 1 as 'value'
raiserror 50000 'ouch of severity 2' select 2 as 'value'

Sybase's driver works fine: 

$ build/Debug/bsqlodbc -S $S -U $U -P $P -i raiserror.sybase.sql 
"[Sybase][ODBC Driver][Adaptive Server Enterprise]ouch of severity 0"
     value
----------
         0
bsqlodbc: error -1: SQLMoreResults: SQL_ERROR: failed
bsqlodbc: error 50000: {ZZZZZ}[Sybase][ODBC Driver][Adaptive Server
Enterprise]ouch of severity 1
     value
----------
         1
bsqlodbc: error -1: SQLMoreResults: SQL_ERROR: failed
bsqlodbc: error 50000: {ZZZZZ}[Sybase][ODBC Driver][Adaptive Server
Enterprise]ouch of severity 2
etc.  

In this case, SQLExecute succeeds and SQLFetch succeeds. SQLMoreResults
returns SQL_ERROR; we fetch the diagnostics and continue.  


What the Sybase driver does very wrong IMO

$ head -4 raiserror.sybase.sql 
raiserror 50000 'ouch of severity 0' select 0 as 'value'
print  'ouch of severity 0' select 0 as 'value'
raiserror 50000 'ouch of severity 1' select 1 as 'value'
raiserror 50000 'ouch of severity 2' select 2 as 'value'

$ build/Debug/bsqlodbc -S $S -U $U -P $P  -i raiserror.sybase.sql 
bsqlodbc: error 50000: {ZZZZZ}[Sybase][ODBC Driver][Adaptive Server
Enterprise]ouch of severity 0
SQLExecute: continuing...
     value
----------
bsqlodbc: error -1: SQLFetch: SQL_ERROR: failed
/Users/jklowden/projects/bsqlodbc/bsqlodbc.c:134: failed assertion `ret ==
SQL_SUCCESS'
Abort trap

SQLExecute returns SQL_ERROR.  We fetch the diagnostics and try to
proceed.  SQLNumResultCols says there's one column, a contradiction:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcwas_a_result_set_created_.asp

        "In a few situations, the programmer cannot possibly know whether a
statement will create a result set.[...]
        "In such cases, the application calls SQLNumResultCols to determine 
the
number of columns in the result set. If this is 0, the statement did not
create a result set; if it is any other number, the statement did create a
result set.

SQL_ERROR meant SQLExecute failed, yet we have a column, ergo a result
set.  OK, so we call SQLFetch, get another error.  Now SQLGetDiagField
fails (for no documented reason), and we give up.  

Conclusion

Sybase's ODBC driver does not treat RAISERROR consistently. If it's first
in the batch, SQLExecute fails and rows cannot be fetched.  If it comes
later, all rows can be fetched.  

I do not think we should emulate this behavior.  IMO SQLExecute should
return SQL_ERROR only when it *failed* e.g. couldn't send the SQL to the
server or the server could not execute the SQL.  If we get an TDS_ERROR
packet followed by a DONE packet, SQLExecute should fail.  Otherwise, it
should return WITH_INFO.  

Whenever the server sends an error message, it sends it before any
corresponding rows.  IMO message-rowfmt packet sequences should never
cause an ODBC function to return SQL_ERROR.  

--jkl