fmSQL Synch (v2.0)
Garrison Computer Services
Products >> fmSQL Synch >> Documentation >> Configuration >>
- Features
- System Requirements
Installation
- fmSQL Synch
- SQL Plugin
- JDBC Driver
Setup Assistant
Configuration
- Field Setup
- SQL Statements
- Database Connection
Integration
- FM Database
- SQL Database
- Delete Log
- Key Ranges
- Merge Conflicts
Conflicts
Date & Time
FAQ
- Miscellaneous
- Installation
- Configuration
- Integration
Diagram
Configuration
After using the Setup Assistant, you can make further changes manually by editing configurations listed in "Configs" tab of "fmSQL Synch.fp7". Select a configuration in the list and click the 'Edit' button.
Each record (or synch config) in the fmSQL Synch.fp7 file corresponds to one FileMaker table and one SQL table that are to be synched with each other. Here is a brief explanation of the configuration options:
| Config Name | name for the config, can be anything you like |
| Abbreviation | short title for this config, should not contain any spaces or other word separator characters; it's used to establish relationships with MergeConflicts and DeleteLog files |
| Sort | sort value, used to control the order configs are processed during the synch run |
| Actions | each synch config can have any of 6 possible actions; 3 for FileMaker and 3 for SQL. The actions are: Update, Insert, Delete |
| Config Groups | controls which synch configs will be used during synching process |
| Log Actions | controls which log files will be written to during synching process |
| FM Database | name of the FileMaker database being synched |
| DB Location | path (folder) or host name of the FileMaker database being synched |
| Location Type | set to 'Path' or 'Host' depending on whether FM database is local or opened via FileMaker Server |
| FM Account | account name to open FileMaker database |
| FM Password | password to open FileMaker database |
| FM Table | name of FM table being synched |
| FM Date Format | specifies either US or international date formats when updating dates in FileMaker during synching process |
| SQL Connection | specifies which SQL Connection to use |
| SQL Table | name of SQL table being synched |
| Min Difference | minimum number of seconds difference between LastMod and LastSynch in order for record to be included in synch run |
Field Setup
These are the fields for FileMaker and SQL that should be synched with each other. The "Get Fields" button will set fields for the "FM Fields" option, using the fields from "SQL->FM (update)" layout of the database specified in "FM Database" option.
IMPORTANT, the first field in both the FileMaker and SQL lists should be the primary key.
| FM Fields | list of FileMaker fields to be used in synching process; should match (exactly) the fields from the "SQL->FM (update)" layout |
| SQL Fields | used to create the field lists for the "statement" options (see below) |
| Field Types | used for the "create table" statement |
| Diff Check | indicates whether each of the fields in "FM Fields" is to be compared to value in SQL field when doing conflict checking |
| Clean Type | the type of cleaning to be done to each field, eg. zero dates (0000-00-00) from SQL converted to blank (empty) values before updating FileMaker |
SQL Statements
Theses options hold the SQL statements used for querying and updating the SQL database. Placeholders (#01#, #02#) will be replaced with field values from FileMaker during the synching process. The "Create SQL Statements" button will generate statements for each of the following options, using the contents of the "SQL Fields" and "SQL Field Types" options.
| Select | return records modified since last synch, used to update FileMaker records; first field should be the primary key |
| Update | update record with values from FileMaker |
| Insert | insert record if update statement fails due to "key does not exist" |
| Rec Exists | check whether record exists for given key |
| Delete | delete record in SQL table for given key (key obtained from DeleteLog.fp7) |
| Select for Delete | return records from delete_log, used to delete FileMaker records with corresponding keys |
| Update for Delete | updates the delete_log after successful deletion of FileMaker record |
| Set LastSynch | updates the last_synch value for records in the SQL database |
| Create Table | used to create tables based on "SQL Fields" and "SQL Field Types" |
Each of the above options for SQL Statements (except Select & Create Table) also has a corresponding Param Count option. The "Param Count" should match the number of placeholders in each SQL statement.
| Param Count | the number of placeholders in each SQL statement |
Note: The SQL Plugin returns records from a SELECT query in a pipe (|) delimited string by default. If any of the fields being synchronised from SQL to FileMaker contain pipe characters, you will need to change the "Field Separator" in the SQL Connection.
Database Connection
The database connection and login details can be different for each synch config. Using different SQL Connections allows each FileMaker table to synch to a different SQL database.
| JDBC Driver | name/class of the JDBC driver for your database; default value for mySQL is "com.mysql.jdbc.Driver" (or "org.gjt.mm.mysql.Driver" if using version 2.0.14 if the driver) |
| JDBC URL | first part of the JDBC connection URL; default value for mySQL is "jdbc:mysql://" |
| Hostname | database/server hostname for the connection; default is localhost |
| Database | name of the database for the connection |
| User Name | name of user for connecting to the SQL database |
| Password | password for connection to the SQL database |
The "JDBC Driver", "JDBC URL" and "Hostname/Database" options all have direct counterparts in the SQL Plugin functions. Use the "test" (X) button next to each to check whether you have entered valid values. Click the "Connect" button to test whether you can connect to your SQL database using the current options. Check the "DB Status" text box after each test to see if the SQL Connection works.