Re: Fwd: [unixODBC-support] PHP PDO unixODBC FreeTDS SQL Server text column size

2012/11/3 Michael Koehmstedt <[email protected]>:
> 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 <[email protected]> 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
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds