Transcript DBI tutorial

DBI tutorial
February 5th, 2007
What is DBI

DBI is short for Database Interface,
which is a Perl module
Relational Database
Relational database is a bunch of
tables, each row of which is a record
 A record contains pieces of information
called fields
 A table looks like:

An Example of Table
id
name
Title
Phone
8
Yeming
4173
3442497
6
Jamie
4175
3442495
4
Jacky
4177
3442499
SQL
SQL is short for Structured Query
Language, which is invented at IBM at
1970’s
 Four main commands: SELECT,
INSERT, DELETE, UPDATE
 Before manipulating a table, use
CREATE TABLE to create a new table
 You can also DROP a table

SQL Examples




SELECT * FROM employees WHERE name =
‘jacky'
DELETE FROM employees WHERE id = 8
UPDATE employees SET phone = phone+1
WHERE id = 8
INSERT INTO employees VALUES (10,
‘joyce', ‘5678’, 3445678 )
What DBI Is for?





Every database system is a little different
Every database system has a library, which is
different from each other
A extra layer is added to solve the difference
among different database systems
That's what Perl's DBI module is all about
Using DBI, the programmer doesn’t need to
know the details of different databases
systems
How DBI works?

DBI talks Database Driver (DBD), which
has the library for the real database
system and knows how to talk to the
database system directly
How to Use DBI?

DBI main functions:
 connect;
 prepare;
 disconnect;
 do;
 execute;
 fetchrow_array;
 rows;
Connect
driver source:
dbi:mysql:host=torch.cs.dal.ca;database
=torchacount;port=3306
 username: torch account
 password: Banner ID

Code Snippet











(1) use DBI;
(2) my $dbh = DBI -> connect
('dbi:mysql:host=torch;database=yeming;port=3306','yeming','B0xxxxxx') ||die "Database
(3) connection not made: $DBI::errstr";
(4) $dbh->do("DROP TABLE employees");
(5) $dbh->do("CREATE TABLE employees ( id INTEGER NOT NULL,
name VARCHAR(128),
title VARCHAR(128),
phone CHAR(8)
)");
(6) $dbh->do("INSERT INTO employees VALUES('8', 'yeming', '4173', '3442497')");
(7) $dbh->do("INSERT INTO employees VALUES('6', 'jamie', '4173', '3442495')");
(8) $dbh->do("INSERT INTO employees VALUES('4', 'jacky', '4445', '3442493')");
Code Snippet

(9) my $sth = $dbh->prepare('SELECT * FROM employees WHERE name=?');

(10) print "Enter name>";
(11) my $name=<>;
(12) my @data;
(13) chomp $name;
(14) $sth->execute($name) or die "Couldn't execute statement". $sth->errstr;
(15) while(@data=$sth->fetchrow_array())
{
(16) my $id=$data[0];
(17) my $name=$data[1];
(18) my $title=$data[2];
(19) my $phone=$data[3];
(20) print " $id $name $title $phone \n"
}
















(21)
(22)
(23)
(24)
$dbh->do("DELETE FROM employees WHERE name='jacky'");
$sth->execute('jacky') || die "Couldn't execute statement". $sth->errstr;
my $rows= $sth->rows;
print "There is $rows records about jacky\n ";
Code Snippet












(25) $dbh->do("UPDATE employees SET phone=4021828 WHERE
name='yeming'");
(26) $name='yeming';
(27) $sth->execute($name) or die "Couldn't execute statement". $sth>errstr;
(28) while(@data=$sth->fetchrow_array())
{
(29) my $id=$data[0];
(30)my $name=$data[1];
(31) my $title=$data[2];
(32) my $phone=$data[3];
(33) print " $id $name $title $phone \n"
}
(34) $dbh->disconnect();
Sources
http://www.perl.com/pub/a/1999/10/D
BI.html
http://www.unix.org.ua/orelly/perl/perlnut/ch12_03.htm
 http://www.pantz.org/database/mysql/
mysqlcommands.shtml

Questions?