connecting FMP and SQL databases

[Rothko]

We have an office-based FileMaker contacts database which includes some sales management, as well as printing functionality etc. (Typical case for Filemaker.)

The client also has a new website, where people can sign up to an email newsletter. This is based on an SQL database.

I need to somehow connect the two, so that the client can enter some contacts locally into the FMP database, to receive the newsletter. Likewise, people having signed up on the website should be manageable via FMP.

How best to do this?

I'd prefer not to get involved with synching-routines if I can avoid it.

One way would be to use FMP as a frontend to the SQL database. But I'm worried about speed issues, even if there's only 5-10.000 records to be managed. Or should I not worry?

This post here seems to suggest to have the FMP database hosted remotely. So that the bottleneck is not between FM Server and SQL Server, but between FM Clients and Server (if I understand it correctly).

Should I not worry about speed? Any other ideas?

Many thanks.

[Josh Ormond]

It is referring to using FM as a front-end to the SQL db. There is one part in there about hosting the FM Server file on the same server with the ESS datasource, but it make not make a difference for you.

Here is a quick brief on ESS Connections in FM, and at the bottom is a link to instructions and, I believe, even a video.

ESS will allow you to connect directly to the SQL db and make changes live. (There are some small things you need to handle differently, but no big issues)

[mr_vodka]

One thing to keep in mind is that if you are in table view with a lot of records, it will take a little while to display it via ESS ( will do active find ). Using ESS in form view works pretty well since it only displays info for one record at a time.

[Rothko]

Hmm I think I may have to reconsider. We can't afford the server version of the Actual Technologies driver plus FMS Advanced; also I keep reading warnings not to use FM as a front-end to SQL databases.

So it might have to be an import-export routine after all. At least I wouldn't have to worry about speed issues, having the FM database hosted externally, etc.

The SQL database has records added to it, but once entered these never change. So a synchronizing routine could look like this (correct me if I'm wrong)

1. Import from ODBC source, update matching records, add remaining as new

2. Delete all records on SQL database

3. Copy all records from FM to SQL database

This would have to be run every night, or by launching a script manually.

[mr_vodka]

Why are you deleting the records? You should be just able to import update according to what you have stated.

BTW, If you have an modification date on the SQL side, you can store when the last time you updated was in a field in FileMaker and then tweak your SQl statement so it only pulls the newly modified stuff, or in your case since they dont modify the records, you can pull by creation date or better yet even a unique ID.

[Josh Ormond]

Hmm I think I may have to reconsider. We can't afford the server version of the Actual Technologies driver plus FMS Advanced; also I keep reading warnings not to use FM as a front-end to SQL databases.

Yeah, I have read that too. But we are using FM as a front-end. And it works fine. You just have to know that you may need to use the "Refresh Window" script step under certain case to refresh the data showing in FM (if there are some other means of altering those records). And that field values can't be indexed like in a native FM table.

As for speed issues, have seen any. Except for what mr_vodka mentioned. The more records you try to show at once, the longer it will take to display. Although, 10,000 records isn't bad, at all.

If the SQL server is setup already, and you have FM 9, test it out. The open-source driver from Actual Technologies is on $30.

Obviously, the import/export thing is an option also. We use that to feed the db that drives our website data.

[Rothko]

Why are you deleting the records? You should be just able to import update according to what you have stated.

since they dont modify the records, you can pull by creation date or better yet even a unique ID.

No, records aren't modified on the SQL side, so I can just import with the ID as matching field.

Hang on - should I import or pull using SQL statements? That's not something I'm familiar with at all.

Why are you deleting the records? You should be just able to import update according to what you have stated.

Isn't that easier? This way I don't have to check which records are already present on the SQL db.

[mr_vodka]

When you do an ODBC import, you have to specify a SQL statement. If you use it as part of a script, you can designate a FileMaker calculation to generate the SQl statement for you.

[Rothko]

Thanks for your input. After much fussing and confusion it looks like this now. This synching routine should run on just one machine:

1. Set up an ESS connection, with remote SQL table showing up in relationships graph.

2. All local FM records have a "last-modified"-timestamp

3. Import script checks if there are any records on the SQL side with the timestamp field empty, and imports them. (The timestamp is assigned by FM. If an SQL record has the timestamp field empty, it means it is new and doesn't exist yet in FM.)

4. The next step is to delete all records on the SQL side after downloading the latest additions.

Reason: If a record is deleted in FM, there is no way that can be also done in the corresponding SQL database (as there is only one machine with ESS set up. I don't want a constant connection to the SQL side, so that the local FM users can still work even if the internet or the SQL host is down).

5. FM then copies the whole set back to the SQL side. I am talking about 10-20.000 records, plain text, so that shouldn't take too long.

Right? Again, thanks for your thoughts.

[Rothko]

I want this script to run on FM Server. Apparently ESS doesn't work there, so here's a way to run it via Import ODBC source.

1. All local FM records have a "last-modified"-timestamp

2. Import from ODBC source. A WHERE-statement checks if there are any records on the SQL side with the timestamp field empty, and imports them. (The timestamp is assigned by FM. If an SQL record has the timestamp field empty, it means it is new and doesn't exist yet in FM.)

3. The next step is to delete all records on the SQL side after downloading the latest additions.

Reason: If a record is deleted in FM, there is no way that can be also done in the corresponding SQL database (as there is only one machine with ESS set up. I don't want a constant connection to the SQL side, so that the local FM users can still work even if the internet or the SQL host is down).

(hmm, how to do this? It will have to be server-scripting-compatible. ESS is ruled out. SQL statements is greyed out in script manager, too.)

4. FM then copies the whole set back to the SQL side. I am talking about 10-20.000 records, plain text, so that shouldn't take too long.

Looks like what I'm trying to do can't be run as a server-side script, as it involves the deletion and/or updating of SQL records. And 'Execute SQL', ESS and even issuing PHP commands via 'Open URL' all aren't server-compatible.

So this is it then:

- All local FM records have a "last-modified"-timestamp

- Either manually, or e.g. once a day this script is launched, on whichever client machine:

1. Import from ODBC source. A WHERE-statement checks if there are any records on the SQL side with the timestamp field empty, and imports them. (The timestamp is assigned by FM. If an SQL record has the timestamp field empty, it means it is new and doesn't exist yet in FM.)

2. The next step is to delete all records on the SQL side after downloading the latest additions.

3. FM then copies the whole set back to the SQL side. I am talking about 10-20.000 records, plain text, so that shouldn't take too long.

Is there a way to do step 2. and 3. not via ESS (as we can't afford FMS Advanced), but by Execute SQL? Or via a PHP command through 'Open URL'?

Edited April 1, 2009 by Guest

[fmsocko]

Rothko and I had a conversation about this on IRC #fmpro.

ESS: FMP connects to two revs of the "Community" version of MySQL. Unless you're installing MySQL on your own server, this is unlikely to be your working version.

I recommended Rothko consider single record updates (as opposed to dropping the entire table on each "sync"), using an XML import whose URL contains the data to be updated. Using FMP 10, this could easily be done on a record, or even field change basis if needed. The import portion of the request contains only verification of success. Any number of server side scripting languages could be used to accomplish this. I use PHP.

If folks are interested in this technique, email me from my web site. If I get a dozen requests or so, I'll post an example on my blog.

[Rothko]

after some further IRC'ing with a very helpful fmsocko I'm going to try to run the script as above, but from a client machine, using ESS. I won't have to buy FMS Advanced, and only one license for the ODBC driver.