Solved

Want to replicate Filemaker data to web/SQL database.

Posted on 2004-10-05
9
504 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
  • 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now