FreeTDS Development Group

[Sebastien FLAESCH <sf AT 4js.com>]

Ok, I could reproduce the new problem:

I don't get SQLSTATE=HY008 when using server-side cursors...

Try attached sample to reproduce.

Seb

Sebastien FLAESCH wrote:
> Frediano,
>
> I have just tested my little sample with the HEAD and I get an HY008 
> SQLSTATE as expected.
>
> But I still have a problem: I do not get this SQLSTATE when using our 
> Genero VM...
>
> Attached the log ... (using default debug flags, tell me what I should 
> set if needed).
>
> ...
>
> Some details that may help:
>
> Our VM is a pseudo-code interpretor where we handle user interruption in 
> different ways.
>
> In console / TUI mode:
>
> When a SIGINT is catched, we just set a global indicator which is 
> checked in the p-code interpreter loop.
> => we do not call directly SQLCancel() from the SIGINT handler.
>
> In GUI mode:
>
> We have a proprietary GUI protocol to connect presentation clients to 
> application with sockets.
> When the front-end waits for the application to respond (running SQL 
> statement), it can still send a asynchronous event on the socket.
> We use non-blocking select() and we detect SIGIO signals if need data is 
> sent by front-ends.
> In this can we do also set a global flag if we catch an interruption GUI 
> event, and processed in pcode interpreter loop.
> => we do not call directly SQLCancel() from the SIGIO handler.
>
> I have tested with TUI and GUI mode, in both cases I can interrupt, but 
> SQLSTATE is 00000.
>
> ...
>
> Would the TDS driver only set HY008 when SQLCancel() is called during a 
> sighandler?
>
> I guess no, according to the code you just check the TDS return code:
>
>      case TDS_CANCELLED:
>           odbc_errs_add(&stmt->errs, "HY008", NULL);
>
> So I really wonder... I will investigate a bit more.
>
> It's possible that my FreeTDS interface does more ODBC calls than the 
> like sample I have send.
>
>
> Thanks,
> Seb
>
> Sebastien FLAESCH wrote:
> > Thanks Frediano.
> >
> > Will cvs update the HEAD and make some tests.
> >
> > Seb
> >
> > ZIGLIO, Frediano, VF-IT wrote:
> > > > Hello James,
> > > >
> > > > First thanks a lot for taking the time to explain the internals.
> > > >
> > > > Resources (just to compare):
> > > >
> > > > With DB2 CLI, they don't talk about SIGINT, but rather 
> > > > multi-threaded applications where a second thread issues SQLCancel().
> > > >
> > > > http://webdocs.caspur.it/ibm/db2/8.1/doc/htmlcd/en_US/ad/r0000567.htm
> > > >
> > > > With Oracle OCI, they talk about Control-C:
> > > >
> > > > http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10779
> > > > /oci02bas.htm#462655
> > > >
> > > > Of course, nothing about SIGINT on MSDN:
> > > >
> > > > http://msdn2.microsoft.com/en-us/library/ms714112.aspx
> > > >
> > > > ...
> > > >
> > > > I can probably use any solution you will provide to handle SQL 
> > > > interruption.
> > > > But I believe there is a good chance to have other ODBC programmers 
> > > > handle SQL interruption with a SIGINT + SQLCancel() as I did.
> > > > Therefore, you better choose the second "robust" solution.
> > > Currently CVS HEAD seems to handle all situations correctly.
> > >
> > > > Note BTW that several SIGINT signals can be generated successively: 
> > > > Frustrated users keeping CONTROL-C pressed down ;-).
> > > > => Many SQLCancel() calls ...
> > > First send cancel than it wait for cancel handling by server. So others
> > > SQLCancel just do nothing.
> > >
> > > > I wonder however why the TDS protocol does not yet specify how to 
> > > > handle query interruption.
> > > > To me this should be part of the protocol definition (= you should 
> > > > not have to choose between different solutions)
> > > >
> > > > ...
> > > ??? it calls cancellation.
> > >
> > > > Looks like you have to do tricky things (cleaning up the connection) 
> > > > to handle this... right?
> > > >
> > > > Just a remark:
> > > >
> > > > Maybe SQLCancel() (query interruption in TDS) is only supported 
> > > > since SQL Server since version 2005?
> > > >
> > > > I just remember that we started to support SQL interruption with SQL 
> > > > Server native ODBC drivers since 2005 only.
> > > > Quite sure it was not working before that version... but must double 
> > > > check.
> > > cancellation is supported even by protocol 4.2.
> > >
> > > > I can only suggest to try my little SQLCancel() example with SQL 
> > > > Server 2005 and sniff the protocol.
> > > See odbc cancel test. I use alarm to emulate ctrl-c
> > >
> > > > But it must be adapted to catch console events to trap CTRL-C (does 
> > > > not with with SIGINT of course)
> > > > I will try to adapt the sample and send it back.
> > > >
> > > > Thanks!
> > > > Seb
> > > The bad thing... it seems that if test is executed using unixODBC it
> > > fails. Our SQLCancel is not called (that is you call SQLCancel and
> > > unixODBC do not call driver one... it hangs). I use unixODBC 2.2.11,
> > > I'll test with 2.2.12
> > >
> > >
> > > freddy77
> > > _______________________________________________
> > > FreeTDS mailing list
> > > FreeTDS AT lists.ibiblio.org
> > > http://lists.ibiblio.org/mailman/listinfo/freetds
> >
> > _______________________________________________
> > FreeTDS mailing list
> > FreeTDS AT lists.ibiblio.org
> > http://lists.ibiblio.org/mailman/listinfo/freetds
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

