1 / 15

DBI tutorial

February 5 th , 2007. DBI tutorial. 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:.

waynebradley
Download Presentation

DBI tutorial

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. February 5th, 2007 DBI tutorial

  2. What is DBI DBI is short for Database Interface, which is a Perl module

  3. 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:

  4. An Example of Table id name Title Phone 8 Yeming 4173 3442497 6 Jamie 4175 3442495 4 Jacky 4177 3442499

  5. 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

  6. 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 )

  7. 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

  8. 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

  9. How to Use DBI? DBI main functions: connect; prepare; disconnect; do; execute; fetchrow_array; rows;

  10. Connect driver source: dbi:mysql:host=torch.cs.dal.ca;database=torchacount;port=3306 username: torch account password: Banner ID

  11. 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')");

  12. 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) $dbh->do("DELETE FROM employees WHERE name='jacky'"); (22) $sth->execute('jacky') || die "Couldn't execute statement". $sth->errstr; (23) my $rows= $sth->rows; (24) print "There is $rows records about jacky\n ";

  13. 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();

  14. Sources http://www.perl.com/pub/a/1999/10/DBI.html http://www.pantz.org/database/mysql/mysqlcommands.shtml http://www.unix.org.ua/orelly/perl/perlnut/ch12_03.htm

  15. Questions?

More Related