[boulder.pm] FW: DBI question
Jason Van Slyke
jvanslyk at matchlogic.com
Fri Feb 4 17:29:09 CST 2000
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
More information about the Boulder-pm
mailing list