| Subject: | prepare() ed sql fails if bind_parm length greater than first call |
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 |
#
#############################################
# #
# 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