Unexpected function sequence error when using SQL_CO_FFO on stored procedure

-

hi all,

in odbc program, try execute stored procedure returning unique result set, using fast forward cursor (sql_co_ffo), "function sequence error" when fetching first row.

ffc forces odbc driver use sp_cursoropen, fine me because need multiple active sql statements.

i appears content of stored procedure matters. when procedure implements simple select statement, works fine. when adding simple set statement or even if block, sequence error! not expected.

i same problem sql server 2000 , 2005.

can check if known bug?

here odbc program reproduce:

/*
    create table t1 ( k int, c char(10) )
    insert t1 values (1, 'aaaaaaaa')
    insert t1 values (2, 'bbbbb')
    insert t1 values (3, 'ccccccc')

    create procedure myproc @key int
    begin
       set @key = @key - 1
       select * t1 k > @key
    end

 */


#include <windows.h>
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
#include <odbcss.h>

#define check_rcode(t,h,m) \
   if ( rcode != sql_success && rcode != sql_success_with_info ) { \
      fprintf(stderr,"error %d at: %s\n",rcode,m); \
      geterrorinfo(t,h); \
      exit(1); \
   }

int geterrorinfo(sqlsmallint sqlhdltype, sqlhandle sqlhandle)
{
    sqlreturn rcode = 0;
    sqlchar sqlstate[sql_sqlstate_size + 1];
    sqlinteger naterror = 0;
    sqlchar msgtext[sql_max_message_length + 1];
    sqlsmallint msgtextl = 0;
    int ifxerror = 0;
    rcode = sqlgetdiagrec((sqlsmallint) sqlhdltype,
                          (sqlhandle) sqlhandle,
                          (sqlsmallint) 1,
                          (sqlchar *) sqlstate,
                          (sqlinteger *) & naterror,
                          (sqlchar *) msgtext,
                          (sqlsmallint) sizeof(msgtext),
                          (sqlsmallint *) & msgtextl);
    fprintf(stderr, "diagnostic info:\n");
    fprintf(stderr, "  sql state: %s\n", (char *) sqlstate);
    fprintf(stderr, "  sql code : %d\n", (int) naterror);
    fprintf(stderr, "  message  : %s\n", (char *) msgtext);
   
    return 0;
}

main(int argc,char **argv)
{
    sqlreturn rcode;
    sqlhenv m_henv;
    sqlhdbc m_hdbc;
    sqlhstmt hstmt;
    sqlinteger key;
    sqlinteger key_indic;
    sqlsmallint m_bind_ctype[3];
    sqlpointer m_bind_buffer[3];
    sqlinteger m_bind_buflen[3];
    sqlinteger m_bind_vindic[3];
    int v_1;
    char v_2[11];
    char * dbname = argv[1];
    char * usernm = argv[2];
    char * passwd = argv[3];

    rcode = sqlallochandle(sql_handle_env, sql_null_handle, &m_henv);
    check_rcode(sql_handle_env,null,"sqlallochandle envh");

    rcode = sqlsetenvattr(m_henv, sql_attr_odbc_version, (sqlpointer) sql_ov_odbc3, sql_is_uinteger);
    check_rcode(sql_handle_env,m_henv,"odbc v3");

    rcode = sqlallochandle(sql_handle_dbc, (sqlhandle) m_henv, (sqlhandle *) &m_hdbc);
    check_rcode(sql_handle_env,m_henv,"sqlallochandle dbch");

    rcode = sqlconnect((sqlhandle) m_hdbc,
                       (sqlchar *) dbname,
                       (sqlinteger) sql_nts,
                       (sqlchar *) usernm,
                       (sqlinteger) sql_nts,
                       (sqlchar *) passwd,
                       (sqlinteger) sql_nts);
    check_rcode(sql_handle_dbc,m_hdbc,"sqlconnect");

    rcode = sqlsetconnectoption(m_hdbc, sql_autocommit, 0);
    check_rcode(sql_handle_dbc,m_hdbc,"sqlsetconnectionoption sql_autocommit");

    rcode = sqlallochandle(sql_handle_stmt, m_hdbc, &hstmt);
    check_rcode(sql_handle_dbc,m_hdbc,"sqlallochandle stmth");

    rcode = sqlsetcursorname(hstmt, "mycursor", sql_nts);
    check_rcode(sql_handle_stmt,hstmt,"sqlsetcursorname");

    rcode = sqlbindparameter(
                         hstmt, 1, sql_param_input,
                         sql_c_long, sql_integer, 0, 0,
                         &key, 0, &key_indic);
    check_rcode(sql_handle_stmt,hstmt,"sqlbindparameter");

    rcode = sqlsetstmtattr(hstmt, sql_sopt_ss_cursor_options,
                           (sqlpointer) sql_co_ffo, sql_is_uinteger);
    check_rcode(sql_handle_stmt,hstmt,"sqlsetstmtattr( sql_co_ffo )");

    key = 1;
    key_indic = 0;
    rcode = sqlexecdirect(hstmt, (sqlchar *) "{ call myproc(?) }", sql_nts);
/*
    rcode = sqlexecdirect(hstmt, (sqlchar *) "select * t1 k>?", sql_nts);
*/
    check_rcode(sql_handle_stmt,hstmt,"sqlexecdirect");

    m_bind_ctype[0]   = sql_c_long;
    m_bind_buffer[0]  = &v_1;
    m_bind_buflen[0]  = sizeof(v_1);
    m_bind_vindic[0]  = 0;
    rcode = sqlbindcol((sqlhstmt)      hstmt,
                       (sqlusmallint)  1,
                       (sqlsmallint)   m_bind_ctype[0],
                       (sqlpointer)    m_bind_buffer[0],
                       (sqlinteger)    m_bind_buflen[0],
                       (sqlinteger *) &(m_bind_vindic[0])
    );
    check_rcode(sql_handle_stmt,hstmt,"sqlbindcol 1");

    m_bind_ctype[1]   = sql_c_char;
    m_bind_buffer[1]  = v_2;
    m_bind_buflen[1]  = sizeof(v_2);
    m_bind_vindic[1]  = 0;
    rcode = sqlbindcol((sqlhstmt)      hstmt,
                       (sqlusmallint)  2,
                       (sqlsmallint)   m_bind_ctype[1],
                       (sqlpointer)    m_bind_buffer[1],
                       (sqlinteger)    m_bind_buflen[1],
                       (sqlinteger *) &(m_bind_vindic[1])
    );
    check_rcode(sql_handle_stmt,hstmt,"sqlbindcol 2");

    while (rcode != sql_no_data_found)
    {
        rcode = sqlfetch(hstmt);
        if (rcode == 100) break;
        check_rcode(sql_handle_stmt,hstmt,"sqlfetch");
        fprintf(stdout, ">> %d %s\n",v_1,v_2);
    }

    rcode = sqlclosecursor(hstmt);
    check_rcode(sql_handle_stmt,hstmt,"sqlclose");

    rcode = sqlfreehandle(sql_handle_stmt, (sqlhandle) hstmt);
    check_rcode(sql_handle_stmt,hstmt,"sqlfreehandle stmth");

    rcode = sqldisconnect(m_hdbc);
    check_rcode(sql_handle_dbc,m_hdbc,"sqldisconnect");

    rcode = sqlfreehandle(sql_handle_dbc, (sqlhandle) m_hdbc);
    check_rcode(sql_handle_dbc,m_hdbc,"sqlfreehandle dbch");

    rcode = sqlfreehandle(sql_handle_env, (sqlhandle) m_henv);
    check_rcode(sql_handle_env,m_henv,"sqlfreehandle envh");

}

 

