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

Kris,

Thanks for the response. I have been using Sun's JDBC.
Do I take it that I need to create and use the Postgres
JDBC to get the ability to do NOTIFY/LISTEN?

Glenn

Kris Jurka wrote:

Glenn Sullivan <glenn.sullivan@varianinc.com> writes:

Kris,

Thanks for the response. I have been using Sun's JDBC.
Do I take it that I need to create and use the Postgres
JDBC to get the ability to do NOTIFY/LISTEN?

You need to call methods in the PG JDBC driver that are not part of
the Sun JDBC API, yes.

-Doug

On Wed, 12 May 2004, Glenn Sullivan wrote:

Thanks for the response. I have been using Sun's JDBC.
Do I take it that I need to create and use the Postgres
JDBC to get the ability to do NOTIFY/LISTEN?

You have been using the JDBC API provided by Sun as a number of interfaces
(java.sql.*) which are implemented behind the scenes by the postgresql
JDBC driver in concrete classes. Listen/Notify is a postgresql extension
not covered in the standard java.sql.* API, so must cast the standard
interfaces to postgresql implementations so that you can access the
additional functionality provided.

For example when using the postgresql JDBC driver java.sql.Connection is
actually an instance of org.postgresql.PGConnection, so you can cast your
Connection to a PGConnection to use the notification API. Unfortunately
the PGConnection and other extensions are either poorly documented or
completely undocumented. Combining my example with a brief look at the
source code for PGConnection and PGNotification should hopefully get you
going.

Kris Jurka