FreeTDS Development Group

[Frediano Ziglio <freddyz77 AT tin.it>]

Il sab, 2004-03-06 alle 17:31, James K. Lowden ha scritto:
> On Sat, 06 Mar 2004 12:37:11 +0100, Frediano Ziglio <freddyz77 AT tin.it>
> wrote:
> > ?? store procedure ??
> > I think there are two problems. I understood row count problem, but I
> > don't know store procedure one... You patch was about SQLRowCount, I
> > don't understand where store procedure problems stand..
> 
> Yes, there are two problems.  
> 
> SQLRowCount() in 0.62.1 fails almost every time.  When SQLMoreResults()
> sees TDS_NO_MORE_RESULTS, it sets stmt->dbc->current_statement = NULL.  If
> SQLRowCount() is called after that (which is perfectly legitimate), it
> sees stmt->dbc->current_statement != hstmt and returns a failure.  Even
> though there's no error.  
> 

Fixed yesterday. SQLRowCount should never return 24000 (invalid state).
I cached row count so now it works correctly.

There can be only an active statement at a time. Current statement is
the statement that issued current query (sql language, procedure call or
whatever). When the server return all data from the query ODBC can send
another query using another statement. So there is no sense to have
server in IDLE state and current_statement set to a no-NULL statement.
ODBC have to cache some information in statement. For example you can
read row count or row format even if server is in IDLE state.
I don't know if this is clear, perhaps it's better to add some
documentation to doxy's one... Any suggestion is welcome.

> The second problem is that real errors aren't propogated back to the user.
>  To illustrate, modify the stored procedure in
> src/odbc/unittests/transaction.c:
> 
>       Create Procedure testinsert 
>               @value int
>       As
>       insert into TestTransaction values ( @value )
>       select * from TestTransaction
>       insert into TestTransaction values ( 'b' )
> 
> You will see the 0xAA error packet come back from the server, but
> SQLExecDirect() will not return an error to the caller.  
> 

This is not a real error. Sybase do not allow to create such procedure
(it seems it optimize and analyze statements much better). Using mssql
server returns

DONE_INPROC (insert)
ROWFMT (select)
ROW (select)
DONE_INPROC (select)
ERROR (insert)
DONE

SQLExecDirect stop at ROW so it can't see the error and return
SQL_SUCCESS. However SQLEndTran fails (TODO: I don't know the correct
behavior... rollback or just nothing?). It seems however that you can't
read error after SQLEndTran (this is surely a FIXME).

> > > To me, it has nothing to do with "rows affected", which, although
> > > interesting, is not critical.  What's critical is to be able to call
> > > stored procedures and be made aware of any errors transmitted by the
> > > server.  
> > > 
> > 
> > It's critical for Perl users, cause DBI::ODBC call SQLRowCount after
> > SQLExecDirect/SQLExecute.
> 
> That's how I found the problem; I'm using DBD::ODBC.  I don't rely on row
> counts, but I do check for errors.  My script was failing because I was
> getting an error from SQLRowCount().  Now it's working, but won't detect
> errors, which isn't much better.  
> 

I extended transaction test to check this, however I didn't test 0.62.
There are still some pending issues:
- procedure and errors returned from SQLExecDirect (error on first
recordset)
- the "select * into #t from testdb..a\nselect * from #t" problem. It's
really strange, isql prepare/execute the two sql statement but server
return error preparing last sql statement (#t not found???)
- SQLEndTran behavior on some statement failure (rollback or simple
exit)

freddy77