0

First time trying to do something like this and I'm not sure what I am missing?

My code:

package SQLOCpackage;
import java.awt.BorderLayout;
import java.awt.EventQueue;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;    
import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.border.EmptyBorder;    
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleStatement;
import oracle.jdbc.dcn.DatabaseChangeEvent;
import oracle.jdbc.dcn.DatabaseChangeListener;
import oracle.jdbc.dcn.DatabaseChangeRegistration;
import oracle.jdbc.dcn.RowChangeDescription;
import oracle.jdbc.dcn.TableChangeDescription;

@SuppressWarnings("serial")
public class SQLONframe extends JFrame {
String URL = "jdbc:oracle:thin:@xxxxx.xxxxx.xx.xxxx:1521:xxxxx";
Properties prop;
private JPanel contentPane;
static String PW                    = "xxxxxxxx";
static String UN                    = "xxxxxxxx";

public static void main(String[] args) {
    EventQueue.invokeLater(new Runnable() {
        public void run() {
            try {
                SQLONframe frame = new SQLONframe();
                frame.setVisible(true);

                SQLONframe dcn = new SQLONframe();
                try {
                  dcn.prop = new Properties();
                  dcn.prop.setProperty("user", UN);
                  dcn.prop.setProperty("password", PW);
                  dcn.run();
                }
                catch(Exception e) {
                  e.printStackTrace();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    });
}

public SQLONframe() {
    setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    setBounds(100, 100, 450, 300);
    contentPane = new JPanel();
    contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
    contentPane.setLayout(new BorderLayout(0, 0));
    setContentPane(contentPane);
}

void run() throws SQLException {
    OracleConnection conn = (OracleConnection)DriverManager.getConnection(URL,prop);
    DatabaseChangeRegistration dcr = null;
    Properties prop = new Properties();
    prop.setProperty(OracleConnection.DCN_QUERY_CHANGE_NOTIFICATION,"true");

    try {
      dcr = conn.registerDatabaseChangeNotification(prop);
      dcnListener list = new dcnListener(this);
      dcr.addListener(list);

      Statement stmt = conn.createStatement();
      ((OracleStatement)stmt).setDatabaseChangeRegistration(dcr);
      ResultSet rs = stmt.executeQuery("select script_name, current_status, Issues_found_during_run, Testers, tools from ALLDATA WHERE ID = 1");
      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(Exception e) {
      //clean up our registration
      if(conn != null)
        conn.unregisterDatabaseChangeNotification(dcr);
      e.printStackTrace();
    }
    finally {
      try {
        conn.close();
      }
      catch(Exception e){
        e.printStackTrace();
      }
    }

    try {
      Thread.currentThread().join();
    }
    catch (Exception e) {
      e.printStackTrace();
    }

    finally {
      OracleConnection conn3 = (OracleConnection)DriverManager.getConnection(URL,prop);
      conn3.unregisterDatabaseChangeNotification(dcr);
      conn3.close();
    }
  }


class dcnListener implements DatabaseChangeListener {
    SQLONframe dcn;
  dcnListener(SQLONframe dem) {
    dcn = dem;
  }

  public void onDatabaseChangeNotification(DatabaseChangeEvent e) {
    TableChangeDescription[] tc = e.getTableChangeDescription();

    for (int i = 0; i < tc.length; i++) {
      RowChangeDescription[] rcds = tc[i].getRowChangeDescription();
      for (int j = 0; j < rcds.length; j++) {
        System.out.println(rcds[j].getRowOperation() + " " + rcds[j].getRowid().stringValue());
      }
    }
    synchronized( dcn ){
      dcn.notify();
    }
  }

}
 }

I can see that it does register the Change Notification but when I go and change something in that table and commit it I never get anything on the code side saying something has changed?

Any help would be great!

update

Using

SELECT * from USER_CHANGE_NOTIFICATION_REGS 

I do get a reg back when doing that query in SQL Developer:

enter image description here

And the ojdbc8.jar version info is:

Oracle 18.3.0.0.0 JDBC 4.2 compiled with javac 1.8.0_171 on

Tue_Jun_26_11:06:40_PDT_2018

Default Connection Properties Resource

Tue Mar 12 10:06:48 EDT 2019

and this is the Oracle SQL Developer status:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

PL/SQL Release 12.2.0.1.0 - Production

"CORE 12.2.0.1.0 Production"

TNS for Linux: Version 12.2.0.1.0 - Production

NLSRTL Version 12.2.0.1.0 - Production

and with my program running and the query registered this is what I see for ports:

enter image description here

StealthRT
  • 10,108
  • 40
  • 183
  • 342
  • Could you explain what you're trying to do when data in the table changes? – Mick Mnemonic Feb 27 '19 at 20:19
  • @MickMnemonic I'm setting this up in each copy of the java program I have. When 1 user changes something in the table then it will alert others and change that value as well on their java program. Think of it as a "live update" type of deal. – StealthRT Feb 27 '19 at 20:36
  • Just checking here ... you are only listening for changes for `ID = 1`. Did you try changing that particular record? – Matthew McPeak Feb 27 '19 at 22:09
  • @MatthewMcPeak yes I have tried changing just the 1st ID's value for Current_status and it never fires anything. – StealthRT Feb 27 '19 at 22:24
  • Are you connecting to your Oracle database via a VPN client? – Matthew McPeak Feb 27 '19 at 22:27
  • @MatthewMcPeak no I’m not. – StealthRT Feb 27 '19 at 22:30
  • Anyone else care to help? – StealthRT Mar 07 '19 at 20:25
  • I'm not seeing anything wrong in your code. What version of the driver and server are you using? You can also query the USER_CHANGE_NOTIFICATION_REGS or DBA_CHANGE_NOTIFICATION_REGS tables to verify that your registration exists. – Jean de Lavarene Mar 12 '19 at 08:41
  • @JeandeLavarene I have updated my OP with that information for you. – StealthRT Mar 12 '19 at 14:09
  • I'm guessing the port used on the driver isn't reachable. Can you try a different port? See OracleConnection.NTF_LOCAL_TCP_PORT in https://docs.oracle.com/database/121/JAJDB/oracle/jdbc/OracleConnection.html#registerDatabaseChangeNotification_java_util_Properties_ – Jean de Lavarene Mar 13 '19 at 08:21

0 Answers0