Business
CourseCatalog
DairyBase
eCatalog
FarmBase
Form Server
Furlough Request
IntraOffice
PDF Merge
SalvageMan
Recycler Inventory Record
Recycler Tow Log
SkillTrak
TaskTrak
TimeTrak
Vehicle Dismantle Request
Vehicle Title and Registration
Web@assessor
Service Apps
MailIn
MailOut
Medical
BloodWorks
Patient Outcomes
Med Credentials
Miscellaneous
MovieListings
SyncMovie
DailyScores
DailyQuotes
Utilities
dbGeocode
LdapClimb
LogImport
LogFilter
RunUrl
Sync2Ldap
SyncTable
WebStats
Project: SyncTable
Client: MicroWeb
Keywords: Java, JDBC, Database Synchronization & Replication
Description: SyncTable is a Java based developer or database administrator utility to perform data synchronization between two databases.
Platform: Java 2 Runtime Environment (Windows, Mac, Unix, Linux)

Problem Summary
Several of our clients have the need to replicate data to remote databases that are used for client and web-based queries. Although the database systems we utilize (MySQL, SQL Server, Oracle, etc) offer replicatation capabilities, our clients needed a batch mode process that would upload database changes at the end of the work day.

Example
A car dealer uses a remote MySQL database to list their vehciles for sale on their website. The car dealer's master database is located behind the corporate firewall. At the end of the work day, the car dealer wants to update the website database with changes made by sales associates during the day.

Solution
SyncTable was scheduled to run at every evening (using the Windows schedule service), to update the website database records with changes made that day on the master database. In this solution, the master database table contained a timestamp field indicating the date/time when the vehicle record was last modified. SyncTable used the timestamp field to determine which records to update or add to the remote database.

Requirements

  • Java 2 runtime environment (J2SE)
  • JDBC driver for database that will be updated

Tested Systems

Note: SyncTable does NOT work with the Sun Microsystems jdbc:odbc bridge.

Program Usage
SyncTable makes use of a simple configuration file for each table you want synchronized on the remote database. The configuration file specifies the database connection properties, table to synchronize, table fields and selection criteria. Internally, we utilize a single batch file that runs the program using different configuration files for each database table that needs updating. The batch file is then scheduled to run using the Windows Schedule Service.

Command Line
	C:\MicroWeb\SyncTable>java -jar SyncTable.jar <config_file> [<config_option>]
<config_file> The name of the configuration file

Tip: On windows machines use the forward slash character '/' to deliminate folder names if your configuration file is not in the same folder as the program.
<config_option> The config_option is used to override the filter parameter specified in the configuration file that determines which records will by synchronized with the remote database. If no filter parameter is specified on command line or configuration file, all records in the master database table will be synchronized with the remote database table.
offset=daysfromtoday This option creates a filter using the table timestamp field to determine which records to update on the remote database. The table timestamp field is defined in the configuration file.

daysfromtoday = integer specifying the number of days from today.

Example 1: To synchronize records that were updated today, use the following:
offset=0

Example 2: To synchronize records that were updated as of yesterday use the following:
offset=-1

Example 2: To synchronize records that were updated within the last 5 days, use the following:
offset=-5

Note: Specifying this command line option overrides the filter parameter specified in the configuration file
filter="filter_parameter" This option creates a filter using the fields you specify to determine which records to update on the remote database.

Example 1: To synchronize records having the showOnline field set to 1, use the following:
filter="showOnline=1"

Note 1: Specifying this command line option overrides the filter parameter specified in the configuration file

Note 2: The filter_parameter must be enclosed in quotes.

Configuration File Parameters
app.debug [yes|no] : turns debug messages on/off
db.driver The JDBC driver used to access the master database.
Example (MySQL database):
db.driver=com.mysql.jdbc.Driver
db.url The JDBC driver connection paramters used to access the master database.
Example (MySQL database):
db.url=jdbc:mysql://127.0.0.1/mydatabase?user=myuser&password=mypassword
db.dateformat The date format used by the master database.
Example (MySQL database):
db.dateformat=yyyy-MM-dd
dbsync.driver The JDBC driver used to access the remote database.
Example (MySQL database):
db.driver=com.mysql.jdbc.Driver
dbsync.url The JDBC driver connection paramters used to access the remote database.
Example (MySQL database):
db.url=jdbc:mysql://myhost.mydomain.com/mydatabase?user=myuser&password=mypassword
sync.table The name of the table that will be synchronized. The table name must be the same on the master and remote databases.
Example: sync.table=Vehicle
sync.keyfield The name of the table primary key field. This must be the same on the master and remote databases.
Example: sync.keyfield=vin
sync.timestampfield The name of the table timestamp field used to record field updates.
Example: sync.timestampfield=vehicleUpdated
sync.filter The filter string used to determine which records to synchronize.
Example: (synchronize records where the status field is 'forsale' or 'pending' in the master table):
sync.filter=status IN ('forsale','pending')

Note: This may be over-ridden by the command line option [offset | filter]
sync.field.N.name SyncTable can synchronize up to 100 table fields. N is a number from 0 to 99 that indicates a field to synchronize. If the record does not exist on the remote server it will be added.
Example: (synchronize six fields on the remote server)
sync.field.0.name=Year
sync.field.1.name=Make
sync.field.2.name=Model
sync.field.3.name=Bodystyle
sync.field.4.name=Color
sync.field.5.name=Mileage
sync.field.6.name=Status
sync.field.7.name=
:
:
sync.field.99.name=


Note: The numbers do not need to be in order. For example, to skip the Mileage field above, remove the text after the = character as shown below.
sync.field.5.name=

Sample Batch File (SyncTable.bat)

# Run SyncTable to synchronize remote databases with local master
# Use java classpath option to tell system where file is located
# The SyncTable.jar file is installed in the C:\MicroWeb\SyncTable folder

cd C:\MicroWeb\SyncTable
java -jar SyncTable.jar SyncTable01.ini
java -jar SyncTable.jar SyncTable02.ini
java -jar SyncTable.jar SyncTable03.ini

 

Copyright (c) 2019 by MicroWeb. All Rights Reserved
www.microwww.com