FreeTDS Development Group

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

Here a test case... (takes 4 args!)

This code works fine when compiled on Windows, using the MDAC driver.

Seb

ZIGLIO, Frediano, VF-IT wrote:
> > Just a little question before I write a sample:
> >
> > Are LOBs supported with FreeTDS?
> >
> > Thanks
> > Seb
>
> And if you find any problem please post the sample anyway even if we
> could not fix the problem in a couple of day we'll save your test for
> future release!
>
> freddy77
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

/* Testing large objects */

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>

#define CHECK_RCODE(t,h,m) \
   if ( rcode != SQL_SUCCESS && rcode != SQL_SUCCESS_WITH_INFO && rcode != SQL_NO_DATA && rcode != SQL_NEED_DATA ) { \
      fprintf(stderr,"Error %d at: %s\n",rcode,m); \
      getErrorInfo(t,h); \
      exit(1); \
   }

void 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 SQLHENV m_henv;
static SQLHDBC m_hdbc;
static SQLRETURN rcode;

int readBlob(SQLHSTMT *stmth, SQLUSMALLINT pos)
{
        char buf[512];
        SQLLEN len;
        int i = 0;
        while (1) {
            i++;
            rcode = SQLGetData(stmth, pos, SQL_C_BINARY,
                           (SQLPOINTER) buf, (SQLINTEGER) sizeof(buf),
                           &len);
            if (!SQL_SUCCEEDED(rcode) || len <= 0)
                break;
            if (len > (SQLLEN) sizeof(buf))
                len = (SQLLEN) sizeof(buf);
            fprintf(stderr,">> field %d, step %d: %d bytes read...\n", pos, i, len);
        }
        return rcode;
}

main(int argc,char **argv)
{
    SQLHSTMT m_hstmt;
    int i,p;
#define NBYTES 4096
    int key;           SQLINTEGER vind0;
    char buf1[NBYTES]; SQLINTEGER vind1;
    char buf2[NBYTES]; SQLINTEGER vind2;
    const char * dbname;
    const char * usernm;
    const char * passwd;
    int cnt;

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

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

    m_hstmt = NULL;
    rcode = SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &m_hstmt);
    CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLAllocHandle StmtH");
    rcode = SQLExecDirect(m_hstmt, "drop table tt",SQL_NTS);
    rcode = SQLExecDirect(m_hstmt, "create table tt ( k int, t text, b image, v int )",SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"Could not create table tt");
    rcode = SQLFreeHandle(SQL_HANDLE_STMT, (SQLHANDLE) m_hstmt);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLFreeHandle StmtH");
 
    /* Insert rows ... */

    for( i=0; i<cnt; i++ ) {

    m_hstmt = NULL;
    rcode = SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &m_hstmt);
    CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLAllocHandle StmtH");

    rcode = SQLPrepare(m_hstmt, (SQLCHAR *) "insert into tt values ( ?, ?, ?, ? )", SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLPrepare");

    SQLBindParameter(m_hstmt, 1, SQL_PARAM_INPUT,
                     SQL_C_LONG,
                     SQL_INTEGER, 0, 0, &key, 0,
                     &vind0);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLBindParameter 1");

    SQLBindParameter(m_hstmt, 2, SQL_PARAM_INPUT,
                     SQL_C_BINARY,
                     SQL_LONGVARCHAR, 0x10000000, 0, buf2, 0,
                     &vind1);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLBindParameter 2");

    SQLBindParameter(m_hstmt, 3, SQL_PARAM_INPUT,
                     SQL_C_BINARY,
                     SQL_LONGVARBINARY, 0x10000000, 0, buf2, 0,
                     &vind2);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLBindParameter 3");

    SQLBindParameter(m_hstmt, 4, SQL_PARAM_INPUT,
                     SQL_C_LONG,
                     SQL_INTEGER, 0, 0, &key, 0,
                     &vind0);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLBindParameter 4");

    key = i;
    vind0 = 0;

    memset(buf1,'t',NBYTES);
    vind1 = SQL_DATA_AT_EXEC;

    memset(buf2,'b',NBYTES);
    vind2 = SQL_DATA_AT_EXEC;

fprintf(stderr,">> insert... %d\n", i);
    rcode = SQLExecute(m_hstmt);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLExecute StmtH");
    while (rcode == SQL_NEED_DATA) {
        char *p;
        int r;
        rcode = SQLParamData(m_hstmt, (SQLPOINTER)&p);
        CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLParamData StmtH");
fprintf(stderr,">> SQLParamData: ptr = %p  rcode = %d\n", (void*) p, rcode);
        if (rcode == SQL_NEED_DATA) {
            SQLRETURN rcode = SQLPutData(m_hstmt, p, NBYTES);
            CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLPutData StmtH");
        }
    }

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

    }

    /* No fetch rows ... */

    for( i=0; i<cnt; i++ ) {

    m_hstmt = NULL;
    rcode = SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &m_hstmt);
    CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLAllocHandle StmtH");

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

    rcode = SQLPrepare(m_hstmt, (SQLCHAR *) "select t, b, v from tt where k = ?", SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLPrepare");

    SQLBindParameter(m_hstmt, 1, SQL_PARAM_INPUT,
                     SQL_C_LONG,
                     SQL_INTEGER, 0, 0, &i, 0,
                     &vind0);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLBindParameter 1");

    SQLBindCol(m_hstmt, 1, SQL_C_BINARY, NULL, 0, &vind1);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLBindCol 2");
    SQLBindCol(m_hstmt, 2, SQL_C_BINARY, NULL, 0, &vind2);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLBindCol 3");
    SQLBindCol(m_hstmt, 3, SQL_C_LONG, &key, 0, &vind0);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLBindCol 1");

    vind0 = 0;
    vind1 = SQL_DATA_AT_EXEC;
    vind2 = SQL_DATA_AT_EXEC;

    rcode = SQLExecute(m_hstmt);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLExecute StmtH");

    rcode = SQLFetchScroll(m_hstmt,SQL_FETCH_NEXT,0);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLFetchScroll StmtH");
fprintf(stderr,">> fetch... %d  rcode = %d\n", i, rcode);

    rcode = readBlob(m_hstmt, 1);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"readBlob 1");
/*
    rcode = readBlob(m_hstmt, 2);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"readBlob 2");
*/

    rcode = SQLCloseCursor(m_hstmt);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLCloseCursor StmtH");

    rcode = SQLFreeHandle(SQL_HANDLE_STMT, (SQLHANDLE) m_hstmt);
    CHECK_RCODE(SQL_HANDLE_STMT,m_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");

}