Re: LISTEN/NOTIFY with JDBC

Glenn Sullivan <glenn.sullivan@varianinc.com> · Wed, 12 May 2004 09:10:52 -0600

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:

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	

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings