[boulder.pm] FW: DBI question

[Jason Van Slyke]

Perl Mongers:

I don't believe it, not a single response to my question posted nearly 8
hours ago!

So, I'll respond to it myself.  In between work things today I've been
searching for an answer under rocks and things.  It turns our that O'Reilly
has a sample chapter of the new, soon to be available, DBI book by Descartes
& Bunce.  And that chapter has the answer,  "the most important utility
method is quote()":

#!/usr/bin/perl -w 
#
# ch04/util/quote1: Demonstrates the use of the $dbh->quote() method 
use DBI; 
### The string to quote 
my $string = "Don't view in monochrome (it looks 'fuzzy')!"; 
### Connect to the database 
my $dbh = DBI->connect( "dbi:Oracle:archaeo", "username", "password" , { 
	RaiseError => 1 } ); 
### Escape the string quotes ... 
my $quotedString = $dbh->quote( $string ); 
### Use quoted string as a string literal in a SQL statement 
my $sth = $dbh->prepare( " 
	SELECT * 
	FROM media 
	WHERE description = $quotedString " ); 
$sth->execute(); 
exit;

http://www.oreilly.com/catalog/perldbi/chapter/ch04.html

Have a good weekend everyone. (Sorry I didn't make the meeting last Tue
evening.)
jvs 


> -----Original Message-----
> From:	Jason Van Slyke 
> Sent:	Friday, February 04, 2000 8:34 AM
> To:	'boulder-pm-list at happyfunball.pm.org'
> Subject:	DBI question
> 
> 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