Using ODBC Router with Specific Applications --> FileMaker Pro on Mac or Windows
Want to rapidly develop in-house applications and reports that feed off of your web server, customer database or accounting system?
FileMaker Pro and ODBC ROUTER enable sharing of data between FileMaker Pro solutions and SQL databases on your web server or in your back room. Records can be exchanged with just about every version of every SQL database system ever produced by MYSQL, ORACLE, IBM, MICROSOFT, SYBASE, REDBRICK and dozens of others, whether your FileMaker Pro solution is hosted on Mac OS X, Mac Classic or Windows and whether your SQL databases are hosted on Linux/Unix/Mac, Windows or mainframes.
Interactive Web/SQL Import to FMPro
FileMaker Pro supports an interactive dialog where you may connect to a SQL database (called an ODBC data source), browse the available tables and columns, then import a selection of records into a FileMaker Pro table. This is great for "initial load" of your data into FileMaker Pro, enabling further manipulation with powerful layouts and the production of elegant reports.
Note: Some Mac users will first need to install an ODBC Driver Manager.
Automatic FMPro/Web/SQL Synchronization
If you know SQL and are running FileMaker Pro 5.5 or later (on Mac or Windows), then an even more powerful way to use ODBC in FileMaker Pro is to exploit two built-in "script steps" that enable your solution to "synchronize" all of its local tables with the remote data in a SQL database. We recommend choosing a SQL database that supports both SQL views and triggers, such as ORACLE7 or later, DB/2, Postgres, or any version of SQLServer. It is possible to use a SQL database that does not support SQL views and triggers (such as SQLLite, MYSQL4 or Access), but those more basic SQL database will need to be taken offline while the FileMaker Pro is synchronizing. Also note that MYSQL5 supports triggers, but not SELECT-triggers, which limits its ability to implement automatic synchronization as described here.
ODBC IMPORT Script Step
This is the script step that corresponds to the Interactive Import capability described above. What is not immediately obvious; however, is that the script step is usually used in conjunction with a "view" on the remote SQL database, as opposed to a "table". More about this in a second.
ODBC EXECUTE Script Step
This is a script step that allows for execution of a statement on a remote SQL database. This can be used, for example, to invoke a "stored-procedure" or to directly INSERT or UPDATE records on the back-end.
These script steps combine to provide for the bi-directional exchange of records between your FileMaker Pro database and your SQL database.
Synchronization Example: Table Layout/Schema Issues
To create a "synchronization" solution, start by creating local tables in FileMaker Pro that correspond exactly to each table on the remote SQL database (same table names, same column/field names, approximately the same data type), if possible. Each table that is to be synchronized should also contain two additional columns: "added_at" and "modified_at", in both FileMaker Pro and the SQL database. (Note that when creating these two columns in FileMaker Pro, you can configure FileMaker Pro options so that the field contents are automatically maintained whenever you add new or modify existing records in the table. When creating these two columns in SQL, you can define a SQL trigger so that the contents of these fields are automatically maintained during INSERT and UPDATE operations on the table. For more help with these FileMaker Pro options or with SQL triggers, please see below.) On FileMaker Pro, you will also want to create a global variable called "g_last_sql_synced_at" in each table containing the timestamp of the last synchronization operation. Finally, on the SQL database, you will want to add a new table "fmpro_sync_table" that contains the name of each SQL table to be synchronized with FileMaker Pro and the timestamp that it was last synchronized. There is but one last step, and that is to create new a SQL "view" for each SQL table that you want to synchronize. These SQL views select only the records that have been added or modified (based on their "added_at" and "modified_at" columns) since the synchronization timestamp for their table's entry in "fmpro_sync_table". A SQL trigger is then added on that view which causes the timestamp in "fmpro_sync_table" to be updated everytime the view is selected. (Again, for further assistance, please see below.).
Synchronization Example: Scripting
Live Updating SQL from FmPro: If your FileMaker Pro solution currently only ever adds, deletes or changes records by calling into scripts (that perform data validation checks, etc..), then you can easily "live update" the SQL server. The first step is to modify those FileMaker Pro scripts to also call ODBC EXECUTE to perform equivalent SQL INSERT, UPDATE or DELETE operations (or SQL stored-procedures if your database administrator has them setup) on the remote SQL database, making sure these operations are successful before performing the final add, delete or change operation in FileMaker Pro. Note that for SQL UPDATE operations, you should also add a WHERE clause to make sure that the "modified_at" column of the record being changed does not contain a newer value than the "g_last_sql_synced_at" gloabl variable in the equivalent FileMaker Pro table. (If the UPDATE fails with 0 rows affected, then you should refuse to commit the FileMaker Pro user's changes and offer to resynchronize the table, by Perform-ing the script described below.) However, if your FileMaker Pro solution currently enables users to directly manipulate records (without pushing a button to call your script to perform the actual "commit"), then live-updating will not be an option, but FileMaker Pro will still be maintaining the "modified_at" field behind the scenes in your local tables for you, so can stil do batch-updating of the SQL database, as described below.
Pulling SQL Data: To pull all new and changed data from a SQL table down into the equivalent FileMaker Pro table, create a script that calls the ODBC IMPORT script-step to retrieve from the SQL table's corresponding SQL view (see above) and stores to a temporary FileMaker Pro table. The script then adds all of the new records (whose "added_at" timestamp is newer than the table's "g_last_sql_synced_at" global variable) into the corresponding FileMaker Pro table. Next, the script must know how to deal with records that have been modified on both FileMaker Pro and on the SQL database since the last time it was run. The script goes on to process updates by modifying all of the fields in the records stored in the local FileMaker Pro table with the field values from the equivalent records from the SQL database, now sitting in the temporary table. If you are preserving local FileMaker Pro changes, then the script Finds records in the temporary table whose "modified_ts" timestamp is newer than the "g_last_sql_synced_at" global variable for the table and if the equivalent record in the corresponding FileMaker Pro table's "modified_ts" field is older, then the record is overwritten, if it is newer, then a "reconcile layout" is displayed, highlighting conflicting fields and asking the user which version to approve. If you are discarding local FileMaker Pro changes, then the "reconcile layout" can be ignored and the local record can simply be overwritten anyway. Of course, the last step is to update the "g_last_sql_synced_at" global variable for the table with the system's current timestamp. In this way, all new and changed data on the SQL server is imported to the FileMaker Pro database with each script execution. By using a timed-AppleScript or Windows job, the script can be set to run periodically, so as to minimize the potential for "reconciliation" conflicts.
Handling Server Deletes: Some SQL databases are designed so that they never truly "delete" anything. Instead, when the application (or a SQL stored-procedure) invokes SQL DELETE, a SQL trigger simply moves the target record(s) into an "attic" table for archival. If an attic table is available on the SQL database, it provides a convenient place for us to check for any recently deleted records since the last synchronization was run (by doing an ODBC IMPORT on a VIEW that SQL SELECTs attic records created since the "g_last_sql_synced_at" global variable), where upon those delete operations can be carried out on the local FileMaker Pro table. (Note that a trigger should also be placed this VIEW of the attic table, so that the corresponding timestamp for the attic table in the previously described "fmpro_sync_table" can be updated.) If no attic is available, then the best solution is to create one specifically in support of FileMaker Pro synchronization, being sure to delete all records from the attic table only after your FileMaker Pro script has finished processing it.
Pushing SQL Data: In the case where your current FileMaker Pro solution exploits the features of FileMaker Pro that enable users to directly edit records (rather than pushing a button to run your script to check the data before writing it to the database), you will not be able to perform "live-updates" to the SQL database, as described above. (Note that this is a Good Thing that competing products such as Access don't consider!) Instead, you must prepare another script that periodically Finds all records in your table with an "added_ts" or "modified_ts" newer than "g_last_sql_synced_at" and calls ODBC EXECUTE to either perform a SQL INSERT or UPDATE, or call the equivalent SQL stored-procedure (if your database administrator has them setup). For SQL UPDATE operations, you should also add a WHERE clause to make sure that the "modified_at" column of the record being changed does not contain a newer value than the "g_last_sql_synced_at" global variable in the local FileMaker Pro table. The script should then Perform the above "Pulling SQL Data" script, which will ultimately result in "g_last_sql_synced_at" being updated and handling of any "reconciliation" conflicts.
Handling FMpro Deletes: If "live-updates" are not available, that is, if users of your FileMaker Pro solution are able to use the FileMaker Pro "Delete Record" menu option rather than being forced to push a button that runs a script, then the just described "push SQL data script" must also walk through the entire table to look for gaps in the primary-key values of its records which may signify user deletes. For example, if you have 1000 records and record numbers 500, 700 and 900 are missing, they likely were deleted by the user. In this case, you can pass these ID numbers to the WHERE clause of a SQL DELETE command and process it with the ODBC EXECUTE script-step. (To minimize future SQL DELETE operations, you should check that the DELETE operation was successful and then record the ID numbers of these deleted records in a local FileMaker Pro "attic" table that is consulted when considering these gaps in future script-runs.)
Table Locking: Another matter to consider when "live-updates" are not available is that any inserts, updates and deletes that take place concurrently with the execution of the "push" script might go unnoticed. For some applications, such as a travel website that might only operate during specific hours, it might be acceptable to avoid this problem by running the synchronizations only at night. But for most, especially when dealing with a web database, the only practical solution is to lock the table on the server (using a SQL command issued via ODBC EXECUTE) at the start of the script and unlock it at the end of the script. This opens the potential for leaving the table locked indefinitely if there is a failure of the communications link or the FileMaker Pro system fails. For this reason, a housekeeping job should be running on the server to detect such situations and notify a database administrator or otherwise force the lock to be released. When accessing very large SQL databases with huge numbers of concurrent users, it might not be possible to lock the entire table on the server. In such circumstances, the SQL database will typically provide for page-locking, whereby you can do some calculations to determine which sections of the table you need to lock, so as to minimize the performance-hit on the other users of the server (who would otherwise be waiting for your synchronization script to complete).
Multiple FmPro Users: The above examples assume that there is only one FileMaker Pro database being synchronized to the SQL database. That FileMaker Pro database may very well be shared with many concurrent FileMaker Pro users; however, you may have the case where you have multiple FileMaker Pro databases (perhaps located in different physical locations) each synchronizing against the same SQL database. In this circumstance, you will want to assign each FileMaker Pro database a "user-id" and add a "user-id" column to the "fmpro_sync_table". What's more is that you will have to create the SQL views not just for each table to be synchronized, but for each user of each table to be synchronized (and the associated SELECT triggers that update fmpro_sync_table need to handle that user-id.) So, for example, if you have a table Customers to be synchronized to three FileMaker Pro databases with user-id "Europe", "USA" and "Canada" you would create three views: Customers_Europe_FMPSync, Customers_USA_FMPSync, Customers_Canada_FMPSync and the associated SELECT triggers would update the synchronization timestamp for the record in "fmpro_sync_table" that corresponded to both the user-id ("Europe" or "USA" or "Canada") and the table name ("Customers").
Multiple SQL Servers: The above examples assume that there is only one SQL database being synchronized to. The SQL database may very well be replicated to other servers or shared by many concurrent users or web-surfers; however, you may have the case where you have multiple SQL databases, perhaps different brands of SQL databases (one ORACLE, one SYBASE, one DB/2) that are not able to replicate between each other. In this circumstance, you will want to create a "g_last_sql_synced_at" global variable in each table, for each SQL database you are synchronizing against (eg, "g_last_db2_synced_at", "g_last_oracle_synced_at", etc..) and make your scripts smart enough to take the name of the SQL database (aka ODBC data source) as a parameter. Otherwise, you can use your FileMaker Pro database as sort of an "integration hub" for all of your various SQL databases that cannot otherwise be replicated.