SQL as backend?

[seadragon]

So... the integration of external data is one of the most exciting features to me.

Correct me if I'm wrong, but can we now use FM as a front end to a full SQL backend?

I especially like that you can create unstored calculation fields in the external data table field definitions.

I think this opens up some really cool possibilities.

[TimD]

Same here as far as the support for external SQL sources (ESS) being the most exciting new feature in 9. They did a great job implementing this feature -- and the ability to "extend" the schemas of the external tables is a powerful and surprising bonus.

FileMaker has stated that they are not trying to position FM as a tool for developing front-end apps for back-end SQL databases. But I agree with you that this can certainly be done -- and I'm already doing it. I've got three small FM9-based apps that are acting as front-ends to SQL Server databases. All of the insert/update/deletes are scripted, and they use Execute SQL to call stored procedures to perform those types of things. It is working perfectly.

I'd love to hear how other developers are using the new ESS feature.

[Steven H. Blackwell]

Correct me if I'm wrong, but can we now use FM as a front end to a full SQL backend?

Not exactly. But you can have live access to the data in the external SQL source file.

Steven

[fmpwnerer]

The word I received directly from FileMaker is that this is not the feature's intended use. They also specifically stated this is not how the feature should be used. Limitations and performance prevent FileMaker from being a good front end.

The real draw to this new feature is using FileMaker's report wizards to create reports in FileMaker for data in MS SQL, MySQL and Oracle. Creating good looking reports its by far easier inside FileMaker (now reliably printing those reports.. thats a different story).

Unfortunately, virtually every developer I've spoken with intends to use this new feature to allow FileMaker to be a front end. I wish them luck down the road when they run into performance issues.

[Frankywinky]

I've made it a complete MySQL server with FM clients solution and it works like a dream.

It sure is a little less performant than with FileMaker bases. Each field takes a certain amount of time to show up, and with a list view, it can take up to .5 second to show up a page (not too much annoying). I suspect FileMaker 9 to make up a different SELECT clause for each field access it makes...

My only big performance issue was with aggregates functions. It were really slow with two or three linked tables and with thousands of records. So, in this case, it was easy to speed up by making VIEWs in MySQL. The performances are great now. And the other normal fields are still editable, as you can make a link on the original tables and the fields from the view with FileMaker relationships.

It works really well, with acceptables performances. I'm very happy with the result.

[Genx]

Thanks for the Feedback Franky.

[seadragon]

Yes, thanks Franky!

[Steven H. Blackwell]

I suggest that you read the Tech Brief on ESS and FileMaker 9, found at Tech Info 6566 on the FMI web site.

Steven

[Genx]

What you are referring to Steven is, and correct me if I'm wrong, this paragraph:

The emphasis with ESS should be on integration. The ESS feature set is not intended to allow FileMaker Pro to act as a “front end” to SQL data sources. In particular:

- ESS does not allow a FileMaker Pro developer to compose their own SQL queries and pass them to the server...

-ESS does not allow a FileMaker Pro developer to alter or extend the schema

of an SQL data source on the server side...

- ESS is not designed as a means to allow a FileMaker Pro solution to scale beyond the limits of a purely FileMaker Pro based solution. The ESS feature set is designed to emphasize the seamless integration of SQL-based tables into a FileMaker Pro solution, rather than to take specific advantage of the high-scalability features of SQL back ends.

So really that says don't use ESS as a front end to SQL if:

1) You want to be able to execute custom SQL commands to retrieve the data.

2) You don't know how to modify the sql schema using the providers interface (or any other such GUI that communicates using ODBC) or basic SQL commands.

3) You want to use ESS to take FileMaker beyond "the limits of a purely FileMaker based solution".

Personally to me that implies: Go ahead and integrate FileMaker as an interface front-end to a SQL database as long as you know what you're doing in SQL and as long as you keep it to a reasonable limit that FM can handle.

I will ackoweldge that the idea of this feature is likely smaller levels of integration to the tune of viewing related sales to a customer sourced from an ecommerce site of the company, or something else like integration into the employee table of a large organisation as the technote provides. However, if the performance experienced is good enough for those people using it (as has been mentioned by franky) and if those people using it know enough of sql to optomize the connection through the creation of views, indexing, the general optomization of datatypes and manipulation of tables - why not let them?

