DBI

DBI
● The standard module for working with RDBMSs
DBD::Pg, DBD::mysql, DBD::SQLite, DBD::Oracle, DBD::ODBC
● Works on some non-RDBMSs
DBD::CSV, DBD::Cassandra, DBD::Amazon, DBD::Google, DBD::Salesforce
● DBD::Sponge

DBI History – The Bad Old Days
Different builds of perl for each database
● oraperl
● syperl
● INGperl
Each with it's own, incompatible API

DBI History – More Modern
First release Sept 1994, coincides with a pre-
release of perl5
One of the oldest maintained modules on CPAN
Over 130 releases

Architecture
Program
DBI
DBD::Pg DBD::mysql DBD::SQLite

DBD - Drivers
Namespace for DBI drivers
● DBD::Pg
● DBD::mysql
● DBD::SQLite
● DBD::Oracle
● etc
Only for access compatibility, not SQL

Lifecycle of a request
● Text
● Compile
● Execute
● Results

Lifecycle of a request
● Text
● Compile
● Execute
● Results
my $sql = “select * from ...”;
my $sth = $dbh->prepare($sql)
$sth->execute('Bob');
my $row = $sth->fetchrow_arrayref()

Simple query
my $name = $ARGV[0];
my $dbh = DBI->connect('dbi:Pg:dbname=DataStore',
$username,
$passwd)
|| die “Can't connect: “ . DBI->errstr;
my $sth = $dbh->prepare(
qq(select * from student where name = '$name'));
$sth->execute();
while (my $row = $sth->fetchrow_arrayref()) {
print join(', ', @$row),”n”;
}

Simple query
$username,
$passwd)
$sth->execute();
}
> ./list_students “Jimbo Jones”

Simple query
$username,
$passwd)
$sth->execute();
}
> ./list_students “Robert';drop table students;--”

Simple query - Placeholders
$username,
$passwd)
qq(select * from student where name = ?));
$sth->execute($name);
}
> ./list_students “Robert';drop table students;--”

Placeholders
my $sth = $dbh->prepare(q(
select name, age, teacher
from students
where name = ?
and age < ?
));
$sth->execute('Bob', 15);
print join(', ', @$_) while $_ = $sth->fetchrow_arrayref;
$sth->execute('Joe', 10);
print join(', ', @$_) while $_ = $sth→fetchrow_arrayref;
$sth->execute('Fred', 20);
print join(', ', @$_) while $_ = $sth->fetchrow_arrayref;

Placeholders - Limitations
my $sth = $dbh->prepare(q(delete from ? where name = ?));

my $sth = $dbh->prepare(q(update students set ? = 2 where name = ?));

my $sth = $dbh->prepare(q(select * from students ?));

my $sth = $dbh->prepare(q(select * from students ?))
my $sth = $dbh->prepare(q(select * from students where id in (?)));

my $sth = $dbh->prepare(q(select * from students where teacher = ?));
$sth->execute(undef);

my $sth = $dbh->prepare(q(select * from students where teacher = ?));
$sth->execute(undef);
● Build SQL on the fly
● SQL::Abstract

Caching
● connect_cached()
my $dbh = DBI->connect_cached('dbi:Pg:dbname=foo;host=bar',
$username,
$passwd,
{ RaiseError => 1, AutoCommit => 0 });
● prepare_cached()
my $sth = $dbh->prepare_cached('select * from foo where bar = ?');

Convenience
my $aref = $sth->fetchrow_arrayref();
my @array = $sth->fetchrow_array();
my $href = $sth->fetchrow_hashref();

Convenience
my $aref_of_aref = $sth->fetchall_arrayref();
my $aref_of_aref = $sth->fetchall_arrayref([0,-1]);
my $aref_of_href = $sth->fetchall_arrayref({});
my $href_of_href = $sth->fetchall_hashref('col_name');

Convenience
my @array = $dbh->selectrow_array($sql);
my $aref = $dbh->selectrow_arrayref($sql);
my $href = $dbh->selectrow_hashref($sql);

Convenience
my @array_of_aref = $dbh->seletall_array($sql);
my $aref_of_aref = $dbh->selectall_arrayref($sql);
my $href_of_href = $dbh->selectall_hashref(
$sql,
'col_name');

Convenience
my $rows_affected = $dbh->do($sql);
$rows_affected = $sth->execute($update_sql);
$rows_affected = $sth->execute($delete_sql);
$rows_affected = $sth->rows();

Binding parameters
my $sth = $dbh->prepare('select key, value from attrs where item_id=?');
foreach my $id ( @id_list ) {
$sth->bind_param(0, $id, { ora_type => ORA_BLOB });
$sth->execute();
while (my $row = $sth->fetchrow_hashref()) {
printf(“%s => %sn”, $row->{key}, $row→{value});
}
}

Binding results
my $sth = $dbh->prepare('select key, value from attrs where item_id=?');
my($key, $value);
$sth->bind_col(0, $key);
$sth->bind_col(1, $value);
foreach my $id ( @id_list ) {
$sth->execute($id);
while( $sth->fetchrow_arrayref() ) {
print “$key => $valuen”;
}
}

last_insert_id
$dbh->do('create table things( id serial, name varchar )');
$sth = $dbh->prepare('insert into things (name) values (?)');
foreach my $name ( @name_list ) {
$sth->execute($name);
my $id = $sth->last_insert_id();
print “for $name, ID is $idn”;
}

