xref: /dports/databases/p5-DBIWrapper/libdbiwrapper-perl-0.30/DBIWrapper.pm

# DBIWrapper.pm - The DataBase Wrapper Class that provides the DBI database
# connection and core functions for working with DBI databases.
# Created by James Pattie, 11/02/2000.

# Copyright (c) 2000-2006 Xperience, Inc. http://www.pcxperience.com/
# All rights reserved.  This program is free software; you can redistribute it
# and/or modify it under the same terms as Perl itself.

package DBIWrapper;
use strict;
use DBI;
use IO::Scalar;
use Spreadsheet::WriteExcel;
use DateTime;
use DateTime::HiRes;
use DBIWrapper::Time::Now::HiRes;
use Digest::SHA qw( sha1_hex );
use POSIX qw(floor);
use vars qw($AUTOLOAD $VERSION @ISA @EXPORT @EXPORT_OK);

require Exporter;

@ISA = qw(Exporter AutoLoader);
@EXPORT = qw();

$VERSION = '0.30';

use vars qw($formUnEncodedCharacters %formUnEncodedCharactersHash);
$formUnEncodedCharacters = '<>"';
%formUnEncodedCharactersHash = ( '<' => '&lt;', '>' => '&gt;', '"' => '&quot;', '&' => '&amp;' );

my $deadlockEncountered = 0;  # global variable to allow sybase to detect when a deadlock happened so code can be re-run.

=head1 NAME

DBIWrapper - Perl extension for generic DBI database access.

=head1 SYNOPSIS

  use DBIWrapper;
  my $db = DBIWrapper->new(dbType => "Pg",
                           dbName => "test_db",
                           dbHost => "localhost",
                           dbUser => "nobody",
                           dbPasswd => "",
                           dbPort => "5432",
                           predefinedDSN => "",
                           printError => 1,
                           raiseError => 1,
                           autoCommit => 0);
  if ($db->error())
  {
    die $db->errorMessage();
  }

  my $sth = $db->read("SELECT * FROM test_tb");
  my $result = $db->write(sql => "INSERT INTO test_tb (name, value) VALUES (?, ?)",
    plug => [ $name, $value ]);
  # this used DBI's substitution features to plugin the name and value.

  $db->close();  # close down the database connection.  Any read()'s
  # or write()'s will no longer be valid with this object until a new() is
  # issued again.

=head1 DESCRIPTION

DBIWrapper is the generic database Object for accessing the DBI database
interface.  It provides the lowest level of functionality needed by any
program wanting to access databases via the DBI.  Currently, DBIWrapper
is only aware of Pg (PostgreSQL), mysql (MySQL), Sybase and ODBC DBD
modules and how to work with them correctly.

Support for transactions on MySQL is now checked for and if found to be
available, the AutoCommit flag is turned off so that transactions will
be used.

The substitution array (if used) will cause each ##?1##, ##?2##, etc.
string in the sql string to be substituted for the corresponding value
in the substitution array.  It must start at ?1.  It is up to the user
to pass in the correct number of elements for both the plug and
substitution arrays.  The plug array is used to pass in the values for
DBI to replace in the sql string of ? which is standard DBI notation.

=head1 DATABASE VERSION INFO
The version info for the database in use, if determinable, is now
stored as:
B<serverVersion> = I<complete version string>
Ex: on Debian, PostgreSQL returns I<8.1.0>
MySQL returns I<4.1.11-Debian_4sarge5-log>

B<serverVerMajor> = I<The Major release number>
From the previous examples, PostgreSQL would be I<8>, MySQL would be I<4>.

B<serverVerMinor> = I<The Minor release number>
From the previous examples, PostgreSQL would be I<1>, MySQL would be I<1>.

B<serverVerRelease> = I<The Point release number>.  This does not include
any text after the point release value that may be included by the distro.
From the previous examples, PostgreSQL would be I<0>, MySQL would be I<11>.

The stored database version info is used to determine if we can still do
I<oid> based lastID lookups in PostgreSQL or if we have to do something
that doesn't depend on the I<oid> since PostgreSQL B<8.1> no longer enables
I<oid>s by default.

=head1 Sybase NOTES

The getDataArray(), getDataArrayHeader(), getDataHash(),
getDataHashHeader(), readXML(), readHTML() methods all properly handle
multiple result sets being returned from Sybase.  This could be the
result of multiple select statements or a compute clause.  In the
case of the Header() methods, the header row is based on the first
returned select statement, which may not be correct for the following
statements or compute blocks.

=head1 Deadlock Detection NOTES

Initial support for detecting a deadlock scenario when using Sybase is
now implemented.  The code will attempt to retry the sql in question,
either a read or write call, upto deadlockNumTries tries and sleeping
for deadlockSleep seconds between tries.  If deadlockRampSleep is enabled,
which it is not by default, then we multiply the deadlockSleep by the
current try #, if > 1, thus sleeping in multiples of deadlockSleep seconds.

There are currently no helper methods to change the values, but you can
just assign new values to the dbObj instance you create as they are
encapsulated within the object.  The only thing that is not encapsulated
is the deadlockEncountered global variable, due to the way the DBI error
handler is defined.  You should not have to touch this variable unless you
wanted to know if a deadlock had been detected in your last read()/write()
command.  It is reset to false whenever a read()/write() is issued.

=head1 Long Running SQL Detection NOTES

You can now define read and write thresholds (in seconds) that if a read()
or write() ran for >= the threshold then it will be logged to the long
running log file you specified or '/var/log/dbiwrapper-long-running-sql.log'.

By default the longRunningRead and longRunningWrite thresholds are 10 seconds.

The format of the logged entries is:
$0|$$|start timestamp (formatted)|end timestamp (formatted)|# seconds ran|read or write|deadlock Encountered|numTries|server or dbHost|dbName|sql statement|plug arguments|uniqueID

  See Logging Notes below for timestamp and duration changes.

sql statement has all newlines turned into spaces so it will fit on a single line.
plug arguments is a comma delimited list.

=head1 Logging SQL statements NOTES

B<LOG FORMAT CHANGES>: timestamps are now using DateTime::HiRes and include milliseconds.

logDateFormat is now ignored and will be removed in a future version.  Date format is
hardcoded as YYYY-MM-DD HH:MM:SS.milliseconds (0 to 999).

specify myTimeZone if you want something other than 'America/Phoenix'.

A hopefully unique ID will be generated that consists of SHA1'ing the concatenation of:
$0 . $$ . timestamp formatted . action . sql statement (newlines replaced)
I'll store the hexified SHA1 value for the uniqueID.

-------------

All sql statements will now default to being logged to
/var/log/dbiwrapper-sql-statements.log.

This can be turned off and an alternate log file specified.

Log file format is:

$0|$$|timestamp (formatted)|read or write|server or dbHost|dbName|sql statement|plug arguments|uniqueID

sql statement has all newlines turned into spaces so it will fit on a single line.
plug arguments is a comma delimited list.

Specify sqlNewlineReplacement if you want a different \n replacement in the sql statement log.
Specify sqlPlugNewlineReplacement if you want a different \n replacement in the plug arguments.

sqlNewlineReplacement = ' '
sqlPlugNewlineReplacement = '\\n'

All |'s are \ escaped in the sql and plug strings that are logged to disk.

2 additional log files will be created to track the start/stop and duration of the
executing sql.

I take the sqlStatementLog and insert -start and -stop before the .log extension.

Start SQL file format is:

uniqueID|$0|$$|start_timestamp (formatted)|first 20 chars of sql statement|length of sql statement|plug arguments

Stop SQL file format is:

uniqueID|$0|$$|start_timestamp (formatted)|stop_timestamp (formatted)|duration.milliseconds|first 20 chars of sql statement|length of sql statement|plug arguments|deadlock Encountered|numTries

duration.milliseconds is the duration in whole seconds plus the # of milliseconds difference.  It is not a fractional value.

=head1 Exported FUNCTIONS

B<NOTE>: I<bool> = 1(true), 0(false)

=over 4

=item scalar new(dbType, dbName, dbHost, dbUser, dbPasswd, dbPort, printError, raiseError, autoCommit, predefinedDSN, setDateStyle, logLevel, server, interfaces, longRunningRead, longRunningWrite, longRunningLog, logSQLStatements, sqlStatementLog, logSQLDurations, myTimeZone)

 Creates a new instance of the DBIWrapper object and opens
 a connection to the specified database.  If predefinedDSN is
 specified then it is used instead of the dbName, dbHost, dbPort
 values.  This is mainly to support ODBC easier.
 If setDateStyle is 1 (default) and dbType = Pg, then the datestyle
 for PostgreSQL is set to US (MM/DD/YYYY).
 logLevel defaults to 0.  There are 4 levels 0, 1, 2 and 3 which log
 the following items when an error occurs:
 0) Nothing is output
 1) dbType, dbHost, dbName, printError, raiseError, autoCommit,
   setDateStyle, supportsTransactions, transactionType, server,
   interfaces
 2) all of 1 plus dbUser, dbPort, predefinedDSN
 3) all of 2 plus dbPasswd

 Sybase specific:
 server allows you to specify the database server to connect to by name
   and must be defined in your interfaces file.
 interfaces allows you to specify the Sybase interfaces file needed
   to properly connect to the Sybase database.

 If you do not specify server and interfaces, then dbHost and dbPort
 will be used.

=cut
sub new
{
  my $that = shift;
  my $class = ref($that) || $that;
  my $self = bless {}, $class;
  my %args = ( dbType => 'Pg', dbHost => 'localhost', dbUser => 'nobody', dbPasswd => '', dbPort => '5432', predefinedDSN => "", printError => 1, raiseError => 1, autoCommit => 0, setDateStyle => 1, logLevel => 0, interfaces => "", server => "", longRunningRead => 10, longRunningWrite => 10, longRunningLog => "/var/log/dbiwrapper-long-running-sql.log", logSQLStatements => 1, sqlStatementLog => "/var/log/dbiwrapper-sql-statements.log", sqlNewlineReplacement => ' ', sqlPlugNewlineReplacement => '\\n', logSQLDurations => 1, myTimeZone => 'America/Phoenix', @_ );
  my ($dbType, $dbName, $dbHost, $dbUser, $dbPasswd, $dbPort, $predefinedDSN, $printError, $raiseError, $autoCommit, $setDateStyle, $logLevel, $interfaces, $server);

  $dbType = $args{dbType};
  $dbHost = $args{dbHost};
  $dbName = $args{dbName};
  $dbUser = $args{dbUser};
  $dbPasswd = $args{dbPasswd};
  $dbPort = $args{dbPort};
  $predefinedDSN = $args{predefinedDSN};
  $printError = $args{printError};
  $raiseError = $args{raiseError};
  $autoCommit = $args{autoCommit};
  $setDateStyle = $args{setDateStyle};
  $logLevel = $args{logLevel};
  $interfaces = $args{interfaces};
  $server = $args{server};
  $self->{supportsTransactions} = 1;  # by default all Databases support Transactions, except for MySQL.
  $self->{transactionType} = "";  # This is only set by MySQL so we know what type of transaction support is available.
  if ($dbType eq "mysql")
  {
    $autoCommit = 1;  # it may not do transactions yet.
    $self->{supportsTransactions} = 0;
  }

  $self->{error} = 0;  # nothing wrong yet.
  $self->{errorString} = "";
  $self->{errorPhrase} = "() - Error!<br />\n";

  $self->{dbType} = $dbType;
  $self->{dbHost} = $dbHost;
  $self->{dbName} = $dbName;
  $self->{dbUser} = $dbUser;
  $self->{dbPasswd} = $dbPasswd;
  $self->{dbPort} = $dbPort;
  $self->{predefinedDSN} = $predefinedDSN;
  $self->{printError} = $printError;
  $self->{raiseError} = $raiseError;
  $self->{autoCommit} = $autoCommit;
  $self->{setDateStyle} = $setDateStyle;
  $self->{logLevel} = $logLevel;
  $self->{interfaces} = (length $interfaces > 0 ? $interfaces : undef);
  $self->{server} = (length $server > 0 ? $server : undef);
  $self->{dbh} = undef;  # set this explicitly now so that we have something to check if an error occurs later.

  # deadlock detection code for sybase.
  $self->{deadlockSleep} = 5;  # number of seconds to sleep before retrying.
  $self->{deadlockNumTries} = 3;  # total # of times to retry sql that deadlocked.
  $self->{deadlockRampSleep} = 0;  # bool indicating if we should increment the amount of sleep between tries.

  # long running sql detection settings
  $self->{longRunningRead} = $args{longRunningRead};
  $self->{longRunningWrite} = $args{longRunningWrite};
  $self->{longRunningLog} = $args{longRunningLog};

  # log sql statements
  $self->{myTimeZone} = $args{myTimeZone};
  $self->{logSQLStatements} = $args{logSQLStatements};
  $self->{logSQLDurations} = $args{logSQLDurations};
  $self->{sqlStatementLog} = $args{sqlStatementLog};
  $self->{sqlNewlineReplacement} = $args{sqlNewlineReplacement};
  $self->{sqlPlugNewlineReplacement} = $args{sqlPlugNewlineReplacement};

  $self->validate_and_connect();

  return $self;
}

=item void validate_and_connect()

