Home · Authors · Recent · News · Mirrors · FAQ · Feedback

in  
Michael R. Davis > DBIx-Array-0.23 > DBIx::Array

Download:
DBIx-Array-0.23.tar.gz

Dependencies

Annotate this POD

CPAN RT

Open  0
View/Report Bugs
Module Version: 0.23   Source  

NAME ^

DBIx::Array - This module is a wrapper around DBI with array interfaces

SYNOPSIS ^

  use DBIx::Array;
  my $dbx=DBIx::Array->new;
  $dbx->connect($connection, $user, $pass, \%opt); #passed to DBI
  my @array=$dbx->sqlarray($sql, @params);

DESCRIPTION ^

This module is for people who truly understand SQL and who understand Perl data structures. If you understand how to modify your SQL to meet your data requirements then this module is for you. In the example below, only one line of code is needed to generate an entire HTML table.

  print &tablename($dba->sqlarrayarrayname(&sql, 15)), "\n";

  sub tablename {
    use CGI; my $html=CGI->new(""); #you would pass this reference
    return $html->table($html->Tr([map {$html->td($_)} @_]));
  }
   
  sub sql { #Oracle SQL
    return q{SELECT LEVEL AS "Number",
                    TRIM(TO_CHAR(LEVEL, 'rn')) as "Roman Numeral"
               FROM DUAL CONNECT BY LEVEL <= ? ORDER BY LEVEL};
  }

This module is used to connect to both Oracle 10g and 11g using DBD::Oracle on both Linux and Win32, MySQL 4 and 5 using DBD::mysql on Linux, and Microsoft SQL Server using DBD::Sybase on Linux and using DBD::ODBC on Win32 systems in a 24x7 production environment. The tests are written against DBD::CSV and DBD::XBase.

USAGE ^

CONSTRUCTOR ^

new

  my $dbx=DBIx::Array->new();
  $dbx->connect(...); #connect to database, sets and returns dbh

  my $dbx=DBIx::Array->new(dbh=>$dbh); #already have a handle

METHODS ^

initialize

METHODS (Properties) ^

name

Sets or returns a user friendly identification string for this database connection

  my $name=$dbx->name;
  my $name=$dbx->name($string);

METHODS (DBI Wrappers) ^

connect

Connects to the database and returns the database handle.

  $dbx->connect($connection, $user, $pass, \%opt);

Pass through to DBI->connect;

Examples:

  $dbx->connect("DBI:mysql:database=mydb;host=myhost", "user", "pass", {AutoCommit=>1, RaiseError=>1});

  $dbx->connect("DBI:Sybase:server=myhost;datasbase=mydb", "user", "pass", {AutoCommit=>1, RaiseError=>1}); #Microsoft SQL Server API is same as Sybase API

  $dbx->connect("DBI:Oracle:TNSNAME", "user", "pass", {AutoCommit=>1, RaiseError=>1});

disconnect

Calls $dbh->disconnect

  $dbx->disconnect;

Pass through to dbh->disconnect

commit

Pass through to dbh->commit

  $dbx->commit;

rollback

Pass through to dbh->rollback

  $dbx->rollback;

AutoCommit

Pass through to dbh->{'AutoCommit'} or dbh->{'AutoCommit'}=shift;

  $dbx->AutoCommit(1);
  &doSomething if $dbx->AutoCommit;

For transactions that must complete together, I recommend

  { #block to keep local... well... local.
    local $dbx->dbh->{"AutoCommit"}=0;
    $dbx->insert($sql1, @bind1);
    $dbx->update($sql2, @bind2);
    $dbx->insert($sql3, @bind3);
  } #What is AutoCommit now?  Do you care?

If AutoCommit reverts to true at the end of the block then DBI commits. Else AutoCommit is still false and still not committed. This allows higher layers to determine commit functionality.

RaiseError

Pass through to dbh->{'RaiseError'} or dbh->{'RaiseError'}=shift;

  $dbx->RaiseError(1);
  &doSomething if $dbx->RaiseError;

  { #local block
    local $dbx->dbh->{"RaiseError"}=0;
    $dbx->insert($sql, @bind); #do not die
  }

errstr

Returns $DBI::errstr

  $dbx->errstr;