Just my two cents: if developers want to try out SQL, and are happy with the "sub-standard" performance, as far as i can tell, there is nothing in that technote that should stop them - feel free to correct me if I'm wrong or otherwise tell FMI to modify their technote so it clearly says:

Please don't use ESS if:

1) you want to deal with SQL records exceeding in number - 10% of the records stored in your FileMaker database tables

2) you are dealing with a reasonable number of records in a SQL db ... BUT you have no FileMaker tables

3) you actually want to use it for something other than reporting or extremely minor integration which could've easily been achieved in an earlier version using a SQL based import

4) your integration, while reasonable, is in any way an attempt to avoid purchasing more of our products.

... and so on and so forth...

Edited July 28, 2007 by Guest

[Søren Dyhr]

Personally to me that implies: Go ahead and integrate FileMaker as an interface front-end to a SQL database as long as you know what you're doing in SQL and as long as you keep it to a reasonable limit that FM can handle

Something that I havn't seen anything on yet, is the use of event triggers made to the SQL backbone with tools outside FM, I mean this is pretty powerfull stuff, say create brand new tables or change the shape of others, just by letting Filemaker change a tiny bit of data, since the method not need to be defined by filemaker.

Well I might confuse things a little here, what I'm on about is stored routines....

http://www.crlab.com/articles/debugging-mysql-stored-routines/

--sd

[Steven H. Blackwell]

What you are referring to Steven is, and correct me if I'm wrong, this paragraph:

No, I am referring to the Tech Brief whose PDF is atatched to that tech Info.

Steven

[Genx]

... So you're not talking about the KB entry with the attached PDF Introduction to ESS that I read and referenced above?...

that must mean that you're on about the Tech Net members only sort of thing... too bad, I'm not privy to that, I'll just have to make my judgements from the what i've got at hand I guess.

[Steven H. Blackwell]

No, no. I [color:red]am talking about the attached brief.

Steven

[Genx]

Oh well then my point stands. I've read through that pdf 4 times now and my above questions and summary's relate to the pdf not anything presented in the little kb note to which it is attached.

Anyway, I still fail to see any point where it tells you not to use FM in the way Franky is using it lest your server be struck down by all manners of natural and unnatural destruction.

[Frankywinky]

No, there should be no limitation on this kind of use. Have a look to this page :

http://www.filemaker.com/support/technologies/sql.html

The first diagram on paragraph "Choose the FileMaker 9 product that is right for your needs". They propose to do this "Great when you want FileMaker users to directly read and/or write data to a SQL data source."

The only thing you can't do, is making a runtime solution with FileMaker 9 Advanced using this kind of connectivity (otherwise, FileMaker wouldn't make money anymore).

---

About my ealier solution, it did works well like I said. But there are lot of things you need to optimize and to think of. By example, making a MySQL view to save on calculation time do not refresh immediately. You have to use the Refresh Window option manualy or in scripts in order to update aggregate functions results (sometime, you should need to make plugin in order to have something that refresh automaticaly).

If you insist to use FileMaker's aggregate functions, it is faster to make a count on a fixed calculation field (example. =1) than on a MySQL field. I'm pretty sure FileMaker does one SELECT instruction for EACH field access, which slow down the performances and you have to thing of strategies to limit MySQL field accesses.

But, if you care well at these things, you get an acceptable solution with correct performances.

---

Maybe someday, we could wish to have something faster. A better integration of SQL capabilities could speed up FileMaker performances (by example, letting SQL calculate aggregate functions by constructing SQL request from the relationship graph rather than making a request on each record). Or maybe FileMaker would keep his position that FileMaker should not be use as a backend. But, is not it a new era of environment database? Why so many people presented that much interest in this kind of use of FileMaker?

If FileMaker doesn't take well advantage of theses possibilities, I think someone else could do it someday. Just look at how many sourceforge projects that create webbased user interfaces to manipulated tables. Is it the beginning of something new, of a revolution on database environment ?

[Genx]

Lol, Steven, Franky's right... that pretty little picture on the left is missing FMS or FMSA.

http://www.filemaker.com/images/graphics/sql_diagram_fmp.png

Not that anyone would realistically want to avoid FMS but as a general note...