FreeTDS Development Group

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

Replying to self.  What a difference a day makes!  

James K. Lowden wrote:
> $ ./bsqlodbc -S $S -U $U -P $P -i raiserror.sql   
> bsqlodbc: error -1: SQLExecute: SQL_ERROR: failed
> bsqlodbc: error 50000: 42000: [FreeTDS][SQL Server]ouch
> 
> Under what circumstances should raiserror cause SQLExecute to fail?  Why
> SQL_ERROR and not SQL_SUCCESS_WITH_INFO?  The query:
> 
>       raiserror('ouch', 16,1) select 1 as 'one'
> 
> returns and error message and a row.  If SQLExecute returns SQL_ERROR,
> should the application continue on and try SQLFetch??  

Latest testing shows SQLExecute no longer returns SQL_ERROR!  A good
thing, no question.  What happens depends on the serverity level, which
might be right.  However, raiserror messages are still lost, see below.  
 
> Unfortunately, it looks like fisql and bsqldb both goof on this, too.  

bsqldb and fisql have both been fixed and process the above query
correctly.  

I decided to torture bsqlodbc a little more with this script:

raiserror('ouch of severity 0', 0,1) select 0 as 'value'
raiserror('ouch of severity 1', 1,1) select 1 as 'value'
raiserror('ouch of severity 2', 2,1) select 2 as 'value'
raiserror('ouch of severity 3', 3,1) select 3 as 'value'
raiserror('ouch of severity 4', 4,1) select 4 as 'value'
raiserror('ouch of severity 5', 5,1) select 5 as 'value'
raiserror('ouch of severity 6', 6,1) select 6 as 'value'
raiserror('ouch of severity 7', 7,1) select 7 as 'value'
raiserror('ouch of severity 8', 8,1) select 8 as 'value'
raiserror('ouch of severity 9', 9,1) select 9 as 'value'
raiserror('ouch of severity 10', 10,1) select 10 as 'value'
raiserror('ouch of severity 11', 11,1) select 11 as 'value'
raiserror('ouch of severity 12', 12,1) select 12 as 'value'
raiserror('ouch of severity 13', 13,1) select 13 as 'value'
raiserror('ouch of severity 14', 14,1) select 14 as 'value'
raiserror('ouch of severity 15', 15,1) select 15 as 'value'
raiserror('ouch of severity 16', 16,1) select 16 as 'value'

Note these are submitted as a single batch.  

As you can see, many of these messages are lost:

$ make bsqlodbc && TDSDUMP=dump ./bsqlodbc -S $S -U $U -P $P -i
raiserror.sql
`bsqlodbc' is up to date.
bsqlodbc: error 50000: 01000: [FreeTDS][SQL Server]ouch of severity 0
SQLExecute: continuing...
     value
----------
         0
     value
----------
         1
     value
----------
         2
     value
----------
         3
     value
----------
         4
     value
----------
         5
     value
----------
         6
     value
----------
         7
     value
----------
         8
     value
----------
         9
     value
----------
        10
bsqlodbc: error -1: SQLFetch: SQL_ERROR: failed
bsqlodbc: error 50000: 42000: [FreeTDS][SQL Server]ouch of severity 11

>From that, it looks like SQLExecute gets an error, but when
print_error_message tries to retrieve the messages (how many should there
be?) it finds only one.  Thereafter SQLFetch gets only SQL_SUCCESS until
it comes across the RAISERROR with severity 11, when it gets SQL_ERROR. 
bsqlodbc then quits, which might not be the optimal choice....

That's as far as I got.  

--jkl