dbh

Sets or returns the database handle object.

  $dbx->dbh;
  $dbx->dbh($dbh);  #if you already have a connection

METHODS (Read) ^

sqlcursor

Returns the prepared and executed SQL cursor so that you can use the cursor elsewhere. Every method in this package uses this single method to generate a sqlcursor.

  my $sth=$dbx->sqlcursor($sql,  @param); #binds are ? values are positional
  my $sth=$dbx->sqlcursor($sql, \@param); #binds are ? values are positional
  my $sth=$dbx->sqlcursor($sql, \%param); #binds are :key

Note: In true Perl fashion extra hash binds are ignored.

  my @foo=$dbx->sqlarray("select :foo, :bar from dual",
                         {foo=>"a", bar=>1, baz=>"buz"}); #returns ("a", 1)

  my $one=$dbx->sqlscalar("select ? from dual", ["one"]); #returns "one"

  my $two=$dbx->sqlscalar("select ? from dual", "two");   #returns "two"

Scalar references are passed in and out with a hash bind.

  my $inout=3;
  $dbx->execute("BEGIN :inout := :inout * 2; END;", {inout=>\$inout});
  print "$inout\n";  #$inout is 6

Direct Plug-in for SQL::Abstract but no column alias support.

  my $sabs=SQL::Abstract->new;
  my $sth=$dbx->sqlcursor($sabs->select($table, \@fields, \%where, \@sort));

sqlscalar

Returns the SQL result as a scalar.

This works great for selecting one value.

  my $scalar=$dbx->sqlscalar($sql,  @parameters); #returns $
  my $scalar=$dbx->sqlscalar($sql, \@parameters); #returns $
  my $scalar=$dbx->sqlscalar($sql, \%parameters); #returns $

sqlarray

Returns the SQL result as an array or array reference.

This works great for selecting one column from a table or selecting one row from a table.

  my $array=$dbx->sqlarray($sql,  @parameters); #returns [$,$,$,...]
  my @array=$dbx->sqlarray($sql,  @parameters); #returns ($,$,$,...)
  my $array=$dbx->sqlarray($sql, \@parameters); #returns [$,$,$,...]
  my @array=$dbx->sqlarray($sql, \@parameters); #returns ($,$,$,...)
  my $array=$dbx->sqlarray($sql, \%parameters); #returns [$,$,$,...]
  my @array=$dbx->sqlarray($sql, \%parameters); #returns ($,$,$,...)

sqlhash

Returns the first two columns of the SQL result as a hash or hash reference {Key=>Value, Key=>Value, ...}

  my $hash=$dbx->sqlhash($sql,  @parameters); #returns {$=>$, $=>$, ...}
  my %hash=$dbx->sqlhash($sql,  @parameters); #returns ($=>$, $=>$, ...)
  my @hash=$dbx->sqlhash($sql,  @parameters); #this is ordered
  my @keys=grep {!($n++ % 2)} @hash;          #ordered keys

  my $hash=$dbx->sqlhash($sql, \@parameters); #returns {$=>$, $=>$, ...}
  my %hash=$dbx->sqlhash($sql, \@parameters); #returns ($=>$, $=>$, ...)
  my $hash=$dbx->sqlhash($sql, \%parameters); #returns {$=>$, $=>$, ...}
  my %hash=$dbx->sqlhash($sql, \%parameters); #returns ($=>$, $=>$, ...)

sqlarrayarray

Returns the SQL result as an array or array ref of array references ([],[],...) or [[],[],...]

  my $array=$dbx->sqlarrayarray($sql,  @parameters); #returns [[$,$,...],[],[],...]
  my @array=$dbx->sqlarrayarray($sql,  @parameters); #returns ([$,$,...],[],[],...)
  my $array=$dbx->sqlarrayarray($sql, \@parameters); #returns [[$,$,...],[],[],...]
  my @array=$dbx->sqlarrayarray($sql, \@parameters); #returns ([$,$,...],[],[],...)
  my $array=$dbx->sqlarrayarray($sql, \%parameters); #returns [[$,$,...],[],[],...]
  my @array=$dbx->sqlarrayarray($sql, \%parameters); #returns ([$,$,...],[],[],...)

sqlarrayarrayname

