1: =head1 NAME 2: 3: DbiFunc.pm -- provides basic DBI/SQL operations 4: 5: =head1 SYNOPSIS 6: 7: use DbiFunc; 8: 9: my $db = DbiFunc->new( "account_name" ); 10: 11: $cell_scalar = $db->queryCell( "select ..." ); 12: = $db->queryCell( $qHandle, $param[, $param2...] ); 13: 14: $row_ary_ref = $db->queryRow( "select ..." ); 15: = $db->queryRow( $qHandle, $param[, $param2...] ); 16: 17: $col_ary_ref = $db->queryColumn( "select ..." ); 18: = $db->queryColumn( $qHandle, $param[, $param2...] ); 19: 20: $ary_ary_ref = $db->queryGrid( "select ..." ); 21: = $db->queryGrid( $qHandle, $param[, $param2...] ); 22: 23: $N_rows_done = $db->sqlOnce( "update or insert statement" ); 24: 25: $qHandle = $db->sqlPrep( "select statement with 1 or more ?" ); 26: $xHandle = $db->sqlPrep( "update/insert statement with 1+ ?" ); 27: 28: $N_rows_done = $db->sqlApply( $xHandle, $param[, $param2...] ); 29: 30: &sqlDone( $anyHandle ); 31: 32: 33: =head1 DESCRIPTION 34: 35: The DbiFunc module is intended to make it easy to manipulate the 36: contents of RDBMS tables using perl scripts -- assuming that the perl 37: programmer is reasonably skilled at using SQL. 38: 39: The names of the methods should be self-explanatory, and some examples 40: are provided below to clarify proper usage. The issues that require 41: extra care by the programmer are: 42: 43: - Make sure that an SQL statement string or handle is 44: appropriate to the particular method you pass it to. 45: 46: - Make sure, when passing an SQL statement handle to 47: sqlApply or a query method, that you pass the correct 48: number of parameters, in the correct order, to fit 49: the statement that defined the handle. 50: 51: The "sqlOnce" method, in addition to performing "update" and "insert" 52: operations, is good for administrative functions as well, such as 53: "create table ...", "alter table ...", "drop table ...", etc. -- use 54: these with caution! 55: 56: 57: =head1 EXAMPLES 58: 59: my $db = DbiFunc->new( "my_account" ); 60: 61: # some simple queries: 62: 63: my $nrows = $db->queryCell("select count( key_fld ) from my_table"); 64: 65: print "my_table has $nrows entries\n"; 66: 67: my $keyref = $db->queryColumn("select key_fld from my_table"); 68: 69: print "list of key_fld entries in my_table:\n"; 70: print join( "\n", @$keyref ), "\n"; 71: 72: my $qry = "select * from my_table where key_fld = \'$$keyref[10]\'"; 73: my $rowref = $db->queryRow( $qry ); 74: 75: $db->sqlOnce( "delete from my_table where keyfld = \'$$keyref[10]\'"; 76: 77: print "Former contents the 11th row in my_table (just deleted):\n"; 78: print join( " ", @$rowref ), "\n"; 79: 80: my $gridref = $db->queryGrid("select name,date from my_table"); 81: 82: print "list of (remaining) names and dates from my_table:\n" 83: foreach my $row ( @$gridref ) 84: { 85: my ($name,$date) = @$row; 86: print "Name = $name, Date = $date\n"; 87: } 88: 89: # some parameterized operations: 90: 91: my $qry = $db->sqlPrep("select age from my_table where name = ?"); 92: my $upd = $db->sqlPrep("update my_table set age = ? where name = ?"); 93: my $ins = $db->sqlPrep("insert into my_table (name,age) values (?,?)"); 94: 95: foreach my $person ( "Barry", "Bill", "Bob" ) 96: { 97: $age = $db->sqlApply( $qry, $person ); 98: if ( $age ) 99: { # person's already in my_table 100: $db->sqlApply( $upd, $age+1, $person ); 101: } 102: else 103: { 104: $db->sqlApply( $ins, $person, 21 ); # "adults only" 105: } 106: } 107: 108: $db->sqlDone( $qry ); 109: $db->sqlDone( $upd ); 110: $db->sqlDone( $ins ); 111: 112: =head1 CAVEAT and discussion 113: 114: Some details in the following code are specific to Oracle, and no 115: attempt has been made by the original author to adapt to other brands 116: of RDBMS. Doing so should be a simple exercise for those who have 117: already used DBI in their particular environs. 118: 119: A nice feature of this DBI-wrapper module is that it allows all your 120: site-specific RDBMS environment stuff, including account names and 121: passwords, to be written just once -- into this module -- rather than 122: being repeated in every perl script that uses a database (so guess how 123: much perl code you need to fix the next time you have to change 124: passwords or migrate to a new RDBMS release). Plus, it is nice being 125: able to call just one method to execute an SQL statement and get back 126: the results. 127: 128: =head1 AUTHORS 129: 130: David Graff <graff@ldc.upenn.edu> 131: Jonathan Wright <jdwright@ldc.upenn.edu> 132: 133: =cut 134: 135: package DbiFunc; 136: 137: @ISA = qw(DBI::db); 138: 139: use DBI; 140: use Carp; 141: use strict; 142: 143: # Put essential environment stuff here, as required by your RDBMS 144: # e.g. for a given (mythical) installation of Oracle: 145: 146: $ENV{ORACLE_SID} = "MYSID"; 147: $ENV{ORACLE_BASE} = "/my/oracle/base"; 148: $ENV{ORACLE_HOME} = "$ENV{ORACLE_BASE}/product/8.1.5"; 149: $ENV{PATH} .= ":$ENV{ORACLE_HOME}/bin"; 150: my $ORA_DSN = 'dbi:Oracle:MYSID.whatsadsn_anyway'; 151: 152: 1; 153: 154: sub new { 155: my ($class,$account,$attr) = @_; 156: my %passwd = ( "some_account" => "some_password", 157: "other_account" => "other_password", 158: ); 159: $attr = { PrintError => 0, RaiseError => 1 } 160: unless ref $attr eq 'HASH'; 161: my $dbh; 162: if ( exists $passwd{$account} ) { 163: $dbh = DBI->connect($ORA_DSN,$account,$passwd{$account},$attr) || 164: croak "Oracle connection failed: $DBI::errstr"; 165: } else { 166: croak "\n$account is not a known account\n"; 167: } 168: return bless $dbh, 'DbiFunc'; 169: } 170: 171: sub DESTROY { 172: my ($dbh) = @_; 173: $dbh->disconnect if ( defined( $dbh )); 174: } 175: 176: sub queryCell 177: { 178: my ($dbh,$sql,@params) = @_; 179: my $rowref; 180: if ( ref( $sql ) =~ /^DBI/ ) { 181: $sql->execute( @params ); 182: $rowref = $sql->fetchrow_arrayref; 183: } elsif ( $sql !~ /^\s*select / ) { 184: carp "queryCell called without a select statement\n"; 185: return undef; 186: } else { 187: my $sth = $dbh->prepare( $sql ); 188: $sth->execute; 189: $rowref = $sth->fetchrow_arrayref; 190: $sth->finish; 191: } 192: return $rowref->[0]; 193: } 194: 195: sub queryRow 196: { 197: my ($dbh,$sql,@params) = @_; 198: my $rowref; 199: if ( ref( $sql ) =~ /^DBI/ ) { 200: $sql->execute( @params ); 201: $rowref = $sql->fetchrow_arrayref; 202: } elsif ( $sql !~ /^\s*select / ) { 203: carp "queryRow called without a select statement\n"; 204: return undef; 205: } else { 206: my $sth = $dbh->prepare( $sql ); 207: $sth->execute; 208: $rowref = $sth->fetchrow_arrayref; 209: $sth->finish; 210: } 211: return $rowref; 212: } 213: 214: sub queryColumn 215: { 216: my ($dbh,$sql,@params) = @_; 217: my $rowref; 218: if ( ref( $sql ) =~ /^DBI/ ) { 219: $sql->execute( @params ); 220: $rowref = $sql->fetchall_arrayref; 221: } elsif ( $sql !~ /^\s*select / ) { 222: carp "queryColumn called without a select statement\n"; 223: return undef; 224: } else { 225: my $sth = $dbh->prepare( $sql ); 226: $sth->execute( @params ); 227: $rowref = $sth->fetchall_arrayref; 228: $sth->finish; 229: } 230: my @col = (); 231: foreach my $r ( @$rowref ) { 232: push( @col, $r->[0] ); 233: } 234: return \@col; 235: } 236: 237: sub queryGrid 238: { 239: my ($dbh,$sql,@params) = @_; 240: my $rowref; 241: if ( ref( $sql ) =~ /^DBI/ ) { 242: $sql->execute( @params ); 243: $rowref = $sql->fetchall_arrayref; 244: } elsif ( $sql !~ /^\s*select / ) { 245: carp "queryGrid called without a select statement\n"; 246: return undef; 247: } else { 248: my $sth = $dbh->prepare( $sql ); 249: $sth->execute; 250: $rowref = $sth->fetchall_arrayref; 251: $sth->finish; 252: } 253: return $rowref; 254: } 255: 256: sub sqlOnce 257: { 258: my ($dbh,$sql) = @_; 259: my $ret = $dbh->do( $sql ); 260: return $ret; 261: } 262: 263: sub sqlPrep 264: { 265: my ($dbh,$sql) = @_; 266: my $this_sth = $dbh->prepare( $sql ); 267: return $this_sth; 268: } 269: 270: sub sqlApply 271: { 272: my ($dbh,$this_sth, @params) = @_; 273: my $ret = $this_sth->execute( @params ); 274: return $ret; 275: } 276: 277: sub sqlDone 278: { 279: my ($dbh,$this_sth) = @_; 280: $this_sth->finish; 281: } 282: 283: 1; 284:
|
---|
Replies are listed 'Best First'. | |
---|---|
Re: DbiFunc.pm -- DBI wrapper
by Juerd (Abbot) on Mar 27, 2002 at 08:46 UTC | |
Re: DbiFunc.pm -- DBI wrapper
by mpeppler (Vicar) on Mar 27, 2002 at 21:07 UTC | |
Re: DbiFunc.pm -- DBI wrapper
by princepawn (Parson) on Oct 26, 2003 at 04:39 UTC |
Back to
Craft