Using Perl DBI by ojp13483

VIEWS: 62 PAGES: 5

									Biological Database                                               2/17/04

                            Using Perl DBI
We start with a program example and then explain the various parts. Here's a
complete example program to select and fetch some data:


  #!/usr/local/bin/perl –w
  use DBI;
  use strict;

  # set up information to connect to database
  my $data_source = "dbi:mysql:mygo";
  my $user = "gbenson";
  my $password ="wouldn’t_you_like_to_know";

  # connect
  my $dbh = DBI->connect($data_source, $user, $password)
      or die "Can't connect to $data_source: $DBI::errstr";

  # prepare a select statement for execution
  my $sth = $dbh->prepare( q{SELECT term_id, term_synonym
          FROM term_synonym WHERE term_id<100
  }) or die "Can't prepare statement: $DBI::errstr";

  # execute the statement
  my $rc = $sth->execute
      or die "Can't execute statement: $DBI::errstr";

  # create local variables for receiving the data
  my ($id, $descriptor);

  # loop through rows of data and print them
  while (($id, $descriptor) = $sth->fetchrow_array) {
      print "$id: $descriptor\n";
  }

  # check for problems which may have terminated the fetch early
  die $sth->errstr if $sth->err;

  # disconnect from database
  $dbh->disconnect;

To use DBI, first you need to load the DBI module:

  use DBI;
  use strict;


