Perl - Database

perl

Basic sample:

#!/usr/bin/perl
use strict;
use warnings;
use DBI ();
use DBD::mysql ();
my $dsn = "DBI:mysql:database=dbname;host=IP;port=3306";
my $dbh = DBI->connect($dsn,"username","password") || die ("$DBI::errstr");

my $query = qq!SELECT DISTINCT(fullURL) as fullURL FROM 
    urlMapping where fullURL NOT REGEXP 'gotgenius|gotgenie|emailOpened' LIMIT 10000!;

my $sth = $dbh->prepare($query) || die($dbh->errstr());
my $stat = $sth->execute() || die(sth->errstr());
while (my $res = $sth->fetchrow_hashref()) {
    print $res->{'fullURL'},"\n";
}
$sth->finish();
$sth->disconnect();
use strict;
use DBI;

my $dsn = "DBI:mysql:database=dbname;host=IP;port=3306";
my $dbh = DBI->connect($dsn,'username','password',{RaiseError => 1, PrintError => 0}) || die("$DBI::errstr");
my $sql = qq/SELECT * FROM USERS WHERE lastname LIKE 'smith%'/;
my $sth = $dbh->prepare($sql);
$sth->execute;

What does a DSN for Oracle look like?

DBI:Oracle:host=$host;sid=$sid;port=$port
DBI:Oracle:T:Machine:SID

How can we use bind parameters?

my $sql = qq/INSERT INTO Products(productCode,productName,price) VALUES(?,?,?)/;
my $sth = $dbh->prepare($sql);
$sth->execute($productCode,$productName,$price);

Bind parameters allows use to prepare a statement once and execute it many times with different parameters.

What is the purpose of binding columns to Perl variables?

Binding columns of a select statement to Perl variables allow: Whenever a row is fetched from database, the corresponding Perl variable is automatically updated.

my $sql = qq/SELECT productCode,productName,price FROM products WHERE price <= 3/;
my ($productCode, $productName, $price);
my $sth = $dbh->prepare($sql);
$sth->execute;
$sth->bind_columns(undef, \$productCode, \$productName, \$price); // the first parameter is a hash reference of attributes
while ($sth->fetch) {
    write $productCode, $productName, $price;
}

How can we catch error?

eval {
    $sth = $dbh->prepare($sql);
    $sth->execute;
}
if ($@) {
    print "Error $@";
    exit;
}

The above code show us how to use $@. Previous examples show us how to use die with $dbh->errstr() and $DBI::errstr.

How can we get more information on DBI?

perldoc DBI
perldoc DBI::mysql
perldoc DBI::Oracle

Other:

use DBD::mysql;
$dsn = "DBI:mysql:database=dbName;host=hostName;port=portNumber";
my $sql = qq/.../;
my $sth = $dbh->prepare($sql);
$sth->execute;
my ($v1, $v2, ...)
$sth->bind_columns(undef, \$v1, \$v2, ...);
while ($sth->fetch) { ... }

Binding columns of a select statement to a variable allow: whenever a row is fetched from the database, the corresponding Perl variable is automatically updated.

Binding parameters allow you to prepare a statement once and execute it many times later with different values:

my $sql = qq/ insert into tableName(col1,col2,col3) values(?,?,?) /;
my $sth = $dbh->prepare($sql);
while (condition) {
    my ($code,$name,$price) = "some values";
    $sth->execute($code,$name,$price);
}
$dbh->do($sql); // Prepare and execute in one step
eval {
    $sth = $dbh->prepare($sql);
    $sth->execute();
}
if ($@) { print "Error $@"; exit; }

@arr = DBI->available_drivers($quiet);  // return a list of available drivers.
@arr = DBI->datasources($driver); // returns a list of database available via the given driver

$sth->{NUM_OF_FIELDS}; // the number of fields in a select statement
$sth->{NUM_OF_PARAM}; // the number of parameters (placeholders) in a prepared statement
$sth->{NAME}; // an array reference containing field names
$sth->{CursorName}; // the name of the cursor associated with the statement handle if available
$sth = $dbh->prepare($sql, \%attr) || die $dbh->errstr;
$dbh->do($sql, \%attr, @bind_values);
$h->{warn} // enable warning
$h->{InactiveDestroy} // disable the effect of destroying a handle
$h->{PrintError}
$h->{RaiseError}
$h->{ChopBlanks}
$h->{LongReadLen} // control max length of fields
$h->{LongTruncOk} // control the effect of fetching long field values which has been truncated
$rc = $dbh->commit;
$rc = $dbh->rollback;
$rc = $dbh->disconnect;

$sql = $dbh->quote($string);
$sql = sprintf("select foo from bar where baz=%s", $dbh->quote("Don't\n"));

$rv = $sth->bindparam($param_num, $bindvalue, \%attr);
$rv = $sth->bindparam($param_num, $bindvalue, $bindtype);
$rv = $sth->execute([@bind_values]);

$sth->fetchrow_arrayref; // returns a reference to an array holding field values;
$sth->rows // returns the number of rows affected by the last database altering command or -1 if unknown

$row = $sth->fetchrow_hashref(); // row is a reference to a hash whose keys are column names
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License