• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Data Change Notification in Java

 
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

I was using Oracle Data Change Notification (DCN) with 10g db (10.2.0.4.0)
with 11g client. I see the registration successful, but we are not receiving the notifications. The same program works on a different DB (same db version though). Appreciate any help on this how to DEBUG this issue.

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleDriver;
import oracle.jdbc.OracleStatement;
import oracle.jdbc.dcn.DatabaseChangeEvent;
import oracle.jdbc.dcn.DatabaseChangeListener;
import oracle.jdbc.dcn.DatabaseChangeRegistration;

public class DBChangeNotification
{
static final String USERNAME= "test";
static final String PASSWORD= "test";
static String URL = "jdbc racle:thin:@name.host.com:1521:SID";

public static void main(String[] argv)
{

DBChangeNotification demo = new DBChangeNotification();
try
{
demo.run();
}
catch(SQLException mainSQLException )
{
mainSQLException.printStackTrace();
}
}

void run() throws SQLException
{
OracleConnection conn = connect();

// first step: create a registration on the server:
Properties prop = new Properties();


prop.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS,"true");

DatabaseChangeRegistration dcr = conn.registerDatabaseChangeNotification(prop);

Properties pr = dcr.getRegistrationOptions();
System.out.println("dcr.getRegistrationOptions() = "+pr);

try
{
// add the listenerr:
DCNDemoListener list = new DCNDemoListener(this);
dcr.addListener(list);

// second step: add objects in the registration:
Statement stmt = conn.createStatement();
// associate the statement with the registration:
((OracleStatement)stmt).setDatabaseChangeRegistration(dcr);
ResultSet rs = stmt.executeQuery("select * from dept");
while (rs.next())
{}
String[] tableNames = dcr.getTables();
for(int i=0;i<tableNames.length;i++)
System.out.println(tableNames[i]+" is part of the registration.");
rs.close();
stmt.close();
}
catch(SQLException ex)
{
// if an exception occurs, we need to close the registration in order
// to interrupt the thread otherwise it will be hanging around.
if(conn != null)
conn.unregisterDatabaseChangeNotification(dcr);
throw ex;
}
finally
{
try
{
// Note that we close the connection!
conn.close();
}
catch(Exception innerex){ innerex.printStackTrace(); }
}

System.out.println("dcr.getRegistrationOptions() 2= "+pr);

synchronized( this )
{
// The following code modifies the dept table and commits:
try
{
OracleConnection conn2 = connect();
conn2.setAutoCommit(false);
Statement stmt2 = conn2.createStatement();
stmt2.executeUpdate("insert into dept (deptno,dname) values ('45','cool dept')",Statement.RETURN_GENERATED_KEYS);
ResultSet autoGeneratedKey = stmt2.getGeneratedKeys();
if(autoGeneratedKey.next())
System.out.println("inserted one row with ROWID="+autoGeneratedKey.getString(1));
stmt2.executeUpdate("insert into dept (deptno,dname) values ('50','fun dept')",Statement.RETURN_GENERATED_KEYS);
autoGeneratedKey = stmt2.getGeneratedKeys();
if(autoGeneratedKey.next())
System.out.println("inserted one row with ROWID="+autoGeneratedKey.getString(1));
stmt2.close();
conn2.commit();
conn2.close();
}
catch(SQLException ex) { ex.printStackTrace(); }

// wait until we get the event
try{ this.wait();} catch( InterruptedException ie ) {}
}

System.out.println("dcr.getRegistrationOptions() 3= "+pr);
try {
Thread.currentThread().join(15*60*1000l);
} catch (InterruptedException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

// At the end: close the registration (comment out these 3 lines in order
// to leave the registration open).
OracleConnection conn3 = connect();
conn3.unregisterDatabaseChangeNotification(dcr);
conn3.close();
}

/**
* Creates a connection the database.
*/
OracleConnection connect() throws SQLException
{
OracleDriver dr = new OracleDriver();
Properties prop = new Properties();
prop.setProperty("user",DBChangeNotification.USERNAME);
prop.setProperty("password",DBChangeNotification.PASSWORD);
return (OracleConnection)dr.connect(DBChangeNotification.URL,prop);
}
}
/**
* DCN listener: it prints out the event details in stdout.
*/
class DCNDemoListener implements DatabaseChangeListener
{
DBChangeNotification demo;
DCNDemoListener(DBChangeNotification dem)
{
demo = dem;
}
public void onDatabaseChangeNotification(DatabaseChangeEvent e)
{
Thread t = Thread.currentThread();
System.out.println("DCNDemoListener: got an event ("+this+" running on thread "+t+")");
System.out.println(e.toString());
synchronized( demo ){ demo.notify();}
}
}



Thanks,
Janardhan.
reply
    Bookmark Topic Watch Topic
  • New Topic