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
MIME-Version: 1.0
X-Mailer: MIME-tools 5.427 (Entity 5.427)
X-RT-Original-Encoding: utf-8
Content-Type: multipart/mixed; boundary="----------=_1361971127-5588-2"
X-RT-Encrypt: 0
X-RT-Sign: 0
Content-Length: 0
Content-Type: text/plain; charset="UTF-8"
Content-Disposition: inline
Content-Transfer-Encoding: binary
Content-Length: 668
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
MIME-Version: 1.0
Content-Type: application/octet-stream; name="DBI-Err.PL"
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline; filename="DBI-Err.PL"
Content-Transfer-Encoding: base64
Content-Length: 2392
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
From h.m.brand [...] xs4all.nl Wed Feb 27 08: 50:42 2013
MIME-Version: 1.0
X-Spam-Status: No, score=-6.481 tagged_above=-99.9 required=10 tests=[AWL=0.419, BAYES_00=-1.9, RCVD_IN_DNSWL_HI=-5] autolearn=ham
In-Reply-To: <rt-3.8.HEAD-5588-1361971128-1186.83663-4-0 [...] rt.cpan.org>
X-Mailer: Claws Mail 3.9.0cvs60 (GTK+ 2.24.10; x86_64-unknown-linux-gnu)
X-Spam-Flag: NO
References: <RT-Ticket-83663 [...] rt.cpan.org> <rt-3.8.HEAD-5588-1361971128-1186.83663-4-0 [...] rt.cpan.org>
Face: iVBORw0KGgoAAAANSUhEUgAAADAAAAAwEAIAAACI8LKTAAAACXBIWXMAAABIAAAASABGyWs+AAAC JElEQVRo3u2aMY4CMQxFczZ6RItEzRm4DBINDbRUSPRInIRbsNK6+dJfezN4kokn48IaCSjysL8d e9Knoj2fr9f9/gllqQ6U9/vxWK3EdwdIEGjRIVCu18NhuxUfK46SH81+fzrdbuKPx/P5ctHQdAdI TKAgpvV6s9ntBEfXEYSGgMQzIHnuFBBjkshCNJ2KtJZ04hHNAugP8bZr3NIHhbcF0AKoK0CoaHXU LUWBIs1n+jV+Fl8CVqOApEXAwyMO/DSR4XVntoAYDR7eBjQupuYAYTMph8Rj21D4m7MChN02tpqs NSnb/KqU2oHCXu5xDCgflj/RAgBiKBIXnICzAsSjWBsTz5K4/HeXYvb8yK5lY3VGEwPi2aONKT+5 AlcxrTPOwcTiraGRChgMEKJh0bVVifGVTq6qgBiNVl8QE29EsK6VE+YJAOG2wz5AvsqUS6uqgHCA n4NGvBYpnJ64Jgg27sCtxtBk1CJIA4S/GhdWKh07QxUB48jWGhZ4jKamRRr/T8/M0AaEyctry6YB 4dTGj9iWZNs3DahES5kPCJOu0RQbF/fQOBprsB9gaO9JtPDzII9U5ySXX7AnuIt91y54AAW7rPpT LCe5gt3F+CLqr2UarGB3MXvMylWGq4+9RCx3TW1oJq1t3HPQlFs6N1fFNEB4s8dn7Ne7ACSm7TPQ I5quAWmw6qBpulHM33B0Csge4Nd8JTTYG2b1XyRe3lH8x34ABJ6aePuQ2N4AAAAASUVORK5CYII=
X-Virus-Scanned: Debian amavisd-new at bestpractical.com
X-Virus-Scanned: by XS4ALL Virus Scanner
Message-ID: <20130227145026.42d0e323 [...] pc09.procura.nl>
Content-Type: text/plain; charset="utf-8"
X-RT-Original-Encoding: utf-8
X-Spam-Score: -6.481
Received: from localhost (localhost [127.0.0.1]) by hipster.bestpractical.com (Postfix) with ESMTP id CB8582404B6 for <cpan-bug+DBI [...] hipster.bestpractical.com>; Wed, 27 Feb 2013 08:50:42 -0500 (EST)
Received: from hipster.bestpractical.com ([127.0.0.1]) by localhost (hipster.bestpractical.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id Mx11FWZz++TF for <cpan-bug+DBI [...] hipster.bestpractical.com>; Wed, 27 Feb 2013 08:50:41 -0500 (EST)
Received: from la.mx.develooper.com (x1.develooper.com [207.171.7.70]) by hipster.bestpractical.com (Postfix) with SMTP id AB5012400A0 for <bug-DBI [...] rt.cpan.org>; Wed, 27 Feb 2013 08:50:39 -0500 (EST)
Received: (qmail 8305 invoked by uid 103); 27 Feb 2013 13:50:39 -0000
Received: from x16.dev (10.0.100.26) by x1.dev with QMQP; 27 Feb 2013 13:50:39 -0000
Received: from smtp-vbr8.xs4all.nl (HELO smtp-vbr8.xs4all.nl) (194.109.24.28) by 16.mx.develooper.com (qpsmtpd/0.84/v0.84-167-g4ed6cab) with ESMTP; Wed, 27 Feb 2013 05:50:32 -0800
Received: from pc09.procura.nl (static.kpn.net [92.66.41.201] (may be forged)) (authenticated bits=0) by smtp-vbr8.xs4all.nl (8.13.8/8.13.8) with ESMTP id r1RDoRgC062875 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES128-SHA bits=128 verify=NO) for <bug-DBI [...] rt.cpan.org>; Wed, 27 Feb 2013 14:50:27 +0100 (CET) (envelope-from h.m.brand [...] xs4all.nl)
Delivered-To: cpan-bug+DBI [...] hipster.bestpractical.com
Subject: Re: [rt.cpan.org #83663] prepare() ed sql fails if bind_parm length greater than first call
Return-Path: <h.m.brand [...] xs4all.nl>
X-Spam-Check-BY: 16.mx.develooper.com
X-Original-To: cpan-bug+DBI [...] hipster.bestpractical.com
X-RT-Mail-Extension: dbi
Date: Wed, 27 Feb 2013 14:50:26 +0100
X-Spam-Level:
To: bug-DBI [...] rt.cpan.org
Content-Transfer-Encoding: 7bit
From: "H.Merijn Brand" <h.m.brand [...] xs4all.nl>
RT-Message-ID: <rt-3.8.HEAD-10048-1361973043-165.83663-0-0 [...] rt.cpan.org>
Content-Length: 2334
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/
From thejenkins [...] live.com Wed Feb 27 09: 16:17 2013
CC: "TIMB [...] cpan.org" <TIMB [...] cpan.org>
X-Originalarrivaltime: 27 Feb 2013 14:16:03.0644 (UTC) FILETIME=[F9D5D7C0:01CE14F4]
MIME-Version: 1.0
X-Spam-Status: No, score=-6.233 tagged_above=-99.9 required=10 tests=[BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_HI=-5, SPF_SOFTFAIL=0.665] autolearn=ham
X-Spam-Flag: NO
Importance: Normal
X-Virus-Scanned: Debian amavisd-new at bestpractical.com
Message-ID: <BAY404-EAS373087938DC22268C036E0CA4FD0 [...] phx.gbl>
Content-Type: multipart/alternative; boundary="_3DCAAC7D-86B8-4189-A341-A545F9EB3F00_"
X-Spam-Score: -6.233
Received: from localhost (localhost [127.0.0.1]) by hipster.bestpractical.com (Postfix) with ESMTP id 386CC2404B6 for <cpan-bug+DBI [...] hipster.bestpractical.com>; Wed, 27 Feb 2013 09:16:17 -0500 (EST)
Received: from hipster.bestpractical.com ([127.0.0.1]) by localhost (hipster.bestpractical.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id HhrcKhP+mibW for <cpan-bug+DBI [...] hipster.bestpractical.com>; Wed, 27 Feb 2013 09:16:11 -0500 (EST)
Received: from la.mx.develooper.com (x1.develooper.com [207.171.7.70]) by hipster.bestpractical.com (Postfix) with SMTP id B58742400A0 for <bug-DBI [...] rt.cpan.org>; Wed, 27 Feb 2013 09:16:10 -0500 (EST)
Received: (qmail 11087 invoked by uid 103); 27 Feb 2013 14:16:10 -0000
Received: from x16.dev (10.0.100.26) by x1.dev with QMQP; 27 Feb 2013 14:16:10 -0000
Received: from bay0-omc4-s8.bay0.hotmail.com (HELO bay0-omc4-s8.bay0.hotmail.com) (65.54.190.210) by 16.mx.develooper.com (qpsmtpd/0.84/v0.84-167-g4ed6cab) with ESMTP; Wed, 27 Feb 2013 06:16:06 -0800
Received: from BAY404-EAS373 ([65.54.190.201]) by bay0-omc4-s8.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Wed, 27 Feb 2013 06:16:03 -0800
Delivered-To: cpan-bug+DBI [...] hipster.bestpractical.com
Subject: RE: [rt.cpan.org #83663] prepare() ed sql fails if bind_parm length greater than first call
Return-Path: <thejenkins [...] live.com>
X-Spam-Check-BY: 16.mx.develooper.com
X-Original-To: cpan-bug+DBI [...] hipster.bestpractical.com
X-RT-Mail-Extension: dbi
X-Originating-Email: [thejenkins [...] live.com]
Date: Wed, 27 Feb 2013 14:16:02 +0000
X-Spam-Level:
X-Eip: [ZOFVwp8sZcMtFeSx0kLythFMvoUwWESU]
To: "bug-DBI [...] rt.cpan.org" <bug-DBI [...] rt.cpan.org>
From: Thomas Jenkins <thejenkins [...] live.com>
RT-Message-ID: <rt-3.8.HEAD-6566-1361974578-949.83663-0-0 [...] rt.cpan.org>
Content-Length: 0
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: base64
X-RT-Original-Encoding: utf-8
Content-Length: 2845
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/
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: base64
X-RT-Original-Encoding: utf-8
Content-Length: 5496
MIME-Version: 1.0
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-5588-1361975379-1852.83663-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 5
moved
MIME-Version: 1.0
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-9170-1362486217-170.83663-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 19
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.