FileMaker Pro's External SQL Source (ESS) is among the most important developer-centric features in the current version (v9). I love ESS. But I am a bit wary of some of the practices I am seeing. First, let me explain how we generally use ESS.
ESS is great for reading data from a SQL source. As a Drupal and FileMaker shop, we do quite a bit of work in helping people get data back and forth between FileMaker and mySQL or MSSQL. Now, the getting data 'back' to FileMaker bit is fairly straightforward. Yes, there are all kinds of quirky mysql compatibility issues that one might have to fight through, but, in general, reading ESS tables is not too scary.
Writing to a SQL table with ESS is quite another ball-of-wax. I generally discourage the practice. Security concerns aside, the problem with writing directly to any application's database is that you are bypassing any logic that is handled at the application level such as required workflow actions, notifications or secondary updates.
If you are lucky, your actions might wind up causing some minor annoyances--perhaps some minor data weirdness that some clean-up, caching-dumping, re-indexing process will automagically cure. If you are unlucky, direct writes to a mysql table could lead to some nasty surprises, even catastrophic data loss.
One real world example. We've noticed that Drupal 6 is now more sophisticated about node-revisions. As a result, editing the mysql node context directly over ESS won't get you your expected results. This is because writing over ESS doesn't update what the application layer chooses to display.
This isn't to say writing to any mysql table with ESS is a bad thing. I am, however, saying that if you are writing to a database that belongs to a complex application, be very, very careful. Unless you can predict the behavior of your actions, it is best to write to your ESS based tables with great care, or preferably not at all.
Thanks!
Ernest, thanks for this article! I will forward it to my developer to make sure he takes it into consideration when designing my new website. Thanks once again!
Affects any Drupal module that implements hook_nodeapi
If your site uses any modules (core or third-party) that implement hook_nodeapi() you should not write directly to the node table via ESS; Drupal has various functions, including node_submit() and node_save() which contain logic allowing any number of other modules to alter a node, save additional data in other tables, or do things like send e-mail notifications when that node is saved. If you write directly to the node table you will completely bypass all of that logic.
It's also worth noting that hook_nodeapi() also supports the loading of additional data into the node object as it is being prepared for display - so even if you're using ESS in a read-only capacity for viewing Drupal node data, you should keep in mind that your results may be different than when the same data gets loaded by Drupal; it depends on what modules you have installed and if/how they implement hook_nodeapi().
Post new comment