FMSA11 Server-side imports/exports with MySQL ESS

[xochi]

I'm trying out FMSA11 and trying to get server-side imports working using an external ESS MySQL data source. I have a single file "database" that contains several tables, one of which is the ESS sql database.

My goal was to simply look for new records in the sql database, and then import these to another table.

I tried to import directly (using "file:database" ) as the source. This gives a 100 error ("File Is Missing" error). From reading the forums, it's not clear whether importing from one table to another is possible server-side? (NB: my database is named "database.fp7" and lives in a subfolder on the server e.g. /Library/FileMaker Server/Data/Databases/MyDatabase/database.fp7 However I tried variations such as "file:database", "file:database.fp7" and "file:MyDatabase/database.fp7" but none of these worked, all giving the 100 error.

As a workaround, I tried to export to an XLS file first:

SetVariable $path = "filemac:" & Get(DocumentsPath) & "Test.xls"

ExportRecords No Dialog "$path"

This didn't work (no errors, but no exported file, either).

Finally, I tried exporting to a plain Text file

SetVariable $path = "filemac:" & Get(DocumentsPath) & "Test.text"

ExportRecords No Dialog "$path"

This works fine -- the MySQL data is exported to the text file. This suggests that my ESS is working properly.

So I'm a bit confused:

(1) Should I be able to, server-side, import records directly from an ESS table into another table in the same database? Or not?

