horace.redelm...@gmail.com wrote:
I have a Perl program that writes binary data to a SQL Server image
field (column of type image) using DBD::ODBC.
This works fine in Perl 5.8.8 (ActivePerl build 822)
($DBD::ODBC::VERSION = '1.13';) I am using Windows XP and SQL Server
2005 and using the "SQL Server" ODBC driver).
However, there are two problems with build 824 with an upgraded ODBC
or with ActivePerl 5.10.0 build 1004. (dbd::odbc version 1.18)
First - data written to the image field is being truncated, i.e. not
all of the data shows up in the database. Writing the data to a file
at the same time as writing to the database shows that program is
still generating the data correctly, so the fault is with DBI or
DBD::ODBC. The data is built with pack, so the UTF-8 flag shouldn't be
a problem.
Second - on reading the data, ODBC thinks the size is longer than it
actually is and gives me right truncation errors unless I increase the
long read length by a factor of 10.
Does anyone have any clue as to what might be happening here for me.
Any help would really be appreciated.
For the example I am having problems with, the data is about 600K in
size.
Thanks
Horace
I may know what the problem is. A comment from a recentish change I made
to DBD::ODBC:
/*
* The following code is a workaround for a problem in SQL Server
* when inserting more than 400K into varbinary(max) or varchar(max)
* columns. The older SQL Server driver (not the native client driver):
*
* o reports the size of xxx(max) columns as 2147483647 bytes in size
* when in reality they can be a lot bigger than that.
* o if you bind more than 400K you get the following errors:
* (HY000, 0, [Microsoft][ODBC SQL Server Driver]
* Warning: Partial insert/update. The insert/update of a text or
* image column(s) did not succeed.)
* (42000, 7125, [Microsoft][ODBC SQL Server Driver][SQL Server]
* The text, ntext, or image pointer value conflicts with the column
* name specified.)
*
* There appear to be 2 workarounds but I was not prepared to do the
first.
* The first is simply to set the indicator to
SQL_LEN_DATA_AT_EXEC(409600)
* if the parameter was larger than 409600 - miraculously it works but
* shouldn't according to MSDN.
* The second workaround (used here) is to set the indicator to
* SQL_LEN_DATA_AT_EXEC(0) and the buffer_length to 0.
*
*/
If you can provide me with a failing example, code, data and schema I
will look in to it. Also, it would be useful to know the version of SQL
Server you are using and the ODBC driver name and version.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
