New forum topics

Active forum topics

Perl DBD::ODBC 1.46_2 released - You REALLY need to test this release

17 December, 2013 - 20:04 — martinjevans

I've just uploaded DBD::ODBC 1.46_2 to the CPAN. In the process of writing Some Common Unicode Problems and Solutions using Perl DBD::ODBC and MS SQL Server and github repo I discovered a serious bug in the way DBD::ODBC can attempt to insert unicode characters into char/varchar/longvarchar columns. This experimental release fixes that issue but it does mean this release contains a significant change in behaviour. Since 1.46_1 yet another unicode fix was added too.

The issue ONLY applied to unicode builds of DBD::ODBC (the default on Windows) and enabled in Linux/Unix via the -u switch to Makefile.PL.

The problem was that when inserting parameter data into char/varchar/longvarchar columns DBD::ODBC ignored what your parameter actually looked like and simply bound the parameter as the type described by the database (SQL_CHAR). This meant that if you bound data was unicode, the separate octets of the perl UTF-8 encoded data would be inserted instead of the unicode characters. A simple example illustrates this easiest:

Say you had a unicode euro in a perl scalar. This is U+20AC and is encoded in UTF-8 as 0xe2,0x82,0xc2. If you inserted into a char/varchar/longvarchar the database would receive it as 3 separate chrs instead of 1 i.e., select len(mycol) from mytable would return 3 instead of 1.

There are a few situations when this did not apply 1) if you overrode the bind type with SQL_WVARCHAR 2) if your ODBC driver did not support SQLDescribeParam or you told DBD::ODBC not to use it.

A new test (45_unicode_varchar.t which has high verbosity set right now) has been added to the test suite. Unfortunately, this test only runs to MS SQL Server right now. If this test does not pass for you please report it and the output to me as soon as possible.

You are strongly advised to test this release with your development environment as I've not implemented a deprecation policy for this change as yet. I'm hoping to release a full version as 1.46_2 is, BUT if it is reported to me that this will cause too many people problems I'll reconsider.

When built with unicode support and odbc_old_unicode is not enabled
columns reported as SQL_LONGVARCHAR were not by default bound as
SQL_WCHAR and hence were not returned correctly unless the bind was
overridden.

[MISCELLANEOUS]

Added test 90_trace_flag.t

=head2 1.46_1 2013-11-16

[CHANGE IN BEHAVIOUR]

As warned in release 1.45, the binding of unicode parameters to
char/varchar columns has changed significantly. If you don't attempt
to insert unicode into char/varchar columns or if you only inserted
unicode into nchar/nvarchar columns you should see no difference.
From this release, unicode data inserted into
char/varchar/longvarchar columns is bound as SQL_WCHAR and not
whatever the driver reports the parameter as (which is mostly
SQL_CHAR).

Previously if DBD::ODBC received an error or (SQL_SUCCESS_WITH_INFO)
from an ODBC API call and then the driver refused to return the
error state/text DBD::ODBC would issue its own error saying "Unable
to fetch information about the error" and state IM008. That state
was wrong and has been changed to HY000.

[BUG FIXES]

Some drivers cannot support catalogs and/or schema names in
SQLTables. Recent changes set the schema/catalog name to the empty
string (good reasons below) which causes "optional feature not
implemented" from MS Access (which does not support schemas - even
for a simply ping (which uses SQLTables)). Now we call
SQLCATALOG_NAME and SQLSCHEMA_USAGE on connect to ascertain support
which modifies SQLTables call.