Returns the SQL result as an array or array ref of array references ([],[],...) or [[],[],...] where the first row contains an array reference to the column names

  my $array=$dbx->sqlarrayarrayname($sql,  @parameters); #returns [[$,$,...],[]...]
  my @array=$dbx->sqlarrayarrayname($sql,  @parameters); #returns ([$,$,...],[]...)
  my $array=$dbx->sqlarrayarrayname($sql, \@parameters); #returns [[$,$,...],[]...]
  my @array=$dbx->sqlarrayarrayname($sql, \@parameters); #returns ([$,$,...],[]...)
  my $array=$dbx->sqlarrayarrayname($sql, \%parameters); #returns [[$,$,...],[]...]
  my @array=$dbx->sqlarrayarrayname($sql, \%parameters); #returns ([$,$,...],[]...)

Create an HTML table with CGI

  my $cgi=CGI->new;
  my $html=$cgi->table($cgi->Tr([map {$cgi->td($_)} $dbx->sqlarrayarrayname($sql, @param)]));

_sqlarrayarray

  my $array=$dbx->_sqlarrayarray(sql=>$sql, param=>[ @parameters], name=>1);
  my @array=$dbx->_sqlarrayarray(sql=>$sql, param=>[ @parameters], name=>1);
  my $array=$dbx->_sqlarrayarray(sql=>$sql, param=>[ @parameters], name=>0);
  my @array=$dbx->_sqlarrayarray(sql=>$sql, param=>[ @parameters], name=>0);

  my $array=$dbx->_sqlarrayarray(sql=>$sql, param=>[\@parameters], name=>1);
  my @array=$dbx->_sqlarrayarray(sql=>$sql, param=>[\@parameters], name=>1);
  my $array=$dbx->_sqlarrayarray(sql=>$sql, param=>[\@parameters], name=>0);
  my @array=$dbx->_sqlarrayarray(sql=>$sql, param=>[\@parameters], name=>0);

  my $array=$dbx->_sqlarrayarray(sql=>$sql, param=>[\%parameters], name=>1);
  my @array=$dbx->_sqlarrayarray(sql=>$sql, param=>[\%parameters], name=>1);
  my $array=$dbx->_sqlarrayarray(sql=>$sql, param=>[\%parameters], name=>0);
  my @array=$dbx->_sqlarrayarray(sql=>$sql, param=>[\%parameters], name=>0);

sqlarrayhash

Returns the SQL result as an array or array ref of hash references ({},{},...) or [{},{},...]

  my $array=$dbx->sqlarrayhash($sql,  @parameters); #returns [{},{},{},...]
  my @array=$dbx->sqlarrayhash($sql,  @parameters); #returns ({},{},{},...)
  my $array=$dbx->sqlarrayhash($sql, \@parameters); #returns [{},{},{},...]
  my @array=$dbx->sqlarrayhash($sql, \@parameters); #returns ({},{},{},...)
  my $array=$dbx->sqlarrayhash($sql, \%parameters); #returns [{},{},{},...]
  my @array=$dbx->sqlarrayhash($sql, \%parameters); #returns ({},{},{},...)

This method is best used to select a list of hashes out of the database to bless directly into a package.

  my $sql=q{SELECT COL1 AS "id", COL2 AS "name" FROM TABLE1};
  my @objects=map {bless $_, MyPackage} $dbx->sqlarrayhash($sql,  @parameters);
  my @objects=map {MyPackage->new(%$_)} $dbx->sqlarrayhash($sql,  @parameters);

The @objects array is now a list of blessed MyPackage objects.

sqlarrayhashname

Returns the SQL result as an array or array ref of hash references ([],{},{},...) or [[],{},{},...] where the first row contains an array reference to the column names

  my $array=$dbx->sqlarrayhashname($sql,  @parameters); #returns [[],{},{},...]
  my @array=$dbx->sqlarrayhashname($sql,  @parameters); #returns ([],{},{},...)
  my $array=$dbx->sqlarrayhashname($sql, \@parameters); #returns [[],{},{},...]
  my @array=$dbx->sqlarrayhashname($sql, \@parameters); #returns ([],{},{},...)
  my $array=$dbx->sqlarrayhashname($sql, \%parameters); #returns [[],{},{},...]
  my @array=$dbx->sqlarrayhashname($sql, \%parameters); #returns ([],{},{},...)

