Re: ODBC - SQL Server image data problems with recent releases of ODBC

horace.redelmyer@xxxxxxxxx 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
.

Relevant Pages

  • Re: Linked Server Query Fails
    ... QODBC is an ODBC driver that was developed to communicate ... give me the power of SQL server to then manipulate the data as I need to do. ...
    (microsoft.public.data.oledb)
  • Re: Using Access to view and edit SQL Server table with Bigint primary key
    ... Your problem has been an ongoing problem between Access and sql server ... In the past I used to play down ODBC because I had ... and the easiest workaround is to use ... ADO to edit your data. ...
    (comp.databases.ms-access)
  • Re: Cant remove SQL native client ODBC connection
    ... NEVER install a beta, CTP or release candidate on any system that can't be formatted. ... Next, the SNAC provider is an OLE DB interface, not ODBC. ... Hitchhiker's Guide to Visual Studio and SQL Server ... "The setup routines for the SQL Server Native Client 10.0 ODBC driver could ...
    (microsoft.public.sqlserver.connect)
  • Re: different mdac, different sql server behaviour
    ... ODBC is part of MDAC and you are using the SQL Server ODBC driver. ...
    (microsoft.public.sqlserver.server)
  • Re: Linked Server via ODBC - help!
    ... Make sure the driver is compatible with the MDAC version on ... the SQL Server box. ... The ODBC points to an 'Alchemy' database and uses an Alchemy driver ...
    (microsoft.public.sqlserver.connect)
Loading