The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.
 / 
DBIx-Connection-0.12
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',
        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 error 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 statistics 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 specific 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 handler 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 parameter.

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

Returns a new plsql handler 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

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

    my $pk_info = $connection->primary_key_info($table);
    my $pk_info = $connection->primary_key_info($table, $schema);
primary_key_columns

Returns primary key columns

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

Return foreign key info.

    my $fk_info = $connection->primary_key_info($table, $ref_table);
    my $fk_info = $connection->primary_key_info($table, $ref_table, $schema, $ref_schema);
foreign_key_columns

Returns foreign key columns

    my @columns = $connection->foreign_key_columns($table, $ref_table);
    my @columns = $connection->foreign_key_columns($table, $ref_table, $schema, $ref_schema);
index_info

Returns index data structure for the specified index.

    my $index_info = $connection->index_info($index);
    my $index_info = $connection->index_info($index, $schema);
    my $index_info = $connection->index_info($index, $schema, $table);
table_info

Returns table info. See also DBI::table_info

    my $table_info = $db_connection->table_info($table);
    my $table_info = $db_connection->table_info($table, $schema);
trigger_info

Returns trigger info.

    my $trigger_info = $connection->trigger_info($trigger);
    my $trigger_info = $connection->trigger_info($trigger, $schema);

Result hash ref has the following keys

    trigger_name 
    table_name
    trigger_schema 
    description
    trigger_body
routine_info

Returns array ref of the following hash ref structure:

    'return_type' => 
    'routine_schema' => 
    'routine_name'  => 
    'routine_arguments'  => 
    'routine_body'  =>
    'routine_type'  => 'FUNCTION|PROCEDURE'
    'args' => [
        {
        mode => ....,
        name => ...,
        type => ...
        },
        ...
    ],

    my $routines_info = $connection->routine_info($function, $schema);
    my $routines_info = $connection->routine_info($function);

Takes procedure/function name as parameter.

set_session_variables
has_table

Returns true if the specified table exists

    $connection->has_table($table, $schema);
    $connection->has_table($table);
has_view

Returns true if the specified view exists

    $connection->has_table($table, $schema);
    $connection->has_table($table);
has_sequence

Returns true if has sequence

sequence_value

Returns sequence's value. Takes sequence 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_info

Returns list of schema tables; Takes optionally schema name.

columns

Returns columns for passed in table.

    $connection->columns($table);
    $connection->columns($table, $schema);
column

Returns the hash ref to column info for given table.

    $connection->column($table, $column);
    $connection->column($table, $column, $schema);
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 clause 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