Thread: FileMakerPro to postgreSQL
I have to update a database system and need some advice.
Most of the database work I've done up to now has been databased backed
websites, using perl and/or php to connect to mSQL, mySQL, or postgreSQL.
One job that I inherited a few years ago was actually two separate
databases, one an in-office FileMakerPro database client/server system and
the other their website. They entered the data separately into each. At
that time they didn't want to make any changes to the FileMakerPro system
already in place, so I wrote a bridge between the two. Basically the FMP
database FTPs the data overnight to the web server and a perl script
checks and ingests the data into the website mySQL database.
Over the years their business has grown, they've been adding to their FMP
database system and I've been adding features to the website. Now I've
got the opportunity to convert to a single database. (Of course, I favor
postgreSQL or I wouldn't be writing this here!)
I found FMPmigrator, a program that appears to do the conversion from FMP
to any popular database (including postgreSQL) which appears quite
promising. The largest table is only now approaching 100,000 rows; It's
not a very big database, but it does contain jpgs.
My biggest concern is the existing FileMakerPro clients. How much work
would it be to connect the existing FMP clients to the postgreSQL database
using ODBC? Is this a viable temporary step or could it be permanent?
If they decide to go forward with this I'll talk to the FMPmigrator
people, but I thought I'd just float this out on the list and see what
other ways exist that I haven't thought of.
Anybody know of a great book for me to learn more about database
client/server architecture? Besides perl and php I also know C and basic,
although I haven't used those in a while, and I've fooled around with
java, so I won't totally rule out writing new client software. Also, I've
read here that using access might be a good quick choice. But if I could
use the existing FileMakerPro clients that might be fastest, eh?
brew
==========================================================================
Strange Brew (brew@theMode.com)
Check out my Stock Option Covered Call website http://www.callpix.com
and my Musician's Online Database Exchange http://www.TheMode.com
==========================================================================
brew@theMode.com writes:
> ... My biggest concern is the existing FileMakerPro clients. How much work
> would it be to connect the existing FMP clients to the postgreSQL database
> using ODBC? Is this a viable temporary step or could it be permanent?
If they can speak ODBC, then in theory you can easily connect them to PG
that way. There's a fair-size gap between theory and practice, however
--- for example, the clients might be depending on some nonstandard SQL
syntax. The only way to really check this out is to test the clients
against PG and see what comes up. See the pgsql-odbc list archives for
some examples of gotchas.
regards, tom lane
Yes filemaker can use ODBC but not as a live data source as you would use access as a frontend. You can use ODBC to import the data to a Filemaker database and display/edit it there and then export back through ODBC. If they use Filemaker to enter all the data (none is entered from the web site) then you can setup a script to export the data through ODBC to update the website. Either one record at a time after it is entered or as a batch one or more times a day. If information is entered into the website then you will also need a script to import the data from the website database. On 18/9/2006 11:58, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: > brew@theMode.com writes: >> ... My biggest concern is the existing FileMakerPro clients. How much work >> would it be to connect the existing FMP clients to the postgreSQL database >> using ODBC? Is this a viable temporary step or could it be permanent? > > If they can speak ODBC, then in theory you can easily connect them to PG > that way. There's a fair-size gap between theory and practice, however > --- for example, the clients might be depending on some nonstandard SQL > syntax. The only way to really check this out is to test the clients > against PG and see what comes up. See the pgsql-odbc list archives for > some examples of gotchas. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > -- Shane Ambler Postgres@007Marketing.com Get Sheeky @ http://Sheeky.Biz
brew@theMode.com writes:
> > ... My biggest concern is the existing FileMakerPro clients. How much
> > work would it be to connect the existing FMP clients to the postgreSQL
> > database using ODBC? Is this a viable temporary step or could it be
> > permanent?
And Tom Lane replies:
> If they can speak ODBC, then in theory you can easily connect them to PG
> that way. There's a fair-size gap between theory and practice,
> however....
And Shane Ambler wrote:
> Yes filemaker can use ODBC but not as a live data source as you would
> use access as a frontend. You can use ODBC to import the data to a
> Filemaker database and display/edit it there and then export back
> through ODBC.
If the client wants to go forward on this I'm leaning toward moving away
from FileMakerPro completely and starting from scratch. In the long run
it'll probably be less work and result in a better end product anyway.
Thanks for your thoughs and info, Tom and Shane.
brew
==========================================================================
Strange Brew (brew@theMode.com)
Check out my Stock Option Covered Call website http://www.callpix.com
and my Musician's Online Database Exchange http://www.TheMode.com
==========================================================================