Error Handling
● $dbh->{RaiseError} = 1;
● $dbh->{PrintError} = 1;
● $dbh->{HandleError} = sub { … };

Error Handling
● $dbh->{RaiseError} = 1;
● $dbh->{PrintError} = 1;
● $dbh->{HandleError} = sub { … };
● $DBI::err and $dbh->err
● $DBI::errstr and $dbh->errstr

Transactions
$dbh->{AutoCommit} = 1;
$sth = $dbh->prepare('update students
set grade = ?
where name = ?');
while(my($name, $grade) = each %student_grades) {
$sth->execute($name, $grade);
}

Transactions
$sth = $dbh->prepare('update students
set grade = ?
where name = ?');
$dbh->begin_work();
while(my($name, $grade) = each %student_grades) {
$sth->execute($name, $grade);
}
$dbh->commit();

Transactions
eval {
$dbh->begin_work();
$dbh->{RaiseError} = 1;
My $sth = $dbh->prepare( … );
some_perl_code();
$sth->execute( … );
$dbh->commit();
};
If ($@) {
$dbh->rollback();
log_the_error($@);
}

Introspection
my $sth = $dbh->table_info($catalog, $schema, $table, $type);
while(my $row = $sth->fetchrow_hashref) {
printf(“name: %s, catalog: %s, schema: %s, type: %s, remarks: %sn”,
@$row{qq( TABLE_NAME
TABLE_CAT
TABLE_SCHEM
TABLE_TYPE
REMARKS )});
}

Introspection
my $sth = $dbh->column_info($catalog, $schema, $table, $column);
while(my $row = $sth->fetchrow_hashref) {
printf(“name: %s, table: %s, type %s, nullable %s, size %sn”,
@$row{qq( COLUMN_NAME
TABLE_NAME
TYPE_NAME
NULLABLE
COLUMN_SIZE )});
}

Introspection
● $dbh->primary_key_info($catalog, $schema, $table);
● $dbh->foreign_key_info($pk_catalog, $pk_schema, $pk_table,
$fk_catalog, $fk_schema, $fk_table);

DBIx::Class
● Canonical Object-Relational Mapper for Perl
● Supports most any DB supported by DBI
● Auto-discovers schema or customize
● First release August 2005
● Hundreds of contributors

DBIx::Class
● Program can have many schemas (databases)
● Schemas have many resultsources (tables)
● Resultsources have many resultsets
● Resultsets have many objects (rows)

DBIx::Class Schema
package MyApp::School;
use base qw/DBIx::Class::Schema/;
__PACKAGE__->load_namespaces();
1;

DBIx::Class Result Source
package MyApp::School::Result::Teacher;
use base qw/DBIx::Class::Core/;
__PACKAGE__->table('teacher');
__PACKAGE__->add_columns(qw/teacher_ id name subject /);
__PACKAGE__->set_primary_key(teacher_'id');
__PACKAGE__->has_many(students =>
'MyApp::School::Result::Student', 'teacher_id');
1;

DBIx::Class Result Source
package MyApp::School::Result::Student;
use base qw/DBIx::Class::Core/;
__PACKAGE__->table('student');
__PACKAGE__->add_columns(qw/ student_id name age grade teacher_id /);
__PACKAGE__->set_primary_key('student_id');
__PACKAGE__->belongs_to(teacher =>
'MyApp::School::Result::teacher', 'teacher_id');
1;

DBIx::Class Fetch All
use MyApp::School;
my $schema = MyApp::School->connect(
$dbi_dsn,
$user,
$passwd);
my @all_teachers = $schema->resultset('Teacher')->all;
foreach my $teacher ( @all_teachers ) {
print “Teacher named “, $teacher->name, “n”;
}

DBIx::Class Find
my $student = $schema->resultset('Student')
->find(62);
print “student name “,$student->name, “n”;

DBIx::Class Search
my $student_rs = $schema->resultset('Student')
->search({ grade => 'B' });
while (my $student = $student_rs->next) {
print $student->name, “ got a Bn”;
}

DBIx::Class Search Related
my $smith_teachers_rs = $schema->resultset('Teacher')
->search({ name => { like => '% Smith' }});
my $student_rs = $smith_teachers_rs->search_related('students',
{ grade => 'C' });

DBIx::Class Create
my $student = $schema->resultset('Student')->new({
Name => 'Jimbo Jones',
Age => 11,
});;
$student->insert();

DBIx::Class Update
$student->teacher($teacher);
$student->grade('D');
$student->update();

DBIx::Class Update
$student->teacher($teacher);
$student->grade('D');
$student->update();
$student_rs = $schema->resultset('Student')->find(teacher => $teacher);
$student_rs->update(grade => 'A');

DBIx::Class Delete
->search(teacher => undef);
$student_rs->delete();

DBIx::Class Transactions
->search(teacher => undef);
$schema->txn_do(sub {
while (my $student = $student_rs->next) {
$student->teacher('Mr. Hand');
}
});

Links
DBI
● https://metacpan.org/pod/DBI
● http://dbi.perl.org/
● http://www.slideshare.net/Tim.Bunce/dbi-advanced-tutorial-2007
DBD drivers
● https://metacpan.org/pod/DBD::Pg
● https://metacpan.org/pod/DBD::SQLite
● https://metacpan.org/search?q=DBI%3A%3A
DBIx::Class
● https://metacpan.org/pod/DBIx::Class
● http://www.dbix-class.org/

DBI

More Related Content

What's hot

Viewers also liked

Similar to DBI

Recently uploaded

DBI