FreeTDS Development Group

["Michael Koehmstedt, RenPet" <mike AT renpetllc.com>]

Thank you Frediano, with your info I simplified my code to the
following, and I can confirm the char(1) is still handled properly:

          /* Handle strings and binary value in a typical fashion. An
extra byte for the buffer is allocated to ensure EOL values are
captured */
            if ( col_type == SQL_C_CHAR || col_type == SQL_C_BINARY )
            {
                buf = (char *) palloc(sizeof(char) * col_size + 1);
                ret = SQLGetData( stmt, i, SQL_C_CHAR, buf, sizeof(char)
* col_size + 1, &indicator );
            }
            /* Otherwise (at least in my simple application) we are
dealing with a numerical value. Ignore the col_size which may not be
wide enough to store the string equivalent. The value of
sizeof(long)*8+1 comes from the C function ltoa() */
            else
            {
                                buf = (char *) palloc(sizeof(long) * 8 + 1);
                                ret = SQLGetData( stmt, i, SQL_C_CHAR, buf, 
sizeof(long) * 8 + 1,
&indicator );
            }

On Sun, Nov 4, 2012 at 2:00 AM, Frediano Ziglio <freddy77 AT gmail.com> wrote:
> 2012/11/3 Michael Koehmstedt <mykillk AT gmail.com>:
>> I was able to resolve my issue with odbc_fdw. The problem was
>> SQLGetData( ). When passed SQL_C_CHAR, it does not properly handle a
>> char(1) column, probably because the EOL character is missing. It will
>> always return zero as the value.
>>
>> The old code:
>>
>>             buf = (char *) palloc(sizeof(char) * col_size);
>>             ret = SQLGetData(stmt, i, SQL_C_CHAR, buf, sizeof(char) *
>> col_size, &indicator);
>>
>
> One of the problems here is that you have to account for NUL
> terminator so for a CHAR(X) you should allocate X+1 char. This not
> taking into account that there could be a conversion causing the
> string length to increase.
>
> Frediano
>
>>
>> The new code:
>>
>>             /* Workaround for improper handling of char(1) columns */
>>             if ( col_size == 1 && col_type == SQL_C_CHAR )
>>             {
>>                 buf = (char *) palloc(sizeof(char) * 2);
>>                 ret = SQLGetData( stmt, i, SQL_C_BINARY, buf,
>> sizeof(char) * 2, &indicator );
>>                 *(buf+1) = '\0';
>>             }
>>             /* If the data is binary, or a C-String, retrieve the data
>> as binary, as conversion is unnecessary */
>>             else if ( col_type == SQL_C_BINARY || col_type == SQL_C_CHAR )
>>             {
>>                 buf = (char *) palloc(sizeof(char) * col_size);
>>                 ret = SQLGetData(stmt, i, SQL_C_BINARY, buf,
>> sizeof(char) * col_size, &indicator);
>>             }
>>             /* Otherwise, the data needs to be converted to a string.
>> The col_size is ignored
>>                because the column size of an integer might not be wide
>> enough to store the string
>>                equivalent. The max size of sizeof(long)*8+1 is the
>> same value used by the C function
>>                ltoa() which can convert any 4-byte integer into the
>> proper string equivalent */
>>             else
>>             {
>>                 buf = (char *) palloc((sizeof(long)*8+1));
>>                 ret = SQLGetData(stmt, i, SQL_C_CHAR, buf,
>> (sizeof(long)*8+1), &indicator);
>>             }
>>
>> If anyone sees any potential issues with this fix, I'm all ears. I'm
>> really new to the whole SQL / ODBC world so I'm afraid this "fix"
>> might be a bit hacky.
>>
>> Regards,
>> Michael Koehmstedt
>>
>> On Thu, Nov 1, 2012 at 7:29 PM, Michael Koehmstedt <mykillk AT gmail.com> 
>> wrote:
>>> I have been running into a similar problem today as described in this 
>>> thread.
>>>
>>> I am using FreeTDS, Unix ODBC, and ODBC-FDW to be able to access my
>>> Microsoft SQL Server 2008 through my PostgreSQL 9.1 database.
>>> Everything has been successful so far, except that one column always
>>> shows up as an empty column when queried.
>>>
>>> I was getting the same issue when using bsqlodbc, it was coming up
>>> empty. Then I saw Frediano's post saying that he had fixed the problem
>>> in the GIT repo. I confirmed that my problematic char(1) column now
>>> displays properly in bsqlodbc after compiling the latest FreeTDS off
>>> GIT.
>>>
>>> Since bsqlodbc properly queries the column, I'm almost sure the
>>> problem lies somewhere within ODBC-FDW, and that the issue is very
>>> similar to the issue with bsqlodbc.
>>>
>>> So my question, what exactly was changed in the bsqlodbc source to
>>> allow it to properly map a text column?
>>>
>>>
>>> Regards,
>>> Michael Koehmstedt
>>>
>>>
>>>
>>>> Frediano Ziglio
>>>> Fri Sep 28 03:00:33 EDT 2012
>>>>
>>>>
>>>> I wrongly pushed a patch that fix some issue with characters and
>>>> bsqlodbc in Branch-0.91.
>>>>
>>>> Wrongly just cause was not correctly tested, I reverted some changes.
>>>>
>>>> Mainly:
>>>> - if you want just to get information from row you should get row
>>>> implementation descriptor, not create a new one as the new one would
>>>> have empty information
>>>> - SQLAllocHandle for descriptor wants a connection, not a statement,
>>>> this is the reason for the invalid handle returned
>>>> - SQLFetch can return SQL_NO_TOTAL (usually -4) if can't translate all
>>>> string to the check data[c].len > 0 can be false, I increased buffer
>>>> allocated for string however if string is empty the assert will still
>>>> raise. It would be better to handle correctly SQL_NO_TOTAL.
>>>>
>>>> Frediano
>>>
>>> --
>>> Michael Koehmstedt
>>
>>
>>
>> --
>> Michael Koehmstedt
>> _______________________________________________
>> 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