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

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


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