[boulder.pm] DBI question

[Jason Van Slyke]

Perl Mongers:

Good Friday Morning!

Got a little project that uses DBI. The following syntax is based off TPJ
article Using Databases with DBI: What Not To Do by Thomas Akin.

My problem seems to be related to variable interpolation in the where
statement because:

This works.

     1  #!/usr/local/bin/perl -w
     2
     3  $CA = "PG" ;
     4
     5  use DBI ;
     6  $drh = DBI->install_driver('Oracle') ;
     7  $dbh = $drh->connect('logp','ml_gobob','ml_gobob',
     8          { RaiseError => 1}) ;
     9
    10  $sql =  "select cust_abbr, pid, date_time 
    11          from ra_online
    12          where cust_abbr = 'PG'" ;
    13
    14  $sth = $dbh->prepare($sql) ;
    15  $sth->execute ;
    16
    17  my ($CustAbbr, $PID, $DT, $Count) ;
    18  $sth->bind_columns(undef, \$CustAbbr, \$PID, \$DT) ;
    19
    20  while ( $sth->fetch) {
    21    $Count += 1 ;
    22     print "$CustAbbr\t$PID\t$DT\t$Count\t$CA\n" ;
    23  }
    24
    25  $dbh->disconnect ;

	giving these results:
:>perl -w count_ra_online.pl
Name "main::CA" used only once: possible typo at count_ra_online.pl line 3.
PG      1099    03-FEB-00       1
PG      1100    03-FEB-00       2
PG      1181    03-FEB-00       3

****************************************************************************
***************************

While this does not.

     1  #!/usr/local/bin/perl -w
     2
     3  $CA = "PG" ;
     4
     5  use DBI ;
     6  $drh = DBI->install_driver('Oracle') ;
     7  $dbh = $drh->connect('logp','ml_gobob','ml_gobob',
     8          { RaiseError => 1}) ;
     9
    10  $sql =  "select cust_abbr, pid, date_time 
    11          from ra_online
    12          where cust_abbr = $CA" ;
    13
    14  $sth = $dbh->prepare($sql) ;
    15  $sth->execute ;
    16
    17  my ($CustAbbr, $PID, $DT, $Count) ;
    18  $sth->bind_columns(undef, \$CustAbbr, \$PID, \$DT) ;
    19
    20  while ( $sth->fetch) {
    21    $Count += 1 ;
    22     print "$CustAbbr\t$PID\t$DT\t$Count\t$CA\n" ;
    23  }
    24
    25  $dbh->disconnect ;

	giving this error: Statement has no result columns to bind (perhaps
you need to successfully call execute first) at count_ra_online.pl line 18.

In line 10, I have also used the qq{ } syntax; no change.


Any ideas?

Thx, Jason