/ 
DBIx-Connection-0.01
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'
  }
);


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 %$rowset;
}

{
    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;

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

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

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

METHODS

initialise_oracle_session
load_module

Loads specyfic rdbms module.

connect

Connects to the database.

check_connection

Checks the database connection and reconnects if neccessary.

do

Executes passed in sql statement.

begin_work

Begins transaction.

commit

Commits curent transaction.

rollback

Rollbacks curent transaction.

initialise

Initialises connection.

_cache_connection

Checks connection

connection

Returns connection object for passed in connection name.

_find_connection

Finds connections

_clone_connection

Clones current connection. Returns a new connection object.

_check_connection

Checks connection state.

_is_idled

Retuns 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

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

Executes passed in statement.

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

Returns a new plsql handeler instance <DBIx::PLSQLHandler>. my $plsql_handler = $connection->plsql_handler( 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 = $cursor->execute(acct => 000212);
print $result_set->{acct_balance};
print $result_set->{extra_info};
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
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.

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</gt>