/* Testing SQLCancel() with server-side cursor */

static const char * g_dbname = "msvtest1_nc";
static const char * g_usernm = "msvuser";
static const char * g_passwd = "fourjs";

#ifdef _WIN32
#include <WINDOWS.H>
#endif
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
#include <stdio.h>
#include <time.h>
#include <sys/time.h>
#include <signal.h>

#define CHECK_RCODE(t,h,m) \
   if ( rcode != SQL_NO_DATA \
     && rcode != SQL_SUCCESS \
     && rcode != SQL_SUCCESS_WITH_INFO  \
     && rcode != SQL_NEED_DATA ) { \
      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);
}

static SQLHSTMT m_hstmt1;

static void sigint_handler(int s)
{
    int rcode;
    printf(">>>> SQLCancel() ...\n");
    rcode = SQLCancel(m_hstmt1);
    printf(">>>> ... SQLCancel done rcode = %d\n", rcode);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLCancel failed");
    signal(SIGINT, sigint_handler);
}

main(int argc,char **argv)
{
    SQLRETURN rcode;
    SQLHENV m_henv;
    SQLHDBC m_hdbc;
    int i,j;
    const char * dbname;
    const char * usernm;
    const char * passwd;
    int rows;
    SQLINTEGER ind;

    if (argc == 4) {
       dbname = argv[1];
       usernm = argv[2];
       passwd = argv[3];
    } else {
       dbname = g_dbname;
       usernm = g_usernm;
       passwd = g_passwd;
    }

    m_henv = 0;
    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");

    m_hdbc = 0;
    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");

    m_hstmt1 = 0;
    rcode = SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &m_hstmt1);
    CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLAllocHandle StmtH 1.1");

    rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "drop table tab1", SQL_NTS);
    rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "create table tab1 ( k int, 
vc varchar(200) )", SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecDirect create table 1");

    rcode = SQLSetStmtAttr(m_hstmt1, SQL_ATTR_CURSOR_SCROLLABLE,
                           (SQLPOINTER) SQL_NONSCROLLABLE, SQL_IS_UINTEGER);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLSetStmtAttr 
SQL_ATTR_CURSOR_SCROLLABLE");
    rcode = SQLSetStmtAttr(m_hstmt1, SQL_ATTR_CURSOR_SENSITIVITY,
                           (SQLPOINTER) SQL_SENSITIVE, SQL_IS_UINTEGER);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLSetStmtAttr 
SQL_ATTR_CURSOR_SENSITIVITY");

    printf(">> Creating tab1...\n");
    rcode = SQLPrepare(m_hstmt1, (SQLCHAR *) "insert into tab1 values ( ?, 
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' )", SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLPrepare 1");
    rcode = SQLBindParameter(m_hstmt1, 1, SQL_PARAM_INPUT, SQL_C_LONG, 
SQL_INTEGER, 0, 0, &i, 0, &ind);
    for( i=1; i<=2000; i++ ) {
        rcode = SQLExecute(m_hstmt1);
        CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecute 1");
    }
    printf(">> ...done.\n");

    signal(SIGINT, sigint_handler);
    printf(">> Select tab1...\n");
    rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "select max(p1.k) from tab1 
p1, tab1 p2, tab1 p3, tab1 p4, tab1 p5", SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecDirect 3");
    printf(">> ...  done rcode = %d\n", rcode);
    signal(SIGINT, NULL);

    rcode = SQLFreeHandle(SQL_HANDLE_STMT, (SQLHANDLE) m_hstmt1);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLFreeHandle StmtH 1");

    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");

}