FreeTDS Development Group

["ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>]

> 
> > > > 2.  We still have issues with error messages, even in 
> current CVS.
> > > > Consider this stored procedure:
> > > > 
> > > > create proc t 
> > > > as begin
> > > >         select 'Here is the first row' as FirstResult
> > > >        raiserror( 'This is an example error message.', 16, 1)
> > > >         select 'Here is the last row' as LastResult
> > > > end 
> > > > 
> > > > We should see one message and two results (one row each). 
> >  But here
> > is
> > > > today's surprise answer:
> > > > 
> > > > $  ./samples/odbc_rpc.pl  -Ddbi:ODBC:mpq -U$U -P$P  t
> > > > Binding parameter #1, the return code
> > > > 
> > > > Executing: "{? = call t }" with parameters ''
> > > > execute returned: '-1'
> > > > Result #1:
> > > > [FirstResult]
> > > > 'Here is the first row'
> > > > DBD::ODBC::st fetchrow failed: [unixODBC][FreeTDS][SQL 
> Server]This
> > is an
> > > > example error message. (SQL-)(DBD: 
> st_fetch/SQLMoreResults err=-1)
> > at
> > > > ./samples/odbc_rpc.pl line 94.
> > > > 'Here is the first row'
> > > > 'Here is the last row'
> > > 
> > > :( 
> > > A complex mix for error and resultsets...
> > > James, post odbc and freetds logs.
> > 
> > Attached (I hope).  
> > 
> > I also wrote dblib_rpc.pl, which I'll commit soon too.  It 
> > demonstrates
> > that the db-lib rpc code handles server messages just fine.  
> > And I'd say
> > -- can you guess? -- the API is easier to use.  Of course, 
> it can only
> > call stored procedures; it's not as flexible as ODBC's 
> > prepare/exeucute.
> > 
> 
> I would have prefer DM log but I manages to update raiserror.c test.
> I discovered some problems:
> - FreeTDS accept SQLGetData calls before SQLFetch (returning NULL)...
> not that fine
> - MS SQLMoreResults return correctly SQL_ERROR/SQL_SUCCESS_WITH_INFO
> however if you get data with SQLGetData it seems to eat error
> (SQLMoreResults returns SQL_SUCCESS, perhaps it returns error on
> SQLFetch/SQL_NO_DATA), I think that for this reason Perl success on
> windows but is mainly broken, if it detect that there are no rows
> (calling SQLFetch) it should assume the SQLMoreResults change 
> recordset
> even returning SQL_ERROR...
> - FreeTDS seems to return a false SQL_SUCCESS after last rows
> 

More on this. It seems that MS odbc read all data it can stopping before
first row or after done token. This happen even reading rows. I detect
this executing store procedure and watching for results

If we create a store like

create proc t
        @i int output
as
  select i from table
  set @i = 1
  return 2

server returns

rowfmt
rows
done in proc (with row number)
param
proc result
done proc

Now if we execute

bind
execute
fetch
on last fetch that return success we get output and return

if we execute

execute
fetch/getdata
on last fetch that return success we get output and return

however executing

execute
fetch
(not reading data)
we will get output when fetch return no data...

it seems that when ms odbc driver read data it read also others tokens,
however if there is a raiserror after a select ms odbc driver return
this error on last fetch (which return no data). 

So if we have a store like

  select ....
  raiserror

if we use bindcol/getdata error is returned after fetch with no data
while if we do not read data but just skip row it return error on
sqlmoreresults. Note that in this last case sqlmoreresult seek on next
recordset and return error... this is the same way FreeTDS run however
DBD::ODBC cannot handle this...

freddy77