Over the past few days Jeff, Martin (from UK based EasySoft) and I have
been working out a problem with an MS SQL server, the easysoft ODBC Bridge
Software and DBI::ODBC drivers. This is the result of what we found for
those out there who may have this same errors.
When I do 'make test' I get the following errors.... any suggestions as to
the cause?
-------------------------------------------------------
[lance@alyssa DBD-ODBC-1.00]# make test TEST_VERBOSE=1
PERL_DL_NONLAZY=1 /usr/local/bin/perl "-MExtUtils::Command::MM" "-e"
"test_harness(1, 'blib/lib', 'blib/arch')" t/*.t
t/01base............1..5
ok 1
ok 2
ok 3
ok 4
ok 5
ok
t/02simple..........1..17
ok 1
Test 2: connecting to the database
ok 2
Test 3: create test table
DBD::ODBC::st fetchrow failed: [unixODBC][][Microsoft][ODBC SQL Server
Driver]String data, right truncation (SQL-01004)(DBD: st_fetch/SQLFetch
(long truncated) err=-1) at t/ODBCTEST.pm line 56.
----------------------------------------------------------------------------
This is what we discovered:
Jeff Urlwin: In the SQL Server connection settings, you must have: use
Regional settings when outputting currency, numbers, dates and times. Take
that off.
Martin: This is a belter of a feature in MS SQL Server. I knew of its
existence but had
not thought of the implication of turning it on for metadata calls generating
result-sets.
BTW, if you want to leave "Use Regional Settings" turned on (say because you
use the DSN from multiple apps some of which display data where you want the
regional settings) you can do this instead:
export
DBI_DSN='dbi:ODBC:DSN=mydsn;UID=dbuser;PWD=dbpassword;Regional=No;'
or put this in the connections string passed to DBI->connect.
Jeff, I understand why DBD::ODBC binds the columns as SQL_C_CHAR but in this
case binding as SQL_C_LONG/SQL_C_SHORT would have worked. When the column is
retrieved as SQL_C_CHAR SQL Server is expecting you to display the data (as
the
help on Use Regional Settings describes).
This is undoubtedly a bug in SQL Server driver as SQLDescribeCol for
COLUMN_SIZE (column 3) returned 10, you used a buffer of 12 bytes to hold the
result and it attempted to return "2,147,483,647.00" which needs 17 bytes!!!!
At the very least when Use Regional Settings is enabled it should adjust the
display size in SQLDescribeCol.
Good find all the same - this is definitely going in my FAQ.
--------------------------
This is also detailed as a FAQ on the Easysoft website at:
http://www.easysoft.com/products/9999/faq_answer.phtml?ID=716&product=2002
Thanks again for both of your help.
Lance Wilson
President
WebGuy Internet, Inc.