=cut
sub validate_and_connect
{
  my $self = shift;

  if (!$self->isValid(new => 1))
  {
    $self->error(errorString => "Error!<br />\n" . $self->errorMessage);
    return $self;
  }

  $self->changeSQLLogFiles($self->{sqlStatementLog}, $self->{longRunningLog});

  my $dbh;
  my $dsn;
  my %errorHandlers = ();
  my $dbType        = $self->{dbType};
  my $predefinedDSN = $self->{predefinedDSN};
  my $interfaces    = $self->{interfaces};

  $dsn = "dbi:$dbType:";

  if ($dbType =~ /^(Pg|mysql)$/)
  {
    if (length $predefinedDSN > 0)
    {
      $dsn .= $predefinedDSN;
    }
    else
    {
      $dsn .= "dbname=$self->{dbName};host=$self->{dbHost};port=$self->{dbPort}";
    }
  }
  elsif ($dbType eq "Sybase")
  {
    if (length $predefinedDSN > 0)
    {
      $dsn .= $predefinedDSN;
    }
    else
    {
      $dsn .= "database=$self->{dbName}";
      if (defined $interfaces)
      {
        $dsn .= ";server=$self->{server};interfaces=$self->{interfaces}";
      }
      else
      {
        # use host and port
        $dsn .= ";host=$self->{dbHost};port=$self->{dbPort}";
      }
    }
    $errorHandlers{syb_err_handler} = \&sybaseErrorHandler;
  }
  elsif ($dbType eq "ODBC")
  {
    if (length $predefinedDSN > 0)
    {
      $dsn .= $predefinedDSN;
    }
    else
    {
      $self->error(errorString => "Error!<br />\nYou must specify the 'predefinedDSN' for dbType = '$dbType'!<br />\n" . $self->debugMessage);
      return $self;
    }
  }

  eval {
    $dbh = DBI->connect($dsn, $self->{dbUser}, $self->{dbPasswd}, { PrintError => $self->{printError}, RaiseError => $self->{raiseError}, AutoCommit => $self->{autoCommit}, %errorHandlers });
  };
  if ($@)
  {
    $self->{dbh} = undef;
    $self->error(errorString => "Eval of connect failed!<br />\nError = '$@'.<br />\nDBIError = '" . $DBI::errstr . "'.<br />\n" . $self->debugMessage);
  }
  else
  {
    if ($dbh)
    {
      if (!$DBI::err)
      {
        $self->{dbh} = $dbh;
        if ($dbType eq "Pg")
        {
          # figure out what version we are working with.
          my @result = $self->getDataArray(sql => "SHOW server_version");
          $self->{serverVersion} = $result[0]->[0];
          ($self->{serverVerMajor} = $self->{serverVersion}) =~ s/^(\d+)\.(\d+\.\d+)$/$1/;
          ($self->{serverVerMinor} = $self->{serverVersion}) =~ s/^(\d+)\.(\d+)(\.\d+)$/$2/;
          ($self->{serverVerRelease} = $self->{serverVersion}) =~ s/^(\d+)\.(\d+)\.(\d+)$/$3/;

          #print "PostgreSQL $self->{serverVersion}\nMajor = '$self->{serverVerMajor}'\nMinor = '$self->{serverVerMinor}'\nRelease = '$self->{serverVerRelease}'\n";

          if ($self->{setDateStyle})
          {
            $self->write(sql => "SET datestyle TO 'POSTGRES,US'");
          }
        }
        if ($dbType eq "mysql")
        {
          # figure out what version we are working with.
          my @result = $self->getDataArray(sql => "SHOW VARIABLES LIKE 'version'");
          $self->{serverVersion} = $result[0]->[1];
          ($self->{serverVerMajor} = $self->{serverVersion}) =~ s/^(\d+)\.(\d+\.\d+)(\-.+)?$/$1/;
          ($self->{serverVerMinor} = $self->{serverVersion}) =~ s/^(\d+)\.(\d+)(\.\d+)(\-.+)?$/$2/;
          ($self->{serverVerRelease} = $self->{serverVersion}) =~ s/^(\d+)\.(\d+)\.(\d+)(\-.+)?$/$3/;

          #print "MySQL $self->{serverVersion}\nMajor = '$self->{serverVerMajor}'\nMinor = '$self->{serverVerMinor}'\nRelease = '$self->{serverVerRelease}'\n";

          # check for Transaction support, and if present disable the AutoCommit flag.
          if ($self->mysqlHasTransactions())
          {
            $self->{supportsTransactions} = 1;
            $self->{dbh}->{AutoCommit} = 0;
          }
          if ($self->error)
          {
            $self->{dbh} = undef;
            $self->error(errorString => "Checking for Transactions with MySQL failed!<br />\n" . $DBI::errstr . "<br />\n" . $self->debugMessage);
          }
        }
      }
      else
      {
        $self->{dbh} = undef;
        $self->error(errorString => "connect failed!<br />\n" . $DBI::errstr . "<br />\n" . $self->debugMessage);
      }
    }
    else
    {
      $self->{dbh} = undef;
      $self->error(errorString => "connect failed!<br />\n" . $DBI::errstr . "<br />\n" . $self->debugMessage);
    }
  }
}

=item bool isValid()

 Returns 1 if the DBI object is valid, else 0 if invalid.

=cut
sub isValid
{
  my $self = shift;
  my %args = ( new => 0, @_ );
  my $new = $args{new};
  my $valid = 1;
  my $errorString = "";

  if (!$new)
  {
    if (!defined $self->{dbh})
    {
      $errorString .= "dbh is not defined!<br />\n";
      $valid = 0;
    }
  }
  if ($self->{dbType} !~ /^(Pg|mysql|ODBC|Sybase)$/)
  {
    $errorString .= "dbType = '$self->{dbType}' is invalid!<br />\n";
    $valid = 0;
  }
  if (length $self->{predefinedDSN} == 0) # only check the dbHost, dbName and dbPort values if not using predefinedDSN
  {
    if (length $self->{dbName} == 0)
    {
      $errorString .= "dbName = '$self->{dbName}' is invalid!<br />\n";
      $valid = 0;
    }
    if ($self->{dbType} eq "Sybase")
    {
      if (defined $self->{server} && !defined $self->{interfaces})
      {
        $errorString .= "server = '$self->{server}'.  interfaces must be specified!<br />\n";
        $valid = 0;
      }
      if (defined $self->{interfaces} && !defined $self->{server})
      {
        $errorString .= "interfaces = '$self->{interfaces}'.  server must be specified!<br />\n";
        $valid = 0;
      }
    }
    if ($self->{dbType} ne "Sybase" || ($self->{dbType} eq "Sybase" && (!defined $self->{server} && !defined $self->{interfaces})))
    {
      if (length $self->{dbHost} == 0)
      {
        $errorString .= "dbHost = '$self->{dbHost}' is invalid!<br />\n";
        $valid = 0;
      }
      if ($self->{dbPort} !~ /^(\d+)$/)
      {
        $errorString .= "dbPort = '$self->{dbPort}' is invalid!<br />\n";
        $valid = 0;
      }
    }
  }
  if (length $self->{dbUser} == 0)
  {
    $errorString .= "dbUser = '$self->{dbUser}' is invalid!<br />\n";
    $valid = 0;
  }
  if ($self->{autoCommit} !~ /^(0|1)$/)
  {
    $errorString .= "autoCommit = '$self->{autoCommit}' is invalid!<br />\n";
    $valid = 0;
  }
  if ($self->{printError} !~ /^(0|1)$/)
  {
    $errorString .= "printError = '$self->{printError}' is invalid!<br />\n";
    $valid = 0;
  }
  if ($self->{raiseError} !~ /^(0|1)$/)
  {
    $errorString .= "raiseError = '$self->{raiseError}' is invalid!<br />\n";
    $valid = 0;
  }
  if ($self->{setDateStyle} !~ /^(0|1)$/)
  {
    $errorString .= "setDateStyle = '$self->{setDateStyle}' is invalid!<br />\n";
    $valid = 0;
  }
  if ($self->{logLevel} !~ /^(0|1|2|3)$/)
  {
    $errorString .= "logLevel = '$self->{logLevel}' is invalid!<br />\n";
    $valid = 0;
  }
  if ($self->{longRunningRead} !~ /^\d+$/ || $self->{longRunningRead} < 1)
  {
    $errorString .= "longRunningRead = '$self->{longRunningRead}' is invalid!  Must be >= 1.<br />\n";
    $valid = 0;
  }
  if ($self->{longRunningWrite} !~ /^\d+$/ || $self->{longRunningWrite} < 1)
  {
    $errorString .= "longRunningWrite = '$self->{longRunningWrite}' is invalid!  Must be >= 1.<br />\n";
    $valid = 0;
  }
  if ($self->{longRunningLog} !~ /^\/.+\.log$/)
  {
    $errorString .= "longRunningLog = '$self->{longRunningLog}' is invalid!  Must start with a / and have at least 1 character for the filename and end in .log.<br />\n";
    $valid = 0;
  }
  if ($self->{logSQLStatements} !~ /^[10]$/)
  {
    $errorString .= "logSQLStatements = '$self->{logSQLStatements}' is invalid!  Must be 1 or 0.<br />\n";
    $valid = 0;
  }
  if ($self->{logSQLDurations} !~ /^[10]$/)
  {
    $errorString .= "logSQLDurations = '$self->{logSQLDurations}' is invalid!  Must be 1 or 0.<br />\n";
    $valid = 0;
  }
  if ($self->{myTimeZone} eq '')
  {
    $errorString .= "myTimeZone = '$self->{myTimeZone}' is invalid!<br />\n";
    $valid = 0;
  }
  if ($self->{sqlStatementLog} !~ /^\/.+\.log$/)
  {
    $errorString .= "sqlStatementLog = '$self->{sqlStatementLog}' is invalid!  Must start with a / and have at least 1 character for the filename and end in .log.<br />\n";
    $valid = 0;
  }

  if (!$valid)
  {
    $self->error(errorString => "$errorString" . $self->debugMessage);
  }

  return $valid;
}

=item void changeSQLLogFiles(sqlStatementLog, longRunningLog)

Changes the internal variables and re-computes the -start and -stop
log file names.

=cut
sub changeSQLLogFiles
{
  my $self = shift;
  my $sqlStatementLog = shift;
  my $longRunningLog = shift;

  $self->{sqlStatementLog} = $sqlStatementLog;
  $self->{longRunningLog} = $longRunningLog;

  my $errorString = "";
  my $valid = 1;
  if ($self->{longRunningLog} !~ /^\/.+\.log$/)
  {
    $errorString .= "longRunningLog = '$self->{longRunningLog}' is invalid!  Must start with a / and have at least 1 character for the filename and end in .log.<br />\n";
    $valid = 0;
  }

  if ($self->{sqlStatementLog} !~ /^\/.+\.log$/)
  {
    $errorString .= "sqlStatementLog = '$self->{sqlStatementLog}' is invalid!  Must start with a / and have at least 1 character for the filename and end in .log.<br />\n";
    $valid = 0;
  }
  if (!$valid)
  {
    $self->error(errorString => "$errorString" . $self->debugMessage);
  }

  # compute the start and stop log files.
  ($self->{sqlStatementLogStart} = $self->{sqlStatementLog}) =~ s/\.log$/-start.log/;
  ($self->{sqlStatementLogStop} = $self->{sqlStatementLog}) =~ s/\.log$/-stop.log/;
}

=item void close()

  Closes the connection to the database.

=cut
sub close
{
  my $self = shift;

  if (defined $self->{dbh} && ref $self->{dbh} eq "DBI::db")
  {
    my $result;
    eval { $result = $self->{dbh}->disconnect; };
    if ($@)
    {
      $self->error(errorString => "Eval of disconnect failed!<br />\nError = '$@'.<br />\n" . $self->debugMessage);
      return;
    }
    else
    {
      eval {
        if (!$result || $self->{dbh}->err)
        {
          $self->error(errorString => "disconnect failed!<br />\n" . $self->{dbh}->errstr . "<br />\n" . $self->debugMessage);
          return;
        }
      };
      if ($@)
      {
        $self->error(errorString => "Eval of result check failed!<br />\nError = '$@'.<br />\n" . $self->debugMessage);
        return;
      }
    }
    $self->{dbh} = undef;  # signal it is no longer valid!
  }
}

=item bool error(errorString)

 This method will set the error condition if an argument is
 specified.

 The current error state is returned, regardless of if we are
 setting an error or not.

 A \n is appended to the errorString so you don't have to provide it.
 errorString is prefixed with the caller's full method name followed
 by the errorPhrase string.

 You can either specify the errorString value by name:

 $self->error(errorString => "This is an error!");

 or by value:

 $self->error("This is an error!");

 If you specify multiple arguments (in pass by value mode), then
 we check to see if the first argument contains %'s that are not
 \ escaped and are not %%.  If this is the case, then the incoming
 arguments will be passed through sprintf() for formatting, else we
 just join them with a space ' ' and append them to the current
 errorString.


 To see if an error happened:

 if ($self->error) { die "Error: " . $self->errorMessage; }

=cut
sub error
{
  my $self = shift;
  my @callerArgs = caller(1);
  (my $subName = $callerArgs[3]) =~ s/^(.+)(::)([^:]+)$/$1->$3/;
  my $callerErrStr = "$subName$self->{errorPhrase}";

  if (scalar @_ > 0)
  {
    # we are setting an error condition.
    if (scalar @_ == 1)
    {
      $self->{errorString} .= $callerErrStr . $_[0];
    }
    else
    {
      if ($_[0] eq "errorString")
      {
        my %args = ( @_ );
        if (!exists $args{errorString})  # make sure we get the errorString argument!
        {
          $self->error($callerErrStr . "<b>errorString</b> is missing!<br />\n");
          return;
        }
        else
        {
          $self->{errorString} .= $callerErrStr . $args{errorString};
        }
      }
      else
      {
        # handle the sprintf case.
        if ($_[0] =~ /(?<!\\)%[^%]/)
        {
          # build up the string to eval for the sprintf.
          my $str = "\"$_[0]\"";
          for (my $i=1; $i < scalar @_; $i++)
          {
            $str .= ", \"$_[$i]\"";
          }
          $self->{errorString} .= $callerErrStr;
          eval "\$self->{errorString} .= sprintf($str);";
          if ($@)
          {
            $self->error($callerErrStr . $@);
            return;
          }
        }
        else
        {
          $self->{errorString} .= $callerErrStr . join(" ", @_);
        }
      }
    }
    $self->{errorString} .= "\n";
    $self->{error} = 1;
  }

  return $self->{error};
}

