unixODBC/FreeTDS results truncated to 255 character

Question

UPDATE 2

I turned on tracing and ran my sample query. Here is the trace. I do see the statement Strlen Or Ind = 0x7fff9c84ee88 -> 255. The indicator variable is defined as SQLLEN indicator; Is this not initialized properly?

[ODBC][22407][1379343424.503572][__handles.c][450]
        Exit:[SQL_SUCCESS]
            Environment = 0x14f8160
[ODBC][22407][1379343424.503627][SQLSetEnvAttr.c][182]
        Entry:            
            Environment = 0x14f8160            
            Attribute = SQL_ATTR_ODBC_VERSION            
            Value = 0x3            
            StrLen = 0
[ODBC][22407][1379343424.503654][SQLSetEnvAttr.c][349]
        Exit:[SQL_SUCCESS]
[ODBC][22407][1379343424.503678][SQLAllocHandle.c][364]
        Entry:
            Handle Type = 2
            Input Handle = 0x14f8160
[ODBC][22407][1379343424.503707][SQLAllocHandle.c][482]
        Exit:[SQL_SUCCESS]
            Output Handle = 0x14f8a90
[ODBC][22407][1379343424.503745][SQLDriverConnect.c][688]
        Entry:            
            Connection = 0x14f8a90            
            Window Hdl = (nil)            
            Str In = [DSN=MyDB;UID=MyUID;][length = 15 (SQL_NTS)]            
            Str Out = 0x7fff9c84cc80            
            Str Out Max = 2048            
            Str Out Ptr = (nil)            
            Completion = 1
        UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'

[ODBC][22407][1379343424.523244][SQLDriverConnect.c][1497]
        Exit:[SQL_SUCCESS]                    
            Connection Out [[DSN=MyDB;UID=MyUID;][length = 15 (SQL_NTS)]]
[ODBC][22407][1379343424.523297][SQLAllocHandle.c][529]
        Entry:
            Handle Type = 3
            Input Handle = 0x14f8a90
[ODBC][22407][1379343424.523343][SQLAllocHandle.c][1064]
        Exit:[SQL_SUCCESS]
            Output Handle = 0x1526b40
