NetSDS::DBI3pm

Usage

        use NetSDS::DBI;
        $dbh = NetSDS::DBI->new(
                dsn    => 'dbi:Pg:dbname=test;host=127.0.0.1;port=5432',
                login  => 'user',
                passwd => 'topsecret',
        );
        print $db->call("select md5(?)", 'zuka')->fetchrow_hashref->{md5};

Overview

"NetSDS::DBI" module provides wrapper around DBI module.

Class API

new(%params) - class constructor

    $dbh = NetSDS::DBI->new(
                dsn    => 'dbi:Pg:dbname=test;host=127.0.0.1;port=5432',
                login  => 'user',
                passwd => 'topsecret',
        );

dbh() - DBI connection handler accessor

Returns: DBI object

This method provides accessor to DBI object and for low level access to database specific methods.

Example (access to specific method):

        my $quoted = $db->dbh->quote_identifier(undef, 'auth', 'services');
        # $quoted contains "auth"."services" now

call($sql, @bind_params) - prepare and execute SQL query

Method "call()" implements the following functionality:

        * check connection to DBMS and restore it
        * prepare chached SQL statement
        * execute statement with bind parameters

Parameters:

        * SQL query with placeholders
        * bind parameters

Return:

        * statement handler from DBI

Example:

        $sth = $dbh->call("select * from users");
        while (my $row = $sth->fetchrow_hashref()) {
                print $row->{username};
        }

fetch_call($sql, @params) - call and fetch result

Paramters: SQL query, parameters

Returns: arrayref of records as hashrefs

Example:

        # SQL DDL script:
        # create table users (
        #       id serial,
        #       login varchar(32),
        #       passwd varchar(32)
        # );
        # Now we fetch all data to perl structure
        my $table_data = $db->fetch_call("select * from users");
        # Process this data
        foreach my $user (@{$table_data}) {
                print "User ID: " . $user->{id};
                print "Login: " . $user->{login};
        }

begin() - start transaction

commit() - commit transaction

rollback() - rollback transaction

quote() - quote SQL string

Example:

        # Encode $str to use in queries
        my $str = "some crazy' string; with (dangerous characters";
        $str = $db->quote($str);

Internal methods

_add_sets() - add initial SQL query

Example:

    $obj->_add_sets("set search_path to myscheme");
    $obj->_add_sets("set client_encoding to 'UTF-8'");

_add_attrs() - add DBI handler attributes

    $self->_add_attrs(AutoCommit => 1);

_check_connection() - ping and reconnect

Internal method checking connection and implement reconnect

_connect() - connect to DBMS

Internal method starting connection to DBMS

Examples

samples/testdb.pl

Todo

1. Make module less PostgreSQL specific.