Solved

Want to replicate Filemaker data to web/SQL database.

Posted on 2004-10-05
9
515 Views
Last Modified: 2010-04-27
I have Filemaker server and a Filemaker database that we are using as our in-house database. I just finished developing a subscription based database for the web with nearly identical tables as our in-house database and I would like to set up a data migration plan to move data from the in-house Filemaker database to the web/SQL database on a nightly basis. I currently am using a plan to re-fresh the on-line data monthly, but the updates need to be more regular / daily. Has anyone had to deal with something like this before? Any suggestions would be greatly appreciated!
0
Comment
Question by:johnmoed
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
9 Comments
 
LVL 28

Assisted Solution

by:lesouef
lesouef earned 250 total points
ID: 12230260
There is a planning plug-in you can use to trigger actions at regular intervals (can't remember the name now, but can find it back if you like).
What I do is use the NT schedule service on a PC which runs 24h/day to launch filemaker with a database which has a startup script which does the SQL queries to write to mysql, then closes filemaker. I mark the records loaded in SQL to avoid to do them twice.
0
 

Author Comment

by:johnmoed
ID: 12230800
I was thinking that I would be able to use a nightly SQL script or something that would connect to Filemaker via ODBC. Anything that was new or modified after the last update would be pulled over to SQL and tagged so it wouldn't be pulled twice. Not sure if this is the right way to do it or even how to do it, if this is the route that I will take. If you can remember the name of the plug in that you use, I would like to investigate that option.
Thanks,
John
0
 
LVL 28

Expert Comment

by:lesouef
ID: 12231960
Actually, there are 2 which can do the job.
http://www.wmotion.com/events.html
http://www.troi.com/software/activatorplugin.html
Of course, you could use FM as ODBC server and read data from yr sql application, but it will be slower, FM is very slow as ODBC server unless yr database is really small, and you have to leave a client open 24h/day also.
This may have been improved with v7, but I haven't used it yet in such a situation.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Expert Comment

by:billmercer
ID: 12284129
Generally speaking the way you described would be the "right way". And if you are only transferring a small amount of data at a time, the ODBC option is viable. But Filemaker ODBC is SLOW, and that's the main drawback.

I currently have a system that synchronizes data from a Filemaker database to an MS SQL server automatically several times per day, in basically the same way Lesouef describes. It runs completely unsupervised multiple times per day, and is much faster than using ODBC for the same purpose. The job is scheduled to run every four hours using nnCron Lite from http://www.nncron.ru

Another option would be to publish the filemaker data as a web page, and have your SQL machine pull the data from there on a scheduled basis.

Lesouef, I can confirm that FM 7 ODBC performance is significantly improved. It's now only kind of slow, instead of slower than a myopic slug in a vat of molasses.;)

0
 
LVL 28

Expert Comment

by:lesouef
ID: 12284336
myopic ... molasses: one may read this before lunch! bon appétit!
so definitly not fast...
0
 

Author Comment

by:johnmoed
ID: 12286964
Sorry that I have not gotten back to you on this yet, I have been very busy but I plan to allocate some time later today to explore your suggestions. Which option do you think is the best over ODBC? My database is huge, so it is probably not a good option to use ODBC. If I can schedule an SQL script on the SQL Server to pull new data off of the Filemaker database, that would probably be the best option.
Thanks,
John
0
 
LVL 19

Accepted Solution

by:
billmercer earned 250 total points
ID: 12288212
If you want the process to originate from the SQL server, then aside from ODBC, publishing your data as a web page is the only real approach. Use Filemaker web publishing to present the needed data, then you could create some sort of user agent on the SQL server that hits the page on a regular basis and extracts the data from the HTML table. I know this can be done, but I can't tell you how to do it.
 

If you want to have the fastest possible update, you can put a Filemaker client on the SQL server, and schedule it to run, generate export files in some predetermined folder on the server, then use a stored procedure or something to import the files. I think that would be the most efficient approach, and probably the easiest to program as well.


0
 

Author Comment

by:johnmoed
ID: 12347930
I have been working on a script to export the required fields from Filemaker into files that can then be imported on the SQL server. I haven't had time to test them yet, but I think this is the right way to go.

Thanks!
0
 
LVL 19

Expert Comment

by:billmercer
ID: 12349633
Thanks! I'd be interested to hear your results.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question