=item void setError(errorString)

 DEPRECATED: see error()

 optional: errorString
 returns: nothing
 Sets error = 1 and errorString = string passed in.
 The errorString is prefixed with the caller's full
 method name followed by the errorPhrase string.

 You can either call as
 setError(errorString => $string)
 or setError($string)

 If you do not specify anything, we blow an error
 telling you to specify errorString.

 \n is appended to the contents of the errorString
 passed in.

=cut

sub setError
{
  my $self = shift;
  my @callerArgs = caller(1);
  (my $subName = $callerArgs[3]) =~ s/^(.+)(::)([^:]+)$/$1->$3/;
  my $callerErrStr = "$subName$self->{errorPhrase}";
  my $deprecated = "DEPRECATED call to setError!  Convert to using error().<br />\n";

  if (scalar @_ == 1)
  {
    $self->{errorString} = $deprecated . $callerErrStr . $_[0];
  }
  else
  {
    my %args = ( @_ );
    if (!exists $args{errorString})  # make sure we get the errorString argument!
    {
      $self->setError($callerErrStr . "<b>errorString</b> is missing!<br />\n");
      return;
    }
    else
    {
      $self->{errorString} = $deprecated . $callerErrStr . $args{errorString};
    }
  }
  $self->{errorString} .= "\n";
  $self->{error} = 1;
}

=item void prefixError(errorString)

 optional: errorString
 returns: nothing
 Sets error = 1 and prefixes errorString with string passed in.
 The errorString is prefixed with the caller's full
 method name followed by the errorPhrase string.

 You can either specify the errorString value by name:

 $self->prefixError(errorString => "This is an error!");

 or by value:

 $self->prefixError("This is an error!");

 If you specify multiple arguments (in pass by value mode), then
 we check to see if the first argument contains %'s that are not
 \ escaped and are not %%.  If this is the case, then the incoming
 arguments will be passed through sprintf() for formatting, else we
 just join them with a space ' ' and append them to the current
 errorString.


 If you don't specify anything then
   If you have a previous error, we prefix the caller info to
     that error message.

=cut

sub prefixError
{
  my $self = shift;
  my @callerArgs = caller(1);
  (my $subName = $callerArgs[3]) =~ s/^(.+)(::)([^:]+)$/$1->$3/;
  my $callerErrStr = "$subName$self->{errorPhrase}";

  if (scalar @_ == 1)
  {
    $self->{errorString} = $callerErrStr . $_[0] . $self->{errorString} . "\n";
  }
  else
  {
    if ($_[0] eq "errorString")
    {
      my %args = ( @_ );
      if (!exists $args{errorString})  # make sure we get the errorString argument!
      {
        if ($self->{errorString})
        {
          # prefix the old errorString value.
          $self->{errorString} = $callerErrStr . $self->{errorString};
        }
        else
        {
          $self->error($callerErrStr . "<b>errorString</b> is missing!<br />\n");
          return;
        }
      }
      else
      {
        $self->{errorString} = $callerErrStr . $args{errorString} . "\n" . $self->{errorString};
      }
    }
    else
    {
      # handle the sprintf case.
      if ($_[0] =~ /(?<!\\)%[^%]/)
      {
        # build up the string to eval for the sprintf.
        my $str = "\"$_[0]\"";
        for (my $i=1; $i < scalar @_; $i++)
        {
          $str .= ", \"$_[$i]\"";
        }
        my $oldErrorStr = $self->{errorString};
        $self->{errorString} = $callerErrStr;
        eval "\$self->{errorString} .= sprintf($str);";
        if ($@)
        {
          $self->error($callerErrStr . $@);
          return;
        }
        $self->{errorString} .= "\n" . $oldErrorStr;
      }
      else
      {
        $self->{errorString} = $callerErrStr . join(" ", @_) . "\n" . $self->{errorString};
      }
    }
  }
  $self->{error} = 1;
}

=item scalar didErrorOccur(void)

 DEPRECATED: see error()

 Returns the value of error.

=cut

sub didErrorOccur
{
  my $self = shift;

  return $self->{error};
}

=item scalar errorMessage(void)

 Returns the value of errorString.

=cut

sub errorMessage
{
  my $self = shift;

  return $self->{errorString};
}

=item scalar errorStr(void)

 Returns the value of errorString.

 Alternative to errorMessage().

=cut

sub errorStr
{
  my $self = shift;

  return $self->{errorString};
}

=item void resetError(void)

 Resets the error condition flag and string.

=cut

sub resetError
{
  my $self = shift;

  $self->{error} = 0;
  $self->{errorString} = "";
}

=item void commit()

 causes the database to commit the current transaction.  Only works
 if AutoCommit is set to 0 and the database supports Transactions.

=cut
sub commit
{
  my $self = shift;

  if (!$self->{supportsTransactions})
  {
    return;
  }
  eval { $self->{dbh}->commit; };
  if ($@)
  {
    $self->error(errorString => "commit failed!<br />\nError = $@" . "<br />\n" . $self->debugMessage);
  }
  elsif ($DBI::err)
  {
    $self->error(errorString => "commit failed!<br />\nError = $DBI::errstr" . "<br />\n" . $self->debugMessage);
  }
}

=item void rollback()

 causes the database to rollback the current transaction.  Only
 works if AutoCommit is set to 0 and the database supports
 Transactions.

=cut
sub rollback
{
  my $self = shift;

  if (!$self->{supportsTransactions})
  {
    return;
  }
  eval { $self->{dbh}->rollback; };
  if ($@)
  {
    $self->error(errorString => "rollback failed!<br />\nError = $@" . "<br />\n" . $self->debugMessage);
  }
  elsif ($DBI::err)
  {
    $self->error(errorString => "rollback failed!<br />\nError = $DBI::errstr" . "<br />\n" . $self->debugMessage);
  }
}

=item ref read(sql => "", plug => [], substitute => [])

 (This function should only be called for SELECT statements).
 executes the specified sql statement passing in any values in plug
 to the execute method after doing any substitutions that are in
 substitute.  The resulting sql data is passed back to the user as a
 reference for them to do with as they please.

=cut
sub read
{
  my $self = shift;
  my $sql = "";
  my @plug = ();
  my @substitute = ();
  if (scalar @_ == 1)
  {
    $sql = shift;
  }
  else
  {
    my %args = ( plug => [], substitute => [], @_ );
    @plug = @{$args{'plug'}};
    @substitute = @{$args{'substitute'}};

    $sql = $args{'sql'};
  }
  # validate we got a sql statement to work with.
  if (length $sql == 0)
  {
    $self->error(errorString => "SQL string not passed in!" . "<br />\n" . $self->debugMessage);

    return undef;
  }

  # check and see if we need to do any substitutions.
  if (scalar @substitute > 0)
  {
    for (my $i=0; $i < scalar @substitute; $i++)
    {
      my $temp_string = "\\#\\#\\?" . ($i+1) . "\\#\\#";
      $sql =~ s/$temp_string/$substitute[$i]/g;
    }
  }

  my $timerStart = $self->getCurrTime();
  $self->logSQLStatement("read", $timerStart, $sql, \@plug) if ($self->{logSQLStatements});

  # now prepare the statement
  my $sth;
  eval {
    $sth = $self->{dbh}->prepare($sql);
  };
  if ($@)
  {
    $self->error(errorString => "Eval of prepare failed!<br />\nError = '$@'.<br />\nsql='$sql'.<br />\nplug='" . join("', '", @plug) . "'.<br />\n" . $self->debugMessage);

    return undef;
  }
  elsif (!$sth || $DBI::err)
  {
    $self->error(errorString => "Preparing failed!<br />\n" . $DBI::errstr . "<br />\nsql='$sql'.<br />\nplug='" . join("', '", @plug) . "'.<br />\n" . $self->debugMessage);

    return undef;
  }

  # now execute the sql statement passing in any parameters given via plug
  my $rc;
  my $done = 0;
  my $numTries = 0;
  while (!$done)
  {
    $deadlockEncountered = 0;  # make sure we turn it off.
    eval {
      $rc = $sth->execute(@plug);
    };
    if (!$deadlockEncountered)
    {
      $done = 1;
      if ($@)
      {
        $self->error(errorString => "Eval of execute failed!<br />\nError = '$@'.<br />\nsql='$sql'.<br />\nplug='" . join("', '", @plug) . "'.<br />\n" . $self->debugMessage);

        $self->checkLongRunningSQL("read", $timerStart, $numTries, $sql, \@plug);
        return undef;
      }
      elsif (!$rc || $DBI::err)
      {
        $self->error(errorString => "Execute failed!<br />\n" . $DBI::errstr . "<br />\nsql='$sql'.<br />\nplug='" . join("', '", @plug) . "'.<br />\n" . $self->debugMessage);

        $self->checkLongRunningSQL("read", $timerStart, $numTries, $sql, \@plug);
        return undef;
      }
    }
    else
    {
      $numTries++;
      if ($numTries >= $self->{deadlockNumTries})
      {
        $self->checkLongRunningSQL("read", $timerStart, $numTries, $sql, \@plug);
        return undef;
      }
      my $sleep = $self->{deadlockSleep};
      if ($numTries > 1 && $self->{deadlockRampSleep})
      {
        $sleep *= $numTries;
      }
      print "ALERT:  deadlock encountered!  Retrying sql.  Attempt #$numTries.  Sleeping for $sleep seconds.\n";
      sleep($sleep);
    }
  }

  $self->checkLongRunningSQL("read", $timerStart, $numTries, $sql, \@plug);
  return $sth;
}
=item $ getCurrTime()

Returns the current time as a DBIWrapper::Time::Now::HiRes instance.

=cut
sub getCurrTime
{
  my $self = shift;
  my $dt = DBIWrapper::Time::Now::HiRes->new;

  return $dt;
}

=item $ getCurrTimeFormatted(offset, showMillisecond)

offset must be specified and is the number of seconds from now
that the time should be computed for.

If showMillisecond is defined then I output the .mmm part.

Returns a formatted timestamp as YYYY-MM-DD HH:MM:SS.mmm.

Normal usage is getCurrTimeFormatted(0, 1).

NOTE: offset is currently being ignored and is deprecated.

=cut
sub getCurrTimeFormatted
{
  my $self = shift;
  my $offset = shift;
  my $showMillisecond = shift;

  my $dt = DBIWrapper::Time::Now::HiRes->new;
  my $currTS_AZ = $dt->ymd . " " . $dt->hms . (defined $showMillisecond && $showMillisecond ? "." . $dt->millisecond : "");

  return $currTS_AZ;
}

=item $ getCurrTimeFormattedFromObj(dt, showMillisecond)

dt must be specified and is the DBIWrapper::Time::Now::HiRes object
to work with.

If showMillisecond is defined then I output the .mmm part.

Returns a formatted timestamp as YYYY-MM-DD HH:MM:SS.mmm.

Normal usage is getCurrTimeFormattedFromObj($dt, 1).

=cut
sub getCurrTimeFormattedFromObj
{
  my $self = shift;
  my $dt = shift;
  my $showMillisecond = shift;
  $dt->set_time_zone($self->{myTimeZone});  # just to be sure.
  # I can't rely on millisecond being <= 999 until all code is only using DBIWrapper::Time::Now::HiRes modules.
  my $millisecond = ($dt->millisecond > 999 ? 999 : $dt->millisecond);  # Have to get around sybase precision issues.
  my $currTS_AZ = $dt->ymd . " " . $dt->hms . (defined $showMillisecond && $showMillisecond ? "." . $millisecond : "");

  return $currTS_AZ;
}

=item $ getCurrTimeFormattedFromEpoch(epoch, showMillisecond)

epoch must be specified and is the epoch timestamp
to work with.

If showMillisecond is defined then I output the .mmm part.

Returns a formatted timestamp as YYYY-MM-DD HH:MM:SS.mmm.

Normal usage is getCurrTimeFormattedFromEpoch($timestampEpoch, 1).

=cut
sub getCurrTimeFormattedFromEpoch
{
  my $self = shift;
  my $epoch = shift;
  my $showMillisecond = shift;
  my $dt = DateTime->from_epoch(epoch => $epoch);
  $dt->set_time_zone($self->{myTimeZone});  # just to be sure.
  # I can't rely on millisecond being <= 999 until all code is only using DBIWrapper::Time::Now::HiRes modules.
  my $millisecond = ($dt->millisecond > 999 ? 999 : $dt->millisecond);  # Have to get around sybase precision issues.
  my $currTS_AZ = $dt->ymd . " " . $dt->hms . (defined $showMillisecond && $showMillisecond ? "." . $millisecond : "");

  return $currTS_AZ;
}

sub prefix
{
  my $self = shift;

  return "[$$ " . $self->getCurrTimeFormatted(0, 1) . "]";
}

=item $ computeDuration(startTime, endTime)

Required:

startTime - DBIWrapper::Time::Now::HiRes object

Optional:

endTime - DBIWrapper::Time::Now::HiRes object

Computes the duration between the given timestamps, down
to the millisecond level, and displays as the # of seconds it took.

Returns a string containing the duration.

=cut
sub computeDuration
{
  my $self = shift;
  my $startTime = shift;
  my $endTime = shift;

  if (!defined $startTime)
  {
    die "computeDuration() did not specify startTime!\n";
  }

  $endTime = $self->getCurrTime() if (!defined $endTime);
  my $dur = $endTime->subtract_datetime_absolute($startTime);
  my $durStr = $dur->seconds() . "." . floor($dur->nanoseconds() / 1000000) . " seconds";
  return $durStr;
}

