Re: Some DBI question -

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.