Re: LISTEN/NOTIFY with JDBC

On Tue, 11 May 2004, Glenn Sullivan wrote:

> Hi,
>
> I have been trying to get LISTEN/NOTIFY working in with JDBC.  I cannot seem
> to get notified.  I looked in the e-mail archive and saw a lot of similiar
> questions a couple of years ago.  I never could find any answers in the
> e-mail nor in the documentation.  Perhaps I just missed it.
>
> I have tried the following code snipit:
>
> Connection db = DriverManager.getConnection(url, user, passwd);
> Statement sql = db.createStatement();
>
> sql.execute("LISTEN mytest");
> db.clearWarnings();
>
> for(int i=0; i < 10 ; i ++) {
>      Thread.sleep(3000);
>      SQLWarning warn = db.getWarnings();
>      if(warn != null)
>          System.out.println("warn: " + warn.getMessage());
>      else
>          System.out.println("warning null");
> }
>
>
> During the running of this loop, I run "psql" on the same database
> and manually execute "NOTIFY mytest;".
>

Notifications don't come back as warning's, but are implemented using pg
specific java code.  Further, there is no asynchronous notification
support in the JDBC driver, so you can't just wait for them to show up.
Instead you must send a backend command every so often to see if a
notification is ready.

Code more like the below should work:

import org.postgresql.PGConnection;
import org.postgresql.PGNotification;

Connection conn = ... // get connection somehow
Statement stmt = conn.createStatement();
while (1) {
    ResultSet rs = stmt.executeQuery("SELECT 1");
    rs.close();
    PGConnection pgconn = (PGConnection)conn;
    PGNotification notif[] = conn.getNotifications();
    for (int i=0; i<notif.length; i++) {
        System.out.println(notif[i].getName());
    }
    Thread.sleep(3000);
}

Kris Jurka