=item void checkLongRunningSQL(method, timerStart, numTries, sql, plug)

Compares $self->getCurrTime() - timerStart against the longRunningRead/Write threshold and
if it's >= then logs to longRunningLog.

Doesn't return anything.

=cut
sub checkLongRunningSQL
{
  my $self = shift;
  my $method = shift;
  my $timerStart = shift;
  my $timerStop = $self->getCurrTime();
  my $numTries = shift;
  my $sql = shift;
  my $plug = shift;  # arrayref

  my $duration = $self->computeDuration($timerStart, $timerStop);
  my $timerStartFormatted = $self->getCurrTimeFormattedFromObj($timerStart, 1);
  my $timerStopFormatted = $self->getCurrTimeFormattedFromObj($timerStop, 1);
  (my $methodUpper = $method) =~ s/^(.)(.+)$/uc($1) . $2/e;
  # turn all newlines in the sql statement into spaces.
  $sql =~ s/\n/$self->{sqlNewlineReplacement}/mg;
  $sql =~ s/\|/\\\|/mg;  # make sure to escape the |'s.
  (my $sqlShort = $sql) =~ s/^(.{1,20})(.+)$/$1/;
  my $plugStr = join(', ', @{$plug});
  $plugStr =~ s/\n/$self->{sqlPlugNewlineReplacement}/mg;
  $plugStr =~ s/\|/\\\|/mg;  # make sure to escape the |'s.
  #print "checkLongRunningSQL($method [$methodUpper], $timerStart, $timerStop, $duration, $numTries, $sql)\n";
  my $uniqueID = sha1_hex($0 . $$ . $timerStartFormatted . $method . $sql);
  if (int($duration) >= $self->{"longRunning$methodUpper"})  # only compare the seconds part.
  {
    # we have a long running sql statement!  Log
    open F, ">>$self->{longRunningLog}" or die "Couldn't open '$self->{longRunningLog}' for append!  $!\n";
    print F "$0|$$|" . $timerStart->hires_epoch() . " ($timerStartFormatted)|" . $timerStop->hires_epoch() . " ($timerStopFormatted)|$duration|$method|$deadlockEncountered|$numTries|" . ($self->{dbType} eq "Sybase" ? $self->{server} : $self->{dbHost}) . "|$self->{dbName}|$sql|$plugStr|$uniqueID\n";
    close F;
  }

  if ($self->{logSQLStatements} && $self->{logSQLDurations})
  {
    # generate the stop file entry.
    open F, ">>$self->{sqlStatementLogStop}" or die "Couldn't open '$self->{sqlStatementLogStop}' for append!  $!\n";
    print F "$uniqueID|$0|$$|" . $timerStart->hires_epoch() . " ($timerStartFormatted)|" . $timerStop->hires_epoch() . " ($timerStopFormatted)|$duration|$sqlShort|" . int(length $sql) . "|$plugStr|$deadlockEncountered|$numTries\n";
    close F;
  }
}

=item void logSQLStatement(method, timerStart, sql, plug)

Logs the sql being run.

Doesn't return anything.

=cut
sub logSQLStatement
{
  my $self = shift;
  my $method = shift;
  my $timerStart = shift;
  my $sql = shift;
  my $plug = shift;  # arrayref

  my $timerStartFormatted = $self->getCurrTimeFormattedFromObj($timerStart, 1);
  # turn all newlines in the sql statement into spaces.
  $sql =~ s/\n/$self->{sqlNewlineReplacement}/mg;
  $sql =~ s/\|/\\\|/mg;  # make sure to escape the |'s.
  (my $sqlShort = $sql) =~ s/^(.{1,20})(.+)$/$1/;
  my $plugStr = join(', ', @{$plug});
  $plugStr =~ s/\n/$self->{sqlPlugNewlineReplacement}/mg;
  $plugStr =~ s/\|/\\\|/mg;  # make sure to escape the |'s.
  #print "logSQLStatement($method, $timerStart, $sql)\n";
  my $uniqueID = sha1_hex($0 . $$ . $timerStartFormatted . $method . $sql);
  open F, ">>$self->{sqlStatementLog}" or die "Couldn't open '$self->{sqlStatementLog}' for append!  $!\n";
  print F "$0|$$|" . $timerStart->hires_epoch() . " ($timerStartFormatted)|$method|" . ($self->{dbType} eq "Sybase" ? $self->{server} : $self->{dbHost}) . "|$self->{dbName}|$sql|$plugStr|$uniqueID\n";
  close F;

  if ($self->{logSQLDurations})
  {
    # generate the start file entry.
    open F, ">>$self->{sqlStatementLogStart}" or die "Couldn't open '$self->{sqlStatementLogStart}' for append!  $!\n";
    print F "$uniqueID|$0|$$|" . $timerStart->hires_epoch() . " ($timerStartFormatted)|$sqlShort|" . int(length $sql) . "|$plugStr\n";
    close F;
  }
}

=item @ getDataArray(sql, plug, substitute)

 requires: sql
 optional: plug, substitute
 returns: array of arrayrefs as the result of
   $sth->fetchall_arrayref

 See read() for argument info.

=cut
sub getDataArray
{
  my $self = shift;
  my $sql = "";
  my $sth = undef;
  if (scalar @_ == 1)
  {
    $sql = shift;
    $sth = $self->read($sql);
  }
  else
  {
    my %args = ( plug => [], substitute => [], @_ );
    $sth = $self->read(%args);
  }

  my @data = ();

  if ($self->error)
  {
    $self->prefixError();
    return @data;
  }

  while (my $row = $sth->fetchrow_arrayref || ($self->{dbType} eq "Sybase" && $sth->{syb_more_results}))
  {
    if (defined $row && ref($row) eq "ARRAY" && scalar @{$row} > 0)
    {
      # don't process the return status of a stored procedure from Sybase.
      next if ($self->{dbType} eq "Sybase" && $sth->{syb_result_type} == 4043);

      my @row = @{$row};
      push @data, \@row;
    }
  }

  $sth->finish;

  return @data;
}

=item @ getDataHash(sql, plug, substitute, case)

 requires: sql
 optional: plug, substitute, case
 returns: array of hashrefs where the column names are
   case preserved if case = 1, or lowercased if case = 0.

 case defaults to 0 (lowercase).

 See read() for argument info.

=cut
sub getDataHash
{
  my $self = shift;
  my $sql = "";
  my $case = 0;
  my $sth = undef;
  if (scalar @_ == 1)
  {
    $self->{dbh}->{FetchHashKeyName} = 'NAME_lc';
    $sql = shift;
    $sth = $self->read($sql);
  }
  else
  {
    my %args = ( plug => [], substitute => [], case => 0, @_ );
    my $case = $args{case};
    $self->{dbh}->{FetchHashKeyName} = ($case == 1 ? 'NAME' : 'NAME_lc');
    $sth = $self->read(%args);
  }

  my @data = ();

  if ($self->error)
  {
    $self->prefixError();
    return @data;
  }

  while (my $row = $sth->fetchrow_hashref || ($self->{dbType} eq "Sybase" && $sth->{syb_more_results}))
  {
    if (defined $row && ref($row) eq "HASH" && scalar keys %{$row} > 0)
    {
      # don't process the return status of a stored procedure from Sybase.
      next if ($self->{dbType} eq "Sybase" && $sth->{syb_result_type} == 4043);

      my %row = %{$row};
      push @data, \%row;
    }
  }

  $sth->finish;

  return @data;
}

=item @ getDataArrayHeader(sql, plug, substitute, case)

 requires: sql
 optional: plug, substitute, case
 returns: array of arrayrefs

   The first row of the array is an array containing the
   column names in the order returned by the database.
   The column names are case preserved if case = 1, or
   lowercased if case = 0.

   NOTE:
   If 0 rows were returned, we still return an array with
   1 row in it, which is the header row.

 case defaults to 0 (lowercase).

 See read() for argument info.

=cut
sub getDataArrayHeader
{
  my $self = shift;
  my $sql = "";
  my $case = 0;
  my $sth = undef;
  if (scalar @_ == 1)
  {
    $sql = shift;
    $sth = $self->read($sql);
  }
  else
  {
    my %args = ( plug => [], substitute => [], case => 0, @_ );
    my $case = $args{case};
    $sth = $self->read(%args);
  }

  my @data = ();

  if ($self->error)
  {
    $self->prefixError();
    return @data;
  }

  # get the column NAMES
  push @data, $sth->{($case == 1 ? 'NAME' : 'NAME_lc')};

  while (my $row = $sth->fetchrow_arrayref || ($self->{dbType} eq "Sybase" && $sth->{syb_more_results}))
  {
    if (defined $row && ref($row) eq "ARRAY" && scalar @{$row} > 0)
    {
      # don't process the return status of a stored procedure from Sybase.
      next if ($self->{dbType} eq "Sybase" && $sth->{syb_result_type} == 4043);

      my @row = @{$row};
      push @data, \@row;
    }
  }

  $sth->finish;

  return @data;
}

=item @ getDataHashHeader(sql, plug, substitute, case)

 requires: sql
 optional: plug, substitute, case
 returns: array of hashrefs where the column names are
   case preserved if case = 1, or lowercased if case = 0.

   The first row of the array is an array containing the
   column names in the order returned by the database.
   The column names respect the case flag.

   NOTE:
   If 0 rows were returned, we still return an array with
   1 row in it, which is the header row.

 case defaults to 0 (lowercase).

 See read() for argument info.

=cut
sub getDataHashHeader
{
  my $self = shift;
  my $sql = "";
  my $case = 0;
  my $sth = undef;
  if (scalar @_ == 1)
  {
    $self->{dbh}->{FetchHashKeyName} = 'NAME_lc';
    $sql = shift;
    $sth = $self->read($sql);
  }
  else
  {
    my %args = ( plug => [], substitute => [], case => 0, @_ );
    my $case = $args{case};
    $self->{dbh}->{FetchHashKeyName} = ($case == 1 ? 'NAME' : 'NAME_lc');
    $sth = $self->read(%args);
  }

  my @data = ();

  if ($self->error)
  {
    $self->prefixError();
    return @data;
  }

  # get the column NAMES
  push @data, $sth->{($case == 1 ? 'NAME' : 'NAME_lc')};

  while (my $row = $sth->fetchrow_hashref || ($self->{dbType} eq "Sybase" && $sth->{syb_more_results}))
  {
    if (defined $row && ref($row) eq "HASH" && scalar keys %{$row} > 0)
    {
      # don't process the return status of a stored procedure from Sybase.
      next if ($self->{dbType} eq "Sybase" && $sth->{syb_result_type} == 4043);

      my %row = %{$row};
      push @data, \%row;
    }
  }

  $sth->finish;

  return @data;
}

=item scalar readXML(sql, plug, substitute, columns, displayNULLAs,
ignoreTags, sequence, displaySQL)

 requires: sql
 optional: plug, substitute, columns = 0, displayNULLAs, ignoreTags,
           sequence, displaySQL = 1
 returns:  valid XML document describing the data selected from the
  database.  Uses getDataHashHeader() to actually validate the data and
  execute the SELECT statement.  The resulting XML document
  will either have an error condition set (if read() signaled
  an error occured) or will be the result of traversing the
  data returned from getDataHashHeader().

  If displaySQL = 0, then we do not output the <select />
  tag in the xml, thus allowing you to send the xml to a web browser
  without potentially giving out sensitive information.

  Any undefined values (NULL) will be output using the displayNULLAs
  variable which defaults to 'NULL'.

  All values are run through the formEncodeString() method to
  make sure that any html/xml tags are properly encoded.  If you
  do not want certain tags encoded, use the ignoreTags and/or
  sequence arguments to affect how the formEncodeString() method
  fixes up the value.  See the formEncodeString() documentation for
  more details.

  If columns = 0, then all info will be returned in the <row>
  tag as attributes where the column name = column value.
  Ex.  <row name="test" value="testing" other="something else"/>
  When the column names were name, value and other.

  If columns = 1, then all info will be returned in <column>
  tags which are children of the <row> tag.  A column tag has
  attributes name and value.  name = column name and value =
  column value.
  Ex.
  <row>
    <column name="name" value="test"/>
    <column name="value" value="testing"/>
  </row>

  If columns = 2, then each row has tags defined named after the
  column with the contents being the value.  They are output in the
  order that the database returned them in.  The column value is not
  encoded, but is wrapped in <![CDATA[ ]]> tags so that any html/xml
  tags are safely ignored without having to be encoded.
  Ex:
  <row>
    <name><![CDATA[test]]></name>
    <value><![CDATA[testing]]></value>
  </row>

  Where there were 2 columns returned with names of name and value, in
  that order.

  The XML format is as follows:
  <?xml version="1.0" encoding="ISO-8859-1"?>
  <resultset version="1.2">
    <select sql="" plug=""/>
    <status result="" error=""/>
    <rows numRows="" columns="0|1|2">
      <row/>
    </rows>
  </resultset>

  If the XML document is an error document, then:
  <status result="Error" error="Error message"/>
  else
  <status result="Ok" error=""/>

  In <select> tag, sql = The sql SELECT string, plug = the
  string made when joining all the plug array entries
  together and comma seperating them.  The entries are
  single quoted.  Ex. plug="''" would represent no plug
  entries used.  plug="'x', 'y'" would mean that 2 entries
  were passed in: x, y.

  In <rows> numRows will be equal to the number of rows
  being returned or 0 if an error had occured.

  The <row> tag will depend on the value of columns.

