FreeTDS Development Group

[Jeff Dahl <jddahl AT micron.com>]

ZIGLIO, Frediano, VF-IT wrote:

> > > 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.  
> > > >        
> > Only the CVS
> >    
> >
> > > > version gives the error.  Version 0.63 doesn't return anything.
> > > >
> > > > Thanks,
> > > > Jeff Dahl
> > > >
> > > >   
> > > >
> > > >        
> > > Is difficult do reply to your question without knowing the 
> > >      
> > tool you are
> >    
> >
> > > using... I suppose is a ODBC tool like isql. Or is it a program you
> > > wrote? 
> > >
> > >      
> > I wrote the program using the ODBC API.  However, if I run the same 
> > query with with tsql (v0.63 and CVS), the data is returned 
> > correctly.  
> > Looks like the problem is in the ODBC layer.
> >
> >    
> >
> > > Does myTable contain 'data1' and 'data2' ?
> > >
> > >
> > >      
> > Yes.
> >
> >    
>
> 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;
       }