On Sun, May 30, 2010 at 01:33, newbie01 perl <[email protected]> wrote:
> Hi,
>
> Am trying out DBI for Oracle and just want to know if for example, I need to
> include column formatting stuff etc., is it best to just put them into a
> .sql file and then execute the .sql file instead of placing them in the Perl
> DBI script?
> Also, is there anyway to sort of "hide" the password somehow when using Perl
> DBI?
>
> Any advise or feedback will be very much appreciated.
>
> Thanks in advance.
>
In general, I like to write code using the DBI like this (you will
need DBD::SQLite to run this example):
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $db = "whatever";
my $user = "username";
my $pass = do {
#this file should by read-only for the user only
open my $fh, "<", "$ENV{HOME}/.dbipass"
or die "could not open password file\n";
local $/;
<$fh>
};
my $dbh = DBI->connect(
"dbi:SQLite:dbname=$db",
$user,
$pass,
{
AutoCommit => 1,
ChopBlanks => 1,
RaiseError => 1,
PrintError => 0,
FetchHashKeyName => "NAME_lc",
}
) or die "could not connect to $db: ", DBI->errstr;
####
# create the database
####
$dbh->do("
CREATE TABLE tab (
tab_id INTEGER,
tab_name VARCHAR(35)
)
");
$dbh->do("
CREATE TABLE col (
tab_id INTEGER,
col_position INTEGER,
col_name VARCHAR(35)
)
");
$dbh->do("
CREATE TABLE idx (
tab_id INTEGER,
idx_name VARCHAR(35)
)
");
####
# load the database
####
my %inserts = (
tab => $dbh->prepare("insert into tab values (?, ?)"),
col => $dbh->prepare("insert into col values (?, ?, ?)"),
idx => $dbh->prepare("insert into idx values (?, ?)"),
);
while (<DATA>) {
chomp;
my ($tab, @row) = split /,/;
$inserts{$tab}->execute(@row);
}
####
# query the database
####
my $sth = $dbh->prepare("
SELECT col.col_position, col.col_name
FROM tab, col
WHERE tab.tab_id = col.tab_id
AND tab.tab_name = ?
ORDER BY col.col_position
");
for my $tab_name (qw/tab col idx/) {
$sth->execute($tab_name);
print "table $tab_name:\n";
while (my $row = $sth->fetchrow_hashref) {
print "\t$row->{col_name}\n";
}
print "\n";
}
####
# drop the tables so this script can run a second time
####
$dbh->do("drop table tab");
$dbh->do("drop table col");
$dbh->do("drop table idx");
####
# data to be inserted into the db
####
__DATA__
tab,1,tab
tab,2,col
tab,3,idx
col,1,1,tab_id
col,1,2,tab_name
col,2,1,tab_id
col,2,2,col_positon
col,2,3,col_name
col,3,1,tab_id
col,3,2,idx_name
--
Chas. Owens
wonkden.net
The most important skill a programmer can have is the ability to read.