=cut
sub readXML
{
  my $self = shift;
  my $sql = "";
  my @plug = ();
  my $plug = "";
  my $columns = 0;
  my $displayNULLAs = 'NULL';
  my $ignoreTags = "";
  my $sequence = "";
  my $displaySQL = 1;
  my $sth = undef;
  my @data = ();
  if (scalar @_ == 1)
  {
    $sql = shift;
    @data = $self->getDataHashHeader($sql);
  }
  else
  {
    my %args = ( plug => [], substitute => [], columns => 0, displayNULLAs => 'NULL', displaySQL => 1, @_ );
    @plug = @{$args{plug}};
    $plug = "'" . join("', '", @plug) . "'";
    $sql = $args{sql};
    $columns = $args{columns};
    $displayNULLAs = $args{displayNULLAs};
    $ignoreTags = $args{ignoreTags};
    $sequence = $args{sequence};
    $displaySQL = $args{displaySQL};
    @data = $self->getDataHashHeader(%args);
  }
  my $xmlDoc = "";

  my $resultSetVersion = "1.2";  # Update whenever the xml format changes.

  # make sure we don't have any invalid XML characters in the sql or plug strings.
  $sql = $self->formEncodeString(string => $sql, ignoreTags => $ignoreTags, sequence => $sequence);
  $plug = $self->formEncodeString(string => $plug, ignoreTags => $ignoreTags, sequence => $sequence);

  if ($self->error)
  {
    $self->prefixError();
    $self->{error} = 0;  # turn off the error message as the XML file will convey it.
    my $errorString = $self->formEncodeString(string => $self->errorString, ignoreTags => $ignoreTags, sequence => $sequence);

    $xmlDoc = <<"END_OF_CODE";
<?xml version="1.0" encoding="ISO-8859-1"?>
<resultset version="$resultSetVersion">
END_OF_CODE
    $xmlDoc .= qq(  <select sql="$sql" plug="$plug"/>\n) if $displaySQL;
    $xmlDoc .= <<"END_OF_CODE";
  <status result="Error" error="$errorString"/>
  <rows numRows="0" columns="$columns"/>
</resultset>
END_OF_CODE
  }
  else
  { # now process the result set returned and generate the XML document.
    $xmlDoc = <<"END_OF_CODE";
<?xml version="1.0" encoding="ISO-8859-1"?>
<resultset version="$resultSetVersion">
END_OF_CODE
    $xmlDoc .= qq(  <select sql="$sql" plug="$plug"/>\n) if $displaySQL;
    $xmlDoc .= <<"END_OF_CODE";
  <status result="Ok" error=""/>
  <rows numRows="#NUMROWS#" columns="$columns">
END_OF_CODE
    my $names = $data[0];  # get the list of column names returned.

    # fixup the names to make sure they don't have any invalid XML characters in them.
    my %encodedNames = ();
    foreach my $name (@{$names})
    {
      $encodedNames{$name} = $self->fixupAttributes(string => $name);
    }

    for(my $rowIndex = 1; $rowIndex < @data; $rowIndex++)
    {
      my $row = $data[$rowIndex];

      if ($columns)
      {
        # start the <row>
        $xmlDoc .= "    <row>\n";

        # now iterate over each entry in the row and create it's column tag.
        if (exists $row->{$names->[0]} && scalar @{$names} == keys %{$row})
        {  # they hopefully are the same row/header info.
          foreach my $name (@{$names})
          {
            my $data = $row->{$name};
            $data = $displayNULLAs if (not defined $data);  # set NULL entries to be NULL
            my $encodedData = $self->formEncodeString(string => $data, ignoreTags => $ignoreTags, sequence => $sequence);
            $encodedNames{$name} = $self->fixupAttributes(string => $name) if (!exists $encodedNames{$name});

            if ($columns == 1)
            {
              $xmlDoc .= "      <column name=\"$encodedNames{$name}\" value=\"$encodedData\"/>\n";
            }
            elsif ($columns == 2)
            {
              $xmlDoc .= "      <$encodedNames{$name}><![CDATA[$data]]></$encodedNames{$name}>\n";
            }
          }
        }
        else
        {
          foreach my $name (keys(%{$row}))
          {
            my $data = $row->{$name};
            $data = $displayNULLAs if (not defined $data);  # set NULL entries to be NULL
            my $encodedData = $self->formEncodeString(string => $data, ignoreTags => $ignoreTags, sequence => $sequence);
            $encodedNames{$name} = $self->fixupAttributes(string => $name) if (!exists $encodedNames{$name});

            if ($columns == 1)
            {
              $xmlDoc .= "      <column name=\"$encodedNames{$name}\" value=\"$encodedData\"/>\n";
            }
            elsif ($columns == 2)
            {
              $xmlDoc .= "      <$encodedNames{$name}><![CDATA[$data]]></$encodedNames{$name}>\n";
            }
          }
        }

        # end the <row>
        $xmlDoc .= "    </row>\n";
      }
      else
      {
        # start the <row>
        $xmlDoc .= "    <row";

        if (exists $row->{$names->[0]} && scalar @{$names} == keys %{$row})
        {  # they hopefully are the same row/header info.
          foreach my $name (@{$names})
          {
            my $data = $row->{$name};
            $data = $displayNULLAs if (not defined $data);  # set NULL entries to be NULL
            $data = $self->formEncodeString(string => $data, ignoreTags => $ignoreTags, sequence => $sequence);
            $encodedNames{$name} = $self->fixupAttributes(string => $name) if (!exists $encodedNames{$name});

            $xmlDoc .= " $encodedNames{$name}=\"$data\"";
          }
        }
        else
        {
          foreach my $name (keys %{$row})
          {
            my $data = $row->{$name};
            $data = $displayNULLAs if (not defined $data);  # set NULL entries to be NULL
            $data = $self->formEncodeString(string => $data, ignoreTags => $ignoreTags, sequence => $sequence);
            $encodedNames{$name} = $self->fixupAttributes(string => $name) if (!exists $encodedNames{$name});

            $xmlDoc .= " $encodedNames{$name}=\"$data\"";
          }
        }

        $xmlDoc .= "/>\n"; # end the <row>
      }
    }
    $xmlDoc .= <<"END_OF_CODE";
  </rows>
</resultset>
END_OF_CODE

    # now update the numRows value.
    my $numRows = (scalar(@data) - 1);
    $xmlDoc =~ s/#NUMROWS#/$numRows/;
  }

  return $xmlDoc;
}

=item scalar fixupAttributes(string)

Attempts to make sure that the given string can be a valid attribute in
an xml document.

Converts (, ), -, \, /, =, >, <, & to _
Deletes ', ", \n

=cut
sub fixupAttributes
{
  my $self = shift;
  my $string = "";
  if (scalar @_ == 1)
  {
    $string = shift;
  }
  else
  {
    my %args = ( string => "", @_ );
    $string = $args{string};
  }

  my @replace = ('&', '<', '>', '-', '\\(', '\\)', '\\\\', '/', '=', ' ', '\\+', '\\*');
  my @delete = ("'", '"', '\n');

  return $string if (length $string == 0);

  foreach my $entity (@replace)
  {
    $string =~ s/$entity/_/g;
  }

  foreach my $entity (@delete)
  {
    $string =~ s/$entity//g;
  }

  return $string;
}

=item scalar readHTML(sql, plug, substitute, tableClass, alternateRows, displayNumRows, displayNULLAs, ignoreTags, sequence, headers, footer)

 requires: sql
 optional: plug, substitute, tableClass, alternateRows,
  displayNumRows, displayNULLAs, ignoreTags, sequence, headers, footer
 returns:  valid HTML <table> describing the data selected from the
  database.  Uses getDataHashHeader() to actually validate the data and
  execute the SELECT statement.  The resulting HTML <table>
  will either have the error displayed (if read() signaled
  an error occured) or will be the result of traversing the
  data returned from getDataHashHeader().

  Any undefined values (NULL) will be output using the displayNULLAs
  variable which defaults to 'NULL'.

  All values are run through the formEncodeString() method to
  make sure that any html/xml tags are properly encoded.  If you
  do not want certain tags encoded, use the ignoreTags and/or
  sequence arguments to affect how the formEncodeString() method
  fixes up the value.  See the formEncodeString() documentation for
  more details.

 If an error occured, then the generated tr and td will have
 class="sqlError" assigned to them so you can change the way the
 sql Error row is displayed.  The error output will also be
 wrapped in a <span class="sqlError"></span> so you can change
 the display behaviour.

 tableClass defines the class to assign to this table so it knows
 how to display itself.  Defaults to "".  This allows you to have
 different readHTML generated tables on the same page and let them
 look different (border, width, cellspacing, cellpadding, etc.).

 alternateRows (boolean) lets the caller indicate they want to
 possibly have different row characteristics on every other row.
 Defaults to 1.

 displayNumRows (boolean) lets the caller indicate they want a <div>
 above the generated table that tells the user how many rows were
 returned.  Defaults to 1.  The generated div has
 class="sqlNumRows" assigned to it so the caller can affect the
 characteristics of the output and the NumRows statement is wrapped
 in a <span class="sqlNumRows"></span>.

 The table header will be made up from the returned columns in the
 sql statement.  Each <th> will have the css class="column_name"
 defined so that the callers style can have th.column_name defined
 to dictate how the <th> is to be displayed.  The <tr> for the table
 header will have class="sqlHeader" assigned to it.  **

 The headings can be specified by passing in a reference to a hash
 called headers.  If you wish to use special characters and/or
 simply change the label for a column that was used in the SQL assign
 it to the hash entry with the column as key.
   $headers{column1} = "Some other text";
   readHTML(headers => \%headers);
 Any columns not specified in the hash will default to the name used
 in the sql query.

 The footer flag is boolean (0|1) and defaults to 0.  If set to 1 the
 heading row will be duplicated as a footer row inside of <tfoot> tags.

 Each <tr> will have class="sqlRow" assigned, unless alternateRows
 is enabled, which then causes the even rows to have
 class="sqlRow sqlEven" and the odd rows to have class="sqlRow sqlOdd"
 assigned. Each <td> will have the css class="column_name" defined so
 the callers style can have td.column_name defined to dictate how the
 <td> is to be displayed. The contents of the <td> entry will be
 wrapped in <span class="column_name"></span> to allow even more
 display control.  **

 ** The column_name is run through the fixupAttributes()
 method to remove any bad values and convert all illegal css
 characters in a name to _.  You should run your column names
 through the fixupAttributes() method to have the same class
 name to work with.

=cut
sub readHTML
{
  my $self = shift;
  my $sql = "";
  my @plug = ();
  my $plug = "";
  my $tableClass = "";
  my $alternateRows = 1;
  my $displayNumRows = 1;
  my $displayNULLAs = 'NULL';
  my $ignoreTags = "";
  my $sequence = "";
  my $sth = undef;
  my @data = ();
  my $headings;
  my $footer = 0;
  if (scalar @_ == 1)
  {
    $sql = shift;
    @data = $self->getDataHashHeader($sql);
  }
  else
  {
    my %args = (
      plug => [],
      substitute => [],
      tableClass => "",
      alternateRows => 1,
      displayNumRows => 1,
      displayNULLAs => 'NULL',
      headers => undef,
      footer => 0,
      @_
    );
    @plug = @{$args{plug}};
    $plug = "'" . join("', '", @plug) . "'";
    $sql = $args{sql};
    $tableClass = $args{tableClass};
    $alternateRows = $args{alternateRows};
    $displayNumRows = $args{displayNumRows};
    $displayNULLAs = $args{displayNULLAs};
    $ignoreTags = $args{ignoreTags};
    $sequence = $args{sequence};
    $headings = $args{headers};
    $footer = $args{footer};
    @data = $self->getDataHashHeader(%args);
  }
  my $html = "";

  # make sure the defaults are sane.  The error handling will drop through and be caught so we output the table, etc.
  if ($alternateRows !~ /^(0|1)$/)
  {
    $self->error("alternateRows = '$alternateRows' is invalid!");
  }
  if ($displayNumRows !~ /^(0|1)$/)
  {
    $self->error("displayNumRows = '$displayNumRows' is invalid!");
  }

  if ($self->error)
  {
    $self->prefixError();
    $self->{error} = 0;  # turn off the error message as the HTML Table will convey it.
    my $errorString = $self->errorMessage;

    if ($displayNumRows)
    {
      $html .= <<"END_OF_CODE";
<div class="sqlNumRows"><span class="sqlNumRows"><b>0</b> rows returned.</span></div>
END_OF_CODE
    }
    $html .= <<"END_OF_CODE";
<table class="$tableClass">
  <tr class="sqlError">
    <td class="sqlError"><span class="sqlError">$errorString</span></td>
  </tr>
</table>
END_OF_CODE
  }
  else
  { # now process the result set returned and generate the HTML Table.
    if ($displayNumRows)
    {
      $html .= <<"END_OF_CODE";
<div class="sqlNumRows"><span class="sqlNumRows"><b>#NUMROWS#</b> rows returned.</span></div>
END_OF_CODE
    }
    $html .= "<table class=\"$tableClass\">\n";

    my $names = $data[0];  # get list of the names of the columns returned. (Lowercased)
    # display the headers
    my $headerstring = "    <tr class=\"sqlHeader\">\n";
    my %encodedNames = ();
    foreach my $name (@{$names})
    {
      $encodedNames{$name} = $self->fixupAttributes(string => $name);
      my $tname = $name;
      if (defined $headings->{$name}) { $tname = $headings->{$name}; }
      $headerstring .= "    <th class=\"$encodedNames{$name}\">$tname</th>\n";
    }
    $headerstring .= "    </tr>\n";

    $html .= "  <thead>\n$headerstring";
    $html .= "  </thead>\n";
    if ($footer)
    {
      $html .= "  <tfoot>\n$headerstring</tfoot>\n";
    }
    $html .= "  <tbody>\n";

    my $counter = 0;
    for (my $rowIndex = 1; $rowIndex < @data; $rowIndex++)
    {
      my $row = $data[$rowIndex];

      my $class = ($alternateRows ? ($counter % 2 == 0 ? "sqlRow sqlEven" : "sqlRow sqlOdd") : "sqlRow");
      $html .= "  <tr class=\"$class\">\n";
      # now iterate over each entry in the row and create it's column tag.
      if (exists $row->{$names->[0]} && scalar @{$names} == keys %{$row})
      {  # they hopefully are the same row/header info.
        foreach my $name (@{$names})
        {
          my $data = $row->{$name};
          $data = $displayNULLAs if (not defined $data);  # set NULL entries to be NULL
          $data = $self->formEncodeString(string => $data, ignoreTags => $ignoreTags, sequence => $sequence);
          $encodedNames{$name} = $self->fixupAttributes(string => $name) if (!exists $encodedNames{$name});

          $html .= "    <td class=\"" . $encodedNames{$name} . "\"><span class=\"" . $encodedNames{$name} . "\">$data</span></td>\n";
        }
      }
      else
      {
        foreach my $name (keys(%{$row}))
        {
          my $data = $row->{$name};
          $data = $displayNULLAs if (not defined $data);  # set NULL entries to be NULL
          $data = $self->formEncodeString(string => $data, ignoreTags => $ignoreTags, sequence => $sequence);
          $encodedNames{$name} = $self->fixupAttributes(string => $name) if (!exists $encodedNames{$name});

          $html .= "    <td class=\"" . $encodedNames{$name} . "\"><span class=\"" . $encodedNames{$name} . "\">$data</span></td>\n";
        }
      }

      # end the <row>
      $html .= "  </tr>\n";
      $counter++;
    }
    $html .= "  </tbody>\n";
    $html .= "</table>\n";

    # now update the numRows value.
    my $numRows = (scalar(@data) - 1);
    $html =~ s/#NUMROWS#/$numRows/;
  }

  return $html;
}

