NAME

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

SYNOPSIS

 newDBH ($DB,$newDSN)
 beginWork ()
 commit ()
 rollback ()
 readFromDB ($DB, $data)
 readFromDBHash ($DB, $data)
 doInDB ($DB, $data)
 storeInDB ($DB, $data, $countPercent)
 deleteFromDB ($DB, $data)
 updateInDB ($DB, $data)
 setConn ($handle, $DSN)
 getConn ()

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

newDBH ($$)

create a new handle for a database connection

 $DB .. hash with connection information like server, database
 $newDSN .. new DSN to be used for connection

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, following keys:
 $DB->{query} .. query string
 $DB->{columnnames} .. optionally return fieldnames of the query here
 $data .. ref to array of hash values (as returned by fetchall_arrayref: $return[row_0based]->{"<fieldname>"}) for return values of query.

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, following keys:
 $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.
 $data .. ref to hash of hash values (as returned by selectall_hashref: $return->{hashkey}->{"<fieldname>"}) for return values of query.

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, following keys:
 $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.
 $data .. optional: ref to array for return values of statement in $DB->{doString} (usually stored procedure).

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, following keys:
 $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.
 $data .. ref to array of hashes to be stored into database:
 $data = [
           {
             'field1Name' => 'DS1field1Value',
             'field2Name' => 'DS1field2Value',
             ...
           },
           {
             'field1Name' => 'DS2field1Value',
             'field2Name' => 'DS2field2Value',
             ...
           },
         ];
  $countPercent .. (optional) percentage of progress where indicator should be output (e.g. 10 for all 10% of progress). set to 0 to disable progress indicator

returns 0 on error, 1 if OK

deleteFromDB ($$)

delete data identified by key-data in database

 $DB .. hash with information for the procedure, following keys:
 $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.
 $data.. ref to hash of hash entries (as returned by selectall_hashref) having key values of records to be deleted

returns 0 on error, 1 if OK

updateInDB ($$)

update data in database

 $DB .. hash with information for the procedure, following keys:
 $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.
 $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',
             ...
           },
         ];

returns 0 on error, 1 if OK

setConn ($$)

set handle with externally created DBD::ODBC connection in case newDBH capabilities are not sufficient

 $handle .. ref to handle
 $setDSN .. DSN used in handle (used for calls to newDBH)
 

getConn

returns the DBI handler and the DSN string to allow direct commands with the handler. This can be used to enable DBI Tracing: (EAI::DB::getConn())[0]->trace(15);

COPYRIGHT

Copyright (c) 2024 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.