Skip to Content.
Sympa Menu

freetds - Re: [freetds] MS SQL Server 2005 and FreeTDS

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] MS SQL Server 2005 and FreeTDS
  • Date: Wed, 15 Feb 2006 12:54:26 +0100

>
> >>>Il giorno ven, 10/02/2006 alle 18.01 -0700, Jeff Dahl ha scritto:
> >>>
> >>>
> >>>
> >>>
> >>>>Why does this query
> >>>>
> >>>> CREATE TABLE #data( myID varchar( 25 ))
> >>>> INSERT into #data select myID from myTable where myID = 'data1'
> >>>> INSERT into #data select myID from myTable where myID = 'data2'
> >>>> CREATE index myindex on #data (myID)
> >>>> SELECT myID
> >>>> FROM #data, myTable
> >>>> WHERE ( #data.myID = myTable.myID )
> >>>> DROP TABLE #data
> >>>>
> >>>>generate this error:
> >>>>
> >>>> SQL_STATE: 24000
> >>>> NATIVE_ERROR: 0
> >>>> MSG: [FreeTDS][SQL Server]Invalid cursor state
> >>>> MSG_LEN: 41
> >>>>
> >>>>I've tried this with both 0.63 and the latest CVS version.
> >>>>
> >>>>
....
> >>
> >
> >Mmm... did you use SQLExecDirect and then SQLFetch return
> this "24000"
> >error ?
> >Which is the call sequence?
> >
> >
> 1. SQLExecDirect
> 2. SQLBindCol
> 3. SQLFetch
>
> ret = SQLExecDirect( <command above> );
> if( ret == SQL_ERROR )
> {
> throw SQLException( SQL_HANDLE_STMT, cmd );
> }
> unsigned char buffer[300];
> SQLLEN indicator;
> ret = SQLBindCol( cmd, 1, SQL_C_CHAR, buffer, 300,
> &indicator );
> if( ret == SQL_SUCCESS_WITH_INFO )
> {
> warning( SQL_HANDLE_STMT, cmd );
> }
> else if( ret == SQL_ERROR )
> {
> throw SQLException( SQL_HANDLE_STMT, cmd );
> }
>
> cerr << "FETCHING" << endl;
> while(( ret = SQLFetch( cmd )) != SQL_NO_DATA )
> {
> if(( ret == SQL_SUCCESS_WITH_INFO ) || ( ret == SQL_ERROR ))
> {
> SQLCHAR SqlState[6], Msg[MAX_MESSAGE_LEN];
> SQLINTEGER NativeError;
> SQLSMALLINT i = 1, MsgLen;
>
> while ((ret = SQLGetDiagRec(SQL_HANDLE_STMT, cmd, i,
> SqlState, &NativeError,
> Msg, sizeof(Msg), &MsgLen)) !=
> SQL_NO_DATA) {
> cerr << "SQL_STATE: " << SqlState << endl;
> cerr << "NATIVE_ERROR: " << NativeError << endl;
> cerr << "MSG: " << Msg << endl;
> cerr << "MSG_LEN: " << MsgLen << endl;
> i++;
> }
> }
> cerr << "[" << buffer << "]" << "\t" << indicator << endl;
> }
>

Now I understand. This is normal and expected. Consider original t-sql:

CREATE TABLE #data( myID varchar( 25 ))
INSERT into #data select myID from myTable where myID = 'data1'
INSERT into #data select myID from myTable where myID = 'data2'
CREATE index myindex on #data (myID)
SELECT myID
FROM #data, myTable
WHERE ( #data.myID = myTable.myID )
DROP TABLE #data

You have 3 resultsets
- two for inserts
- one for select
On first resultset (insert) SQLFetch return 24000 error cause there is
no rows, only rowcount is available. You can test this calling
SQLNumResultCols (which returns 0).

I tested a similar situation and even ms odbc behave the same.

freddy77




Archive powered by MHonArc 2.6.24.

Top of Page