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 Oracle 10g (DBD::Oracle), MySql 4 and 5 (DBD::mysql) and Microsoft SQL Server (DBD::Sybase) databases 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 handleMETHODS
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;errstr
Returns $DBI::errstr
$dbx->errstr;dbh
Sets or returns the database handle object.
$dbx->dbh;
$dbx->dbh($dbh); #if you already have a connectionMETHODS (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 :keyNote: 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 refererences 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 6Direct 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.
$scalar=$dbx->sqlscalar($sql, @parameters); #returns $
$scalar=$dbx->sqlscalar($sql, \@parameters); #returns $
$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.
$array=$dbx->sqlarray($sql, @parameters); #returns [$,$,$,...]
@array=$dbx->sqlarray($sql, @parameters); #returns ($,$,$,...)
$array=$dbx->sqlarray($sql, \@parameters); #returns [$,$,$,...]
@array=$dbx->sqlarray($sql, \@parameters); #returns ($,$,$,...)
$array=$dbx->sqlarray($sql, \%parameters); #returns [$,$,$,...]
@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, ...}
$hash=$dbx->sqlhash($sql, @parameters); #returns {$=>$, $=>$, ...}
%hash=$dbx->sqlhash($sql, @parameters); #returns ($=>$, $=>$, ...)
@hash=$dbx->sqlhash($sql, @parameters); #this is ordered
@keys=grep {!($n++ % 2)} @hash; #ordered keys
$hash=$dbx->sqlhash($sql, \@parameters); #returns {$=>$, $=>$, ...}
%hash=$dbx->sqlhash($sql, \@parameters); #returns ($=>$, $=>$, ...)
$hash=$dbx->sqlhash($sql, \%parameters); #returns {$=>$, $=>$, ...}
%hash=$dbx->sqlhash($sql, \%parameters); #returns ($=>$, $=>$, ...)sqlarrayarray
Returns the SQL result as an array or array ref of array references ([],[],...) or [[],[],...]
$array=$dbx->sqlarrayarray($sql, @parameters); #returns [[$,$,...],[],[],...]
@array=$dbx->sqlarrayarray($sql, @parameters); #returns ([$,$,...],[],[],...)
$array=$dbx->sqlarrayarray($sql, \@parameters); #returns [[$,$,...],[],[],...]
@array=$dbx->sqlarrayarray($sql, \@parameters); #returns ([$,$,...],[],[],...)
$array=$dbx->sqlarrayarray($sql, \%parameters); #returns [[$,$,...],[],[],...]
@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
$array=$dbx->sqlarrayarrayname($sql, @parameters); #returns [[$,$,...],[]...]
@array=$dbx->sqlarrayarrayname($sql, @parameters); #returns ([$,$,...],[]...)
$array=$dbx->sqlarrayarrayname($sql, \@parameters); #returns [[$,$,...],[]...]
@array=$dbx->sqlarrayarrayname($sql, \@parameters); #returns ([$,$,...],[]...)
$array=$dbx->sqlarrayarrayname($sql, \%parameters); #returns [[$,$,...],[]...]
@array=$dbx->sqlarrayarrayname($sql, \%parameters); #returns ([$,$,...],[]...)_sqlarrayarray
$array=$dbx->_sqlarrayarray(sql=>$sql, param=>[ @parameters], name=>1);
@array=$dbx->_sqlarrayarray(sql=>$sql, param=>[ @parameters], name=>1);
$array=$dbx->_sqlarrayarray(sql=>$sql, param=>[ @parameters], name=>0);
@array=$dbx->_sqlarrayarray(sql=>$sql, param=>[ @parameters], name=>0);
$array=$dbx->_sqlarrayarray(sql=>$sql, param=>[\@parameters], name=>1);
@array=$dbx->_sqlarrayarray(sql=>$sql, param=>[\@parameters], name=>1);
$array=$dbx->_sqlarrayarray(sql=>$sql, param=>[\@parameters], name=>0);
@array=$dbx->_sqlarrayarray(sql=>$sql, param=>[\@parameters], name=>0);
$array=$dbx->_sqlarrayarray(sql=>$sql, param=>[\%parameters], name=>1);
@array=$dbx->_sqlarrayarray(sql=>$sql, param=>[\%parameters], name=>1);
$array=$dbx->_sqlarrayarray(sql=>$sql, param=>[\%parameters], name=>0);
@array=$dbx->_sqlarrayarray(sql=>$sql, param=>[\%parameters], name=>0);sqlarrayhash
Returns the SQL result as an array or array ref of hash references ({},{},...) or [{},{},...]
$array=$dbx->sqlarrayhash($sql, @parameters); #returns [{},{},{},...]
@array=$dbx->sqlarrayhash($sql, @parameters); #returns ({},{},{},...)
$array=$dbx->sqlarrayhash($sql, \@parameters); #returns [{},{},{},...]
@array=$dbx->sqlarrayhash($sql, \@parameters); #returns ({},{},{},...)
$array=$dbx->sqlarrayhash($sql, \%parameters); #returns [{},{},{},...]
@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
$array=$dbx->sqlarrayhashname($sql, @parameters); #returns [[],{},{},...]
@array=$dbx->sqlarrayhashname($sql, @parameters); #returns ([],{},{},...)
$array=$dbx->sqlarrayhashname($sql, \@parameters); #returns [[],{},{},...]
@array=$dbx->sqlarrayhashname($sql, \@parameters); #returns ([],{},{},...)
$array=$dbx->sqlarrayhashname($sql, \%parameters); #returns [[],{},{},...]
@array=$dbx->sqlarrayhashname($sql, \%parameters); #returns ([],{},{},...)_sqlarrayhash
Returns the SQL result as an array or array ref of hash references ({},{},...) or [{},{},...]
$array=$dbx->_sqlarrayhash(sql=>$sql, param=>\@parameters, name=>1);
@array=$dbx->_sqlarrayhash(sql=>$sql, param=>\@parameters, name=>1);
$array=$dbx->_sqlarrayhash(sql=>$sql, param=>\@parameters, name=>0);
@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 DESCsqlarrayarraynamesort
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)
update, delete, execute, insert
Returns the number of rows updated or deleted by the SQL statement.
$rows=$dbx->insert( $sql, @parameters);
$rows=$dbx->update( $sql, @parameters);
$rows=$dbx->delete( $sql, @parameters);
$rows=$dbx->execute($sql, \@parameters);
$rows=$dbx->execute($sql, \%parameters);Hey doesn't that look just like the return from SQL::Abstract
my $sabs=SQL::Abstract->new;
$rows=$dbx->insert($sabs->insert($table, \%field));
$rows=$dbx->update($sabs->update($table, \%field, \%where));
$rows=$dbx->delete($sabs->delete($table, \%where));Remember to commit or use AutoCommit
Note: It appears that some drivers do not support the count of rows. For example, DBD::Oracle does not support row counts on delete instead the value appears to be a success code.
Note: Currently update, insert, delete, and execute all point to the same method. This may change in the future if we need to change the behavior of one method. So, please use the correct method name for your function.
TODO
Sort functions are not 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 LicenseThe 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