##########################################################################
#
# Package: do_dbi_pkg.pl
#
# Purpose: Provides a set of routines to run queries using the DBI method
#
# Description: Intended for use in scripts that are run in pipelines that
#    require special error codes when terminating in error. It also provides
#    a standard method of reporting errors. Some DBI methods are combined.
#    For queries that do not fit any of the models in this package the
#    script can always use DBI directly and use DoDBIerr for error reporting. 
# 
# Usage: Generally the DoDBIopen and DoDBIclose are always used. In between
#        the other calls are used to access the database or terminate with a
#        error exit. If all records are not read out of a statement handle 
#        (sth) using DoDBIfetch, then the calling script must uise the DBI 
#        finish command. The case where only one record is read is handled
#        by DoDBIselect which has a built-in finish command.
# 
# Member routines:
#   $dbh    = DoDBIopen( $server, $database, $err_exit )
#             Make connection to server, open database in server, and set the
#             global $DO_DBI_ERR_EXIT to the $err_exit value (an integer). It
#             returns the database handle $dbh needed for subsequent calls.
#             This convenience routine is optional and the other DoDBI
#             routines can work even if $DO_DBI_ERR_EXIT variable is not
#             defined. 
#
#             DoDBIclose( $dbh)
#             Disconnect the database handle $dbh. This convenience routines is
#             optional if the user choses to use the DBI disconnect directly.
#             This routine reports a warning message if a statement handle
#             needed a finish command.  No error conditions. Always returns 
#             true.
#
#             DoDBIerr($dbh, $query, $msg) 
#             (No return from this routine.) Exit the script after 
#             printing the $msg (usually containing $h->errstr) and print
#             the query taht failed. It disconnects the database session. It
#             uses error code in the global $DO_DBI_ERR_EXIT if it is is 
#             defined, else it uses the Perl die command.
#
#   $count  = DoDBI( $dbh, $query) 
#             Return the count of the number of records
#             affected by a query that does not do a select. It may return a
#             value 0E0 which Perl evaluates as true is a logical context and
#             as zero in any numeric context. If query is invalid the method
#             will exit the value of the global $DO_DBI_ERR_EXIT, or die.
#             The $dbh is a DBI database handle.
#
#   $sth    = DoDBIexecute ($dbh, $query)
#             Return statement handle (sth) for a select query. If query is
#             invalid the method will exit using the value of the global
#             $DO_DBI_ERR_EXIT, or die.  
#
#   @record = DoDBIfetch($dbh, $query, $sth)
#             Return next record for the statement handle (sth) obtained with
#             the DoDBIexecute routine. Null (undef) record is returned after
#             last record is fetched. The $dbh and $query are used for error
#             reporting. 
#
#   @record = DoDBIselect ($dbh, $query)
#             Return a single record resulting from $query for the $dbh handle
#             object. Null (undef) is returned if no data found. If the query
#             could return multiple records, the other records are discarded. 
#             If query is invalid the method will exit using the value of the
#             global $DO_DBI_ERR_EXIT, or die.
#
# Error handling:
#    Uses DoDBIerr method. See above.
#
# Calls:
#    PrintMsg,  which must be declared prior to this module.
#
# Modification history:
#
#   date    opr     who     reason
# -------- -----  --------  --------------------------------------
# 12/07/04 52482  J.Baum    Initial version
# 03/25/10 64273  MSwam     Moved to SHARE
# 
#########################################################################
#
use STScI::DBI;
use strict;
# declare global variables
use vars ('$DO_DBI_ERR_EXIT');
#---------------------------------------------------------------------
#---  used to connect to server and open database
# Usage: $dbh    = DoDBIopen( $server, $database, $err_exit );
sub DoDBIopen
{
    my ($server,$database,$err_exit) = @_;
    my $dbh = STScI::DBI->connect( "dbi:Sybase:server=$server");

    if (!defined($dbh)) {
        PrintMsg("E","Cannot connect to server: $DBI::errstr");
        exit( $err_exit);
    }
    # set global parameter for later use
    $DO_DBI_ERR_EXIT = $err_exit;
     
    DoDBI($dbh,"use $database"); # open database
    $dbh;
}
#---------------------------------------------------------------------
#---  used to disconnect from server with possible warning message 
# Usage: DoDBIclose( $dbh);
sub DoDBIclose
{
    my ($dbh) = @_;
    if (!$dbh->disconnect) {
        PrintMsg("W","Disconnect warning: ".$dbh->errstr);
    }
    1;
}
#---------------------------------------------------------------------
#---  used to exit after reporting error in query
#     and always disconnects the session
# Usage: DoDBIerr( $dbh, $query, "Error in query: ".$dbh->errstr); 
sub DoDBIerr
{
    my ($dbh, $query, $msg) = @_;
    PrintMsg("E",$msg);
    PrintMsg("I","Failed query: $query");
    $dbh->disconnect;
       
    if (defined($DO_DBI_ERR_EXIT) ) {
        PrintMsg("I","Setting exit status to $DO_DBI_ERR_EXIT");
        exit( $DO_DBI_ERR_EXIT);
    } else {
        die "DoDBI error";
    }
}
#---------------------------------------------------------------------
#---  used for queries that return only a count of affected rows, 0E0,
#     or -1 (which are all values that evaluate as true in Perl)
#
# 03/05/2008  44712   MSwam   translate 0E0 to zero for reporting
#
# Usage:  $count  = DoDBI( $dbh, $query);
sub DoDBI
{
    my ($dbh, $query) = @_;
    my $count = $dbh->do($query);
    
    if ($dbh->err) {
        DoDBIerr($dbh, $query, "Cannot do query: ".$dbh->errstr);
    }
    $count = 0 if $count eq "0E0";
    $count;
}
#---------------------------------------------------------------------
#---  used to setup a query for records to fetched using DoDBIfetch
#     Usage: $sth    = DoDBIexecute ($dbh, $query);
sub DoDBIexecute
{
    my ($dbh, $query) = @_;

    my $sth = $dbh->prepare($query);
    
    if ($dbh->err) {
        DoDBIerr($dbh, $query, "Cannot prepare query: ".$dbh->errstr);
    }
    $sth->execute;

    if ($sth->err) {
        DoDBIerr($dbh, $query, "Cannot execute query: ".$sth->errstr);
    }
    $sth;
}
#---------------------------------------------------------------------
#---  used to return next record from a query 
#  Usage: @record = DoDBIfetch($dbh, $query, $sth);
sub DoDBIfetch
{
    my ($dbh, $query, $sth) = @_;
    my @record = $sth->fetchrow_array;

    if ($sth->err) {
        DoDBIerr($dbh, $query, "Cannot fetch row: ".$sth->errstr);
    }
    @record;
}
#---------------------------------------------------------------------
#---  used only to return one record from a table 
#  Usage:  @record = DoDBIselect ($dbh, $query); 
sub DoDBIselect
{
    my ($dbh, $query) = @_;
    my $sth  = DoDBIexecute($dbh, $query);
    my @record = DoDBIfetch($dbh, $query, $sth);
    $sth->finish;  # required here if more than one record in query
    @record;
}
1;