DBIx::Array - This module is a wrapper around DBI with array interfaces
use DBIx::Array; my $dbx=DBIx::Array->new; $dbx->connect($connection, $user, $pass, \%opt); #passed to DBI my @array=$dbx->sqlarray($sql, @params);
With a connected database handle
use DBIx::Array; my $dbx=DBIx::Array->new(dbh=>$dbh);
With stored connection information from a File
use DBIx::Array::Connect; my $dbx=DBIx::Array::Connect->new(file=>"my.ini")->connect("mydatabase");
This module provides a Perl data structure interface for Structured Query Language (SQL). 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.
This module is used to connect to Oracle 10g and 11g using DBD::Oracle on both Linux and Win32, MySQL 4 and 5 using DBD::mysql on Linux, Microsoft SQL Server using DBD::Sybase on Linux and using DBD::ODBC on Win32 systems, and PostgreSQL using DBD::Pg in a 24x7 production environment. Tests are written against DBD::CSV and DBD::XBase.
Loop through data
foreach my $row ($dbx->sqlarrayhash($sql, @bind)) { do_something($row->{"id"}, $row->{"column"}); }
Easily generate an HTML table
my $cgi = CGI->new(""); my $html = $cgi->table($cgi->Tr([map {$cgi->td($_)} $dbx->sqlarrayarrayname($sql, @param)]));
Bless directly into a class
my ($object) = $dbx->sqlarrayobject("My::Package", $sql, {id=>$id}); #bless({id=>1, name=>'foo'}, 'My::Package'); my @objects = $dbx->absarrayobject("My::Package", "myview", '*', {active=>1}, ["name"]); #($object, $object, ...)
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
Sets or returns the database handle object.
my $dbh=$dbx->dbh; $dbx->dbh($dbh); #if you already have a connection
Sets or returns a user friendly identification string for this database connection
my $name=$dbx->name; $dbx->name($string);
Wrapper around DBI->connect; Connects to the database, sets dbh property, and returns the database handle.
$dbx->connect($connection, $user, $pass, \%opt); #sets $dbx->dbh my $dbh=$dbx->connect($connection, $user, $pass, \%opt);
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});
Wrapper around dbh->disconnect
$dbx->disconnect;
Wrapper around dbh->commit
$dbx->commit;
Wrapper around dbh->rollback
$dbx->rollback;
Wrapper around dbh->prepare with a local cache.
my $sth=$dbh->prepare($sql);
Wrapper around dbh->{'AutoCommit'}
$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->sqlinsert($sql1, @bind1); $dbx->sqlupdate($sql2, @bind2); $dbx->sqlinsert($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.
Wrapper around dbh->{'RaiseError'}
$dbx->RaiseError(1); &doSomething if $dbx->RaiseError; { #local block local $dbx->dbh->{'RaiseError'}=0; $dbx->sqlinsert($sql, @bind); #do not die }
Wrapper around $DBI::errstr
my $err=$dbx->errstr;
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, \@columns, \%where, \@sort));
Returns the first row first column value 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 $
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 ($,$,$,...)
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 ($=>$, $=>$, ...)
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 ([$,$,...],[],[],...)
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)]));
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.
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 ([],{},{},...)
Returns the SQL result as an array of blessed hash objects in to the $class namespace.
my $array=$dbx->sqlarrayobject($class, $sql, @parameters); #returns (bless({}, $class), ...) my @array=$dbx->sqlarrayobject($class, $sql, @parameters); #returns [bless({}, $class), ...] my ($object)=$dbx->sqlarrayobject($class, $sql, {id=>$id}); #$object is bless({}, $class)
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
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.
Returns the prepared and executed SQL cursor.
my $sth=$dbx->abscursor($table, \@columns, \%where, \@order);
my $scalar=$dbx->absscalar($table, \@columns, \%where, \@order); #returns $
Returns the SQL result as a array.
my @array=$dbx->absarray($table, \@columns, \%where, \@order); #returns () my $array=$dbx->absarray($table, \@columns, \%where, \@order); #returns []
my $hash=$dbx->abshash($table, \@columns, \%where, \@order); #returns {} my %hash=$dbx->abshash($table, \@columns, \%where, \@order); #returns ()
my $array=$dbx->absarrayarray($table, \@columns, \%where, \@order); #returns [[$,$,...],[],[],...] my @array=$dbx->absarrayarray($table, \@columns, \%where, \@order); #returns ([$,$,...],[],[],...)
my $array=$dbx->absarrayarrayname($table, \@columns, \%where, \@order); #returns [[$,$,...],[],[],...] my @array=$dbx->absarrayarrayname($table, \@columns, \%where, \@order); #returns ([$,$,...],[],[],...)
my $array=$dbx->absarrayhash($table, \@columns, \%where, \@order); #returns [{},{},{},...] my @array=$dbx->absarrayhash($table, \@columns, \%where, \@order); #returns ({},{},{},...)
my $array=$dbx->absarrayhashname($table, \@columns, \%where, \@order); #returns [[],{},{},...] my @array=$dbx->absarrayhashname($table, \@columns, \%where, \@order); #returns ([],{},{},...)
my $array=$dbx->absarrayobject($class, $table, \@columns, \%where, \@order); #returns (bless({}, $class), ...) my @array=$dbx->absarrayobject($class, $table, \@columns, \%where, \@order); #returns [bless({}, $class), ...]
Remember to commit or use AutoCommit
Note: It appears that some drivers do not support the count of rows.
Returns the number of rows inserted by the SQL statement.
my $count=$dbx->sqlinsert( $sql, @parameters); my $count=$dbx->sqlinsert( $sql, \@parameters); my $count=$dbx->sqlinsert( $sql, \%parameters);
Returns the number of rows updated by the SQL statement.
my $count=$dbx->sqlupdate( $sql, @parameters); my $count=$dbx->sqlupdate( $sql, \@parameters); my $count=$dbx->sqlupdate( $sql, \%parameters);
Returns the number of rows deleted by the SQL statement.
my $count=$dbx->sqldelete($sql, @parameters); my $count=$dbx->sqldelete($sql, \@parameters); my $count=$dbx->sqldelete($sql, \%parameters);
Note: Some Oracle clients do not support row counts on delete instead the value appears to be a success code.
Executes stored procedures.
my $out; my $return=$dbx->execute($sql, $in, \$out); #pass in/out vars as scalar reference my $return=$dbx->execute($sql, [$in, \$out]); my $return=$dbx->execute($sql, {in=>$in, out=>\$out});
Note: Currently sqlupdate, sqlinsert, sqldelete, 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.
Returns the number of rows inserted.
my $count=$dbx->absinsert($table, \%column_values);
Returns the number of rows updated.
my $count=$dbx->absupdate($table, \%column_values, \%where);
Returns the number of rows deleted.
my $count=$dbx->absdelete($table, \%where);
Insert records in bulk.
my @arrayarray=( [data1, $data2, $data3, $data4, ...], [@row_data_2], [@row_data_3], ... ); my $count=$dbx->bulksqlinsertarrayarray($sql, \@arrayarray);
Step 1 select data from table 1 in database 1
my $sth1=$dbx1->sqlcursor('Select Col1 AS "ColA", Col2 AS "ColB", Col3 AS "ColC" from table1');
Step 2 insert in to table 2 in database 2
my $count=$dbx2->bulksqlinsertcursor($sql, $sth1);
Note: If you are inside a single database, it is much more efficient to use insert from select syntax. As there is no need for data to be transferred between the server and the client.
Update records in bulk.
my @arrayarray = ( [$data1, $data2, $data3, $data4, $id], [@row_data_2], [@row_data_3], ... ); my $count = $dbx->bulksqlupdatearrayarray($sql, \@arrayarray);
These bulk methods do not use SQL::Abstract but our own similar SQL insert and update methods.
my @columns=("Col1", "Col2", "Col3", "Col4", ...); my @arrayarray=( [data1, $data2, $data3, $data4, ...], [@row_data_2], [@row_data_3], ... ); my $count=$dbx->bulkabsinsertarrayarray($table, \@columns, \@arrayarray);
my @columns=("Col1", "Col2", "Col3", "Col4", ...); #case sensative with respect to @arrayhash my @arrayhash=( {C0l1=>data1, Col2=>$data2, Col3=>$data3, Col4=>$data4, ...}, #extra hash items ignored when sliced using @columns \%row_hash_data_2, \%row_hash_data_3, ... ); my $count=$dbx->bulkabsinsertarrayhash($table, \@columns, \@arrayhash);
my $count=$dbx2->bulkabsinsertcursor($table2, $sth1); my $count=$dbx2->bulkabsinsertcursor($table2, \@columns, $sth1); #if your DBD/API does not support column alias support
Note: If you are inside a single database, it is much more efficient to use insert from select syntax. As no data needs to be transferred to and from the client.
my @setcolumns = ("Col1", "Col2", "Col3", "Col4"); my @wherecolumns = ("ID"); my @arrayarray = ( [$data1, $data2, $data3, $data4, $id], [@row_data_2], [@row_data_3], ... ); my $count = $dbx->bulkabsupdatearrayarray($table, \@setcolumns, \@wherecolumns, \@arrayarray);
Returns a SQL::Abstract object
Return the DBMS Name (e.g. Oracle, MySQL, PostgreSQL)
These methods allow the setting of Oracle session features that are available in the v$session table. If other databases support these features, please let me know. But, as it stands, these method are non operational unless SQL_DBMS_NAME is Oracle.
Sets and returns the v$session.module (Oracle) value.
Note: Module is set for you by DBD::Oracle. However you may set it however you'd like. It should be set once after connection and left alone.
$dbx->module("perl@host"); #normally set by DBD::Oracle $dbx->module($module, $action); #can set initial action too. my $module=$dbx->module();
Sets and returns the v$session.client_info (Oracle) value.
$dbx->client_info("Running From crontab"); my $client_info=$dbx->client_info();
You may use this field for anything up to 64 characters!
$dbx->client_info(join "~", (ver => 4, realm => "ldap", grp =>25)); #tilde is a fairly good separator my %client_info=split(/~/, $dbx->client_info());
Sets and returns the v$session.action (Oracle) value.
$dbx->action("We are Here"); my $action=$dbx->action();
Note: This should be updated fairly often. Every loop if it runs for more than 5 seconds and may end up in V$SQL_MONITOR.
while ($this) { local $dbx->{'action'}="This Loop"; #tied to the database with a little Perl sugar }
Sets and returns the v$session.client_identifier (Oracle) value.
$dbx->client_identifier($login); my $client_identifier = $dbx->client_identifier();
Note: This should be updated based on the login of the authenticated end user. I use the client_info->{'realm'} if you have more than one authentication realm.
For auditing add this to an update trigger
new.UPDATED_USER = sys_context('USERENV', 'CLIENT_IDENTIFIER');
Sort functions sqlsort and sqlarrayarraynamesort may not be portable.
Add some kind of capability to allow hash binds to bind as some native type rather than all strings.
Hash binds scan comments for bind variables e.g. /* :variable */
Improve error messages
Send email to author and log on RT.
DavisNetworks.com supports all Perl applications including this package.
Michael R. Davis CPAN ID: MRDVT STOP, LLC domain=>stopllc,tld=>com,account=>mdavis http://www.stopllc.com/
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.
DBIx::DWIW, DBIx::Wrapper, DBIx::Simple, Data::Table::fromSQL, DBIx::Wrapper::VerySimple, DBIx::Raw
DBI, SQL::Abstract
To install DBIx::Array, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::Array
CPAN shell
perl -MCPAN -e shell install DBIx::Array
For more information on module installation, please visit the detailed CPAN module installation guide.