=item scalar readCSV(sql =>, plug =>, quote =>, quoteAll =>, delimeter =>, sep =>, header =>, computeRowHeader =>, case => ) or readCSV('SELECT foo FROM bar')

  This returns the data selected in sql query in comma separated value
  format.  Returns undef if an error occured.

  Optional parameters:
  sep - defaults to ', ', but can be whatever you want to seperate fields
    with.
  header - (boolean) defaults to 0.  If 1 (true), then we output the
    column names as the first line of the output.  If using Sybase and
    COMPUTE rows, then at each detected compute row, we output the
    compute rows headers before the compute rows data and prefix the
    compute row headers with '(!!COMPUTE ROW!!)'.  This is so scripts
    can detect a compute row and handle accordingly.
  computeRowHeader - (boolean) defaults to 1. determines if the string
    '(!!COMPUTE ROW!!)' should be prefixed to compute row headers
    if using Sybase and header => 1.
  case - (boolean) defaults to 0.  If 1 then we preserve the case for
    column names in the header row.  If 0 then we lowercase all column
    names in the header row.
  quote - defaults to single quotes.  will escape any found in data with
    backslash.
  quoteAll - (boolean) defaults to 1.  If 1 (true), then all data is
    quoted using the quote value.  If 0 (false), then only those fields
    detected to be non-numeric are quoted.
  delimeter - defaults to newline (\n).  Will escape any found in data
    with backslash (\\n)

=cut
sub readCSV
{
  my $self = shift;
  my $sep = ', ';
  my $quote = "'";
  my $quoteAll = 1;
  my $header = 0;
  my $computeRowHeader = 1;
  my $case = 0;
  my $delim = "\n";
  my $plug = [];
  my $sql;
  if (scalar @_ > 1)
  {
    my %args = (sql => undef, @_);
    if (defined $args{quote}) { $quote = $args{quote}; }
    if (defined $args{quoteAll}) { $quoteAll = $args{quoteAll}; }
    if (defined $args{delimeter}) { $delim = $args{delimeter}; }
    if (defined $args{plug}) { $plug = $args{plug}; }
    if (defined $args{sep}) { $sep = $args{sep}; }
    if (defined $args{header}) { $header = $args{header}; }
    if (defined $args{computeRowHeader}) { $computeRowHeader = $args{computeRowHeader}; }
    if (defined $args{case}) { $case = $args{case}; }

    $sql = $args{sql};
  }
  else { $sql = shift; }
  if (!defined $sql || length $sql == 0) { $self->error(errorString => "SQL string not passed in!\n"); return undef; }
  if (length $quote == 0) { $self->error(errorString => "Invalid quote passed to readCSV!\n"); return undef; }
  if (length $delim == 0) { $self->error(errorString => "Invalid delimeter passed to readCSV!\n"); return undef; }
  if (length $sep == 0) { $self->error(errorString => "Invalid seperator passed to readCSV!\n"); return undef; }
  if ($header !~ /^(0|1)$/) { $self->error(errorString => "header must be 0 or 1!\n"); return undef; }
  if ($header && $computeRowHeader !~ /^(0|1)$/) { $self->error(errorString => "computeRowHeader must be 0 or 1!\n"); return undef; }
  if ($header && $case !~ /^(0|1)$/) { $self->error("case must be 0 or 1!\n"); return undef; }

  # make sure the user isn't trying to do something that will cause a csv reader to choke.
  if ($sep eq $quote) { $self->error(errorString => "seperator can not equal quote value!"); return undef; }

  if ($sep eq $delim) { $self->error(errorString => "seperator can not equal delimeter value!"); return undef; }

  if ($delim eq $quote) { $self->error(errorString => "delimeter can not equal quote value!"); return undef; }

  my $r = '';  # the string to build up.

  my $sth = $self->read(sql => $sql, plug => $plug);
  if ($self->error) { return undef; }

  if ($header)
  {
    my $headers = $sth->{($case == 1 ? 'NAME' : 'NAME_lc')};
    # I should probably make sure that sep and delim do not exist in the returned column names.
    $r .= join($sep, @{$headers}) . $delim;
  }

  while (my $info = $sth->fetchrow_arrayref || ($self->{dbType} eq "Sybase" && $sth->{syb_more_results}))
  {
    if (defined $info && ref($info) eq "ARRAY" && scalar @{$info} > 0)
    {
      # don't process the return status of a stored procedure from Sybase.
      next if ($self->{dbType} eq "Sybase" && $sth->{syb_result_type} == 4043);

      my $headers = $sth->{($case == 1 ? 'NAME' : 'NAME_lc')};
      if ($self->{dbType} eq "Sybase" && $sth->{syb_result_type} == 4045 && $header)
      {
        # generate the Compute row header
        $r .= "(!!COMPUTE ROW!!)" if ($computeRowHeader);
        # I should probably make sure that sep and delim do not exist in the returned column names.
        $r .= join($sep, @{$headers}) . $delim;
      }
      # use the length of the headers array to determine how many actual columns we are working with,
      # since the sybase compute row apparently returns the same number of columns as the main select,
      # but the headers for the compute row shows the correct number.
      for (my $i = 0; $i < scalar @{$headers}; $i++)
      {
        if ($i > 0) { $r .= $sep; }
        my $t = $info->[$i];
        $t =~ s/$quote/\\${quote}/;
        $t =~ s/$delim/\\${delim}/;
        if ($quoteAll)
        {
          $r .= $quote . $t . $quote;
        }
        else
        {
          # if this doesn't look like a number, trying to cover all bases here, and it's not the empty string, then quote, else don't quote.
          if ($t !~ /^([-\+]?\d+(\.\d+)?([Ee]\d+)?)$/ && $t !~ /^$/)
          {
            $r .= $quote . $t . $quote;
          }
          else
          {
            $r .= $t;
          }
        }
      }
      $r .= $delim;
    }
  }
  return $r;
}

=item @ readSpreadSheet(sql =>, plug =>, case =>, sheetName =>, formats => {}, returnFile =>, workbook => )

=item @ readSpreadSheet('SELECT foo FROM bar')

  This returns the data selected in sql query in a Excel(R) SpreadSheet.
  NOTE:  You must call binmode() on whatever file handle you are
  planning on printing the results to.  This is an IO::Scalar instance.

  The returned array contains the following entries:
    [0] = excel spreadsheet or the Spreadsheet::WriteExcel object
    [1] = # rows of data processed
    [2] = # rows changed by callback handler
    [3] = # compute rows encountered
    [4] = # compute rows changed by callback handler

  Returns undef if an error occured.

  Optional parameters:
  returnFile - (boolean) defaults to 1.  If 1, then we return the
    spreadsheet data ready to be written to a file.
    If 0, then we return the Spreadsheet::WriteExcel object to allow
    the caller to continue modifying things or to pass back into us
    for another sheets worth of data.
  workbook - Spreadsheet::WriteExcel object to work with.
    Defaults to undef.
  case - (boolean) defaults to 0.  If 1 then we preserve the case for
    column names in the header row.  If 0 then we lowercase all column
    names in the header row.
  sheetName - defaults to "Sheet1".  Specify the name of the sheet
    being created.  If you specify workbook, then I check to make sure
    that the given sheetName has not already been created.  If it has,
    then I let the module pick the next valid name, else I use the
    name you specified.
  formats - (hash ref) defaults to {}.  Define the name of the format
    and then the attributes you want it to have, where each format
    is a hashref containing the attributes.
    Ex:  { header => { bold => 1, align => "center" },
           date => { num_format => "yyyy-mm-dd", align => "right" }
         }
    If you define an entry called 'header', it will be used as the
    format when displaying the header row and outputting the
    column headers, otherwise no formatting will be done.
  headers - (hash ref) defaults to {}.  Allows you to override the
    displayed name for each data column.
    Ex:  { start_date => "Start Date" }
  columnWidths - (hash ref) defaults to {}.  Allows you to specify
    which columns need a specific width set.  Only those columns
    that have an index value defined, will have their width set.
    The index value is based on the column name returned by the
    result set using the case flag to determine if it is all
    lowercase or left alone.  This allows you to re-order your
    output and still have the correct column widths defined.
    Ex:  { start_date => 35, num_users => 12, whatever => 15 }
  types - (hash ref) defaults to {}.  Allows you to specify the
    type of each column, indexed by the column name.  If you
    don't specify a type for a column, then it defaults to 'string'.
    See columnWidths for a description of the column name index.
    The possible types are:
      string, date, time, date_time, number, url, 0number

    0number displays this field as a string, thus keeping any leading 0's.

    date appends the "T" required by excel to indicate there is no time part.

    time prepends the "T" required by excel to indicate there is no date part.

    date_time assumes you have inserted the "T" between the date and time parts,
    with no surrounding spaces, otherwise it won't display properly.

    You must also specify a format so that your date and/or time values
    display properly.

    Ex:  { start_date => "date", num_users => "number",
           whatever => "string" }

  Callback handlers:

    rowHandler - anonymous sub that will be called and passed in
      a hashref that contains the following entries:
        data - array ref with each columns value indexed from 0
        format - array ref with each columns format to be applied
        type - array ref with each columns default type
        row - int containing the current excel row # being processed.
      You can delete, modify or insert entries into each of the arrays.
      Make sure you also delete or insert the appropriate entries from
      the format and type arrays so they stay in sync with the data
      array.
      If you make changes, return the hashref, else return undef to
      indicate we should use the original values.

    computeRowHandler - callback handler that handles compute rows.
      See rowHandler for details on the input and output handling.

  CAVEATS
    If you want to run multiple sql queries and generate a seperate
    sheet per query, you must instantiate your own instance of the
    Spreadsheet::WriteExcel module and pass it in, specifying
    returnFile => 0.  You then must close() the workbook before
    trying to work with the result, otherwise you won't get any
    output as desired.

    Example:

    # instantiate my workbook instance.
    my $workbook;
    unless ($workbook = new Spreadsheet::WriteExcel("test.xls"))
    {
      die "Failed to instantiate Spreadsheet::WriteExcel instance!  $!";
    }

    # do a loop that passes in the workbook and turns off returnFile.
    .
    .
    .

    $workbook->close();

