/ 
EAI-Wrap-0.2
River stage zero No dependents
/ EAI::DB

NAME

EAI::DB - Database wrapper functions (for DBI / DBD::ODBC)

SYNOPSIS

newDBH ($DB,$execute)
beginWork ()
commit ()
rollback ()
readFromDB ($DB, $data)
readFromDBHash ($DB, $data)
doInDB ($DB, $data)
storeInDB ($DB, $data)
deleteFromDB ($DB, $data)
updateInDB ($DB, $data)

DESCRIPTION

EAI::DB contains all database related API-calls. This is for creating a database connection handle with newDBH, transaction handling (beginWork, commit, rollback), reading from the database (hash or array), doing arbitrary statements in the database, storing data in the database, deleting and updating data.

API

getConn

returns the DBI handler and the DSN string to allow direct commands with the handler

newDBH

create a new handle for a database connection

$DB .. hash with connection information like server, database
$execute .. additional hash with execution information, mainly used for interpolation in DSN, especially environment: 'driver={SQL Server};Server=$DB->{server}{$execute->{env}};...'

returns 0 on error, 1 if OK (handle is stored internally for further usage)
beginWork

start transaction in database

returns 0 on error, 1 if OK
commit

commit transaction in database

returns 0 on error, 1 if OK
rollback

roll back transaction in database

returns 0 on error, 1 if OK
readFromDB

read data into array returned in $data

$DB .. hash with information for the procedure
$data .. ref to array of hash values (as returned by fetchall_arrayref: $return[row_0based]->{"<fieldname>"}) for return values of query.
$DB->{query} .. query string
$DB->{columnnames} .. optionally return fieldnames of the query here
returns 0 on error, 1 if OK
readFromDBHash

read data into hash using column $DB->{keyfield} as the unique key for the hash (used for lookups), returned in $data

$DB .. hash with information for the procedure
$data .. ref to hash of hash values (as returned by selectall_hashref: $return->{hashkey}->{"<fieldname>"}) for return values of query.
$DB->{query} .. query string
$DB->{columnnames} .. optionally return fieldnames of the query here
$DB->{keyfield} .. field contained in the query string that should be used as the hashkey for the hash values of $data.
returns 0 on error, 1 if OK
doInDB

do general statement $DB->{doString} in database using optional parameters passed in array ref $DB->{parameters}, optionally passing back values in $data

$DB .. hash with information for the procedure
$data .. optional: ref to array for return values of statement in $DB->{doString} (usually stored procedure).
$DB->{doString} .. sql statement to be executed
$DB->{parameters} .. optional: if there are placeholders defined in $DB->{doString} for parameters (?), then the values for these parameters are passed here.
returns 0 on error, 1 if OK
storeInDB

store row-based data into database, using insert or an "upsert" technique

$DB .. hash with information for the procedure
$data .. ref to array of hashes to be stored into database:
$data = [
          {
            'field1Name' => 'DS1field1Value',
            'field2Name' => 'DS1field2Value',
            ...
          },
          {
            'field1Name' => 'DS2field1Value',
            'field2Name' => 'DS2field2Value',
            ...
          },
        ];
$DB->{tableName} .. table where data should be inserted/updated (can have a prepended schema, separated with ".")
$DB->{addID} .. add an additional, constant ID-field to the data (ref to hash: {"NameOfIDField" => "valueOfIDField"}), only one field/value pair is possible here
$DB->{upsert} .. update a record after an insert failed due to an already existing primary key (-> "upsert")
$DB->{primkey} .. WHERE clause (e.g. primID1 = ? AND primID2 = ?) for building the update statements
$DB->{ignoreDuplicateErrs} .. if  $DB->{upsert} was not set and duplicate errors with inserts should be ignored
$DB->{deleteBeforeInsertSelector} .. WHERE clause (e.g. col1 = ? AND col2 = ?) for deleting existing data before storing: all data that fullfills the criteria of this clause for values in the first data record of the data to be stored are being deleted (following the assumption that these criteria are the fulfilled for all records to be deleted)
$DB->{incrementalStore} .. if set, then undefined (NOT empty ("" !) but undef) values are not being set to NULL but skipped for the insert/update statement
$DB->{doUpdateBeforeInsert} .. if set, then the update in "upserts" is done BEFORE the insert, this is important for tables with an identity primary key and the inserting criterion is a/are different field(s).
$DB->{debugKeyIndicator} .. key debug string (e.g. Key1 = ? Key2 = ?) to build debugging key information for error messages.
returns 0 on error, 1 if OK
deleteFromDB

delete data identified by key-data in database

$DB .. hash with information for the procedure
$data.. ref to hash of hash entries (as returned by selectall_hashref) having key values of records to be deleted
$DB->{tableName} .. table where data should be deleted
$DB->{keycol} .. a field name or a WHERE clause (e.g. primID1 = ? AND primID2 = ?) to find data that should be removed. A contained "?" specifies a WHERE clause that is simply used for a prepared statement.
returns 0 on error, 1 if OK
updateInDB

update data in database

$DB .. hash with information for the procedure
$data.. ref to hash of hash entries (as returned by selectall_hashref) having key values of records to be updated (keyval keys are artificial keys not being used for the update, they only uniquely identify the update records)
$data = [ 'keyval1' => {
            'field1Name' => 'DS1field1Value',
            'field2Name' => 'DS1field2Value',
            ...
          }, 'keyval2' => {
            'field1Name' => 'DS2field1Value',
            'field2Name' => 'DS2field2Value',
            ...
          },
        ];
$DB->{tableName} .. table where data should be updated
$DB->{keycol} .. a field name or a WHERE clause (e.g. primID1 = ? AND primID2 = ?) to find data that should be updated. A contained "?" specifies a WHERE clause that is simply used for a prepared statement.
returns 0 on error, 1 if OK

COPYRIGHT

Copyright (c) 2023 Roland Kapl

All rights reserved. This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

The full text of the license can be found in the LICENSE file included with this module.