_sqlarrayhash

Returns the SQL result as an array or array ref of hash references ({},{},...) or [{},{},...]

  my $array=$dbx->_sqlarrayhash(sql=>$sql, param=>\@parameters, name=>1);
  my @array=$dbx->_sqlarrayhash(sql=>$sql, param=>\@parameters, name=>1);
  my $array=$dbx->_sqlarrayhash(sql=>$sql, param=>\@parameters, name=>0);
  my @array=$dbx->_sqlarrayhash(sql=>$sql, param=>\@parameters, name=>0);

sqlsort (Oracle Specific?)

Returns the SQL statement with the correct ORDER BY clause given a SQL statement (without an ORDER BY clause) and a signed integer on which column to sort.

  my $sql=$dbx->sqlsort(qq{SELECT 1,'Z' FROM DUAL UNION SELECT 2,'A' FROM DUAL}, -2);

Returns

  SELECT 1,'Z' FROM DUAL UNION SELECT 2,'A' FROM DUAL ORDER BY 2 DESC

sqlarrayarraynamesort

Returns a sqlarrayarrayname for $sql sorted on column $n where n is an integer ascending for positive, descending for negative, and 0 for no sort.

  my $data=$dbx->sqlarrayarraynamesort($sql, $n,  @parameters);
  my $data=$dbx->sqlarrayarraynamesort($sql, $n, \@parameters);
  my $data=$dbx->sqlarrayarraynamesort($sql, $n, \%parameters);

Note: $sql must not have an "ORDER BY" clause in order for this function to work correctly.

METHODS (Write) ^

Remember to commit or use AutoCommit

Note: It appears that some drivers do not support the count of rows.

insert

Returns the number of rows inserted by the SQL statement.

  my $rows=$dbx->insert( $sql,   @parameters);
  my $rows=$dbx->insert( $sql,  \@parameters);
  my $rows=$dbx->insert( $sql,  \%parameters);

  my $sabs=SQL::Abstract->new;
  my $rows=$dbx->insert($sabs->insert($table, \%field));

update

Returns the number of rows updated by the SQL statement.

  my $rows=$dbx->update( $sql,   @parameters);
  my $rows=$dbx->update( $sql,  \@parameters);
  my $rows=$dbx->update( $sql,  \%parameters);

  my $sabs=SQL::Abstract->new;
  my $rows=$dbx->update($sabs->update($table, \%field, \%where));

delete

Returns the number of rows deleted by the SQL statement.

  my $rows=$dbx->delete( $sql,   @parameters);
  my $rows=$dbx->delete( $sql,  \@parameters);
  my $rows=$dbx->delete( $sql,  \%parameters);

  my $sabs=SQL::Abstract->new;
  my $rows=$dbx->delete($sabs->delete($table, \%where));

Note: Some Oracle clients do not support row counts on delete instead the value appears to be a success code.

execute, exec

Executes stored procedures.

  my $out;
  my $rows=$dbx->execute($sql, $in, \$out);            #pass in/out vars as scalar reference
  my $rows=$dbx->execute($sql, [$in, \$out]);
  my $rows=$dbx->execute($sql, {in=>$in, out=>\$out});

TODO ^

Sort functions may not be portable.

BUGS ^

Send email to author and log on RT.

SUPPORT ^

DavisNetworks.com supports all Perl applications including this package.

AUTHOR ^

  Michael R. Davis
  CPAN ID: MRDVT
  STOP, LLC
  domain=>stopllc,tld=>com,account=>mdavis
  http://www.stopllc.com/

COPYRIGHT ^

This program is free software licensed under the...

  The BSD License

The full text of the license can be found in the LICENSE file included with this module.

SEE ALSO ^

The Competition

DBIx::DWIW, DBIx::Wrapper, DBIx::Simple, Data::Table::fromSQL, DBIx::Wrapper::VerySimple

The Building Blocks

DBI, SQL::Abstract

syntax highlighting:
90845 Uploads, 27580 Distributions 121409 Modules, 10669 Uploaders
hosted by YellowBot
do. tag. write. share.