DBI example

Can anyone provide me with an example of a perl script that uses DBI to interface to an Oracle database that resides in a remote server? I'm mainly interested in the functionality to connect/login to the database and the function calls to execute an SQL statement. I appreciate any help with this.

Feel free to post here or email me directly.

Dave Smith
Sr. Software Engineer
Nike, Inc.
(503) 671-4238
DavidH.Smith@nike.com

Find more posts tagged with

Comments

Migrateduser

Sure... params are basically read in from a config file but this is the basic syntax. Let me know if you need explanation / detail.

Dave

sub ts_db_connect
{
$myerr = 0;

$dbh = DBI->connect($CONFIG{ECM_DB_NAME}, $CONFIG{ECM_DB_USER}, $CONFIG{ECM_DB_PASSWORD}) || ($myerr = 1);

if ($myerr)
{
return (1, DBI::errstr);
}
return 0;
}

---
Interwoven Humor Page
http://iw.simpleinternet.com

Adam Stoller

My customer has a module called 'database.pm' which they use to perform a number of database interactions. This was, I believe, originally created by some folks in Accenture and looks something like this:I've modified this slightly for posting purposes, your mileage may vary:
require 5; # 4 won't cut it.
package ****::database;
use TeamSite::Config;
use DBI;

BEGIN {
$ENV{ORACLE_HOME} = '/opt/oracle/product/8.1.7';
$ENV{TWO_TASK} = '';
}
my $iwhome = TeamSite::Config::iwgethome();
my $strConn = "dbi Smiley Surprised

racle:";
# somewhere you need to get the name of the user and password for
# the connection - we do it one way, but I'd rather not broadcast
# that here - so assume the following variables are filled in
# ('Prev' is for the preview server):
my($strUser, $strPrevUser, $strPassword, $strPrevPassword);

#attribute to pass to Oracle to disable automatic error checking.
my %attr = (
PrintError => 1,
RaiseError => 1,
AutoCommit => 1,
);
#=====================================================================
#Params:
# 1) Use Preview database?
#
# Returns
# The handle to the database connection object.
#=====================================================================
sub connect {
my ($huh, $boolToPreview) = @_;
my $dbconn;

if ($boolToPreview eq "true") {
$dbconn = DBI->connect($strConn, $strPrevUser, $strPrevPassword)
or die "Can't connect to database: ", $DBI::errstr;
}
else {
$dbconn = DBI->connect($strConn, $strUser, $strPassword)
or die "Can't connect to database: ", $DBI::errstr;
}
return $dbconn;
}
#=====================================================================
#Params:
# 1) Query to execute
# 2) Use Preview database?
#
# Returns
# The result set as an array of hashes.
#=====================================================================
sub execute_query {
my ($mod, $strQuery, $boolToPreview)= @_;
my ($sth, $dbconn, $ref, @rows);
eval {
$dbconn = ****::database->connect($boolToPreview);
die "Can't get Database Connection;" if(!$dbconn);
$sth = $dbconn->prepare($strQuery)
or die "Can't prepare query: ", $dbconn->errstr();
$sth->execute
or die "Can't execute query: ", $sth->errstr();

while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
push @rows, $ref;
}
$dbconn->disconnect
or die "Can't disconnect from database: ", $dbh->errstr();
};
if ($@) { #an error occured
die("DBCONN ERR: ".$dbconn->errstr()) if($dbconn);
die("STMT ERR: ".$sth->errstr()) if($sth);
die("DB ERR: ".$dbh->errstr()) if($dbh);
die("Unable to update database: $@);
}
return @rows;
}
#=====================================================================
#Params:
# 1) Query to execute
# 2) Use Preview database?
#
# Returns
# The success or failure (1 or 0).
#=====================================================================
sub execute_update {
my ($mod, $strQuery, $boolToPreview)= @_;
my $sth;
my $dbconn;
eval { ##Auto commit is enabled by default.##
$dbconn = ****::database->connect($boolToPreview);
die "Can't get Database Connection;" if(!$dbconn);
$sth = $dbconn->prepare($strQuery)
or die "Can't prepare query $strQuery";
die "Can't execute query $strQuery" if (!$sth->execute);
die "Can't disconnect from database" if (!$dbconn->disconnect);
};
if ($@) { #an error occured
die("DBCONN ERR: ".$dbconn->errstr()) if($dbconn);
die("STMT ERR: ".$sth->errstr()) if($sth);
die("DB ERR: ".$dbh->errstr()) if($dbh);
die("Unable to update database: $@);
}
return 1;
}

--fish
Senior Consultant, Quotient Inc.
http://www.quotient-inc.com

Migrateduser

Awesome - thank you for the detail. The query parts of it look very familiar - I used DBI about a dozen years ago - and that part makes complete sense. Great detail.

But the connection part I still have questions about. The two ENV vars you set up (ORACLE_HOME and TWO_TASK). Clearly the ORACLE_HOME is set up to the path to the Oracle DB. What is TWO_TASK? In your example is your database on the same server as the perl script is running on or is it on a different server?

Dave Smith
Sr. Software Engineer
Nike, Inc.
(503) 671-4238
DavidH.Smith@nike.com

Migrateduser

If my Oracle server is a remote server, how do I set up the $CONFIG{ECM_DB_NAME} variable - what does that look like? Let's say my Oracle DB is located on the server oracle.nike.com - how do I connect to it?

Dave Smith
Sr. Software Engineer
Nike, Inc.
(503) 671-4238
DavidH.Smith@nike.com

Adam Stoller

I'm not sure what TWO_TASK is for - like I said I didn't write this module.

--fish
Senior Consultant, Quotient Inc.
http://www.quotient-inc.com

Migrateduser

Do you have an Oracle client set up on the machine you want to connect from? That is what it is used for, to specify the path to the Oracle drivers and other binaries, etc.

Dave

---
Interwoven Humor Page
http://iw.simpleinternet.com

Adam Stoller

This does work for an Oracle server on a remove system - the variables $strUser and $strPrevUser are slightly overloaded - the value would be something like "dbausername@hostname-or-ipaddress" - and that's how it connects to the remote server.

--fish
Senior Consultant, Quotient Inc.
http://www.quotient-inc.com

Migrateduser

Outstanding - that's what I was looking for. Thank you very much. Thanks to dave as well. This was incredibly helpful.

Dave Smith
Sr. Software Engineer
Nike, Inc.
(503) 671-4238
DavidH.Smith@nike.com