Perl Programming/DBI - Perl database interface
DBI - Perl database interface
[edit | edit source ]There is a comprehensive library of modules for connecting to databases from Perl programs. It is maintained by Tim Bunce and it is called DBI - database interface. The main Website for DBI is http://dbi.perl.org/.
Installing the necessary modules
[edit | edit source ]To use DBI to connect to a database, you need at least two CPAN modules. One is the main DBI module, simply called DBI. The other one is a DBD - a database driver module. There are DBI drivers for many mainstream database management systems, such as MySQL and Oracle. The examples in this tutorial concern Oracle; accordingly, the database driver for Oracle is called DBD::Oracle.
So, to run the examples in this chapter you need the modules DBI and DBD::Oracle.
Installing DBI
[edit | edit source ]Installing DBI is straightforward. (For information about installing CPAN modules, see the chapter about CPAN.)
Installing DBD::Oracle
[edit | edit source ]When installing DBD::Oracle, there are a few things that you need to prepare.
Getting the correct version
[edit | edit source ]First of all, when installing DBD::Oracle, make sure that you get a version by Pythian. Pythian maintains DBD::Oracle since version 1.17 (February 2006).
Preparing environment variables
[edit | edit source ]You need to create the environment variables ORACLE_USERID and ORACLE_SID. They are needed to test the connection to your database after DBD::Oracle is installed. The value for ORACLE_USERID must be a valid username and password for your database. ORACLE_SID must be set to database name as it appears in TNSNAMES.ORA. On a Unix-like system run:
export ORACLE_USERID="username/password"
export ORACLE_SID="DATABASE_NAME"
On Windows, you only need ORACLE_USERID:
set ORACLE_USERID="username/password@DATABASE_NAME"
When you have finished defining these environment variables, install DBD::Oracle from CPAN the usual way.
Using DBI
[edit | edit source ]Here is a very simple test script to get you going.
usestrict; usewarnings; # There is no need to use DBD::Oracle. The DBD is loaded automatically later. useDBI; # Connecting to the database. Take the SID from TNSNAMES.ORA. # Here the DBD is loaded. # $dbh will be the database handle - a variable through which # you connect to your database. my$dbh=DBI->connect("dbi:Oracle:SID","username","password",{RaiseError=>1}); # A simple date fetch # Prepare the SQL statement. # $sth is a statement handle - an environment for running an SQL statement. my$sth=$dbh->prepare('select sysdate from dual');# note no ';' at the end of the SQL # Execute the SQL statement; don't print it yet $sth->execute; # This "loop" prints all the rows (actually just one, in this case) while(my@row=$sth->fetchrow_array){ print"@row\n"; } # A real SELECT statement. # Replace my_favourite_table with a name of a table from your database. $sth=$dbh->prepare('select * from my_favourite_table'); $sth->execute; # This is a real loop, that prints all the rows. # It's very rudimentary; see the DBI documentation # for better tools for pretty printing. # # $sth->fetchrow_array returns a Perl array, # in which every member represents one of the retrieved columns. # In turn, every row is an array of values. while(my@row=$sth->fetchrow_array){ # replace undefined values (NULLs in the db) with the string "NULL" @row=map{defined($_)?$_:"NULL"}@row; my$row_as_string=join("\t",@row); printf"%s\n",row_as_string; } # A real SELECT statement using a hash reference and place holder. # Replace my_favourite_table with a name of a table from your database. $sth=$dbh->prepare('select * from my_favourite_table where my_field = ?'); my$field_value='australia'; $sth->execute($field_value); # Here is the loop again, this time we use fetchrow_hashref # which makes our code more resistant to breaks due to schema changes. # It also spares us from remember which location a field is # positioned in an array. # Recall that %{$var} de-references the hashref $var # and that $var->{field} specifies the key 'field' while(my$row=$sth->fetchrow_hashref){ foreachmy$next_field(keys%{$row}){ if(defined$row->{$next_field}){ print$row->{$next_field}; } else{ print'NULL'; } print"\t"; } print"\n"; } # gracefully disconnect from the database $dbh->disconnect();