Importing Records via System DSN

[DGEE]

Greetings. I've been going through some FM9 Pro tutorials on lynda.com and have been able to setup a ODBC system DSN with Actual Technologies plug-in that connects to our mySQL database, but it's connecting in real time (which is very cool), but the solution needs to 'import' those live records so that if any from the mySQL database are deleted, I still have that record locally.

When I try to set up a USER DSN FM9 Pro doesn't see that external ODBC connection. Can I configure a System DSN to import the records? I'm using OS X.4

Thanks in advance.

[mr_vodka]

Connecting "live" using ESS is seperate from setting up an ODBC import. If you want to strictly import rather than use ESS, then you dont even need to use Manage --> External Data Sources. You can just specify the ODBC DSN in the import script step.

P.S. I am not sure if there is something in addition specially for Macs.

[pmconaway]

Set them up in a "System DSN" Those are the only data sources that show up in script steps. I ran into the same problem myself.

[mr_vodka]

DGEE already got that to work. He has the opposite issue that you had. :)

[pmconaway]

When doing the import script step, which I'm assuming is what he is trying to should still be able to pull from the System DSN configurations. Those are the only ones that showed up for me when I was creating my scripts.

I thought that said he set them up in the USER DSN.

Edited June 12, 2008 by Guest

[DGEE]

Thanks John.

I tried that solution, but on importing the SQL file, it only displays the one field in the import window. I am not sure what's going on there...

But having surfed around a bit, I wonder if I may be coming at this from the wrong side --

A bigger issue is that we remove older files from the SQL db on a regular basis, but want a means to keep them in our local db for future reference.

I have two exact same table layouts & fields. One layout is fed from the mySQL database in real time...I would like to simply copy all records from that table layout to the other.

That way, as records are deleted on-line, we still have copies in the other table layout. However, I don't see a script step to copy and past entire records... any suggestions on that?

Thanks in advance.

-Derek

Edited June 12, 2008 by Guest

[elo]

This might be better accomplished outside FileMaker Pro. I wrote a Ruby script that goes more or less the opposite direction (FileMaker -> SQL Server 2005 cloning, drops the SQL Server 2005 tables, re-creates them, and INSERTS the records from FMPro) it does all of this by holding open two ODBC connections using ADO (COM/Win32OLE flavor, but ADO.NET could work too).

FileMaker's ESS/ODBC support from within the application isn't all that suited to deleting the records from the source.

If you want my code I posted it here already, but can repost.

But, I would suggest a simple VB/VB.NET application or Ruby script that holds open two ODBC connections.

This will let you focus on the business logic which really has three parts and a lot of error checking:

(1) Extract data from SQL Server [sELECT command]

(2) Insert data into FileMaker Pro [iNSERT commands]

(3) Careful deletion of the data from SQL server

I'm not even sure you want to do step 3 if your DB admins regularly purge, you might want to instead do something like

(0.5) Query FileMaker for the ID's (primary keys) you already have

(1) Extract only new records from DB (select * FROM tablename WHERE primarykey NOT IN (comma separated list from step 0.5, e.g. 1, 2, 3, 4, etc)

(2) Insert into FMPro

(3) no 3

Or use date ranges, etc.

There's a lot of logic here and again, I'm pretty skeptical that FMPro is the place to implement this logic. Further, if you write a Ruby/VB whatever you can create a FMPro button that calls a script that invokes your application.