Skip Menu |
 

This queue is for tickets about the DBD-ADO CPAN distribution.

Report information
The Basics
Id: 83665
Status: open
Priority: 0/
Queue: DBD-ADO

People
Owner: Nobody in particular
Requestors: thejenkins [...] live.com
Cc:
AdminCc:

Bug Information
Severity: Important
Broken in: 2.99
Fixed in: (no value)



Subject: prepare() ed sql fails if bind_parm length greater than first call
Download (untitled) / with headers
text/plain 795b
Initially reported to DBI queue (#83663), but they ruled this most likely an ADO issue since it did not replicate in Postgre. Simple MS SQL Server database (2008R2). Running under Windows 7 64b Enterprise, using ActiveState Perl 5.10.1 x86, DBI 1.622, DBD-ADO 2.99. I am using ADO with a SQLOLEDB connect string, so I suppose the issue could be in DBD-ADO. create table GlobalOptions( OptionName varchar(255), OptionValue varchar(MAX)) go Insert into GlobalOptions('SECFIN_SERVER','servername') go Insert into GlobalOptions('SECFIN_UID','batch') go Then run the attached perl script. If you reverse the order of the calls to GetGlobalOption, it works as expected. Thanks for taking a look! Will be glad to provide a WebEx session to demostrate issue in my environemnt if desired. Tom
Subject: DBI-Err.PL
Download DBI-Err.PL
text/x-perl 2.4k
# ############################################# # # # U S E # # # ############################################# # use Modern::Perl ; no warnings 'uninitialized' ; use Data::Dumper ; use DBI qw(:sql_types) ; my $dbh ; my $sth ; my $SQL ; my $SQL_SERVER='servernamehere' ; my $SQL_DB='yourdbname here' ; # ##################################################################### # G e t G l o b a l O p t i o n # ##################################################################### # sub GetGlobalOption($) { my($option_name) = @_ ; my($OptionValue) ; # $sth->bind_param(1, $option_name, SQL_VARCHAR) ; if(!($sth->execute())) { print STDERR "GetGlobalOption(): unable to execute against statement handle [sth_GetGlobal] DBI::errstr:[$DBI::errstr]\n" ; exit(1) ; } ; $sth->bind_columns(\$OptionValue) ; while($sth->fetch()) { print "$OptionValue " ; } ;#while $sth->finish() ; return $OptionValue ; } ; #GetGlobalOption # if(!($dbh = DBI->connect("dbi:ADO:Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=$SQL_SERVER;Initial Catalog=$SQL_DB", { PrintError => 0, RaiseError => 1}))) { print STDERR "\n\nCannot connect to data source: [$DBI::errstr]\n" ; exit(2) ; } ; #Unable to connect # $SQL =<<"__SQL_GetGlobalOption__" ; SELECT [OptionValue] FROM [SFConfigDB].[dbo].[GlobalOptions] WHERE [OptionName]= ? __SQL_GetGlobalOption__ if(!($sth = $dbh->prepare($SQL))) { print STDERR "DBPrepare(): unable to prepare SQL [$SQL] DBI::errstr:[$DBI::errstr]\n" ; exit(3) ; } ; #if # # Using first call with shorter option value causes subsequent calls with larger value lengths to fail. # Reversing the order of the calls corrects the issue as does re-issuing prepare (but that defeats the # purpose of prepare's performance enhancements). # my $UID = GetGlobalOption("SECFIN_UID") ; print "Option SECFIN_UID = [$UID]\n" ; my $DSN = GetGlobalOption("SECFIN_SERVER") ; print "Option SECFIN_SERVER = [$DSN]\n" ; if(!($dbh->disconnect())) { print STDERR "\n\nCannot disconnect to data source: [$DBI::errstr]\n" ; exit(4) ; } ; #Unable to disconnect
Download (untitled) / with headers
text/plain 176b
We know about such problems for INSERT: <http://search.cpan.org/~sgoeldner/DBD-ADO-2.99/lib/DBD/ ADO.pm#ADO_providers> Would you be so kind to try the ado_size workaround?
Subject: RE: [rt.cpan.org #83665] prepare() ed sql fails if bind_parm length greater than first call
Date: Wed, 27 Feb 2013 15:12:38 +0000
To: "bug-DBD-ADO [...] rt.cpan.org" <bug-DBD-ADO [...] rt.cpan.org>
From: Thomas Jenkins <thejenkins [...] live.com>
Download (untitled) / with headers
text/plain 971b
That did correct the issue. Of possible interest: I had to change my call format from: $sth->bind_param(1, $option_name, SQL_VARCHAR) ; to: $sth->bind_param(1, $option_name, { ado_type => SQL_VARCHAR, ado_size => 255 }) ; As an experiment, I change the call to: $sth->bind_param(1, $option_name, { ado_type => SQL_VARCHAR }) ; and this also corrected the issue. Tom Sent from Windows Mail From: Steffen Goeldner via RT Sent: ‎February‎ ‎27‎, ‎2013 ‎9‎:‎55‎ ‎AM To: thejenkins@live.com Subject: [rt.cpan.org #83665] prepare() ed sql fails if bind_parm length greater than first call <URL: https://rt.cpan.org/Ticket/Display.html?id=83665 > We know about such problems for INSERT: <http://search.cpan.org/~sgoeldner/DBD-ADO-2.99/lib/DBD/ ADO.pm#ADO_providers> Would you be so kind to try the ado_size workaround?
Download (untitled) / with headers
text/plain 507b
On Wed Feb 27 10:12:57 2013, TomJ wrote: Show quoted text
> That did correct the issue.
Great, so at least we have a workaround. Show quoted text
> $sth->bind_param(1, $option_name, SQL_VARCHAR) ;
is the same as $sth->bind_param(1, $option_name, { TYPE => SQL_VARCHAR } ) ; The DBI (and standard SQL) TYPE SQL_VARCHAR = 12 is mapped to the ado_type adVarChar = 200. But Show quoted text
> $sth->bind_param(1, $option_name, { ado_type => SQL_VARCHAR } ) ;
mixes DBI and ADO types, i.e. you bind with adVariant = 12. Why *this* works - I don't know!


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.