[ODBC][22407][1379343424.523377][SQLExecDirect.c][236]
        Entry:            
            Statement = 0x1526b40            
            SQL = [SELECT '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890...][length = 309 (SQL_NTS)]
[ODBC][22407][1379343424.523948][SQLExecDirect.c][499]
        Exit:[SQL_SUCCESS]
[ODBC][22407][1379343424.523982][SQLNumResultCols.c][152]
        Entry:            
            Statement = 0x1526b40            
            Column Count = 0x7fff9c84eeae
[ODBC][22407][1379343424.524005][SQLNumResultCols.c][244]
        Exit:[SQL_SUCCESS]                
            Count = 0x7fff9c84eeae -> 1
[ODBC][22407][1379343424.524030][SQLFetch.c][158]
        Entry:            
            Statement = 0x1526b40
[ODBC][22407][1379343424.524056][SQLFetch.c][340]
        Exit:[SQL_SUCCESS]
[ODBC][22407][1379343424.524084][SQLGetData.c][233]
        Entry:            
            Statement = 0x1526b40            
            Column Number = 1            
            Target Type = 1 SQL_CHAR            
            Buffer Length = 5000            
            Target Value = 0x7fff9c84da90            
            StrLen Or Ind = 0x7fff9c84ee88
[ODBC][22407][1379343424.524115][SQLGetData.c][497]
        Exit:[SQL_SUCCESS]                
            Buffer = [12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678]                
            Strlen Or Ind = 0x7fff9c84ee88 -> 255
[ODBC][22407][1379343424.524142][SQLColAttribute.c][286]
        Entry:            
            Statement = 0x1526b40            
            Column Number = 1            
            Field Identifier = SQL_DESC_NAME            
            Character Attr = 0x7fff9c84ee20            
            Buffer Length = 100            
            String Length = 0x7fff9c84ee86            
            Numeric Attribute = (nil)
[ODBC][22407][1379343424.524167][SQLColAttribute.c][657]
        Exit:[SQL_SUCCESS]
[ODBC][22407][1379343424.524229][SQLFetch.c][158]
        Entry:            
            Statement = 0x1526b40
[ODBC][22407][1379343424.524257][SQLFetch.c][340]
        Exit:[SQL_NO_DATA]
[ODBC][22407][1379343424.524321][SQLDisconnect.c][204]
        Entry:            
            Connection = 0x14f8a90
[ODBC][22407][1379343424.524375][SQLDisconnect.c][341]
        Exit:[SQL_SUCCESS]
[ODBC][22407][1379343424.524415][SQLFreeHandle.c][279]
        Entry:
            Handle Type = 2
            Input Handle = 0x14f8a90
[ODBC][22407][1379343424.524438][SQLFreeHandle.c][330]
        Exit:[SQL_SUCCESS]
[ODBC][22407][1379343424.524463][SQLFreeHandle.c][212]
        Entry:
            Handle Type = 1
            Input Handle = 0x14f8160

UPDATE I investigated my C++ program further. I now see that the query result is being truncated in the call to

data_ret = SQLGetData(stmt, i, SQL_C_CHAR, buf, sizeof(buf), &indicator);

buf is of sufficent length and data_ret is zero (success). Am I misusing SQLGetData, or does it have some behavior I am unaware of?

I have seen others with a similar problem, but I haven't quite been able to sort it out. If I query for a long string it gets truncated to 255 characters. I am querying a SQL Server DB from a bash script on a Linux machine using unixODBC and FreeTDS.

I set up the driver by putting

[FreeTDS]
Description = v0.91 with protocol v7.2
Driver = /usr/lib64/libtdsodbc.so.0

in a template and running

odbcinst -i -d -f tds.driver.template

I then put

[MyDB]
Driver = FreeTDS
Description = Database Description
Trace = No
Server = <serverIP>
Port = 1433
Database = <myDB>
UID = <myUID>

in a template and run

odbcinst -i -s -f tds.datasource.template

I tried this answer, but I must be doing something wrong. Any suggestions are appreciated.

score 0 · Answer 1 · answered Sep 10 '13 at 15:21

0

You don't say what you are using to issue the query from the shell but if it is isql, then it hides very big columns and truncates smaller columns - probably at 255.

answered Sep 10 '13 at 15:21

bohica

5,932
3
23
28

I have tried it two way and I see the same truncation behavior in both; when I use isql and inside a C++ program I wrote that uses the `sql.h` and `sqlext.h` headers. I had read that isql might be the cause, but I thought the C++ way would have at least worked. – dseiple Sep 10 '13 at 18:39
Then I guess you'll have to show the code as I know isql will truncate but if coded correctly a C/C++ program should not. Another way for you to debug this is to enable unixodbc tracing in your odbcinst.ini file and rerun the c++ code then examine the unixodbc trace file. Add [ODBC]\nTrace = yes\nTraceFile = trace_file_path\n to your odbcinst.ini file and rerun c++ code. Examine log or paste here. – bohica Sep 10 '13 at 20:31
Sorry, I didn't responded sooner. I got pulled off this for a few days. I've updated my question. – dseiple Sep 16 '13 at 14:31
Your SQLGetData call looks ok so long as buf is big enough (which we cannot tell from what you've given us. I would still enable unixodbc tracing to see if there is a clue in there. – bohica Sep 16 '13 at 14:46
The trace shows `Strlen Or Ind = 0x7fff9c84ee88 -> 255`. See my update. – dseiple Sep 16 '13 at 16:38
I'd add a call to SQLDescribeCol after the SQLNumResultCols and see what it comes back as. – bohica Sep 17 '13 at 08:00

unixODBC/FreeTDS results truncated to 255 character

1 Answers1

Linked