[freetds] new utility: bsqlodbc

ZIGLIO, Frediano, VF-IT Frediano.Ziglio at vodafone.com
Wed Feb 7 04:11:40 EST 2007


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

You are using ODBC which is quite strange with this things. Try using
latest unixODBC and ODBC 3.
The problem came from SQLFetch return. If a data is fetched a
SQL_SUCCESS is returned and server error get read by SQLMoreResults
however this function has to return SQL_SUCCESS when we have another
recordset so DM lose the error. unixODBC (as my suggestion) read errors
even if SQLMoreResults returns SQL_SUCCESS (by default errors are readed
by unixODBC only if SQL_ERROR/SQL_SUCCESS_WITH_INFO). ODBC 3 support
recordset without rows but with error to separate errors during fetch
from single errors.

freddy77




More information about the FreeTDS mailing list