[Pljava-dev] Trigger problem

Hi,

I am running PostgreSQL 8.1 on Windows and installed pl/java automatically
using the Windows installer. I have created a simple table and executed the
following:

Create function emailme()
Returns trigger
As 'dbudf.SendMail.send'
Language java;

Create trigger trg_emailme
After insert on aTable
For each row
Execute procedure emailme();

I have written the following class:

package dbudf;

import javax.mail.*;
import javax.mail.internet.*;
import java.util.*;

public class SendMail
{
public static void send(org.postgresql.pljava.TriggerData td)
{try{
Properties props = System.getProperties();
props.put("mail.smtp.host", "smtp.tiscali.co.uk");
Session session = Session.getDefaultInstance(props, null);
Message msg = new MimeMessage(session);
msg.setFrom(new InternetAddress("macolman at tiscali.co.uk"));
msg.setRecipients(Message.RecipientType.TO,
InternetAddress.parse("m.a.colman at durham.ac.uk", false));
msg.setSubject("A change to your data");
msg.setText("Your database has been changed.\n\nPlease use
the PGAdmin tool to browse the table involved.\n\nMark Colman");
msg.setHeader("X-Mailer", "LOTONtechEmail");
msg.setSentDate(new Date());
Transport.send(msg);
}
catch (Exception e)
{
System.err.println("Error: "+e.toString());
}
}
}
Which compiles fine, put it in a directory dbudf, and added the directory to
a jar called SendMail.jar - I can then call dbudf.SendMail.send(null) from
within a simple test harness and it sends the e-mail message expected.

I have then executed

Select
sqlj.install_jar('file:///c:/j2sdk1.4.2_09/lib/SendMail.jar','SendMail',true
);
Select sqlj.set_classpath('public','SendMail');

Which seems fine. Then I insert a row into the table, and get

ERROR: Unable to find static method dbudf.SendMail.send with signature
(Lorg/postgresql/pljava/TriggerData;)V

Any help greatly appreciated - I am sure this is something glaringly obvious
that I'm missing.

Mark

Hi Mark,
My guess would be that you have a jar containing the javax.mail stuff on
the classpath when you compile and run your test harness but you never
load this jar into your database. The JVM is then unable to instantiate
your SendMail class since it's depedent on Message, MimeMessage, etc.

Kind Regards,
Thomas Hallgren

Mark Colman wrote:
> Hi,
>
> I am running PostgreSQL 8.1 on Windows and installed pl/java automatically
> using the Windows installer. I have created a simple table and executed the
> following:
>
> Create function emailme()
> Returns trigger
> As 'dbudf.SendMail.send'
> Language java;
>
> Create trigger trg_emailme
> After insert on aTable
> For each row
> Execute procedure emailme();
>
> I have written the following class:
>
> package dbudf;
>
> import javax.mail.*;
> import javax.mail.internet.*;
> import java.util.*;
>
> public class SendMail
> {
> public static void send(org.postgresql.pljava.TriggerData td)
> {try{
> Properties props = System.getProperties();
> props.put("mail.smtp.host", "smtp.tiscali.co.uk");
> Session session = Session.getDefaultInstance(props, null);
> Message msg = new MimeMessage(session);
> msg.setFrom(new InternetAddress("macolman at tiscali.co.uk"));
> msg.setRecipients(Message.RecipientType.TO,
> InternetAddress.parse("m.a.colman at durham.ac.uk", false));
> msg.setSubject("A change to your data");
> msg.setText("Your database has been changed.\n\nPlease use
> the PGAdmin tool to browse the table involved.\n\nMark Colman");
> msg.setHeader("X-Mailer", "LOTONtechEmail");
> msg.setSentDate(new Date());
> Transport.send(msg);
> }
> catch (Exception e)
> {
> System.err.println("Error: "+e.toString());
> }
> }
> }
> Which compiles fine, put it in a directory dbudf, and added the directory to
> a jar called SendMail.jar - I can then call dbudf.SendMail.send(null) from
> within a simple test harness and it sends the e-mail message expected.
>
> I have then executed
>
> Select
> sqlj.install_jar('file:///c:/j2sdk1.4.2_09/lib/SendMail.jar','SendMail',true
> );
> Select sqlj.set_classpath('public','SendMail');
>
> Which seems fine. Then I insert a row into the table, and get
>
> ERROR: Unable to find static method dbudf.SendMail.send with signature
> (Lorg/postgresql/pljava/TriggerData;)V
>
> Any help greatly appreciated - I am sure this is something glaringly obvious
> that I'm missing.
>
> Mark
> _______________________________________________
> Pljava-dev mailing list
> Pljava-dev at gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/pljava-dev
>

Hi Thomas,
> My guess would be that you have a jar containing the
> javax.mail stuff on
> the classpath when you compile and run your test harness but
> you never
> load this jar into your database. The JVM is then unable to
> instantiate
> your SendMail class since it's depedent on Message, MimeMessage, etc.

You're absolutely right. I have now executed

Select
sqlj.install_jar('file:///c:/j2sdk1.4.2_09/lib/Mail.jar','Mail',true);
Select
sqlj.install_jar('file:///c:/j2sdk1.4.2_09/lib/Activation.jar','Activation',
true);
Select sqlj.set_classpath('public','Activation:Mail:SendMail');

The old error has been replaced by:

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

Which I guess means my SendMail.send method is faulty in some way. Any
further suggestions? In particular, could I amend my exception handler to
write any error messages to the PostgreSQL log file?

Mark

Hi Mark,
Can you please provide the backend log? You're likely to find it under
<postgresql installation directory>\data\pg_log if you're using the
Windows installer defaults.

From the sound of it, this is not an error in your code. It's not
unlikely that you're bitten by one of the bugs in 1.2 that has been
fixed in the coming 1.3 version. Perhaps you could try the latest
snapshot? You'll find the latest windows binaries at:

http://eng.tada.se/pljava/pljava-i686-pc-mingw32-pg8.1-1.3.dev.tar.gz

Install instructions here:
http://wiki.tada.se/display/pljava/Windows+Installation

Regards,
Thomas Hallgren

Mark Colman wrote:
> Hi,
>
> I am running PostgreSQL 8.1 on Windows and installed pl/java automatically
> using the Windows installer. I have created a simple table and executed the
> following:
>
> Create function emailme()
> Returns trigger
> As 'dbudf.SendMail.send'
> Language java;
>
> Create trigger trg_emailme
> After insert on aTable
> For each row
> Execute procedure emailme();
>
> I have written the following class:
>
> package dbudf;
>
> import javax.mail.*;
> import javax.mail.internet.*;
> import java.util.*;
>
> public class SendMail
> {
> public static void send(org.postgresql.pljava.TriggerData td)
> {try{
> Properties props = System.getProperties();
> props.put("mail.smtp.host", "smtp.tiscali.co.uk");
> Session session = Session.getDefaultInstance(props, null);
> Message msg = new MimeMessage(session);
> msg.setFrom(new InternetAddress("macolman at tiscali.co.uk"));
> msg.setRecipients(Message.RecipientType.TO,
> InternetAddress.parse("m.a.colman at durham.ac.uk", false));
> msg.setSubject("A change to your data");
> msg.setText("Your database has been changed.\n\nPlease use
> the PGAdmin tool to browse the table involved.\n\nMark Colman");
> msg.setHeader("X-Mailer", "LOTONtechEmail");
> msg.setSentDate(new Date());
> Transport.send(msg);
> }
> catch (Exception e)
> {
> System.err.println("Error: "+e.toString());
> }
> }
> }
> Which compiles fine, put it in a directory dbudf, and added the directory to
> a jar called SendMail.jar - I can then call dbudf.SendMail.send(null) from
> within a simple test harness and it sends the e-mail message expected.
>
> I have then executed
>
> Select
> sqlj.install_jar('file:///c:/j2sdk1.4.2_09/lib/SendMail.jar','SendMail',true
> );
> Select sqlj.set_classpath('public','SendMail');
>
> Which seems fine. Then I insert a row into the table, and get
>
> ERROR: Unable to find static method dbudf.SendMail.send with signature
> (Lorg/postgresql/pljava/TriggerData;)V
>
> Any help greatly appreciated - I am sure this is something glaringly obvious
> that I'm missing.
>
> Mark
> _______________________________________________
> Pljava-dev mailing list
> Pljava-dev at gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/pljava-dev
>

Mark Colman wrote:
> In particular, could I amend my exception handler to
> write any error messages to the PostgreSQL log file?
>
Forgot to answer this one. PL/Java configures the standard java.util.logging.Logger to use
the underlying PostgreSQL log functionality. Please read
http://wiki.tada.se/display/pljava/Logging for more info.

Regards,
Thomas Hallgren

Hi Thomas,

I followed your instructions for the exception logging: added

java.util.logging.Logger.getAnonymousLogger().info("Exception:
"+e.toString());

(commenting out the System.err line) And recompiled the class, built the jar
and replaced it in the database, and everything worked fine. Thanks very
much for your help with this.

Mark

Hi Thomas,

Take back what I just said, it's stopped working again!

The successful attempt had log_min_messages=debug5, and when it all worked I
replaced this with =info, restarted postgresql and this is when the crash
re-occurred. I tried putting debug5 back in but no joy.

I have sent you the log files. I will now have a look at the new version
which you suggest.

Mark

Hi (again) Thomas,

> Perhaps you could try the latest
> snapshot?

I've downloaded this, and replaced the various files in <PGDir>\lib and
<PgDir>\Share\pljava with the new versions (not following your no-doubt
excellent instructions). And it seems to work, not just once but
repeatedly, including after re-booting and changing the log_min_messages to
info.

No doubt I'll run into further difficulties and will be bothering you again,
but thanks very much for your help over the last few hours.

Mark