raphw · August 12, 2022 20:45 · Aug 12, 2022
diff --git a/OracleNotificationTest.java b/OracleNotificationTest.java
@@ -0,0 +1,169 @@
+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.pool.OracleDataSource;
+import org.junit.Rule;
+import org.junit.Test;
+import org.testcontainers.Testcontainers;
+import org.testcontainers.containers.JdbcDatabaseContainer;
+import org.testcontainers.containers.OracleContainer;
+
+import java.io.IOException;
+import java.io.UncheckedIOException;
+import java.net.ServerSocket;
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.Properties;
+
+public class OracleNotificationTest {
+
+    @Rule
+    public JdbcDatabaseContainer<?> container = new OracleContainer("gvenzl/oracle-xe").withAccessToHost(true);
+
+    private final int port;
+
+    public OracleNotificationTest() {
+        try (ServerSocket socket = new ServerSocket(0)) {
+            socket.setReuseAddress(true);
+            port = socket.getLocalPort();
+        } catch (IOException e) {
+            throw new UncheckedIOException(e);
+        }
+        Testcontainers.exposeHostPorts(port);
+    } 
+
+    @Test
+    public void test_notification() throws Exception {
+        OracleDataSource dataSource = new OracleDataSource();
+        dataSource.setURL(container.getJdbcUrl());
+        dataSource.setUser(container.getUsername());
+        dataSource.setPassword(container.getPassword());
+        try (Connection conn = dataSource.getConnection("system", container.getPassword()); Statement stmt = conn.createStatement()) {
+            stmt.execute("GRANT CHANGE NOTIFICATION TO " + container.getUsername());
+        }
+        try (Connection conn = dataSource.getConnection(); Statement stmt = conn.createStatement()) {
+            stmt.execute("create table dept (deptno varchar2(500), dname varchar2(500))");
+        }
+
+        OracleConnection conn = dataSource.getConnection().unwrap(OracleConnection.class);
+
+        // first step: create a registration on the server:
+        Properties prop = new Properties();
+
+        // if connected through the VPN, you need to provide the TCP address of the client.
+        // For example:
+        //prop.setProperty(OracleConnection.NTF_LOCAL_HOST, GenericContainer.INTERNAL_HOST_HOSTNAME);
+        prop.setProperty(OracleConnection.NTF_LOCAL_HOST, "172.17.0.3");
+        prop.setProperty(OracleConnection.NTF_LOCAL_TCP_PORT, String.valueOf(port));
+
+        // Ask the server to send the ROWIDs as part of the DCN events (small performance cost):
+        prop.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS,"true");
+
+        //Set the DCN_QUERY_CHANGE_NOTIFICATION option for query registration with finer granularity.
+        prop.setProperty(OracleConnection.DCN_QUERY_CHANGE_NOTIFICATION,"true");
+
+        // The following operation does a roundtrip to the database to create a new
+        // registration for DCN. It sends the client address (ip address and port) that
+        // the server will use to connect to the client and send the notification
+        // when necessary. Note that for now the registration is empty (we haven't registered
+        // any table). This also opens a new thread in the drivers. This thread will be
+        // dedicated to DCN (accept connection to the server and dispatch the events to
+        // the listeners).
+        DatabaseChangeRegistration dcr = conn.registerDatabaseChangeNotification(prop);
+
+        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 where deptno='45'");
+            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(); }
+        }
+
+        synchronized( this )
+        {
+            // The following code modifies the dept table and commits:
+            try
+            {
+                OracleConnection conn2 = dataSource.getConnection().unwrap(OracleConnection.class);
+                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 ) {}
+        }
+
+        // At the end: close the registration (comment out these 3 lines in order
+        // to leave the registration open).
+        OracleConnection conn3 = dataSource.getConnection().unwrap(OracleConnection.class);
+        conn3.unregisterDatabaseChangeNotification(dcr);
+        conn3.close();
+    }
+
+    class DCNDemoListener implements DatabaseChangeListener {
+
+        OracleNotificationTest demo;
+        DCNDemoListener(OracleNotificationTest 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();}
+        }
+    }
+}
diff --git a/gistfile1.txt b/gistfile1.txt
@@ -0,0 +1,7 @@
+Test container maps: host.testcontainers.internal:172.17.0.3
+
+But
+select * from USER_CHANGE_NOTIFICATION_REGS;
+> 301        4         net8://(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.0.1)(PORT=41829))?PR=0     0            0            4294967295      TEST.DEPT
+
+172.17.0.1 is the own IP. Is this value not send by the JDBC driver? "NTF_LOCAL_HOST" is not considered. No matter what I send here, the IP remains the one of the container.