Solved

Want to replicate Filemaker data to web/SQL database.

Posted on 2004-10-05
9
518 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
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…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

622 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