Re: MSSQL 2000, ODBC, and placeholders

[Marcello]

Charles Lawrence ha scritto:
Gentlemen,
 

I'm hoping you can answer a question for me.
 

I am using an XP Pro platform accessing an MSSQL 2000 server.  I use
ActivePerl 5.8.4 to access my database via ODBC.  The latest versions of
the DBI and DBD-ODBC modules are installed.  When I attempt to use
placeholders in my script, NOTHING HAPPENS.  The script runs to
completion with no errors, but no data is stored.  If I do the same
thing with a MySQL database, it all works OK.  Am I missing something or
what?  Does the MS SQL Server ODBC driver 3.8.5.1117 that my DSN uses
actually support placeholder use?  It would appear that it does not, but
does not complain either.  Below is a code snippet.  Thanks in advance
for any help.  In the meantime, I have abandoned the placeholder concept
with its inherent efficiency in loops, and now use a DBI do command
instead.  It works, but...
 

Charles Lawrence
[EMAIL PROTECTED]
 

 

########################################################################
 

use strict;
 

use DBI;
 

my ($dsn_MSSQL,$dbh_MSSQL,$sth_MSSQL);
 

my $DSNname_MSSQL = 'OPERATIONS'; # DSN
my $username_MSSQL = 'operations';
my $password_MSSQL = 'qwertyuiop';
 

$dsn_MSSQL = "DBI:ODBC:$DSNname_MSSQL";
 

$dbh_MSSQL =
DBI->connect($dsn_MSSQL,$username_MSSQL,$password_MSSQL,{RaiseError =>
0, PrintError => 0})
{ RaiseError => 1, PrintError => 1, AutoCommit => 0 }
  or bail_out("   CANNOT connect to MSSQL database [$dsn_MSSQL]");
 

$sth_MSSQL = $dbh_MSSQL->prepare(qq{INSERT INTO msag
(pfx,streetName,rangeLow,rangeHigh,community,state,oddEven,esn,[911ID],[
date],telco,reserved,exchange,clli) VALUES
(?,?,?,?,?,?,?,?,?,?,?,?,?,?)})
  or bail_out("   CANNOT prepare MSSQL statement for transactions");
 

# It's better to specify these at connection time (see above)
# and *before* you create any statement handle
# or at least change these lines
$dbh_MSSQL->{RaiseError} = 1; # cause errors to raise exception
$dbh_MSSQL->{PrintError} = 0; # suppress error messages
$dbh_MSSQL->{AutoCommit} = 0; # don't commit until we say so
like this:
$sth_MSSQL->{RaiseError} = 1;
$sth_MSSQL->{PrintError} = 1;
$sth_MSSQL->{AutoCommit} = 0;
 

foreach ...
  {
.
.
.
          parseLine();
          storeMSSQL();
.
.
.
  }
 

$dbh_MSSQL->disconnect();
 

exit;
 

sub parseLine($)
  {
.
.
.
  }
 

sub bail_out($)
  {
     my $message = shift;
     die("$message\nError [$DBI::err] ($DBI::errstr)\n");
  }
 

sub storeMSSQL($)
  {
     eval
       {
 
$sth_MSSQL->execute($PFX_1,$StreetName_1,$Range_Low_1,$Range_High_1,$Com
munity_1,$State_1,$Odd_Even_1,$ESN_1,$_911_ID_1,$Date_1,$Telco_1,$Reserv
ed_1,$Exchange_1,$CLLI_1);

       };
      if ($@) {
           my $storeErr = $@;
           eval {
                $dbh_MSSQL->rollback();
                $dbh_MSSQL->disconnect();
           };
           die("A MSSQL transaction error occurred: 
[$storeErr]\n[$linecount] records rolled back\n");
      } else {
           eval {
                $dbh_MSSQL->commit();
           };
           die ("Error while committing: $@") if ($@);
  }
 

########################################################################
#############

Marcello