=cut
sub readSpreadSheet
{
  my $self = shift;
  my $case = 0;
  my $sheetName = "Sheet1";
  my $formats = {};
  my $headers = {};
  my $columnWidths = {};
  my $types = {};
  my $rowHandler = undef;
  my $computeRowHandler = undef;
  my $plug = [];
  my $sql;
  my $returnFile = 1;
  my $workbook;

  if (scalar @_ > 1)
  {
    my %args = (sql => undef, @_);
    if (defined $args{plug}) { $plug = $args{plug}; }
    if (defined $args{case}) { $case = $args{case}; }
    if (defined $args{sheetName}) { $sheetName = $args{sheetName}; }
    if (defined $args{formats}) { $formats = $args{formats}; }
    if (defined $args{headers}) { $headers = $args{headers}; }
    if (defined $args{columnWidths}) { $columnWidths = $args{columnWidths}; }
    if (defined $args{types}) { $types = $args{types}; }
    if (defined $args{rowHandler}) { $rowHandler = $args{rowHandler}; }
    if (defined $args{computeRowHandler}) { $computeRowHandler = $args{computeRowHandler}; }
    if (defined $args{returnFile}) { $returnFile = $args{returnFile}; }
    if (defined $args{workbook}) { $workbook = $args{workbook}; }

    $sql = $args{sql};
  }
  else { $sql = shift; }
  if (!defined $sql || length $sql == 0) { $self->error(errorString => "SQL string not passed in!\n"); return undef; }
  if ($case !~ /^(0|1)$/) { $self->error("case must be 0 or 1!\n"); return undef; }
  if ($sheetName eq "") { $self->error("sheetName can not be empty!\n"); return undef; }
  if (ref($formats) ne "HASH") { $self->error("formats must be a hash ref, not a '" . ref($formats) . "'!"); return undef; }
  if (ref($headers) ne "HASH") { $self->error("headers must be a hash ref, not a '" . ref($headers) . "'!"); return undef; }
  if (ref($columnWidths) ne "HASH") { $self->error("columnWidths must be a hash ref, not a '" . ref($columnWidths) . "'!"); return undef; }
  if (ref($types) ne "HASH") { $self->error("types must be a hash ref, not a '" . ref($types) . "'!"); return undef; }
  if (defined $rowHandler && ref($rowHandler) ne "CODE") { $self->error("rowHandler must be an anonymous sub, not a '" . ref($rowHandler) . "'!"); return undef; }
  if (defined $computeRowHandler && ref($computeRowHandler) ne "CODE") { $self->error("computeRowHandler must be an anonymous sub, not a '" . ref($computeRowHandler) . "'!"); return undef; }

  my $objSpecified = (defined $workbook ? 1 : 0);

  if ($objSpecified && $returnFile)
  {
    $self->error("You can not specify the workbook object and ask for the file to be returned!");
    return undef;
  }

  my $sth = $self->read(sql => $sql, plug => $plug);
  if ($self->error) { return undef; }

  my $result;
  my $numRows = 0;
  my $numComputeRows = 0;
  my $numRowsChanged = 0;
  my $numComputeRowsChanged = 0;

  #print "workbook specified = $objSpecified, ref = '" . ref($workbook) . "'\n";

  if (!$objSpecified)
  {
    tie(*EXCEL, 'IO::Scalar', \$result);
    unless ($workbook = new Spreadsheet::WriteExcel(\*EXCEL))
    {
      $self->error("Failed to instantiate Spreadsheet::WriteExcel instance!  $!");
      return undef;
    }
  }

  # create the worksheet we are going to populate.
  my $worksheet;

  my $sheetNameDefined = 0;
  if ($objSpecified)
  {
    foreach my $tmpSheet ($workbook->sheets())
    {
      if ($tmpSheet->get_name() eq $sheetName)
      {
        $sheetNameDefined = 1;
        last;
      }
    }
  }

  if ($sheetNameDefined)
  {
    #print "Letting workbook pick sheet name...\n";
    $worksheet = $workbook->add_worksheet();
  }
  else
  {
    $worksheet = $workbook->add_worksheet($sheetName);
  }

  # now define all the user supplied formats.
  my %formats = ();
  foreach my $format ( keys %{$formats} )
  {
    $formats{$format} = $workbook->add_format(%{$formats->{$format}});
  }

  # now display the header row
  my $colHeaders = $sth->{($case == 1 ? 'NAME' : 'NAME_lc')};
  my $row = 0;
  my $col = 0;
  foreach my $header (@{$colHeaders})
  {
    my $format = (exists $formats{header} ? $formats{header} : undef);
    # define the column width, if so desired.
    if (exists $columnWidths->{$header})
    {
      # define the width.
      $worksheet->set_column($col, $col, $columnWidths->{$header});
    }
    # see if we have a header display override.
    my $headerValue = (exists $headers->{$header} ? $headers->{$header} : $header);
    # actually display the header value.
    $worksheet->write($row, $col, $headerValue, $format);
    $col++;
  }

  # now to actually gather the data and display it.
  # allow the user to do any processing of the data as needed, by calling any handlers they
  # specified.  If we call a handler, it will be provided with a hashref containing
  # data, format, type arrayrefs that represent the
  # data being displayed for that row and the formats being applied to each column.
  # By default all types will be "string", though you can define:
  #   string, date, time, date_time, number, url, 0number
  # 0number displays this field as a string, thus keeping any leading 0's.
  # date appends the "T" required by excel to indicate there is no time part.
  # time prepends the "T" required by excel to indicate there is no date part.
  # date_time assumes you have inserted the "T" between the date and time parts,
  # with no surrounding spaces, otherwise it won't display properly.  You must also
  # specify a format so that your date and/or time values display properly.
  #
  # The formats are stored as just the name of the format to be applied, not the actual
  # format object that the Spreadsheet::WriteExcel module created.
  # They can either delete, modify or insert data and formats.
  # They return the hashref to indicate changes have been made or undef to indicate use
  # the original values.

  while (my $info = $sth->fetchrow_arrayref || ($self->{dbType} eq "Sybase" && $sth->{syb_more_results}))
  {
    if (defined $info && ref($info) eq "ARRAY" && scalar @{$info} > 0)
    {
      # don't process the return status of a stored procedure from Sybase.
      next if ($self->{dbType} eq "Sybase" && $sth->{syb_result_type} == 4043);

      $row++;  # increment our row counter.
      $numRows++;

      my $colHeaders = $sth->{($case == 1 ? 'NAME' : 'NAME_lc')};
      my $computeRow = 0;
      if ($self->{dbType} eq "Sybase" && $sth->{syb_result_type} == 4045)
      {
        $computeRow = 1;
        $numComputeRows++;
      }

      # build up the data structure that will be passed into the callback method, if the user specified one.
      my %row = ( data => [], format => [], type => [], row => $row );

      # use the length of the headers array to determine how many actual columns we are working with,
      # since the sybase compute row apparently returns the same number of columns as the main select,
      # but the headers for the compute row shows the correct number.
      for (my $i = 0; $i < scalar @{$colHeaders}; $i++)
      {
        push @{$row{data}}, $info->[$i];
        if (exists $formats->{$colHeaders->[$i]})
        {
          # only store the name of the format, not the actual object.
          $row{format}->[$i] = $colHeaders->[$i];
        }
        else
        {
          $row{format}->[$i] = undef;
        }
        if (exists $types->{$colHeaders->[$i]})
        {
          $row{type}->[$i] = $types->{$colHeaders->[$i]};
        }
        else
        {
          $row{type}->[$i] = "string";
        }
      }

      # now we see if the user gave us any callback methods to run.
      my $callbackResult = undef;
      if ($computeRow)
      {
        if (defined $computeRowHandler)
        {
          $callbackResult = &$computeRowHandler(\%row);
        }
      }
      else
      {
        if (defined $rowHandler)
        {
          $callbackResult = &$rowHandler(\%row);
        }
      }

      if (defined $callbackResult && ref($callbackResult) eq "HASH")
      {
        $row{data} = $callbackResult->{data};
        $row{format} = $callbackResult->{format};
        $row{type} = $callbackResult->{type};
        if ($computeRow)
        {
          $numComputeRowsChanged++;
        }
        else
        {
          $numRowsChanged++;
        }
      }

      # now we walk the row hash and generate output.
      for (my $col=0; $col < scalar @{$row{data}}; $col++)
      {
        my $type = $row{type}->[$col];
        my $value = $row{data}->[$col];
        my $format = (defined $row{format}->[$col] && exists $formats{$row{format}->[$col]} ? $formats{$row{format}->[$col]} : undef);

        if ($type eq "string")
        {
          $worksheet->write_string($row, $col, $value, $format);
        }
        elsif ($type eq "number")
        {
          $worksheet->write_number($row, $col, $value, $format);
        }
        elsif ($type eq "0number")
        {
          $worksheet->write_string($row, $col, $value, $format);
        }
        elsif ($type eq "date")
        {
          $worksheet->write_date_time($row, $col, $value . "T", $format);
        }
        elsif ($type eq "time")
        {
          $worksheet->write_date_time($row, $col, "T" . $value, $format);
        }
        elsif ($type eq "date_time")
        {
          # They have to already have fixed this up to include the T seperator.
          $worksheet->write_date_time($row, $col, $value, $format);
        }
        elsif ($type eq "url")
        {
          $worksheet->write_url($row, $col, $value, $format);
        }
        else  # unknown type, but let write() figure it out.
        {
          $worksheet->write($row, $col, $value, $format);
        }
      }

      # skip a row if we just output a compute row.
      $row++ if ($computeRow);
    }
  }

  # make the header row always visible.
  $worksheet->freeze_panes(1, 0);

  # wrap things up.
  $workbook->close if ($returnFile == 1);

  # They need to binmode() the filehandle before this is displayed.
  return (($returnFile ? $result : $workbook), $numRows, $numRowsChanged, $numComputeRows, $numComputeRowsChanged);
}

=item int write(sql => "", plug => [], substitute => [])

 (This function should be called for any sql statement other than
 SELECT).
 executes the specified sql statement passing in any values in plug
 to the execute method after doing any substitutions that are in
 substitute.

 Returns the number of rows affected.

 If the sql to execute is an INSERT statement, then the oid or
 insertid (Postgresql or MySQL) values will be stored in the
 oid value in this object, for later access by getID().

