/ 
DBIx-Connection-0.10
River stage one • 2 direct dependents • 2 total dependents
/ DBIx::Connection

NAME

DBIx::Connection - Simple database interface.

SYNOPSIS

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'
  }
);

or
my $dbh = DBI->connect(...);
my $connection = DBIx::Connection->new(
  name  => 'my_connection_name',
  dbh   => $dbh,
  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 invoking 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;

DESCRIPTION

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');

RDBMS session variables supports.

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 based on handler's name.

$connection->sql_handler(name => 'emp_ins', sql => "INSERT INTO emp(empno, ename) VALUES(?, ?)");
my $sql_handler = $connection->find_sql_handler('emp_ins');
$sql_handler->execute(1, 'Smith');

Database usage:

This module allows gathering sql statistics issued by application

Automatic reporting:

$connection->set_collect_statistics(1);
$connection->set_statistics_dir('/sql_usage');

Error handler customization:

It supports eroror handler customization.

my $error_handler = sub {
    my (self, $message, $sql_handler) = @_;
    #do some stuff
};
$connection->set_custom_error_handler($error_handler);

Sequences support:

$connection->sequence_value('emp_seq');

Large Object support;

$connection->update_lob(lob_test => 'blob_content', $lob_content,  {id => 1}, 'doc_size');
my $lob = $connection->fetch_lob(lob_test => 'blob_content', {id => 1}, 'doc_size');

ATTRIBUTES

name

Connection name.

dsn

Database source name.

username
password
database handler
db_session_variables
query_cursors
sql_handlers
plsql_handlers
custom_error_handler

Callback that overwrites default error_handler on SQLHandler object.

stats
action_start_time
collect_statistics

Flag that indicate if statisitcs are collected.

statistics_dir
in_use
is_connected
last_in_use
no_cache

Prepares statements each time, otherwise use prepare statement once and reuse it

_active_transaction

Flag that indicate that connection has pending transaction

METHODS

load_module

Loads specyfic rdbms module.

connect

Connects to the database.

check_connection

Checks the database connection and reconnects if necessary.

do

Executes passed in sql statement.

sql_handler

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');
find_sql_handler

Returns cached sql handler. Takes sql handler name as parameter.

my $sql_handler = $connection->find_sql_handler('emp_ins');
$sql_handler->execute(1, 'Scott');
execute_statement

Executes passed in statement.

$connection->execute_statement("INSERT INTO emp(empno, ename) VALUES(?, ?)", 1, 'Smith');
query_cursor
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 
}
find_query_cursor

Returns cached query cursor. Takes query cursor name as parmeter.

my $cursor = $connection->find_query_cursor('my_cusror');
my $result_set = $cursor->execute([1]);
plsql_handler

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};
find_plsql_handler

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);
record

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
begin_work

Begins transaction.

commit

Commits current transaction.

rollback

Rollbacks current transaction.

initialise

Initialises connection.

connection

Returns connection object for passed in connection name.

has_autocomit_mode

Returns true if connection has autocommit mode

_find_connection

Finds connections

_cache_connection

Checks connection

_clone_connection

Clones current connection. Returns a new connection object.

_check_connection

Checks connection state.

_is_idled

returns true if connection is idle.

check_connnections

Checks all connection and disconnects all inactive for longer the 5 mins

close

Returns connection to the connection pool, so that connection may be reused by another call Connection->connection('connection_name') rather then its clone.

disconnect

Disconnects from current database.

dbms_name

Returns database name

dbms_version

Returns database version

primary_key_info

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.

primary_key_columns

Returns primary key columns

my @primary_key_columns = $connection->primary_key_columns('emp');
table_info

Returns table info. See also DBI::table_info

set_session_variables
has_table

Returns true if table exists in database schema

has_sequence

Returns true if has sequence

sequence_value

Returns sequence's value. Takes seqence name.

$connection->sequence_value('emp_seq');
reset_sequence

Restart sequence. Takes sequence name, initial sequence value, incremental sequence value.

$connection->reset_sequence('emp_seq', 1, 1);
tables

Returns list of schme tables; Takes optionally schema name.

columns

Returns columns for passed in table.

record_action_start_time

Records database operation start time.

record_action_end_time

Records database operation end time.

format_usage_report

Formats usage report.

Prints usage report to stander output.

Prints usage report to file

error_handler

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.

update_lob

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');
fetch_lob

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');
_where_clause

Returns Where caluse sql fragment, takes hash ref of fields values.

DESTORY

COPYRIGHT AND LICENSE

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.

SEE ALSO

DBIx::QueryCursor DBIx::SQLHandler DBIx::PLSQLHandler.

AUTHOR

Adrian Witas, adrian@webapp.strefa.pl