SQL logging

[Julian Field]

One question on your code:

     # Insert @fields into a database table
     my($sth) = $dbh->prepare("INSERT INTO maillog VALUES
(?,?,?,?,?,?,?,?,?,?,?,
?,?,?)");
     $sth->execute($fields[0],$fields[1],$fields[2],$fields[3],$fields[4],$fields
[5],$fields[6],$fields[7],$fields[8],$fields[9],$fields[10],$fields[11],$fields[
12],$fields[13]) or MailScanner::Log::DieLog("Cannot insert row: %s",
$DBI::errs
tr);

Surely you can just do the dbh->prepare once before the loop and then run
sth->execute inside the loop. Otherwise you are doing loads of "prepare"
statements you don't need.

Can you test this for me please before I commit the code into the main
codebase?

At 17:58 26/02/2003, you wrote:
>Julian,
>
>Thanks again for all your work on this.
>
>As promised - I've attached a tar.gz with all the patches that you wrote
>with my modifications and instructions on how to install SQL logging under
>RedHat 7.3.
>
>I've been using this live on by eval box for a few days now with no problems
>- there is still plenty that could be improved on, but at least it's a
>start.
>
>Kind regards,
>Steve.
>
>-----Original Message-----
>From: Julian Field [mailto:mailscanner at ECS.SOTON.AC.UK]
>Sent: 23 February 2003 14:33
>To: MAILSCANNER at jiscmail.ac.uk
>Subject: Re: SQL logging
>
>
>Folks,
>
>I have written you some code which will do this:
>
>1. Log information about every message to a temporary file while
>MailScanner is running, giving you minimum performance hit. I've written
>all the code you need to do this bit.
>2. When MailScanner worker processes die of old age, or are shutdown
>cleanly (don't use kill -9 on them!), they will read their log file and
>pump the contents into an SQL database all at 1 go, which should be nice
>and quick. There are bits missing from this, see the comments in the code.
>
>I don't log everything about each message, but I log a fair chunk of info
>about each one. I assume you will customise this to log what you reckon you
>need.
>It creates 1 record per message (whether clean or infected) and then also
>generates an extra record for each infection report. So a clean message
>will produce 1 record, a message with 2 infections will produce 3 records.
>The SQL "INSERT" code is deliberately missing. You can write that better
>than me anyway.
>
>To use it, apply the other patches I supplied the other day to give you a
>new configuration option "Always Looked Up Last".
>Then insert the attached file on the end of CustomConfig.pm and write the
>SQL code you need, and create the database and the db table this lot is
>going to be written to.
>Then set
>         Always Looked Up Last = &SQLLogging
>in your MailScanner.conf and "restart" (not just "reload") MailScanner.
>
>To find obvious syntax errors, I would advise you do
>         perl -c CustomConfig.pm
>before trying to use it, as that will catch syntax screw-ups.
>
>I make no guarantees whatsoever about the attached code actually working. I
>have written it to get you started, and to show you how to pull interesting
>things out of the "Message" data structure. If you want the full list of
>Message attributes, read the top of Message.pm.
>
>Please let me know how you get on. If someone can post me some working code
>with all the SQL bits written and tested, I will happily include it in
>future releases (next release due on 1st March).
>
>
>
>**********************************************************************
>This email and any files transmitted with it are confidential and
>intended solely for the use of the individual or entity to whom they
>are addressed. If you have received this email in error please notify
>the system manager.
>
>This footnote also confirms that this email message has been swept by
>MIMEsweeper for the presence of computer viruses.
>
>www.lbsltd.co.uk
>**********************************************************************
>

--
Julian Field
www.MailScanner.info
MailScanner thanks transtec Computers for their support