#!/usr/bin/perl -w

use TGI;
use DBI;
use strict;
use vars qw($err $dbh);
use DBD::Pg;
use sigtrap qw(die normal-signals); # grep for atexit in perlfaq8(1) for why
if (defined $ENV{'SERVER_LIBEXEC'}) {
  use lib $ENV{'SERVER_LIBEXEC'};
}

END {
  if(defined $dbh) { 
    $dbh->disconnect();
  }
}

sub error {
  my $error = shift;

  TGI::set(return => '-1');
  if(defined $error) {
    TGI::set(tgierror => $error);
  }
  else {
    TGI::set(tgierror => '0');
  }
  exit 0;
}

# This is how to get variables from the caller.
# my $foo = $TGI::QUERY{'foo'};

my $dbh = DBI->connect('dbi:Pg:dbname=cci', "postgres", "");
if(! $dbh) {
    error "Cannot connect: $DBI::errstr";
}

# This could arguably be prepare() because this script is only
# supposed to be called once.  However, I'll wager that I'll find this
# as boilerplate in dozens of other TGIs in the near future which may
# need to be reentrant, so...
my $sth = $dbh->prepare_cached('SELECT * FROM transactions');
if(! defined $sth) {
    error "Cannot prepare SQL statement: " . $dbh->errstr;
}
$err = $sth->execute();
if(! defined $err) { # returns undef if error
    error "Cannot execute statement: ". $dbh->errstr;
}

my $dataref = $sth->fetchall_arrayref();

# Do stuff here...

$dbh->disconnect;

# This how to return stuff.  You must return a value or bayonne will segfault.
# Note that set is a function and QUERY is a hash.
# TGI::set(return => '0');
# TGI::set(return, "$var");

exit 0;