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

[Michael Koehmstedt]

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.
- 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]

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

Post by Michael Koehmstedt
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.
- 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

[Frediano Ziglio]

Post by Michael Koehmstedt
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.
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

Post by Michael Koehmstedt
/* 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

Post by Michael Koehmstedt
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.
- 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

[Michael Koehmstedt, RenPet]

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

Post by Frediano Ziglio

Post by Michael Koehmstedt
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.
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

Post by Michael Koehmstedt
/* 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

Post by Michael Koehmstedt
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.
- 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