Pity to see your question sitting there with no responses - but I guess you did post it before the membership of the forum was extended.
FWIW, I would be very pleased to see the ESS enhancements you've mentioned, along with a range of built in SQL functionality - so that's at least two of us that are using it! ;)
R J Cologon, Ph.D.
FileMaker Certified Developer
Author, FileMaker Pro 10 Bible
NightWing Enterprises, Melbourne, Australia
I 2nd that motion. While I don't want SQL to be the primary way to do things in FileMaker, more access to the internal SQL would open a ton of possibilities.
Yes, got this in during the testing phase to rattle some replies. I don't think SQL will ever be the norm in Filemaker, and that's OK. I'd just like to see a little more control over the black box functionality that is built in. It is relatively efficient at very basic stuff, but once you start getting into large SQL Databases and anything with relations, it takes a hit pretty fast. A few key features would really allow ESS to be much more functional in real world scenarious.
Will keep our fingers crossed for future versions.
- Direct execution of stored procedures from within ESS (no table trigger tricks etc.)
- 64 bit architecture (that's not just ESS and ODBC, but the server per se)
- BLOB/CLOB datatype handling
would be my favorites; your points above deserve support, too.
Oh yes, BLOB/CLOB support would be awesome. I concur!
Also, now that Apple is adopting Postgres over MySQL on Mac OS X, I hope FileMaker will soon offer Postgres ESS support soon too. And, as LeeSnover says, JDBC as ESS sure would be nice too!
Great to see some hits on this topic. I'd love to see Postgres support. BLOB/CLOB support and direct access to the DB through the server are the top of the list. I'd also like to see the ability to handle sorting on the SQL side of the equation. The current situation makes for very poor performance in many circumstances.
Hopefully we will see some improvements in the not too distant future.
Am I on the wrong page when I think that not being able to retrieve a sorted set in sorted order is a proper bug!?
So, FileMaker might want to fix that bug with high priority...
I think it should be considered a bug too, but sadly it is simply not in the specification. There is no means for specifying a sort order at the SQL database level from within Filemaker. I don't think it would be much of a stretch for them to grab the specified sort fields on the primary table or in a portal and pass that through to the underlying database. But at present that is definately not happening. Go to the Filemaker suggestions page and enter it as a feature request. The more folks that ask, the more likely it will be considered for an new version down the pike.
One more for the wish list:
FileMaker's Number type proves to be a headache whenever a query expects an integer and FileMaker delivers something like "SELECT ... FROM xxx WHERE z = 4'700.00"
"SELECT ... FROM xxx WHERE z = 4700" would be correct.
It would help if FileMaker would return type conformantly formatted numbers in SQL Query texts in its (never displayed) internal queries.
I recall reading something about FileMaker handling integers differently when the request is sent as SELECT ... FROM xxx WHERE z = ?; 4700 rather than including it directly in the select statement.
But I could be wrong.
Well, since it happens under the hood of ESS, I can only interpret the SQL Server error messages I get presented with, or trace things from the SQL Server side.
BTW: one partial improvement on the problem is if we define the thousands separator to be empty instead of the usual single tick - so, a presentation level setting. I can understand what happens (SQL separates strings with single ticks, so it disrupts the string definition), but since this is background stuff, a presentation level setting should not ever have any influence on the formatting of those numbers...
Be sure to submit your request to the guys at Filemaker. I spoke with some of the FMI folks at Devcon, and I think the idea of getting the older Execute SQL SCRIPT Step to work through the ODBC driver on the server is something that could be put implemented relatively easy. Now that the Execute SQL FUNCTION is available, this is exposing many FM Developers to SQL who would never otherwise have bothered with this functionality.
Unfortunately, the Execute SQL FUNCTION is really HORRIFIC to use against ESS tables. It will work, but FM sucks ALL the data into Filemaker first before it evaluates the SQL in the scope of Fimemakers ODBC functionality. This can lock up your Filemaker app quicker than just about anything else I've seen. So, getting us similar functionality against REAL SQL Tables is really a current weak point. The old script step is there, but needs a little modernization to bring up to speed, and being able to use the Server ODBC/DSN is key to keeping deployment practical in the scope of the current Filemaker Desktop model and for FMGO, since there's no way of installing ODBC drivers on iPads and iPhones.
Getting this functionality would make calling Stored Procedures and other functions called against the SQL server TONS more convenient.
The other real performance killer is simple sorting, even in small portals that use ESS data. Filemakers sort speed on ESS based portals is really poor. Since these are relatively straight connections to the SQL Data, it would be a great help if any Portal based sort specifications were simply passed back to the SQL database. These two small enhancements would increase usability dramatically. There's much more that I'd love to see, but this is "low hanging fruit" as they say.
If you agree, send a note to the product managers, and put a request on the FM Web page. It's important that ESS not get left on the back burner, especially in the scope of capabilities we may see in future versions of Filemaker. After what I saw and heard at Devcon, I am even more optimistic about the future of Filemaker. But ESS functionality will be key for my organization and my clients to stick with Filemaker over the long haul.