there couple of issues here.

firstly, it's restriction of server cursors can return single result set. result set can row count or other status information, so you're limited single result set or stored proceedure contains single select statement. if you violate restriction request converted default result set.

second, if run autocommit off in cases you'll need to call sqlendtran before disconnect. it's error attempt disconnect if there active transaction. autocommit off, transaction become active execute sql statement @ server.

  



SQL Server  >  SQL Server Data Access



Comments

Post a Comment

Popular posts from this blog

more indexes

-
i want 2 indexes. likes can't mssql. i have example fields: school class number i want school , class in index can this: school class number 1 2 435 1 4 4535 1 5 265 2 1 8234 2 2 8493 4 3 943 4 2 493 4 6 934 but how can in sql server management studio i declare unique constraint. borrowing segio's ddl: create table dbo.orderrows ( id int identity (1, 1) not null primary key ,ordernr varchar (20) not null ,orderrow varchar (20) not null ,rowtotal decimal (11, 3) default 0 not null , constraint uq_orderrows unique (ordernr, orderrow) ); above under hood create unique index on column combination (ordernr, orderrow). tibor karaszi, sql server mvp | web | blog SQL Server  >  ...
Read more

ActiveDirectory

-
hello togehter, is possible disable bloody warning?: event id: 2887 event source: activedirectory_domainservice event type: warning event description: during previous 24 hour period clients attempted perform ldap binds either: (1) sasl (negotiate kerberos ntlm or digest) ldap bind did not request signing (integrity validation) or (2) ldap simple bind performed on cleartext (non-ssl/tls-encrypted) connection directory server not configured reject such binds.  the security of directory server can enhanced configuring server reject such binds.  for more details , information on how make configuration change server please see http://go.microsoft.com/fwlink/linkid=87923. summary information on number of these binds received within past 24 hours below. can enable additional logging log event each time client makes such bind including information on client made bind.  to please raise setting "ldap interface events" even...
Read more

Virtual Channel

-
on condition rdp decide protocoll extension necessary? use standard gdi , if there movie playing, switch dynamically window ms-rdpev video transfering? or full session send on additional ms-rdpev. , in cases host rendering , in case client rendering? thanks in advance! hi, the remote desktop protocol video redirection virtual channel extension used redirect audio/video streams terminal server terminal client . protocol specifies communication between application playing audio/video on terminal server , terminal client. regards, wilson jia this posting provided "as is" no warranties, , confers no rights. please remember click “mark answer” on post helps you, , click “unmark answer” if marked post not answer question. can beneficial other community members reading thread. ” Windows Server  >  ...
Read more