IT Staff Claims FM Cant Select Table

[CCBtx]

I have asked my IT staff to create an ODBC frontend to mysql. Our mysql database has about 150 tables. I am wanting to construct a report that will run every day. It will use about 20 tables. They are telling me that filemaker can't programatically select a table. In essence, they are saying they have to select each table manually rather than in a script. Is this correct?

[Genx]

If i'm understanding your question correctly... they probably just don't like FileMaker.

FileMaker has a script step called "Import Data"

You can dynamically generate the actual statement and loop through any given list of tables or whatever you wanted... e.g.

SetVariable[ $tables ; "customers|orders|people" ]

SetVariable[ $tables ; Substitute($tables ; "|" ; ¶ ]

SetVariable[ $i ; ValueCount($tables) ]

Loop

Exit Loop If[ $i ; 0 ]

Import Records[DNS: "mySQLDatabase" ; Calculated SQL Text: "Select * From " & GetValue($tables ; $i)]

Set Variable[ $i ; $i + 1]

End Loop

The above will generate and execute the following SQL statements and import the data form the result of those statements:

Select * FROM customers

Select * FROM orders

Select * FROM people

You can ofcourse make it more complicated add actual search criteria into there using WHERE and practically dyamically generate any sql statement you need.

[CCBtx]

Ahhhhhh. My suspiscion confirmed.

I have done a lot in filemaker... just not with sql yet. So, when the youngsters say it can't be done, I ask you guys.

Thanks a lot.

[Genx]

Hey I'm a youngster!

But glad to help .

[CCBtx]

Yes. I saw that after I wrote that. Impressive.

[Genx]

Hey, I wasn't gloating, just saying we're not all so rash :)