Perl and Databases: MySQL,Sybase Oracle, dbi

Perl Tutorial : Perl data types
Perl Tutorial : Perl Operators
Perl Tutorial : Perl loops
Perl Tutorial : Perl Conditionals
Perl Tutorial : Perl Subroutines

Perl Tutorial : Perl regex
Perl Tutorial : Perl Files
Perl Tutorial : Perl & Databases
Perl Tutorial : Perl OOPs
Perl Tutorial : Perl signals
Perl Tutorial : Perl threads
Perl Tutorial : Perl Debugging

Introcution to Perl CGI

Introduction to modperl

Home >> perldatabases >>

Perl DBI Example

The DBI module enables your Perl applications to access multiple database types transparently. You can connect to MySQL, MSSQL, Oracle, Informix, Sybase, ODBC etc. without having to know the different underlying interfaces of each. The API defined by DBI will work on all these database types and many more. What is DBI and DBD::Oracle and where can one get it from?
DBI (previously called DBperl) is a database independent interface module for Perl. It defines a set of methods, variables and conventions that provide a consistent database interface independent of the actual database being used. DBD::Oracle is the Oracle specific module for DBI. It can be downloaded from CPAN.

What DBI drivers have I got?
In DBI you can programmatically discover what DBI drivers are installed.

#!/usr/bin/perl -w
require DBI;
my @drivers = DBI->available_drivers;
print join(", ", @drivers), "\n";

Example:

use strict;
use DBI;
my $dbh = DBI->connect( 'dbi:Oracle:orcl',
                        'username',
                        'password',
                        {
                          RaiseError => 1,
                          AutoCommit => 0
                        }
                      ) || die "Database connection not made: $DBI::errstr";
my $sql = qq{ CREATE TABLE employees ( id INTEGER NOT NULL,
                                       name VARCHAR2(128),
                                       position VARCHAR2(128),
                                     ) };
$dbh->do( $sql );
$dbh->disconnect();

SYBASE
Sybperl implements three Sybase extension modules to perl (version 5.002 or higher). Sybase::DBlib adds a subset of the Sybase DB-Library API. Sybase::CTlib adds a subset of the Sybase CT-Library (aka the Client Library) API. Sybase::Sybperl is a backwards compatibility module (implemented on top of Sybase::DBlib) to enable scripts written for sybperl 1.0xx to run with Perl 5. Using both the Sybase::Sybperl and Sybase::DBlib modules explicitly in a single script is not garanteed to work correctly.

The general usage format for both Sybase::DBlib and Sybase::CTlib is this:

use Sybase::DBlib;
# Allocate a new connection, usually refered to as a database handle
$dbh = new Sybase::DBlib username, password;
# Set an attribute for this dbh:
$dbh->{UseDateTime} = TRUE;
# Call a method with this dbh:
$dbh->dbcmd(sql code);

The DBPROCESS or CS_CONNECTION that is opened with the call to new() is automatically closed when the $dbh goes out of scope:

sub run_a_query {
my $dbh = new Sybase::CTlib $user, $passwd;
my @dat = $dbh->ct_sql("select * from sysusers");
return @dat;
}
# The $dbh is automatically closed when we exit the subroutine.

Sybase::DBlib
A generic perl script using Sybase::DBlib would look like this:

use Sybase::DBlib;
$dbh = new Sybase::DBlib 'sa', $pwd, $server, 'test_app';
$dbh->dbcmd("select * from sysprocesses\n");
$dbh->dbsqlexec;
$dbh->dbresults;
while(@data = $dbh->dbnextrow)
{
 .... do something with @data ....
}

$dbh = Sybase::DBlib->dbopen([$server [, $appname, [{attributes}] ]])
Open an additional connection, using the current LOGINREC information.

$status = $dbh->dbuse($database)
Executes ``use database $database'' for the connection $dbh.

$status = $dbh->dbcmd($sql_cmd)
Appends the string $sql_cmd to the current command buffer of this connection.

$status = $dbh->dbsqlexec
Sends the content of the current command buffer to the dataserver for execution. See the DB-library documentation for a discussion of return values.

$status = $dbh->dbresults
Retrieves result information from the dataserver after having executed dbsqlexec().

$status = $dbh->dbcancel
Cancels the current command batch.

$status = $dbh->dbcanquery
Cancels the current query within the currently executing command batch.

$dbh->dbfreebuf
Free the command buffer (required only in special cases - if you don't know what this is you probably don't need it :-)

$dbh->dbclose
Force the closing of a connection. Note that connections are automatically closed when the $dbh goes out of scope.

$dbh->DBDEAD

Returns TRUE if the DBPROCESS has been marked DEAD by DBlibrary.

$status = $dbh->DBCURCMD
Returns the number of the currently executing command in the command batch. The first command is number 1.

$status = $dbh->DBMORECMDS
Returns TRUE if there are additional commands to be executed in the current command batch.

$status = $dbh->DBCMDROW
Returns SUCCEED if the current command can return rows.

$status = $dbh->DBROWS
Returns SUCCEED if the current command did return rows

$status = $dbh->DBCOUNT
Returns the number of rows that the current command affected.

$row_num = $dbh->DBCURROW
Returns the number (counting from 1) of the currently retrieved row in the current result set.

$status = $dbh->dbhasretstat
Did the last executed stored procedure return a status value? dbhasretstats must only be called after dbresults returns NO_MORE_RESULTS, ie after all the selet, insert, update operations of he sored procedure have been processed.

$status = $dbh->dbretstatus
Retrieve the return status of a stored procedure. As with dbhasretstat, call this function after all the result sets of the stored procedure have been processed.

$status = $dbh->dbnumcols How many columns are in the current result set.

$status = $dbh->dbcoltype($colid)
What is the column type of column $colid in the current result set.

$status = $dbh->dbcollen($colid)
What is the length (in bytes) of column $colid in the current result set.

$string = $dbh->dbcolname($colid)
What is the name of column $colid in the current result set.

@dat = $dbh->dbretdata[$doAssoc])
Retrieve the value of the parameters marked as 'OUTPUT' in a stored procedure. If $doAssoc is non-0, then retrieve the data as an associative array with parameter name/value pairs.