On Sun, May 30, 2010 at 01:33, newbie01 perl <newbie01.p...@gmail.com> 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. -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/