FreeTDS Development Group

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

Ok, once again ;-)

Attached the little sample to reproduce.

I fact, I use column-wise binding, but when LOB columns need to be
fetched, I use an array size of 1. It may look strange, but I have
to write generic code, to execute SQL statements that are not known
at compile time.

Compilation command used:

xlc -O2 -qmaxmem=8192 -q64 -I/dbs/64bits/uxo/2.3.1/include
    -o odbctest2.bin odbctest2.c
    -L/dbs/64bits/tds/0.83/lib -ltdsodbc


ODBC source:

[ftm_msvtest1_cobra]
Description     = SQL Server 2012
Trace           = No
Server          = cobra
Database        = msvtest1
Port            = 1680
TDS_Version     = 8.0



Output on the Linux 32b machine:

num cols: 2
fetch 1: rows fetched = 1
get data 1: len = 0

Output on the AIX 6.1 machine:

num cols: 2
fetch 1: rows fetched = 1
get data 1: len = 4095


Maybe this can help:

When I set a (valid) ClientCharset like ISO8859-15 or UTF-8, I get the
unexpected length of 4095, but with an invalid ClientCharset, I get zero.

!!! The length returned by SQLGetData() is in fact the size of the buffer
provided, minus one...


(sorry for the confusion)
Seb


On 03/18/2013 10:25 AM, Sebastien FLAESCH wrote:
> Sorry:
>
> After a closer look I don't use column-wise binding if a text/byte
> column is in the result set.
>
> Seb
>
> On 03/18/2013 10:04 AM, Sebastien FLAESCH wrote:
> > OK.
> >
> > FYI, I am using column-wise binding with:
> >
> >        rcode = SQLSetStmtAttr(m_hstmt_2, SQL_ATTR_ROW_BIND_TYPE,
> >                               (SQLPOINTER) SQL_BIND_BY_COLUMN, 
> > SQL_IS_UINTEGER);
> >
> >        rcode = SQLSetStmtAttr(m_hstmt_2, SQL_ATTR_ROW_ARRAY_SIZE,
> >                               (SQLPOINTER) ARRAY_SIZE, SQL_IS_UINTEGER);
> >
> >        rcode = SQLSetStmtAttr(m_hstmt_2, SQL_ATTR_ROWS_FETCHED_PTR,
> >                               (SQLPOINTER)&   (nrows), SQL_IS_UINTEGER);
> >
> > I will try to reproduce with a small sample.
> >
> > Seb
> >
> > On 03/16/2013 03:04 PM, Frediano Ziglio wrote:
> > > 2013/3/14 Sebastien FLAESCH<sf AT 4js.com>:
> > > > Hi,
> > > >
> > > > I have already reported an issue regarding SQLGetData()/iconv, see mails with 
> > > > title:
> > > >
> > > > "SQLGetData() returns TEXT length minus one on Solaris 64b with SQL Server 
> > > > 2005"
> > > >
> > > > But now we have a similar problem on AIX 6.1, still with version 0.92.377.
> > > >
> > > > After inserting an empty TEXT, we fetch it back (see mail history for 
> > > > details),
> > > > and SQLGetData() returns a len of 4095 bytes, when zero is expected.
> > >
> > > I downloaded 0.92.377, forced indirect conversion and checked
> > > SQLGetData with empty text (there is also a spcific test in getdata
> > > test but I enhanced it) but unfortunately was not able to reproduce
> > > your result (getting 4095 for an empty string). Could you help repro
> > > the case? Or I didn't understand your problem?
> > >
> > > > This occurs if no ClientCharset is defined in the ODBC data source...
> > > >
> > > > When setting:
> > > >
> > > >        ClientCharset = en_US.8859-15
> > > >
> > > > or:
> > > >
> > > >        ClientCharset = en_US.UTF-8
> > > >
> > > > The len returned by SQLGetData() is zero, as expected.
> > > >
> > > > Some news about this?
> > > >
> > > > As I suggested, if there is some iconv conversion issues, FreeTDS should stop
> > > > with a fatal error, to avoid any data loss / mis-conversion. It should not
> > > > silently send or return invalid character data.
> > > >
> > > > Seb
> > >
> > > Mmm... from my log
> > >
> > > 13:44:19.515397 (token.c:321):looking for login token, got  ad(LOGINACK)
> > > 13:44:19.515401 (token.c:357):server reports TDS version 71.0.0.1
> > > 13:44:19.515404 (token.c:359):Product name for 0x71000001 is 2000 SP1
> > > 13:44:19.515409 (token.c:391):Product version 89001388
> > > 13:44:19.515413 (token.c:321):looking for login token, got  e3(ENVCHANGE)
> > > 13:44:19.515417 (token.c:107):tds_process_default_tokens() marker is
> > > e3(ENVCHANGE)
> > > 13:44:19.515422 (token.c:2045):changing block size from 4096 to 4096
> > > 13:44:19.515426 (token.c:321):looking for login token, got  fd(DONE)
> > > 13:44:19.515430 (token.c:107):tds_process_default_tokens() marker is fd(DONE)
> > > 13:44:19.515435 (token.c:1905):tds_process_end: more_results = 0
> > >                    was_cancelled = 0
> > >                    error = 0
> > >                    done_count_valid = 0
> > > 13:44:19.515440 (token.c:1921):                rows_affected = 0
> > > 13:44:19.515443 (token.c:1924):tds_process_end() state set to TDS_IDLE
> > > 13:44:19.515448 (token.c:423):tds_process_login_tokens() returning TDS_SUCCESS
> > > 13:44:19.515467 (iconv.c:967):tds_iconv_get: what is charset "en_US.8859-15"?
> > > 13:44:19.515475 (odbc.c:1723):SQLAllocStmt(0x207f380, 0x606de0)
> > > 13:44:19.515479 (odbc.c:1621):_SQLAllocStmt(0x207f380, 0x606de0)
> > >
> > >
> > > As you reported another charset is silently used. Also strangely seems
> > > that client charset is readed after the login. Oh, probably cause is
> > > used only to convert data as now wide is the default and UTF-8 is used
> > > in libTDS. Yes, that is, disabling wide cause connection to fail using
> > > an invalid charset! So this is a regression of wide support in odbc.
> > >
> > > Frediano
> > > _______________________________________________
> > > 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

