Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

DbiFunc.pm -- DBI wrapper

by graff (Chancellor)
on Mar 27, 2002 at 08:20 UTC ( #154616=perlcraft: print w/ replies, xml ) Need Help??

   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: 

Comment on DbiFunc.pm -- DBI wrapper
Download Code
Re: DbiFunc.pm -- DBI wrapper
by Juerd (Abbot) on Mar 27, 2002 at 08:46 UTC

    $sql !~ /^\s*select /

    Suggested improvement:

    $sql !~ /^\s*select\s/i
    This would also allow "SELECT", and tabs or newlines after the select keyword.

    U28geW91IGNhbiBhbGwgcm90MTMgY
    W5kIHBhY2soKS4gQnV0IGRvIHlvdS
    ByZWNvZ25pc2UgQmFzZTY0IHdoZW4
    geW91IHNlZSBpdD8gIC0tIEp1ZXJk
    

Re: DbiFunc.pm -- DBI wrapper
by mpeppler (Vicar) on Mar 27, 2002 at 21:07 UTC
    The checking for SELECT, UPDATE, INSERT, DELETE limits your code to pure SQL, and will not let the user execute stored procedures via your wrapper. This makes it less useful for users of Sybase, MS-SQL, and possibly other RDBMS engines that support stored procedures of some form.

    Michael

Re: DbiFunc.pm -- DBI wrapper
by princepawn (Parson) on Oct 26, 2003 at 04:39 UTC
    I like the convenience methods. They are the exact same as the ones in DBIx::AnyDBD's sample app, included with the distro... one thing. I dont think it is a good idea that this package attempts to simplify database connections as well, thus stepping into the realm of DBIx::Connect.

    I think it should do one thing well and that is make it easy to retrieve results from DBI queries.

    DBSchema::Sample

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlcraft [id://154616]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (8)
As of 2014-07-29 07:13 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (211 votes), past polls