(The use strict; isn't required but is strongly recommended.)
Then you need to “connect” to your data source and get a handle for that
connection:

connect:

  $dbh = DBI->connect($data_source, $user, $password)
                or die $DBI::errstr;

This establishes a database connection to the requested $data_source and
returns a database handle object if the connection succeeds. The
$data_source value will be "dbi:driver_name:database_name". Note
that we are using the mysql driver. The database name is this example is mygo.
$user is your MYSQL login name, $password is your MYSQL password. If the
connect fails (see below), it returns undef and sets both $DBI::err and
$DBI::errstr. You should generally test the return status of connect and print
$DBI::errstr if it has failed.

Preparing and using a SELECT statement

DBI allows an application to "prepare" statements for later execution. A prepared
statement is identified by a statement handle held in a Perl variable. The variable
is $sth in the example above. The typical sequence of calls for a SELECT
statement is:

  prepare,
    execute, fetch, fetch, ...


prepare:

  $sth = $dbh->prepare(q{SELECT term_id, term_synonym
                         FROM term_synonym
                         WHERE term_id<100 })
           or die "Can't prepare statement: $DBI::errstr";

Prepares a statement for later execution and returns a statement handle. q{ }
puts the correct quotes around the string so that you don’t have to.


execute:

 $rc = $sth->execute or die $DBI::errstr;


execute sends the statement to the DBMS. A successful execute always returns
true. It is always important to check the return status of execute. For SELECT
statements, execute returns a true value. For a non-SELECT statements (insert,
update, delete), execute returns the number of rows affected, if known. If no rows
were affected, then execute returns "0E0", which Perl will treat as 0 but will
regard as true. Note that it is not an error for no rows to be affected by a
statement. If the number of rows affected is not known, then execute returns -1.

fetchrow_array:

@ary = $sth->fetchrow_array;


Data from a select is typically a sequence of rows. Use fetchrow_array to get
one row at a time. It fetches the next row of data and returns it as a list containing
the field values. Null fields are returned as undef values in the list. If there are no
more rows or if an error occurs, then fetchrow_array returns an empty list. You
should check $sth->err afterwards to discover if the empty list returned was
due to an error. Non-sequential record reads are not supported in this version of
the DBI. In other words, records can only be fetched in the order that the
database returned them, and once fetched they are forgotten.

Finally, when you have finished working with the data source, you should
“disconnect” from it:

$dbh->disconnect;




Adding Data to the Database

Here's a complete example program to insert some data from a file.

  #!/usr/local/bin/perl –w
  use DBI;
  use strict;
  my $data_source = "dbi:mysql:myflowersdb";
  my $user = "gbenson";
  my $password ="wouldn’t_you_like_to_know"

  my $dbh = DBI->connect($data_source, $user, $password, {
      RaiseError => 1});

  my $sth = $dbh->prepare( q{
      INSERT INTO flowers (id, name) VALUES (?, ?)
  });

  open FH, "<flowers.txt" or die "Unable to open flowers.txt: $!";
  while (<FH>) {
      chomp;
      my ($flower_id, $flower_name) = split /,/;
      $sth->execute($flower_id, $flower_name);
  }
  close FH;
  $dbh->disconnect;
The typical method call sequence for a non-SELECT statement is:

  prepare,
    execute,
    execute,
    execute.

The prepare statement uses placeholders (the question marks) for the values to
be inserted. Then, the execute statement provides the variable names that hold
those values.

$sth = $dbh->prepare(q{INSERT INTO flowers (id, name) VALUES (?, ?)});


The data comes from a text file “flowers.txt” which is given a file handle in the
open statement.

open FH, "<flowers.txt" or die "Unable to open flowers.txt: $!";

The lines of the file are processed one at a time in the while loop. Each line
contains two values, an id number and a flower name separated by a comma
(comma delimited). The split statement breaks the line at the comma and
assigns the two values to the two variables $flower_id and $flower_name. Within
the while loop, the insert statement is executed with the supplied values in
$flower_id and $ flower_name.

while (<FH>) {
    chomp;
    my ($flower_id, $flower_name) = split /,/;
    $sth->execute($flower_id, $flower_name);


This example uses RaiseError in the connect statement to avoid explicitly
checking for errors in each database call (i.e., we don’t have to write the “or die
…” statement after each database operation).

  my $dbh = DBI->connect($data_source, $user, $password, {
      RaiseError => 1});


When RaiseError is "on", any database method which results in an error
effectively does a die("$class $method failed: $DBI::errstr"), where $class is the
driver class and $method is the name of the method that failed. For example
modifying the prepare statement in the example by adding a third field without
adding a third parameter to the execute parameters produces the following
message:
my $sth = $dbh->prepare( q{
      INSERT INTO flowers (id, name, type) VALUES (?, ?)});
$sth->execute($flower_id, $flower_name);

DBD::mysql::st execute failed: Column count doesn't match value count
at row 1 at load.data.flowers.pl line 19, <FH> line 1.

Another useful statement

dump_results:

  $rows = $sth->dump_results($maxlen, $lsep, $fsep, $fh)


Fetches all the rows from statement handle $sth, calls DBI::neat_list for each
row, and prints the results to file handle $fh (defaults to STDOUT) separated by
line separation character $lsep (default is "\n" but will be different in non-
Linux/Unix operating system). Field separator $fsep defaults to ", " (comma
delimited) . $maxlen, which is the maximum length of any one field, defaults to
35.

This method is designed as a handy utility for prototyping and testing queries.
Since it uses “neat_list” to format and edit the string for reading by humans, it is
not recommended for data transfer applications. Here is a complete program
that uses dump_results:

#!/usr/local/bin/perl –w
  use DBI;
  use strict;

  # set up information to connect to database
  my $data_source = "dbi:mysql:mygo";
  my $user = "gbenson";
  my $password ="wouldn’t_you_like_to_know";

  # connect
  my $dbh = DBI->connect($data_source, $user, $password,
                   {RaiseError => 1});

  # prepare a select statement for execution
  my $sth = $dbh->prepare( q{SELECT * FROM term_synonym WHERE
                             term_id<100});

  # execute the statement
  my $rc = $sth->execute;

  # do a dump to see if the select was executed correctly
  # note no print required
  my $rows = $sth->dump_results("20");

  # disconnect from database
  $dbh->disconnect;

								
To top