FreeTDS Development Group

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

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