Forum OpenACS Q&A: Response to email handler & postgres DBD

I did modify the script so that it works for me -- it wasn't difficult. I have the Perl DBI & DBD Postgres modules installed. I haven't really looked at it since I hacked it so there may be some cruft left. I hope this is helpful for you.

#!/usr/bin/perl -I /usr/lib/perl5/site_perl/5.005/i386-linux/DBD
#
# Respond to incoming mail message on STDIN
#
# hqm@ai.mit.edu
#
# modified for posgres by
#   Steven Caranci 
#   caranci@eecg.toronto.edu
#
# This script does the following:
#
sub usage () {
    print '
  usage: queue_message.pl db_datasrc db_user db_passwd destaddr

  Inserts the data from stdin into a queue table.

  Assumes the following table and sequence are defined in the db:

    create table incoming_email_queue (
	    id 		int4 not null,
	    destaddr	varchar(256),
	    content		text,		-- the entire raw message content
					    -- including all headers
	    arrival_time	datetime
    );

    create sequence incoming_email_queue_sequence;

';
}

use strict;
use DBI;

################################################################
# Global Definitions

# max string size we allow in db. Truncate any message content beyond this
#$MAX_MSGSIZE = 400000;
# is limit of text type 1 block? 
# assume so for now. also assume 8k block.
my $MAX_MSGSIZE = 8000;

my $db_datasrc        = shift;
my $db_user           = shift;
my $db_passwd         = shift;
my $destaddr          = shift;
my $datasource_full   = "dbi:Pg:dbname=$db_datasrc";
my ($conn, $content, $sql, $ntuples, $result);

# we don't support hosts/ports other than the default (localhost/5432).
# we probably should.

my $DEBUG = 1;
my $debug_logfile = "/tmp/mailhandler-log.txt";

if (!defined $db_datasrc) {
    $db_datasrc = 'dbi:Pg';
}

if (!defined $db_user) {
    usage();
    die("You must pass a db user in the command line");
}

if (!defined $db_passwd) {
    usage();
    die("You must pass a db passwd in the command line");
}

#################################################################
## Snarf down incoming msg on STDIN
#################################################################

while (<>) {
    $content .= $_; 
}

# double the single quotes
#$content =~ s/'/''/g;
$content = qq( $content );

# limit content length
$content = substr($content,0,$MAX_MSGSIZE);


if ($DEBUG) {
    open (LOG, ">>$debug_logfile");
    debug("================================================================
");
    debug("Received content:
$content
");
}


# Open the database connection.
$conn = DBI->connect( $datasource_full, 
                      $db_user, 
                      $db_passwd,
                      { AutoCommit => 1 }
                    ) || die "errorMessage from Pg::connect: $DBI::errstr";

# Insert mail into db
debug("Status: inserting into email queue
");
$sql = "
INSERT INTO incoming_email_queue 
  (id, destaddr,  content, arrival_time) 
VALUES 
  (nextval('incoming_email_queue_sequence'), '$destaddr', '$content', 'now')";

my $sth = $conn->prepare( $sql );
$sth->execute();

my $rowcount = $sth->rows;
if ( $rowcount != 1 ) {
    debug( "
Error! inserted $rowcount rows instead of 1)
" );
}

debug("[closing log]
");
if ($DEBUG) { close LOG; }

sub debug () {
    my ($msg) = @_;
    print LOG $msg;
}

$conn->disconnect();