/*
 Author           : sf AT 4js.com
 FreeTDS version  : 0.92.377
 Platform         : AIX 6.1 64b
 Fetching a TEXT with default locale, first SQGetData() call returns
 a length of 4095 bytes, when 0 bytes are expected.
 */

#include <stdlib.h>
#include <stdio.h>
#include <string.h>

#include <sql.h>
#include <sqlext.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); \
   }

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

main(int argc,char **argv)
{
    SQLRETURN rcode;
    SQLHENV m_henv;
    SQLHDBC m_hdbc;
    SQLHSTMT m_hstmt;
    SQLHSTMT m_hstmt_2;
    int i,j;
    char * dbname;
    char * usernm;
    char * passwd;
#define ARRAY_SIZE 1   /* Expected, since we use SQLGetData()... */
    int v_k[ARRAY_SIZE];
    SQLLEN v_ind_1[ARRAY_SIZE];
    SQLLEN v_ind_2[ARRAY_SIZE];
    SQLULEN nrows;
    SQLSMALLINT ncols;
    SQLSMALLINT fc;
#define LOB_BLOCK_SIZE 4096
    char buf[LOB_BLOCK_SIZE];
    SQLLEN len;

    if (argc == 1) {
        dbname = "ftm_msvtest1_cobra";
        usernm = "msvuser";
        passwd = "fourjs";
    } else if (argc == 4) {
        dbname = argv[1];
        usernm = argv[2];
        passwd = argv[3];
    } else {
        fprintf(stderr,"Usage: %s dsn user pswd\n", argv[0]);
        exit(1);
    }

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

    rcode = SQLSetConnectAttr(m_hdbc,
               SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) SQL_AUTOCOMMIT_ON, SQL_IS_UINTEGER);
    CHECK_RCODE(SQL_HANDLE_ENV,m_henv,"SQLSetConnectAttr(autocommit)");

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

    SQLExecDirect(m_hstmt, (SQLCHAR *) "drop table mytab1", SQL_NTS);
    SQLExecDirect(m_hstmt, (SQLCHAR *) "create table mytab1 ( k int, t text)", SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLExecDirect create table failed");
    SQLExecDirect(m_hstmt, (SQLCHAR *) "insert into mytab1 values ( 1, '' )", SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLExecDirect insert 1 failed");

    rcode = SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &m_hstmt_2);
    CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLAllocHandle StmtH 1");

    rcode = SQLSetStmtAttr(m_hstmt_2, SQL_ATTR_CURSOR_SCROLLABLE,
                           (SQLPOINTER) SQL_NONSCROLLABLE,
                           SQL_IS_UINTEGER);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt_2,"SetStmtAttr NONSCROLLABLE");
    rcode = SQLSetStmtAttr(m_hstmt_2, SQL_ATTR_CURSOR_SENSITIVITY,
                           (SQLPOINTER) SQL_SENSITIVE, SQL_IS_UINTEGER);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt_2,"SetStmtAttr SENSITIVE");

    for (i = 0; i < ARRAY_SIZE; i++) {
        v_ind_1[i] = 0;
        v_ind_2[i] = SQL_DATA_AT_EXEC;
    }

    rcode = SQLPrepare(m_hstmt_2, "select k, t from mytab1", SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt_2,"SQLPrepare #1");

    rcode = SQLExecute(m_hstmt_2);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt_2,"SQLExecute #1");

    rcode = SQLNumResultCols(m_hstmt_2, &fc);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt_2,"SQLNumResultCols #1");
    fprintf(stdout, "num cols: %d\n", fc);

    rcode = SQLMoreResults(m_hstmt_2);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt_2,"SQLMoreResults #1");

    rcode = SQLSetStmtAttr(m_hstmt_2, SQL_ATTR_ROW_BIND_TYPE,
                           (SQLPOINTER) SQL_BIND_BY_COLUMN, SQL_IS_UINTEGER);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt_2,"SQLSetStmtAttr SQL_ATTR_ROW_BIND_TYPE");
    rcode = SQLSetStmtAttr(m_hstmt_2, SQL_ATTR_ROW_ARRAY_SIZE,
                           (SQLPOINTER) ARRAY_SIZE, SQL_IS_UINTEGER);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt_2,"SQLSetStmtAttr SQL_ATTR_ROW_ARRAY_SIZE");
    rcode = SQLSetStmtAttr(m_hstmt_2, SQL_ATTR_ROWS_FETCHED_PTR,
                           (SQLPOINTER) & (nrows), SQL_IS_UINTEGER);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt_2,"SQLSetStmtAttr SQL_ATTR_ROWS_FETCHED_PTR");

    rcode = SQLBindCol(m_hstmt_2, 1, SQL_C_LONG, v_k, sizeof(v_k), v_ind_1);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt_2,"SQLBindCol 1");
    rcode = SQLBindCol(m_hstmt_2, 2, SQL_C_CHAR, NULL, 0, v_ind_2);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt_2,"SQLBindCol 1");

    rcode = SQLFetchScroll(m_hstmt_2, SQL_FETCH_NEXT, 0);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt_2,"SQLFetch #1 failed");
    fprintf(stdout, "fetch 1: rows fetched = %d\n", nrows);

    rcode = SQLGetData(m_hstmt_2, 2, SQL_C_CHAR, (SQLPOINTER) buf, sizeof(buf), &len);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt_2,"SQLGetData #1 failed");
    fprintf(stdout, "get data 1: len = %ld\n", len);


    rcode = SQLMoreResults(m_hstmt_2);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt_2,"SQLMoreResults #2");

    rcode = SQLFreeHandle(SQL_HANDLE_STMT, (SQLHANDLE) m_hstmt_2);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt_2,"SQLFreeHandle");

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

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

}