agora inbox for pljava-dev@postgresql.org
help / color / mirror / Atom feed[Pljava-dev] Trigger problem
8+ messages / 0 participants
[nested] [flat]
* [Pljava-dev] Trigger problem
@ 2006-04-23 01:00
2006-04-23 08:06 ` [Pljava-dev] Trigger problem
2006-04-23 10:27 ` [Pljava-dev] Trigger problem
0 siblings, 2 replies; 8+ messages in thread
From: @ 2006-04-23 01:00 UTC (permalink / raw)
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* [Pljava-dev] Trigger problem
2006-04-23 01:00 [Pljava-dev] Trigger problem
@ 2006-04-23 08:06 `
2006-04-23 09:56 ` [Pljava-dev] Trigger problem
1 sibling, 1 reply; 8+ messages in thread
From: @ 2006-04-23 08:06 UTC (permalink / raw)
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* [Pljava-dev] Trigger problem
2006-04-23 01:00 [Pljava-dev] Trigger problem
2006-04-23 08:06 ` [Pljava-dev] Trigger problem
@ 2006-04-23 09:56 `
2006-04-23 10:31 ` [Pljava-dev] Trigger problem
0 siblings, 1 reply; 8+ messages in thread
From: @ 2006-04-23 09:56 UTC (permalink / raw)
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* [Pljava-dev] Trigger problem
2006-04-23 01:00 [Pljava-dev] Trigger problem
2006-04-23 08:06 ` [Pljava-dev] Trigger problem
2006-04-23 09:56 ` [Pljava-dev] Trigger problem
@ 2006-04-23 10:31 `
0 siblings, 0 replies; 8+ messages in thread
From: @ 2006-04-23 10:31 UTC (permalink / raw)
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* [Pljava-dev] Trigger problem
2006-04-23 01:00 [Pljava-dev] Trigger problem
@ 2006-04-23 10:27 `
2006-04-23 11:00 ` [Pljava-dev] Trigger problem
2006-04-23 11:32 ` [Pljava-dev] Trigger problem
2006-04-23 12:13 ` [Pljava-dev] Trigger problem
1 sibling, 3 replies; 8+ messages in thread
From: @ 2006-04-23 10:27 UTC (permalink / raw)
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* [Pljava-dev] Trigger problem
2006-04-23 01:00 [Pljava-dev] Trigger problem
2006-04-23 10:27 ` [Pljava-dev] Trigger problem
@ 2006-04-23 11:00 `
2 siblings, 0 replies; 8+ messages in thread
From: @ 2006-04-23 11:00 UTC (permalink / raw)
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* [Pljava-dev] Trigger problem
2006-04-23 01:00 [Pljava-dev] Trigger problem
2006-04-23 10:27 ` [Pljava-dev] Trigger problem
@ 2006-04-23 11:32 `
2 siblings, 0 replies; 8+ messages in thread
From: @ 2006-04-23 11:32 UTC (permalink / raw)
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* [Pljava-dev] Trigger problem
2006-04-23 01:00 [Pljava-dev] Trigger problem
2006-04-23 10:27 ` [Pljava-dev] Trigger problem
@ 2006-04-23 12:13 `
2 siblings, 0 replies; 8+ messages in thread
From: @ 2006-04-23 12:13 UTC (permalink / raw)
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2006-04-23 12:13 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-23 01:00 [Pljava-dev] Trigger problem
2006-04-23 08:06 `
2006-04-23 09:56 `
2006-04-23 10:31 `
2006-04-23 10:27 `
2006-04-23 11:00 `
2006-04-23 11:32 `
2006-04-23 12:13 `
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox