Product: | Syniti Data Replication, DBMoto |
Version: | All |
ID: | 1657 |
Summary: | Converting a MySQL Datetime source field for target table compatibility |
Unlike other relational databases, MySQL permits a zero value in Datetime fields: 0000-00-00 00:00:00. While it is not generally recommended to use a zero value in Datetime fields for replication, it is possible to handle this type of value using a script when performing a refresh (snapshot) replication to a different database.
The script converts the zero Datetime in MySQL to Null and inserts it into the target database. Note that this approach will not work if replicating in mirroring mode because once you set the Allow Zero Datetime option to True, the MySQL client will return the Datetime in a non-standard format. To work around this issue, you can either change all the zero Datetime fields to Null in your source table or set the Allow Zero Datetime option to True and use the above script to run the refresh, then stop the Data Replicator, and change the Allow Zero Datetime option to False. The mirroring should work as long as you do not insert 0 as a value in a Datetime field.
The steps below were tested with a Microsoft SQL Server target.
- In the Syniti DR/DBMoto Management Center, expand the tree in the Metadata Explorer.
- Right click on the MySQL connection and choose Connection Properties from the right mouse button menu.
- In the Source Connection Properties dialog, select the Connection | MySQL.NET Driver field.
- Click on the ... button in the field to open the Connection Properties for the MySQL Connector/Net.
- Scroll down to the Optional section and locate the Allow Zero Datetime field.
- Set the Allow Zero Datetime field value to True.
- Click OK to close the MySQL Connection Properties dialog.
- Click OK to close the Source Connection Properties dialog.
- In the Metadata Explorer, right click the metadata name and select Global Script from the right mouse button menu.
- Copy and paste the global script below into the Global Script Editor.
Imports System
Imports System.Data
Imports Microsoft.VisualBasic
Imports DBMotoPublic
Imports DBMotoScript
Namespace DBRS
Public Class GlobalScript : Inherits IGlobalScript
Public Shared Function AllowZero(obj as Object) as Object
If obj is Nothing Then
Return obj
ElseIf not obj.IsValidDateTime Then
Return DBNull.Value
Else
Return obj.GetDateTime()
End If
End Function
End Class
Public Class MappingRule : Inherits IMappingRule
End Class
Public Class GlobalEvents : Inherits IGlobalEvents
End Class
End Namespace
- Click OK to compile the script and close the editor.
- In the Metadata Explorer, right click the replication, and choose Replication Properties from the right mouse button menu.
- In the Replication Properties dialog, click Mapping.
- In the Fields Mapping dialog, right click the target SQL Server field with data type smalldatetime and select Map to Expression.
- In the Expression Generator, type AllowZero([field_name]) where AllowZero is the name of the global script function and [field_name] is the name of the field you selected above.
- Click OK
- Start the Replication Agent/Data Replicator and run the replication.
The script will convert the 0 Datetime in the MySQL table to Null and insert it into the SQL Server target table.