Filemaker MS SQL -> Filemaker as Client, backend MSSQL

[rich williams]

Hi All,

I am an old time FM user. Versions 4-6. I stopped developing in FM about 6 or more years ago. I am now a .Net developer.

I miss the ease of creating FM layouts. But hated the lack of in memory variables, non separation of BL, DB and UI..

I know many of these issues have been dealt with in one way or another.

SO i have downloaded version 11. I am specifically trying to figure out if and how i can create a UI frontend that runs completely off of MS SQL DB.

In the olden days i used ODBC DSNs to connect to databases to make queries. It worked but was a pain as i was basically importing and exporting result sets.

A few years ago, maybe version 9 i look at a promo video which showed some sort of FM built in ER diagram thing that showed external datasource tables along side FM "tables". It also seemed as though they were saying tables were no longer tied to layouts..

Ok off topic. My question is.. Are there NATIVE data providers in FM for MS SQL? Are we still using ODBC? Is the connection live?? meaning.. do we have to manually trigger SQL statements that read and write data??

I just imagine the day i can connect to MS SQL or MySQL and dump the tables i want in an FM ER diagram.. and then go about creating my layouts and have FM control the data access.

Is this the case?? Can i add external tables (whether ODBC or some native provider) and build layouts against them as if they were FM tables?

And have the data i change in the layout automatically be sent to the external source without triggering a manually created SQL query (insert/update). Can i define a relationship in FM that exists in the MS SQL db and automatically have things like portals work?

If so can someone please let me know what versions of FM i would need to accomplish this? Pro? Pro advanced? Server?

Thanks! I hope this makes sense.. i really hope this is possible because FM is hands down the easiest and fastest way to create a front end for a DB.

[Jonathan Monroe]

The fundamental concept to keep in mind with FM's "External SQL Sources" (ESS) feature is:

It lets you work with a SQL table as if it were a FM table.

That means you can make a layout based on the SQL table, and you can reference the SQL table in your FM scripts. ESS takes care of creating and executing the SQL statements needed to make the SQL table act like a FM table. You have no visibility or control over the SQL statements it generates.

ESS only uses ODBC drivers for connecting to the SQL database, and only supports 3 DBMS: MySQL, Oracle and MS SQL Server.

It is supported by both FMP and FM Server. A nice benefit is that, when used with FMS, the ODBC driver only needs to be installed on the server. FMS handles all the ODBC connections on behalf of the FMP clients.

Jonathan Monroe

Actual Technologies - ODBC for Mac OS X

www.actualtech.com

[rich williams]

Thanks for the reply.

So i've done some playing around.. And have to say. AMAZING.

"I just imagine the day i can connect to MS SQL or MySQL and dump the tables i want in an FM ER diagram.. and then go about creating my layouts and have FM control the data access. " <-- AND THIS IS EXACTLY WHAT IT'S ABLE TO DO!

FileMaker has come a long way. This is a MAJOR improvement. MAJOR. I am really impressed. Working on something right now and have to say that it is an absolute pleasure being able to work like this.

I have setup my ODBC connection to my SQL 2008 db. Dumped all my tables into the FM ER builder/diagram thingy, defined all my relationships and voila. I am amazed at how fast the access to this remote SQL server is. Smoking.

I am so sick of dealing with web based front-ends.. And Silverlight, Flex.. i've worked with them all. And each has their place. But for straight ahead DB apps.. Filemaker is the *******. They should call me to do a commercial. When i walked away from FM i thought i would never come back. Amazing.