Skip Menu |
 

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

Report information
The Basics
Id: 83663
Status: resolved
Priority: 0/
Queue: DBD-ADO

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

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



Subject: prepare() ed sql fails if bind_parm length greater than first call
Download (untitled) / with headers
text/plain 668b
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.3k
# ############################################# # # # U S E # # # ############################################# # use Modern::Perl ; no warnings 'uninitialized' ; use Data::Dumper ; use DBI qw(:sql_types) ; my $dbh ; my $sth ; my $SQL ; # ##################################################################### # 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=s1-sql-8;Initial Catalog=SFConfigDB", { PrintError => 0, RaiseError => 1}))) { print STDERR "\n\nCannot connect to data source: [$DBI::errstr]\n" ; exit(1) ; } ; #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(1) ; } ; #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(2) ; } ; #Unable to disconnect
Subject: Re: [rt.cpan.org #83663] prepare() ed sql fails if bind_parm length greater than first call
Date: Wed, 27 Feb 2013 14:50:26 +0100
To: bug-DBI [...] rt.cpan.org
From: "H.Merijn Brand" <h.m.brand [...] xs4all.nl>
Download (untitled) / with headers
text/plain 2.2k
On Wed, 27 Feb 2013 08:18:48 -0500, "Tom Jenkins via RT" <bug-DBI@rt.cpan.org> wrote: Show quoted text
> 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.
I guess so. With DBI-1.623 + DBD::Pg-2.19.3 on PostgreSQL-9.2.3 I get IMHO certainly NOT a DBI error. MAYBE an DBD::ADO error I suggest transferring the ticket to that queue Show quoted text
psql> create table GlobalOptions (OptionName varchar (255), OptionValue varchar); psql> insert into GlobalOptions values ('SECFIN_SERVER', 'servername'); psql> insert into GlobalOptions values ('SECFIN_UID', 'batch');
$ cat test.pl use 5.016; use warnings; use DBI qw(:sql_types); my $dbh = DBI->connect ("dbi:Pg:", undef, undef, { PrintError => 1, RaiseError => 1, ShowErrorStatement => 1, }); my $sth = $dbh->prepare (qq; select OptionValue from GlobalOptions where OptionName = ?; ); sub GetGlobalOption { my $option_name = shift; $sth->bind_param (1, $option_name, SQL_VARCHAR); $sth->execute (); $sth->bind_columns (\my $OptionValue); while ($sth->fetch ()) { print "$OptionValue "; } $sth->finish (); return $OptionValue; } # GetGlobalOption my $UID = GetGlobalOption ("SECFIN_UID"); say "Option SECFIN_UID = [$UID]"; my $DSN = GetGlobalOption ("SECFIN_SERVER"); say "Option SECFIN_SERVER = [$DSN]"; $ perl test.pl batch Option SECFIN_UID = [batch] servername Option SECFIN_SERVER = [servername] Show quoted text
> 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.
-- H.Merijn Brand http://tux.nl Perl Monger http://amsterdam.pm.org/ using perl5.00307 .. 5.17 porting perl5 on HP-UX, AIX, and openSUSE http://mirrors.develooper.com/hpux/ http://www.test-smoke.org/ http://qa.perl.org http://www.goldmark.org/jeff/stupid-disclaimers/
CC: "TIMB [...] cpan.org" <TIMB [...] cpan.org>
Subject: RE: [rt.cpan.org #83663] prepare() ed sql fails if bind_parm length greater than first call
Date: Wed, 27 Feb 2013 14:16:02 +0000
To: "bug-DBI [...] rt.cpan.org" <bug-DBI [...] rt.cpan.org>
From: Thomas Jenkins <thejenkins [...] live.com>
Download (untitled) / with headers
text/plain 2.7k
Thanks for taking a look. Will move the issue to dbd::ado. Tom Sent from Windows Mail From: h.m.brand@xs4all.nl via RT Sent: ‎February‎ ‎27‎, ‎2013 ‎8‎:‎50‎ ‎AM To: thejenkins@live.com CC: TIMB@cpan.org Subject: Re: [rt.cpan.org #83663] prepare() ed sql fails if bind_parm length greater than first call <URL: https://rt.cpan.org/Ticket/Display.html?id=83663 > On Wed, 27 Feb 2013 08:18:48 -0500, "Tom Jenkins via RT" <bug-DBI@rt.cpan.org> wrote: Show quoted text
> 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.
I guess so. With DBI-1.623 + DBD::Pg-2.19.3 on PostgreSQL-9.2.3 I get IMHO certainly NOT a DBI error. MAYBE an DBD::ADO error I suggest transferring the ticket to that queue Show quoted text
psql> create table GlobalOptions (OptionName varchar (255), OptionValue varchar); psql> insert into GlobalOptions values ('SECFIN_SERVER', 'servername'); psql> insert into GlobalOptions values ('SECFIN_UID', 'batch');
$ cat test.pl use 5.016; use warnings; use DBI qw(:sql_types); my $dbh = DBI->connect ("dbi:Pg:", undef, undef, { PrintError => 1, RaiseError => 1, ShowErrorStatement => 1, }); my $sth = $dbh->prepare (qq; select OptionValue from GlobalOptions where OptionName = ?; ); sub GetGlobalOption { my $option_name = shift; $sth->bind_param (1, $option_name, SQL_VARCHAR); $sth->execute (); $sth->bind_columns (\my $OptionValue); while ($sth->fetch ()) { print "$OptionValue "; } $sth->finish (); return $OptionValue; } # GetGlobalOption my $UID = GetGlobalOption ("SECFIN_UID"); say "Option SECFIN_UID = [$UID]"; my $DSN = GetGlobalOption ("SECFIN_SERVER"); say "Option SECFIN_SERVER = [$DSN]"; $ perl test.pl batch Option SECFIN_UID = [batch] servername Option SECFIN_SERVER = [servername] Show quoted text
> 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.
-- H.Merijn Brand http://tux.nl Perl Monger http://amsterdam.pm.org/ using perl5.00307 .. 5.17 porting perl5 on HP-UX, AIX, and openSUSE http://mirrors.develooper.com/hpux/ http://www.test-smoke.org/ http://qa.perl.org http://www.goldmark.org/jeff/stupid-disclaimers/
moved
Duplicate of #83665


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.