DBIx::Connection - Simple database interface.
use DBIx::Connection; my $connection = DBIx::Connection->new( name => 'my_connection_name', dsn => 'dbi:Oracle:localhost:1521/ORCL', username => 'user', password => 'password', db_session_variables => { NLS_DATE_FORMAT => 'DD.MM.YYYY' } ); my $cursor = $connection->query_cursor(sql => "select * from emp where deptno > ?", name => 'emp_select'); my $dataset = $cursor->execute(20); while ($cursor->fetch) { #do some stuff ... print $_ . " => " . $dataset->{$_} for keys %$dataset; } { my $cursor = $connection->find_query_cursor('emp_select'); my $dataset = $cursor->execute(20); ... } my $record = $connection->record("select * from emp where empno = ?", 'xxx'); my $sql_handler = $connection->sql_handler(sql => "INSERT INTO emp(empno, ename) VALUES(?, ?)", name => 'emp_ins'); $sql_handler->execute(1, 'Smith'); $sql_handler->execute(2, 'Witek'); { my $sql_handler= $connection->find_sql_handler('emp_ins'); $sql_handler->execute(3, 'Zzz'); ... } #or $connection->execute_statement("INSERT INTO emp(empno, ename) VALUES(?, ?)", 1, 'Smith'); #gets connection by name. my $connection = DBIx::Connection->connection('my_connection_name'); do stuff # returns connection to connection pool $connection->close(); #turn on connection pooling $DBIx::Connection::CONNECTION_POOLING = 1; In this mode only connection may have the following states : in_use and NOT in_use, Only connection that is "NOT in use" state can be retrieve by callin DBIx::Connection->connection, and state changes to "in use". Close method change state back to NOT in_use. If in connection pool there are not connections in "NOT in use" state, then the new connection is cloned. my $connection = DBIx::Connection->connection('my_connection_name'); # do stuff ... $connection->close(); #preserving resource by physical disconnecting all connection that are idle by defined threshold (sec). $DBIx::Connection::IDLE_THRESHOLD = 300;
Represents a database connection handler.
It provides simple interface to managing database connections with the all related operations wrapped in the different sql handlers.
$connection = DBIx::Connection->connection('my_connection_name'); eval { $connection->begin_work(); my $sql_handler = $connection->sql_handler(sql => "INSERT INTO emp(empno, ename) VALUES(?, ?)"); $sql_handler->execute(1, 'Smith'); ... $connection->commit(); }; if($@) { $connection->rollback(); } $connection->close();
It supports:
sql handlers(dml) -(INSERT/UDPDATE/DELETE)
my $sql_handler = $connection->sql_handler(sql => "INSERT INTO emp(empno, ename) VALUES(?, ?)"); $sql_handler->execute(1, 'Smith');
query cursors - SELECT ... FROM ...
my $query_cursor = $connection->query_cursor( sql => " SELECT t.* FROM ( SELECT 1 AS col1, 'text 1' AS col2 " . ($dialect eq 'oracle' ? ' FROM dual' : '') . " UNION ALL SELECT 2 AS col1, 'text 2' AS col2 " . ($dialect eq 'oracle' ? ' FROM dual' : '') . " ) t WHERE 1 = ? " ); my $resultset = $cursor->execute([1]); while($cursor->fetch()) { # do some stuff # $resultset }
plsql handlers - BEGIN ... END
my $plsql_handler = $connection->plsql_handler( name => 'test_block', connection => $connection, plsql => "BEGIN :var1 := :var2 + :var3; END;", bind_variables => { var1 => {type => 'SQL_INTEGER'}, var2 => {type => 'SQL_INTEGER'}, var3 => {type => 'SQL_INTEGER'} } ); my $resultset = $plsql_handler->execute(var2 => 12, var3 => 8);
Connection is cached by its name.
DBIx::Connection->new( name => 'my_connection_name', dsn => 'dbi:Oracle:localhost:1521/ORCL', username => 'user', password => 'password', ); $connection = DBIx::Connection->connection('my_connection_name');
Supports setting specyfic RDBMS session variables.
my $databaseHandler = DBIx::Connection->new( name => 'my_connection_name', dsn => 'dbi:Oracle:localhost:1521/ORCL', username => 'user', password => 'password', db_session_variables => { NLS_DATE_FORMAT => 'DD.MM.YYYY' } )
It caches sql statements and uses named sql handlers.
Database usage:
This module allows getting coverage about any sql issued by application, so that it's realy easy to find any bottelnecks to tune it.
Automatic reporting:
This module allows for automatic reporting in case of any errors.
It supports eroror handler customization.
my $error_handler = sub { my (self, $message, $sql_handler) = @_; #do some stuff }; $connection->set_custom_error_handler($error_handler);
Connection name.
Database source name.
Callback that overwrites default error_handler on SQLHandler object.
Flag that indicate if statisitcs are collected.
Prepares statements each time, otherwise use prepare statement once and reuse it
Flag that indicate that connection has pending transaction
Loads specyfic rdbms module.
Connects to the database.
Checks the database connection and reconnects if necessary.
Executes passed in sql statement.
Returns a new sql handeler instance.
my $sql_handler = $connection->sql_handler( name => 'emp_ins' sql => "INSERT INTO emp(empno, ename) VALUES(?, ?)", ); $sql_handler->execute(1, 'Smith');
Returns cached sql handler. Takes sql handler name as parameter.
my $sql_handler = $connection->find_sql_handler('emp_ins'); $sql_handler->execute(1, 'Scott');
Executes passed in statement.
$connection->execute_statement("INSERT INTO emp(empno, ename) VALUES(?, ?)", 1, 'Smith');
my $cursor = $connection->query_cursor(sql => "SELECT * FROM emp WHERE empno = ?"); my @result_set; $cursor->execute([1], \@result_set); or # my $result_set = $cursor->execute([1]); my $iterator = $cursor->iterator; while($iterator->()) { #do some stuff #@result_set } # or while($cusor->fetch()) { #do some stuff #@result_set }
Returns cached query cursor. Takes query cursor name as parmeter.
my $cursor = $connection->find_query_cursor('my_cusror'); my $result_set = $cursor->execute([1]);
Returns a new plsql handeler instance <DBIx::PLSQLHandler>. Takes DBIx::PLSQLHandler constructor parameters.
my $plsql_handler = $connection->plsql_handler( name => 'my_plsql', plsql => "DECLARE debit_amt CONSTANT NUMBER(5,2) := 500.00; BEGIN SELECT a.bal INTO :acct_balance FROM accounts a WHERE a.account_id = :acct AND a.debit > debit_amt; :extra_info := 'debit_amt: ' || debit_amt; END;"); my $result_set = $plsql_handler->execute(acct => 000212); print $result_set->{acct_balance}; print $result_set->{extra_info};
Returns cached plsql handler, takes name of handler.
my $plsql_handler = $connection->find_plsql_handler('my_plsql'); my $result_set = $plsql_handler->execute(acct => 000212);
Returns resultset record. Takes sql statement, and bind variables parameters as list.
my $resultset = $connection->record("SELECT * FROM emp WHERE ename = ? AND deptno = ? ", 'scott', 10); #$resultset->{ename} # do some stuff
Begins transaction.
Commits current transaction.
Rollbacks current transaction.
Initialises connection.
Returns connection object for passed in connection name.
Returns true if connection has autocommit mode
Finds connections
Checks connection
Clones current connection. Returns a new connection object.
Checks connection state.
returns true if connection is idle.
Checks all connection and disconnects all inactive for longer the 5 mins
Returns connection to the connection pool, so that connection may be reused by another call Connection->connection('connection_name') rather then its clone.
Disconnects from current database.
Returns database name
Returns database version
Returns primary key information, takes table name Return array ref (DBI::primary_key_info)
TABLE_CAT: The catalog identifier. This field is NULL (undef) if not applicable to the data source, which is often the case. This field is empty if not applicable to the table.
TABLE_SCHEM: The schema identifier. This field is NULL (undef) if not applicable to the data source, and empty if not applicable to the table.
TABLE_NAME: The table identifier.
COLUMN_NAME: The column identifier.
KEY_SEQ: The column sequence number (starting with 1). Note: This field is named ORDINAL_POSITION in SQL/CLI.
PK_NAME The primary key constraint identifier. This field is NULL (undef) if not applicable to the data source.
Returns primary key columns
my @primary_key_columns = $connection->primary_key_columns('emp');
Returns table info. See also DBI::table_info
Returns true if table exists in database schema
Returns true if has sequence
Returns sequence's value. Takes seqence name.
$connection->sequence_value('emp_seq');
Restart sequence. Takes sequence name, initial sequence value, incremental sequence value.
$connection->reset_sequence('emp_seq', 1, 1);
Records database operation start time.
Records database operation end time.
Formats usage report.
Prints usage report to stander output.
Prints usage report to file
Returns error message, takes error message, and optionally bind variables. If bind variables are passed in the sql's place holders are replaced with the bind_variables.
Updates lob.
Takes table_name, lob column name, lob content, hash_ref to primary key values. optionally lob size column name.
$connection->update_lob(lob_test => 'blob_content', $lob_content, {id => 1}, 'doc_size');
Returns lob, takes table name, lob column name, hash ref of primary key values, lob size column name
my $lob = $connection->fetch_lob(lob_test => 'blob_content', {id => 1}, 'doc_size');
Returns Where caluse sql fragment, takes hash ref of fields values.
The DBIx::Connection module is free software. You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.
DBIx::QueryCursor DBIx::SQLHandler DBIx::PLSQLHandler.
Adrian Witas, adrian@webapp.strefa.pl
To install DBIx::Connection, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::Connection
CPAN shell
perl -MCPAN -e shell install DBIx::Connection
For more information on module installation, please visit the detailed CPAN module installation guide.