Skip to Content.
Sympa Menu

freetds - Re: [freetds] SQLGetData() returns len 4095 for empty TEXT on AIX 6.1 without ClientCharset defined

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Sebastien FLAESCH <sf AT 4js.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] SQLGetData() returns len 4095 for empty TEXT on AIX 6.1 without ClientCharset defined
  • Date: Mon, 18 Mar 2013 10:50:19 +0100

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

}





Archive powered by MHonArc 2.6.24.

Top of Page