(2) If I have to use the workaround (export to database in the server's documents path, then re-import) -- why is XLS format not working for export? Is tab-delimited text a decent compromise or should I use CSV?

[Steven H. Blackwell]

(1) Should I be able to, server-side, import records directly from an ESS table into another table in the same database? Or not?

(2) If I have to use the workaround (export to database in the server's documents path, then re-import) -- why is XLS format not working for export? Is tab-delimited text a decent compromise or should I use CSV?

#1. No. Table to table within a file is not supported.

#2. Please see this article in the Forums.

Steven

[xochi]

Thanks Steven,

Re: #2 - Not sure I see the answer. I'm finding that .text and .csv work fine, but exporting to .xls server-side doesn't seem to work for me.

[mr_vodka]

Why dont you just import via ODBC?

[xochi]

Is that possible? When I select "ODBC" as the import source, there are no data sources shown on my local FM 11 machine (which makes sense, as the ESS is set up as a System DSN on the FM Server machine).

I need to have this run in a server-scheduled script. Is there some trick to set it up so it'd work both on server-scheduled script and on FM client ?

[mr_vodka]

Sure. Set it up with the same DSN ( named the same as on your server ) on your client machine. Then script the import.

Once you have the import working and tested locally, set it up to run server side.

[Steven H. Blackwell]

The article is intended to point out the correct locations for import/export.

Steven

[xochi]

Thanks for the suggestions, I'll give that a try. I wonder, is it safe to use both ODBC import, AND have an ESS data source in the same file?

[mr_vodka]

I think so especially if you are doing some kind of nightly batch process where the users are not really in it etc.

[wbr]

We have a very complex Filemaker system that transfers data OUT to a MySQL database, and also back IN to Filemaker from the same MySQL database. I have tried just about every possible technology for making this happen reliably and quickly, and I have to say the results are discouraging. Here is what I recall from the endeavor, using Filemaker client & server, 9 & 10. About 6 months ago, we abandoned all Filemaker ODBC technologies, and I have since forgotten many of the painful details. So apologies (and please correct me) for any inaccuracies.

1. It is true that you cannot import ESS-to-FMP in a server-side FM script. I recall that in FM client, it might be possible to import FMP-to-FMP.

2. Using FM Server's server-side import/export features require that you have your data placed in the exact location that FM Server expects, and that you have the permissions of that directory and your data files set exactly as FM Server expects. There is documentation (possibly mentioned in one of the previous posts) that explains this, though I remember some trial-and-error to get it just right.

3. Prior to Filemaker 11, using ODBC to access FM data was flakey and slow with inadequate documentation to get it working reliably. Drivers are only available for Mac & Windows - no Linux or BSD. FM 11 is supposed to offer improvements in this area.

4. ESS and ODBC-import can use the same drivers but appear to use different code libraries within Filemaker, sporting slightly different features. Your drivers must be set up exactly the same on FM server and FM client.

5. On Mac, you pretty much have one driver option to access MySQL, and you must pay for a license for each connected instance (the base purchase we made allowed us fewer than 10 concurrent connections).

6. Every FM ESS table of every FM user will create it's own ESS ODBC connection, on demand, even if you are using the server-side ODBC driver installation with FM Server based files. These ESS ODBC connections will stay open until the user closes the file containing the ESS table that opened the connection. We immediately exceeded our licensed allotment with 10+ ESS tables and 40+ users, with the resulting dialog giving no information as to the issue at hand. (I think it threw a password dialog, leading us to believe we had authentication issues.)

7. The ODBC-import feature, when used server-side, only opens a connection to the SQL database for the duration of the ODBC-import script step. Much friendlier for ODBC licensing.

8. Using ODBC-import with MySQL prepared statements was extremely difficult to set up and required undocumented workarounds in Filemaker.

Filemaker's ESS and ODBC features are very cool, and work beautifully when set up for simple situations. But we ultimately abandoned these features due to flakiness in complex setups, lack of adequate features, lack of adequate documentation, lack of 3rd party support, and poor scaleability. Here is what we are currently doing instead, all handled automatically by cron scripts or FM Server schedules.

To move data from Filemaker into MySQL: A shell script on our MySQL server logs into a headless FM client (egad!) and triggers a FM script which exports all the necessary FM data. The data is then zipped and pulled back onto the MySQL server, where it is massaged with a ruby script, then imported into MySQL using the mysqlimport command. This all works very quickly and is fairly reliable (the weak link is Applescripting FM client). In the future we intend to move the exporting off of the headless FM client and onto FM Server and/or use a ruby script (and the brilliant RFM) on the MySQL server to handle the entire transfer.

To move data from MySQL to Filemaker, we have a FM Server schedule trigger a shell script that queries a URL on the MySQL server and downloads the MySQL data to the required FM Server directory location, where it is then imported into Filemaker with a subsequent FM Server script. The URL that is queried is part of a Rails application and triggers a series of MySQL prepared statements that compile the data exactly as we need and package it into a downloadable zip file. The process is quick and rock solid.

To move small amounts of data between Filemaker and MySQL, one record at a time, we use the Rails application (and RFM) to talk to both databases. The Rails app is the hub of communications between the Filemaker system, MySQL, and the web site that interacts with both.

One thing I've learned from this (and other similar situations with other clients) is that synchronization between databases is a huge PITA! I hope that the future sees great improvements to Filemaker's ODBC features, allowing ESS to provide a high-performance robust front end for SQL databases. On the ruby side, with improved Filemaker ODBC features, I hope we eventually see an ActiveRecord driver for Filemaker. I look forward to hearing more about FM 11's ODBC improvements.

Bill

[liltbrockie]

I agree with Bill!

ESS and obdc support is not robust or quick enough to meet our needs.

[Peter Wagemans]

Had the same problem with a development that does a nightly update of the mirror FileMaker tables of a MS SQL database. Allthough ESS is possible and running in server side scripts, you all of a sudden discover that FileMaker did not go all the way in supporting it server side. Thanks FileMaker, for making me look bad in front of my customer.

 

ODBC doesn't cut it either, not compatible. What left me with "set field" scripts for a large number of tables. Oh my gosh.

 

But then I discovered that the DoSQL 2 plug-in is server side compatible with ESS tables. I used it to turn my import scripts into INSERT SELECT statements and they worked flawlessly on the server side. I installed the plug-in using a server side script, but noticed I still had to enable it using the admin console. No problem though, after enabling it there, it just worked.

 

For most tables, I had an identical structure for the Microsoft SQL tables and the FileMaker tables. So an INSERT INTO <filemakerTable> ( SELECT * FROM <ESS MSSQL TABLE> ) worked nicely.

For some other tables I had defined some extra fields in the FIleMaker tables, and the DoSQL 2 plug-in alerted me correctly when trying that, that the number of fields did not match.

But since the field names matched, a custom function that created the SQL for all fields present in both the source and target table was quickly constructed.

Kinda cool. You are actually talking SQL to FIleMaker who translates the SQL into other SQL to talk to the ESS table. I tested DoSQL 2 already with ESS tables on the client side, but was happily surprised it worked also very well in server side scripts.

 

One thing though. Regardless of the technology used to get the data into the tables, regular import, ODBC import or the DoSQL way. It's easy to make the following mistake:

I copied the ESS tables in the field definitions window and just pasted them again, creating local FileMaker tables, and considered myself pretty smart for having found a very fast way to create the local tables.

Until I started importing data. Glacial. I then removed all extra bells and whistles from the fields, all the verification stuff, and turned the indexing to automatically again, because locally I'm using these tables for relational reports. So much for my time saving, it took more time to remove all this, then if I had just created the tables from scratch. After modifying them, importing is much much faster.

Not lightning fast, but sufficiently fast since the server side script has all night to do the job.

 

So I was finally able to deliver what I promised to the customer. in my initial analysis I did not count in the additional cost of the plug-in though, not to mention the hours I lost trying to solve the problem I unexpectedly ran in to.

[Wim Decorte]

Good info, Peter.  What was the performance like with the plugin?  How many records in how much time?

[Peter Wagemans]

Hi Wim,

Thanks!

I have been testing some more today. I downloaded the free SQLExpress 2012 server and put it together with a server 12 on a Windows 7 X64 virtual machine. I also downloaded the AdventureWorks database, attached it, and made an ESS connections to it.

Then from my Mac, I then put all the 142 tables in my FRG, and made a few scripts and tables to support listing tables and fields. Finally I wrote some code to generate local tables on demand automatically, and do the inserts. The finishing touch was to put some timers on it.

Here are my results:

It took the script about 1 hour and 9 minutes to create and populate the tables, when I ran it from my Mac. As the VM is on the same machine, this is not an optimal setup for speed, but it's quite a fancy i7 PowerBook with an SSD, so this setup not so unrealistic.

The CREATE TABLE command also has some defaults for fields I do not like, as I presumed ( above ) that all those extra field checks slow down the INSERTs.

So I created an global option not to create tables and work with existing tables, copied the created FileMaker tables in my other favourite tool :-) and stripped out the options in the XML, then replacied the tables again.

Since I did not have much time left today - I immediately went into testing the script server side, after installing the DoSQL 2 plug-in on the server.

I was very very surprised that leaving out the options in the fields did not give me any speed improvement.

The script is almost done now, and I see I'm going to finish at around the same benchmark time as when it created the tables automatically.

 

The AdventureWorks ESS setup is a nice testing environment, and easy to reproduce. If you want, I can mail you the FileMaker file with the create and insert scripts.

Can I enclose stuff here? I don't dare making a backup now while the script is running, I guess it's not a good idea.

O yes, a bit of topic, the Production.Document table and the Production.ProductDocument table contain some fields that are not compatible with ESS. So the INSERT does not work of course for those tables.

[Peter Wagemans]

BTW, I just tested the imports with the 360Works JDBC plug-in. When I use the queries with the jdbcXmlImportUrl function, the imports are blazing fast, but there's a few problems I ran into.

1. I have to hardcode all the imports, but this can be overcome, it's just a hassle

2. The MSSQL JDBC driver is not working correctly on OSX, but this can be overcome, the imports have to run on the server side, and that's a windows machine

3. The JDBC plug-in does not activate on the server. This is a show stopper. I posted a question on the 360Works subforum, maybe they can fix it.