=cut
sub write
{
  my $self = shift;
  my $sql = "";
  my @plug = ();
  my @substitute = ();
  if (scalar @_ == 1)
  {
    $sql = shift;
  }
  else
  {
    my %args = ( plug => [], substitute => [], @_ );
    @plug = @{$args{'plug'}};
    @substitute = @{$args{'substitute'}};
    $sql = $args{'sql'};
  }

  # validate we got a sql statement to work with.
  if (length $sql == 0)
  {
    $self->error(errorString => "SQL string not passed in!" . "<br />\n" . $self->debugMessage);

    return 0;
  }

  # check and see if we need to do any substitutions.
  if (scalar @substitute > 0)
  {
    for (my $i=0; $i < scalar @substitute; $i++)
    {
      my $temp_string = "\\#\\#\\?" . ($i+1) . "\\#\\#";
      $sql =~ s/$temp_string/$substitute[$i]/g;
    }
  }

  my $timerStart = $self->getCurrTime();
  $self->logSQLStatement("write", $timerStart, $sql, \@plug) if ($self->{logSQLStatements});

  # now prepare the statement
  my $sth;
  eval {
    $sth = $self->{dbh}->prepare($sql);
  };
  if ($@)
  {
    $self->error(errorString => "Eval of prepare failed!<br />\nError = '$@'.<br />\nsql='$sql'.<br />\nplug='" . join("', '", @plug) . "'.<br />\n" . $self->debugMessage);

    return 0;
  }
  elsif (!$sth || $DBI::err)
  {
    $self->error(errorString => "Preparing failed!<br />\n" . $DBI::errstr . "<br />\nsql='$sql'.<br />\nplug='" . join("', '", @plug) . "'.<br />\n" . $self->debugMessage);

    return 0;
  }

  # now execute the sql statement passing in any parameters given via plug
  my $rv;
  my $done = 0;
  my $numTries = 0;
  while (!$done)
  {
    $deadlockEncountered = 0;  # make sure we turn it off.
    eval {
      $rv = $sth->execute(@plug);
    };
    if (!$deadlockEncountered)
    {
      $done = 1;
      if ($@)
      {
        $self->error(errorString => "Eval of execute failed!<br />\nError = '$@'.<br />\nsql='$sql'.<br />\nplug='" . join("', '", @plug) . "'.<br />\n" . $self->debugMessage);

        $self->checkLongRunningSQL("write", $timerStart, $numTries, $sql, \@plug);
        return 0;
      }
      elsif (!$rv || $DBI::err)
      {
        $self->error(errorString => "Execute failed!<br />\n" . $DBI::errstr . "<br />\nsql='$sql'.<br />\nplug='" . join("', '", @plug) . "'.<br />\n" . $self->debugMessage);

        $self->checkLongRunningSQL("write", $timerStart, $numTries, $sql, \@plug);
        return 0;
      }
    }
    else
    {
      $numTries++;
      if ($numTries >= $self->{deadlockNumTries})
      {
        $self->checkLongRunningSQL("write", $timerStart, $numTries, $sql, \@plug);
        return undef;
      }
      my $sleep = $self->{deadlockSleep};
      if ($numTries > 1 && $self->{deadlockRampSleep})
      {
        $sleep *= $numTries;
      }
      print "ALERT:  deadlock encountered!  Retrying sql.  Attempt #$numTries.  Sleeping for $sleep seconds.\n";
      sleep($sleep);
    }
  }

  # store the oid in the object, if this was an INSERT statement.
  if ($sql =~ /^INSERT/i)
  {
    if ($self->{dbType} eq "Pg")
    {
      if ($self->{serverVerMajor} < 8 || ($self->{serverVerMajor} == 8 && $self->{serverVerMinor} < 1))
      {
        $self->{oid} = $sth->{pg_oid_status};
      }
      else
      {
        # have to figure out the table name to specify to the last_insert_id function.
        if ($sql =~ /^(INSERT INTO\s+)([^\(\s]+)\s*(\(|SELECT )(.+)$/i)
        {
          my $tableName = $2;
          #print "You just inserted into '$tableName'...\n";

          local $self->{dbh}->{PrintError};  # don't print an error when last_insert_id can't find the sequence on a table.

          $self->{oid} = $self->{dbh}->last_insert_id(undef, undef, $tableName, undef);
        }
      }
    }
    elsif ($self->{dbType} eq "mysql")
    {
      $self->{oid} = $sth->{mysql_insertid};
    }
    elsif ($self->{dbType} eq "Sybase")
    {
      # kill the $sth so this hopefully works properly.
      #$sth = undef;
      $self->{oid} = 0; #$self->{dbh}->last_insert_id();
    }
  }
  else
  {
    $self->{oid} = 0;
  }

  $self->checkLongRunningSQL("write", $timerStart, $numTries, $sql, \@plug);
  return $rv;
}

=item scalar getID("table.column")

This method will attempt to return the ID value of the just
INSERTed statement as implemented by MySQL, Sybase and PostgreSQL.
This is assuming that you just used the write() method and
that is was able to update the oid value.

This method requires a string value specifying the table.column that
is the ID field for the INSERT statement that just executed, if you
are using a PostgreSQL backend.

If using PostgreSQL <= 8.0, then the old oid lookup code will be run,
otherwise the ID has already been looked-up in the write() method and
will be returned.

If using MySQL, you do not need to specify the table.column value,
but your table must have an AUTO_INCREMENT field defined.

If using Sybase, you do not need to specify the table.column value,
but you may not get a valid ID back if you used ? substitution or
the INSERT was in a stored procedure.  See the DBD::Sybase man-page
for more information.

UPDATE:  At this point, I can't reliably get this to work with
Sybase, so it will always return a value of 0 until I can get this
figured out.  Sorry if this causes an issue.

If the database type is unsupported or an error happened, a value
of 0 will be returned.

=cut
sub getID
{
  my $self = shift;
  my $arg = shift;
  my $id = 0;

  if ($self->{dbType} eq "Pg")
  {
    my $oid = $self->{oid};

    if ($self->{serverVerMajor} < 8 || ($self->{serverVerMajor} == 8 && $self->{serverVerMinor} < 1))
    {
      # now I have to split the table.column apart and then do the lookup.
      if ($arg !~ /^(\w+\.\w+)$/)
      {
        $self->error("table.column = '$arg' is invalid!");
        return 0;
      }
      my ($table, $column) = split /\./, $arg;
      my $sth = $self->read(sql => "SELECT $column FROM $table WHERE oid = ?", plug => [ $oid ]);
      if ($self->error)
      {
        $self->prefixError();
        return 0;
      }
      my @result = $sth->fetchrow_array();
      if (defined $result[0])
      {
        $id = $result[0];
      }
    }
    else
    {
      $id = $oid;
    }
  }
  elsif ($self->{dbType} =~ /^mysql|Sybase$/)
  {
    $id = $self->{oid};
  }

  return $id;
}

# This routine checks to see if the mysql database (currently connected) supports transactions
# and sets the transaction type variable.
sub mysqlHasTransactions
{
  my $self = shift;
  return 0 if (!defined $self->{dbh});

  return 1 if ($self->{dbType} ne "mysql");  # make sure we are working with a MySQL database.
  my $sth = $self->{dbh}->prepare("SHOW VARIABLES");
  my $rv;
  eval { $rv = $sth->execute(); };
  if ($@ || $DBI::err)
  {
    $self->error(errorString => "Execute failed!<br />\n" . $DBI::errstr . "<br />\n" . $self->debugMessage);
    return 0;
  }
  while (my $row = $sth->fetchrow_hashref())
  {
    if ($row->{Variable_name} eq 'have_bdb' && $row->{Value} eq 'YES')
    {
      $self->{transactionType} = "bdb";
      last;
    }
    if ($row->{Variable_name} eq 'have_innobase' && $row->{Value} eq 'YES')
    {
      $self->{transactionType} = "innobase";
      last;
    }
    if ($row->{Variable_name} eq 'have_gemini' && $row->{Value} eq 'YES')
    {
      $self->{transactionType} = "gemini";
      last;
    }
  }
  return $self->{transactionType};
}

=item string debugMessage()

 Returns the string that contains all the info that is to be logged
 at the current logLevel level.  If logLevel is not 0, 1, 2 or 3
 then a default of 3 is used.

=cut
sub debugMessage
{
  my $self = shift;
  my $logLevel = $self->{logLevel};
  my $result = "";
  $logLevel = 3 if ($logLevel !~ /^(0|1|2|3)$/);

  return $result if ($logLevel == 0);  # jump out now since they don't want any debug info displayed.

  # output the stuff that will always be done (level = 1)
  $result .= "dbType = '$self->{dbType}', dbHost = '$self->{dbHost}', dbName = '$self->{dbName}', printError = '$self->{printError}', raiseError = '$self->{raiseError}'";
  $result .= ", autoCommit = '$self->{autoCommit}', setDateStyle = '$self->{setDateStyle}', supportsTransactions = '$self->{supportsTransactions}', transactionType = '$self->{transactionType}'";
  $result .= ", server = '$self->{server}', interfaces = '$self->{interfaces}'";

  # output level 2 stuff
  if ($logLevel >= 2)
  {
    $result .= ", dbUser = '$self->{dbUser}', dbPort = '$self->{dbPort}', predefinedDSN = '$self->{predefinedDSN}'";
  }

  # output level 3 stuff
  if ($logLevel == 3)
  {
    $result .= ", dbPasswd = '$self->{dbPasswd}'";
  }

  $result .= "<br />\n";  # tack on the newline ending

  return $result;
}

=item int getLogLevel()

 returns the current logLevel value.

=cut
sub getLogLevel
{
  my $self = shift;

  return $self->{logLevel};
}

=item int setLogLevel(logLevel => 1)

 sets the logLevel value.  If the value is not specified then it
 defaults to logLevel 1.

 Returns 1 on Success, 0 on Error.

 We validate that the logLevel is 1, 2 or 3.

=cut
sub setLogLevel
{
  my $self = shift;
  my $logLevel = 1;
  if (scalar @_ == 1)
  {
    $logLevel = shift;
  }
  else
  {
    my %args = ( logLevel => 1, @_ );
    $logLevel = $args{logLevel};
  }

  if ($logLevel !~ /^(0|1|2|3)$/)
  {
    $self->error(errorString => "logLevel = '$logLevel' is invalid!<br />\n" . $self->debugMessage);
    return 0;
  }

  $self->{logLevel} = $logLevel;
  return 1;
}

=item string boolToDBI(string)

 Takes the string and returns a 1 for 1|t|true,
 returns a 0 for anything else.

 This method basically will output a true or false
 value that any database should recognize based upon
 the input string.

=cut
sub boolToDBI
{
  my $self = shift;
  my $string = "";
  if (scalar @_ == 1)
  {
    $string = shift;
  }
  else
  {
    my %args = ( string => "", @_ );
    $string = $args{string};
  }

  if ($string =~ /^(1|t|true)$/i)
  {
    return 1;
  }
  # must be false!
  return 0;
}

=item string dbiToBool(string)

 Takes the 1 or 0 from the DBI and returns
 true or false.

=cut
sub dbiToBool
{
  my $self = shift;
  my $string = "";
  if (scalar @_ == 1)
  {
    $string = shift;
  }
  else
  {
    my %args = ( string => "", @_ );
    $string = $args{string};
  }

  if ($string =~ /^(1)$/i)
  {
    return "true";
  }
  # must be false!
  return "false";
}

=item scalar formEncodeString(string, ignoreTags, sequence)

=item scalar formEncodeString(scalar)

 In scalar mode, takes the incoming string and encodes it to
 escape all <, > values as &lt;, &gt; unless they are \ escaped.

 To have the \ showup, you will have to do a \\ when defining this
 in perl, otherwise perl interprets the \whatever internally.

 In non-scalar mode, you specify the arguments by name.

 optional:
   string - string to encode all &, <, > characters to their html
     equivalents of &amp;, &lt;, &gt;.
   ignoreTags - string of pipe (|) seperated tag names that should not
     be encoded.  Ex:  ignoreTags => "b|i|u|span" would ignore all
     <b>, </b>, <i>, </i>, <u>, </u>, <span>, </span> tags that were
     not \ escaped.
   sequence - a named set of ignoreTags values that you want used.
     If both sequence and ignoreTags are specified, the ignoreTags
     value is used.  If you want to apply multiple sequences, specify
     them in a comma delimited format.
     Ex: sequence => 'formatting,seperator'

     available sequences are:
       formatting - "b|i|u|span|sub|sup|big|code|font|h1|h2|h3|h4|h5|h6|pre|small|strike|strong"
       block - "p|div|form"
       tables - "table|tr|td|th|tbody|tfoot|thead"
       seperator - "br|hr"
       formItems - "input|textarea|select|option"
       grouping - "ol|ul|li"

 returns: form encoded string ignoring those entries defined in
   ignoreTags or sequence and where the &, <, > was not \ escaped.

   Any &, <, > that were \ escaped will have the \ removed on output.

=cut
sub formEncodeString
{
  my $string;
  my $ignoreTags;
  my $sequence;
  my %sequences = (
       "formatting" => "b|i|u|span|sub|sup|big|code|font|h1|h2|h3|h4|h5|h6|pre|small|strike|strong",
       "block" => "p|div|form",
       "tables" => "table|tr|td|th|tbody|tfoot|thead",
       "seperator" => "br|hr",
       "formItems" => "input|textarea|select|option",
       "grouping" => "ol|ul|li",
       );

  if (scalar @_ == 1)  # handle being called as function
  {
    $string = shift;
  }
  else # handle being called as method
  {
    my $self = shift;
    if (scalar @_ == 1)
    {
      $string = shift;
    }
    else
    {
      my %args = ( string => "", ignoreTags => "", sequence => "", @_ );
      $string = $args{string};
      $ignoreTags = (length $args{ignoreTags} > 0 ? $args{ignoreTags} : undef);
      $sequence = (length $args{sequence} > 0 ? $args{sequence} : undef);

      if (!defined $ignoreTags && defined $sequence)
      {
        my @sequences = split /,/, $sequence;
        foreach my $sequence (@sequences)
        {
          $ignoreTags .= "|" if (length $ignoreTags);  # make sure multiple sequences are | seperated for the regular expression.
          $ignoreTags .= $sequences{$sequence};
          if (!exists $sequences{$sequence})
          {
            $self->setError(code => "1017");
            $self->displayError(message => "sequence = '$sequence' does not exist!");
          }
        }
      }
    }
  }

  if (length $string > 0)
  {
    # handle the special cases first.
    foreach my $char (qw/&/)
    {
      $string =~ s/(?<!\\)[$char]/$formUnEncodedCharactersHash{$char}/emg;
      # now remove the \ from any chars that were escaped.
      $string =~ s/(\\([$char]))/$2/mg;
    }
    # now handle the rest.
    if (defined $ignoreTags)
    {
      # handle the < case where we encode < if it is not \ escaped and also not one of the ignoreTags values.
      $string =~ s/(?<!\\)(<)(?!(\/)?($ignoreTags)(\s+[^>]+)?(\/)?>)/$formUnEncodedCharactersHash{$1}/emg;

      # handle the > case where we encode > if it is not \ escaped and does not have one of the ignoreTags before it.
      # escape the > tag when it is part of an ignoreTag entry.
      $string =~ s/(<(\/)?($ignoreTags)(\s+[^>]+)?(\/)?)(>)/$1\\$6/mg;
      # convert all non-escaped >'s.
      $string =~ s/(?<!\\)(>)/$formUnEncodedCharactersHash{$1}/emg;

      # now remove the \ from any chars that were escaped.
      $string =~ s/(\\([$formUnEncodedCharacters]))/$2/mg;
    }
    else
    {
      $string =~ s/(?<!\\)([$formUnEncodedCharacters])/$formUnEncodedCharactersHash{$1}/emg;
      # now remove the \ from any chars that were escaped.
      $string =~ s/(\\([$formUnEncodedCharacters]))/$2/mg;
    }
    # now fixup \n to \\n, as long as it is not already \ escaped.
    $string =~ s/(?<!\\)(\n)/\\n/mg;
  }

  return $string;
}

sub AUTOLOAD  # Read only access to data objects.
{
  my $self = shift;
  my $type = ref($self) || DBIWrapper::myDie("$self is not an object.\n");
  my $name = $AUTOLOAD;
  $name =~ s/.*://; # strip fully-qualified portion
  unless (exists $self->{$name})
  {
    die "Can't access `$name' field in object of class $type";
  }
  return $self->{$name};
}

sub myDie
{
  my $message = shift;
  my @callerArgs = caller(2);
  (my $subName = $callerArgs[3]) =~ s/^(.+)(::)([^:]+)$/$1->$3/;
  die "$subName: $message";
}

sub DESTROY
{
  my $self = shift;

  if (ref $self->{dbh} eq "DBI::db")
  {
    $self->close;
    if ($self->error)
    {
      die "DBIWrapper->DESTROY: " . $self->errorMessage;
    }
  }
}

=item bool sybaseErrorHandler()

returns 0 if the "error" is an informational error from sybase that we
can safely ignore.  Currently ignores the Text conversion errors that
are causing the connect to fail.

returns 1 for all other errors to cause DBI to process them.

=cut
sub sybaseErrorHandler
{
  my ($err, $sev, $state, $line, $server, $proc, $msg, $sql, $errType) = @_;

  # ignore the "errors" that are informational.
  if (($err == 2401 && $sev == 11 && $state == 2)
   || ($err == 2411 && $sev == 10 && $state == 1)
   || ($err == 2409 && $sev == 11 && $state == 2))
  {
    return 0;
  }
  if ($err == 1205)
  {
    if ($deadlockEncountered)
    {
      print "ERROR:  deadlockEncountered is already set and we just encountered another deadlock!\n";
      return 1;
    }
    $deadlockEncountered = 1;
    return 0;
  }
  elsif ($deadlockEncountered)
  {
    print "WARNING:  Temporarily ignoring err=$err, severity=$sev, state=$state, line=$line, server=$server, proc=$proc, msg=$msg, sql='$sql' due to deadlock being encountered.\n";
    return 0;
  }
  #print "err=$err, sev=$sev, state=$state, line=$line, server=$server, proc=$proc, msg=$msg, sql='$sql', errType=$errType\n";
  return 1;
}

=back

=cut

1;
__END__

=head1 NOTE:

 All data fields are accessible by specifying the object and
 variable as follows:
 Ex.  $value = $obj->variable;

 Any methods where it is possible to specify just a single
 argument and still have it be valid, you can now specify
 the argument without having to name it first.

 Ex:  calling read() without using the substitute or plug
 options can be done as $dbi->read("SELECT * from test");

 Methods updated to support this:
 setError, read, readXML, write, setLogLevel

=head1 AUTHOR

James A. Pattie, james at pcxperience dot com

=head1 SEE ALSO

perl(1), DBI(3), DBIWrapper::XMLParser(3), DBIWrapper::ResultSet(3)

=cut