Perl DBI

1
Perl DBI

• Chris Bailey
• Bacterial Pathogenesis Genomics Unit
• cmb036_at_bham.ac.uk

2
What is DBI

• DBI is a data access module for perl.
• Sits between perl and database server
• Incorporates database driver common abstraction
  layer

3
Using DBI

• ! /usr/bin/perl
• Use DBI

4
Creating a database handle

• 1st thing you need to do in order to get data
• Requires a data source name
• Has the form
• dbiDriverNameDatabaseName
• dbiDriverNameDatabaseName_at_hostnameport
• dbiDriverNamedatabaseDatabaseNamehosthostname
  portport

5
Creating a database handle

• The data source name is then passed as a string
• E.g.
• dsn dbimysqlmyco
• dbh DBI-gtconnect(dsn, username,password,
  \attr)

6
Available drivers

• To get a list of drivers that can be used in DBI
• _at_drivers DBI-gtavailable_drivers
• E.g.
• mysql
• oracle
• odbc
• postgresql

7
Database handle attributes

• Attributes set as key value pairs
• AutoCommit Automatically commit
• (transactions)
• PrintWarn Automatically print warning
• PrintError Print error messages
• RaiseError Die on error messages

8
Testing the connection

• dbh will be true if there is a connection, false
  otherwise
• If there is an error message it will be available
  in DBIerrstr
• Assuming success dbh contains an instance of a
  DBI object which is connected to the database set
  in dsn

9
Sending a query

• If you dont want access to the result (just a
  return value) can use do
• rv dbh-gtdo(DELETE FROM cds)
• rv will contain the number of rows affected by
  the operation

10
Sending a query

• If you need to return data use the prepare method
  on your database handle
• sth dbh-gtprepare(query)
• This creates a statement handle object
• Can be used to get information on the query
• sth will be undef in the event of a problem with
  the statement

11
Statement handles

• What information you can get on your statement
  depends on the database engine
• Some engines do not pre-process queries
• Those that do will not all return the same
  information

12
Statement handles

• Statement preparation does have advantages though
  (more on that later)
• Once statement is prepared, you execute it
• rv sth-gtexecute()
• rv contains the number of rows affected/returned
• rv stores 0 as 0E0, which evaluates as true
• ? rv is only false if the execute fails

13
Statement handles

• Once the statement is executed, you can get data
  from the handle
• Several methods
• fetchrow_array
• fetchrow_arrayref
• fetchrow_hashref
• fetchall_arrayref
• fetchall_hashref

14
Fetchrow_array

• Returns the next row in the result set as an
  array
• _at_array sth-gtfetchrow_array()
• array0 1st col
• arrayn nth col

15
Fetchrow_arrayref

• Returns a reference to an array containing the
  next row in the result set
• array_ref sth-gtfetchrow_arrayref
• array_ref-gt0 1st col
• array_ref-gtn nth col

16
Fetchrow_hashref

• Returns a reference to a hash containing the next
  row in the result set
• hash_ref sth-gtfetchrow_hashref
• hash_ref-gtname name col
• hash_ref-gtxyz xyz col

17
Fetchall_arrayref

• Returns a reference to an array containing all
  the rows in the result set
• array_ref sth-gtfetchall_arrayref
• array_ref-gt0-gt0 1st row,1st col
• array_ref-gtn-gtm nth row,mth col

18
Fetchall_arrayref

• Can define the columns you want to return
• Maximum number of rows to return
• Whether to return an array of array refs or and
  array of hash refs
• Full syntax
• sth-gtfetchall_arrayref(slice,max_rows)
• Where max_rows is an integer
• slice is an array ref

19
Fetchall_arrayref

• fetchall_arrayref(1)
• Return 2nd column of every row
• Access via array_ref-gtrow-gtcol
• fetchall_arrayref(-2,-1,100)
• Return 2nd to and last column of next 100 rows
• Access via array_ref-gtrow-gtcol

20
Fetchall_arrayref

• fetchall_arrayref()
• Return all columns, as hash refs
• Access via array_ref-gtrow-gtcol_name
• fetchall_arrayref( col1 gt 1, col2 gt 1)
• Return columns col1 and col 2, as hash refs
• Access via array_ref-gtrow-gtcol_name

21
Closing the statement handle

• sth-gtfinish gets rid of all data and resources
  needed by the statement handle.
• Often isnt needed
• (finish is automatically called after the last
  row of a select is returned)

22
More advanced statements

• DBI can substitute values into prepared
  statements
• E.g.
• SELECT FROM table WHERE name ?

23
More advanced statements

• You can include 1 or more question marks in a
  statement to be prepared
• Question marks act as placeholders for variables
  you insert later

24
Placeholders

• Caveat
• If the question marks prevent the database from
  preparing an execution plan then prepare() will
  fail
• E.g.
• SELECT name,age FROM ?
• SELECT name,? FROM people
• Will NOT work

25
Placeholders

• NULL values
• SELECT FROM table WHERE col1 ?
• Binding undef to ? will not find NULL in col1
• Have to explicitly ask IS NULL
• So you have to write
• WHERE col1 ? OR (? IS NULL AND col1 IS NULL)

26
Using placeholders

• query SELECT FROM table WHERE name?
• sth dbh-gtprepare(query)
• sth-gtexecute(tom)
• _at_rowsth-gtfetchrow_array()
• sth-gtexecute(dave)
• _at_rowsth-gtfetchrow_array()
• sth-gtfinish()

27
Placeholder Advantages

• Saves have to prepare a statement multiple times
• Will automatically quote strings for you
• Performance increase

28
Fetchall_hashref

• Returns a reference to a hash containing all the
  rows in the result set
• hash_ref sth-gtfetchall_hashref(id)
• hash_ref-gt1-gtname row where id col 1,
  name col
• hash_ref-gtn-gtxyz row where id col n, xyz
  col

29
Fetchall_hashref

• Requires 1 variable to be passed
• Name of column to use as hash key
• E.g. sth-gtfetchall_hashref(accession)
• If the data in the column selected is not unique,
  previous rows will be overwriten

30
Putting it all together

• ! /usr/bin/perl
• use DBI
• dsn dbimysqlcolibase
• dbh DBI-gtnew(dsn, root, password,
  raise_error gt 0)
• unless (dbh)
• die Couldnt connect to database .
  DBIerrstr

31
Putting it all together

• _at_accessions (U00096, BA000007, AE005174)
• sth-gtprepare(SELECT FROM genome WHERE
  accession ?)
• foreach (_at_accessions)
• sth-gtexecute(_)

32
Putting it all together

• print Data for accession _\n
• while (_at_rows sth-gtfetchrow_array)
• print row0, row2, row4\n
• OR

33
Putting it all together

• print Data for accession _\n
• while (rows sth-gtfetchrow_arrayref)
• print row-gt0, row-gt2, .
• row-gt4\n

34
Tomorrow

• Using the database you created last Monday
• Generate 2 web pages using CGI perl module
• 1st page input search criteria
• 2nd page output search results