/** executeStoredProcedure definition. */ private void executeQuery(String query, boolean ignoreError) throws ReplicatorException { Statement stmt = null; ResultSet resultset = null; try { stmt = connection.getConnection().createStatement(); resultset = stmt.executeQuery(query); } catch (SQLException e) { if (!ignoreError) throw new ReplicatorException("Failed to execute query " + query, e); else logger.warn("Ignoring exception : " + e.getMessage()); } finally { try { if (resultset != null) resultset.close(); if (stmt != null) stmt.close(); } catch (SQLException ignore) { if (logger.isDebugEnabled()) logger.debug("Failed to close resultset", ignore); } } }
/** * {@inheritDoc} * * @see com.continuent.tungsten.replicator.extractor.RawExtractor#extract() */ @Override public DBMSEvent extract() throws ReplicatorException, InterruptedException { ArrayList<DBMSData> data = new ArrayList<DBMSData>(); long maxSCN = -1; Timestamp sourceTStamp = null; boolean noData = true; try { if (logger.isDebugEnabled()) logger.debug("Extending Window"); executeQuery( "BEGIN DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW(subscription_name => 'TUNGSTEN_PUB');END;", false); if (logger.isDebugEnabled()) logger.debug("Handling " + subscriberViews.keySet().size() + "views"); for (String view : subscriberViews.keySet()) { OracleCDCSource cdcSource = subscriberViews.get(view); Statement stmt = connection.getConnection().createStatement(); String statement; if (lastSCN != null) statement = "SELECT " + cdcSource.getPublication(view).getColumnList() + " , CSCN$, COMMIT_TIMESTAMP$, OPERATION$" + " from " + view + " where cscn$ > " + lastSCN + " order by cscn$, rsid$"; else statement = "SELECT " + cdcSource.getPublication(view).getColumnList() + " , CSCN$, COMMIT_TIMESTAMP$, OPERATION$" + " from " + view + " order by cscn$, rsid$"; resultset = stmt.executeQuery(statement); int userColumns = cdcSource.getPublication(view).getColumnsCount(); if (logger.isDebugEnabled()) logger.debug("Running " + statement); OneRowChange updateRowChange = null; OneRowChange oneRowChange = null; while (resultset.next()) { noData = false; long currentSCN = resultset.getLong("CSCN$"); if (maxSCN < currentSCN) maxSCN = currentSCN; if (sourceTStamp == null) sourceTStamp = resultset.getTimestamp("COMMIT_TIMESTAMP$"); if (logger.isDebugEnabled()) { logger.debug("Receiving data"); StringBuffer buffer = new StringBuffer(); for (int i = 1; i <= resultset.getMetaData().getColumnCount(); i++) { if (buffer.length() > 0) buffer.append('\t'); buffer.append(resultset.getString(i)); } logger.debug("Received : " + buffer.toString()); } String operation = resultset.getString("OPERATION$").trim(); RowChangeData rowData = new RowChangeData(); if (operation.equals("I")) { if (oneRowChange == null || !oneRowChange.getAction().equals(ActionType.INSERT)) { oneRowChange = new OneRowChange(cdcSource.getSchema(), cdcSource.getTable(), ActionType.INSERT); rowData.appendOneRowChange(oneRowChange); data.add(rowData); } parseRowEvent(oneRowChange, false, userColumns); } else if (operation.equals("D")) { if (oneRowChange == null || !oneRowChange.getAction().equals(ActionType.DELETE)) { oneRowChange = new OneRowChange(cdcSource.getSchema(), cdcSource.getTable(), ActionType.DELETE); rowData.appendOneRowChange(oneRowChange); data.add(rowData); } parseRowEvent(oneRowChange, true, userColumns); } else if (operation.startsWith("U")) { if (updateRowChange == null) { updateRowChange = new OneRowChange(cdcSource.getSchema(), cdcSource.getTable(), ActionType.UPDATE); rowData.appendOneRowChange(updateRowChange); data.add(rowData); if (operation.equals("UO")) { parseRowEvent(updateRowChange, true, userColumns); } else if (operation.equals("UN")) { parseRowEvent(updateRowChange, false, userColumns); } } else { if (operation.equals("UO")) { parseRowEvent(updateRowChange, true, userColumns); } else if (operation.equals("UN")) { parseRowEvent(updateRowChange, false, userColumns); } } } else { logger.error( "Unable to extract data from CDC (operation should be I, D, UO or UN - found " + operation + ")"); } } resultset.close(); resultset = null; stmt.close(); } lastSCN = null; } catch (SQLException e) { throw new ReplicatorException(e); } finally { if (resultset != null) try { resultset.close(); } catch (SQLException e) { logger.warn("Failed to close resultset"); } resultset = null; } if (noData) { logger.warn("Retrieved empty resultset... no data available... sleeping"); Thread.sleep(1000); } if (logger.isDebugEnabled()) logger.debug("Purging window"); executeQuery( "BEGIN DBMS_CDC_SUBSCRIBE.PURGE_WINDOW(subscription_name => 'TUNGSTEN_PUB');END;", false); if (data.size() > 0) { DBMSEvent event = new DBMSEvent(String.valueOf(maxSCN), data, sourceTStamp); // Mark the event as coming from Oracle. event.setMetaDataOption(ReplOptionParams.DBMS_TYPE, Database.ORACLE); // Strings are converted to UTF8 rather than using bytes for this // extractor. event.setMetaDataOption(ReplOptionParams.STRINGS, "utf8"); return event; } else { return null; } }