| Product: | Syniti Data Replication, DBMoto |
| Version: | All |
| ID: | 1629 |
| Summary: | VB.NET Example of scripts to filter records during mirroring for Oracle, MySQL |
In some cases it can be useful to add a filter to replications in order to propagate only records that have specific values (or ranges of values) in certain fields. In refresh mode, the Refresh Filter property can be used to achieve this purpose, but in mirroring mode you need to write a script to achieve the same functionality. In this article we’ll show how to write a replication script that filters data during a mirroring replication. Note that this example works for databases such as SQL Server and Oracle where the log does not report information for non-primary key fields involved in the filter. Check knowledge base article 1627 for a filter example where the log includes complete BEFORE and AFTER values such as IBM DB2 on i.
Let’s define a simple table to replicate from source to target:
|
Oracle |
MySQL |
|
# create table scott.ADVICEDOC OBJECT_ID varchar2(16) not null , |
# create table test.ADVICEDOC OBJECT_ID varchar(16) not null , |
The following is a sample script that demonstrate how to filter records with a specific numeric field value. It overrides the event Record_onBeforeMapping to control the record propagation.
The onBeforeMapping event code performs a select with the following cases:
- if an INSERT has been executed, check if the value of the record after insertion matches the desired value, otherwise abort the INSERT;
- if an UPDATE has been executed, depending on the value of the TRANSFER_NO field before and after the operation, abort the UPDATE, or switch it to an INSERT or a DELETE;
- if a DELETE has been executed, the operation will be aborted only in cases where the value before was not “555-2551”.
Here is the full script class:
Imports System
Imports System.Data
Imports Microsoft.VisualBasic
Imports DBMotoPublic
Imports DBMotoScript
Imports DBRS.GlobalScript
Namespace DBRS
Public Class ReplicationScript : Inherits IReplicationScript
Private Function Compare (obj As Object, value as Object) As Boolean
If obj Is Nothing OrElse IsDBNull(obj) Then
Return (value Is Nothing)
Else
Return (String.Compare(obj.ToString().Trim(), value) = 0)
End If
End Function
Public Overrides Sub Record_onBeforeMapping(recSource As IRecord, ByRef AbortRecord As Boolean)
Dim valueBefore As Object
Dim valueAfter As Object
Select Case recSource.OperationType
Case enmOperationType.Insert
If Not Compare (recSource.GetValueAfter("TRANSFER_NO"),"555-2551") Then
‘ Insert aborted
AbortRecord = True
End If
Case enmOperationType.Update
valueBefore = recSource.GetValueBefore("TRANSFER_NO")
valueAfter = recSource.GetValueAfter("TRANSFER_NO")
If valueAfter Is Nothing Then
recSource.SetValueAfter("TRANSREF_NO", valueBefore)
End If
If Not Compare (recSource.GetValueBefore("TRANSFER_NO"),"555-2551") Then
If Not Compare (recSource.GetValueAfter("TRANSFER_NO"),"555-2551") Then ' xxx --> xxx
'Delete record
AbortRecord = True
Else ' xxx --> “MYVALUE”
'Value after for OBJECT_ID
valueBefore = recSource.GetValueBefore("OBJECT_ID")
valueAfter = recSource.GetValueAfter("OBJECT_ID")
If valueAfter Is Nothing Then
recSource.SetValueAfter("OBJECT_ID", valueBefore)
End If
'Value after for ACCOUNT_TYPE
valueBefore = recSource.GetValueBefore("ACCOUNT_TYPE")
valueAfter = recSource.GetValueAfter("ACCOUNT_TYPE")
If valueAfter Is Nothing Then
recSource.SetValueAfter("ACCOUNT_TYPE", valueBefore)
End If
'Value After for RUBRIC
valueBefore = recSource.GetValueBefore("RUBRIC")
valueAfter = recSource.GetValueAfter("RUBRIC")
If valueAfter Is Nothing Then
recSource.SetValueAfter("RUBRIC", valueBefore)
End If
‘ Change operation from update to insert
recSource.OperationType = enmOperationType.Insert
End If
ElseIf Not Compare (recSource.GetValueAfter("TRANSFER_NO"),"555-2551") Then ' ”MYVALUE” --> xxx
‘ Change operation from update to delete
recSource.OperationType = enmOperationType.Delete
End If
Case enmOperationType.Delete
If Not Compare (recSource.GetValueBefore("TRANSFER_NO"),"555-2551") Then
‘ Delete aborted
AbortRecord = True
End If
End Select
End Sub
End Class
End Namespace
Notes:
- This filter will apply also in refresh mode (the Record_onBeforeMapping event is also called during refresh) therefore it is not necessary to use the Refresh Filter property to execute the filter during refresh.
- This script works on databases where the log information is not complete with BEFORE and AFTER values since in some cases, like SQL Server and Oracle, not all BEFORE values are available during transactional replication.
- This script works on databases where primary keys or unique indexes are NOT defined on the source table. If they are defined, you need to use a different method such as running a SELECT on the source table to retrieve the original values. Make sure that in such cases, the method used to retrieve the BEFORE value is consistent and well-tested. It should not be used if there is any chance of an inaccurate value during replication.