Solved

Want to replicate Filemaker data to web/SQL database.

Posted on 2004-10-05
9
512 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

Independent Software Vendors: 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!

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…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

756 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