| Subject: | Additional info for #43451 |
| Date: | Mon, 23 Feb 2009 08:49:09 -0700 |
| To: | <bug-DBD-ODBC [...] rt.cpan.org> |
| From: | "Flynn, William - Fort Collins, CO" <William.Flynn [...] ftc.usda.gov> |
Last week I reported this problem:
Inserting more than 8,000 characters into SQL Server varchar(max) field
gives "Invalid precision value" error
It was assigned #43451.
I was able to further characterize this problem and have additional
information and sample code that should help with troubleshooting.
There are two variables involved:
Parameter value length: <= 8,000 bytes or > 8,000 bytes
Parameter value binding: Yes or No
<= 8,000 chars > 8,000 chars
No binding Works Works
Binding Works Fails
To reproduce the problem create a SQL Server database named MyDB with a
single table named MyTable with a single column named BigData of type
varchar(max).
Execute this Perl code:
use DBI;
my $eightThousandChars .=
'12345678901234567890123456789012345678901234567890123456789012345678901
234567890'x(100);
my $eightThousandAndOneChars = $eightThousandChars . "1";
my $dbh = DBI->connect("DBI:ODBC:Driver={SQL Native
Client};Server=localhost;Database=MyDB;Trusted_Connection=yes;");
# Insert 8,000 characters into a varchar(max) without parameter value
binding.
# This works.
my $qry = "insert into MyTable values ('$eightThousandChars')";
my $insh = $dbh->prepare($qry);
my $rowcount = $insh->execute();
# Insert 8,001 characters into a varchar(max) without parameter value
binding.
# This works.
$qry = "insert into MyTable values ('$eightThousandAndOneChars')";
$insh = $dbh->prepare($qry);
$rowcount = $insh->execute();
# Insert 8,000 characters into a varchar(max) with parmeter value
binding.
# This works.
my @data = ($eightThousandChars);
$qry = "insert into MyTable values (?)";
$insh = $dbh->prepare($qry);
$rowcount = $insh->execute(@data);
# Insert 8,001 characters into a varchar(max) with parameter value
binding.
# This fails with: Invalid precision value (SQL-HY104)
@data = ($eightThousandAndOneChars);
$qry = "insert into MyTable values (?)";
$insh = $dbh->prepare($qry);
$rowcount = $insh->execute(@data);
The very last execute will fail.
Bill...
Bill Flynn (Vistronix Contractor)
Senior Programmer (.NET, C#)
USDA NRCS ITC
2150 Centre Ave., Building A, Suite 231
Fort Collins, CO 80526-8121
(970) 295-5613 (voice)
william.flynn@ftc.usda.gov