[freetds] FreeTDS, Perl, unicode

[Brian Johnson]

Hey Folks -

First off, all my details:

Microsoft SQL Server 2008
Ubuntu 12.04
Perl 5.14.2
DBI 1.616
DBD::ODBC 1.43
FreeTDS 0.91-1
unixODBC 2.2.14

I'm using perl/freetds to connect to a Microsoft SQL server at a remote
location. The server is not controlled by me, I have read-only access,
nothing more. My perl script connects using:

DBI->connect
("dbi:ODBC:DRIVER={FreeTDS};Server=IPOFHOST;Port=1433;DATABASE=NAMEOFDATABASE;OpenMode=0"
);

I'm able to connect, and run queries on some tables. But, there is a
table that has some NCHAR columns. One of the columns contains some
unicode. When I select from that table I get the following error:

DBD::ODBC::st fetchrow_hashref failed: st_fetch/SQLFetch (long truncated
DBI attribute LongTruncOk not set and/or LongReadLen too small) (SQL-HY000)

This is using TDSVER=7.0 and I have also tried TDSVER=8.0. Anything
lower than 7.0 fails to connect. The table only has 3 rows in it, so
I'm able to easily figure out that the row causing the problem has the
following without the quotes:

"CPT ? "

Setting LongTruncOK lets me run queries, but that is not an acceptable
solution in this case. I've tried setting LongReadLen to something
insanely huge - 500000000 - but that failed with the same error.

I've given tsql a try, both with TDSVER=7.0 and 8.0, and while it
returns the rows from the table, the row with the unicode character has
some funky characters and doesn't appear totally correct.

I'm stumped, and have never really had luck with unicode/freetds/perl,
so I thought I'd reach out and pray that some graceful person would have
mercy on me. :)

Thanks folks!

-Brian

[Brian Johnson]

Completely forgot to mention I had done exactly that. I didn't see
anything in it that jumped out at me, though I admit it's a bit greek to me.

I don't see any user/pass info in it, so if needed I could attach the
log. Though I do see "SQLExecute returns SQL_SUCCESS".

Thanks.

-Brian

Post by Brian Johnson
I'm stumped, and have never really had luck with unicode/freetds/perl,
so I thought I'd reach out and pray that some graceful person would have
mercy on me. :)

The first thing i would suggest doing is setting the TDSDUMP environment
variable as documented at
http://www.freetds.org/userguide/logging.htm and examining the output of
the dump file to see what it says is going over the wire.

[James K. Lowden]

On Mon, 11 Mar 2013 02:42:32 -0700

Post by Brian Johnson
I don't see any user/pass info in it, so if needed I could attach the
log. Though I do see "SQLExecute returns SQL_SUCCESS".

You can post a log here. A TDS 7+ dump won't contain user
credentials.

You're sure the client encoding is set up correctly, and the Perl knows
what to expect?

Test other than TDSDUMP: Besides using Perl, you might try with
bsqldb, because if it behaves the same way you have either a setup
problem or a bug, and with sqsh, because sqsh also uses the ct-lib
API.

HTH.

--jkl

[Brian Johnson]

Log file is attached.

I'll confess, this whole unicode thing is new to me so I'm not certain
*how* to set the client encoding. Is there some docs anybody can recommend?

I'll give the other items you tried a shot when I get going in the
morning.

Thanks for the assistance folks, much appreciated!

-Brian

Post by James K. Lowden
On Mon, 11 Mar 2013 02:42:32 -0700

Post by Brian Johnson
I don't see any user/pass info in it, so if needed I could attach the
log. Though I do see "SQLExecute returns SQL_SUCCESS".

You can post a log here. A TDS 7+ dump won't contain user
credentials.
You're sure the client encoding is set up correctly, and the Perl knows
what to expect?
Test other than TDSDUMP: Besides using Perl, you might try with
bsqldb, because if it behaves the same way you have either a setup
problem or a bug, and with sqsh, because sqsh also uses the ct-lib
API.
HTH.
--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: freetds.log
Url: http://lists.ibiblio.org/pipermail/freetds/attachments/20130311/58ae1bb8/attachment-0001.pl

[James K. Lowden]

On Mon, 11 Mar 2013 04:31:41 -0700

Post by Brian Johnson
Log file is attached.
I'll confess, this whole unicode thing is new to me so I'm not
certain *how* to set the client encoding. Is there some docs anybody
can recommend?

I recommend the appendix in the UG for background information. :-)

Post by Brian Johnson
I'll give the other items you tried a shot when I get going in the
morning.

Forget sqsh; thought you were using DBD::Sybase. You're using
DBD::ODBC, which surely should work. Try the query with bsqlodbc. It
should be very easy, e.g.

$ bsqlodbc -S $S <<< 'select cast(min(name) as
varchar (30)) as name from systypes'

I'm not able to try that myself right now. My installed version of
FreeTDS fails with an old error, and atm I can't compile the master
branch.

Your log file says

iconv.c:349:setting up conversions for client charset "UTF-8"

so Unicode should be presented to the client as UTF-8. When it gets
the metadata, is says

Post by Brian Johnson
colname = bill_cd_typ_desc (16 bytes)
type = 47 (char)
server's type = 239 (x UCS-2 char)
column_varint_size = 2
column_size = 20 (20 on server)

which seems to indicate that the UCS-2 NCHAR column is being converted
to CHAR. Then when you read the row (2 rows, in fact)

Post by Brian Johnson
token.c:555:processing result tokens. marker is d1(ROW)
token.c:2304:tds_process_row(): reading column 0
token.c:2049:tds_get_data: type 47, varint size 2
token.c:2110:tds_get_data(): wire column size is 20

I don't see iconv being invoked to convert to UTF-8. But I'm not sure
I should....

--jkl

Post by Brian Johnson

Post by James K. Lowden
On Mon, 11 Mar 2013 02:42:32 -0700

Post by Brian Johnson
I don't see any user/pass info in it, so if needed I could attach
the log. Though I do see "SQLExecute returns SQL_SUCCESS".

You can post a log here. A TDS 7+ dump won't contain user
credentials.
You're sure the client encoding is set up correctly, and the Perl
knows what to expect?
Test other than TDSDUMP: Besides using Perl, you might try with
bsqldb, because if it behaves the same way you have either a setup
problem or a bug, and with sqsh, because sqsh also uses the ct-lib
API.
HTH.
--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

[David Dick]

Post by Brian Johnson
I'm stumped, and have never really had luck with unicode/freetds/perl,
so I thought I'd reach out and pray that some graceful person would have
mercy on me. :)

The first thing i would suggest doing is setting the TDSDUMP environment
variable as documented at
http://www.freetds.org/userguide/logging.htm and examining the output of
the dump file to see what it says is going over the wire.