One of the most common complaints that I hear from SQL DBAs with regards to ESS is that they don't trust it to perform INSERTs, UPDATEs, or DELETEs against the tables in their databases. I don't trust it either (and neither should you!). That's not a knock against FileMaker -- I simply don't trust any external process to interact directly with the tables. If that is what your DBA is doing (or proposing to do), you should ask them why.
Instead, they should provide a set of stored procedures for all of the INSERT, UPDATE, and DELETE operations that you'll need. That way, the data can be evaluated, business rules can be applied, and so on by the stored procedures -- and the stored procedures can perform the INSERTs, UPDATEs, and DELETEs.
Ideally, when getting things setup for you to access it via FileMaker, your SQL DBA should:
(1) Provide you with a SQL account that allows you read-only access to the data. If possible, your account should have SELECT privileges on a set a of VIEWs -- not on the base tables themselves. That way, through the definition of the VIEWs, they can limit the data that you can see and have already worked through any issues with regards to joining data in multiple tables. Using ESS, you can integrate these views into your FileMaker database (as table occurrences).
(2) Setup the SQL account so that it has EXECUTE privileges on a set of stored procedures that you can use to insert, update, and delete records. You can use the FileMaker "Execute SQL" script step to call the stored procedures and pass parameters to them. The issue is getting feedback from the store procedures. (For example, was the request successful, or did it fail, and if it failed, then why?). Some stored procedures will return parameters to you after they've executed and/or one or more data sets. The "Execute SQL" script step allows you to call stored procedures, but it doesn't provide you with a way to access the output from them. (There are ways to work around this. If you are interested in how I'm doing it, drop me a line.)
So, to summarize:
- Use ESS to view the data in the SQL databases. Never use it to insert, update, or delete records!
- Use the Execute SQL script step to call stored procedures to update data in the SQL databases.
-- Tim
5 comments:
I don't think it's necessary to require stored procedures for all writes (inserts / updates / deletes). The use of triggers, views, and strict account permissions should be more than sufficient for most DBA's to enforce data consistency, privileges, etc.
One of the nice things about ESS is that it allows you to set up ODBC on the server, and FM Server will take care of database communication on behalf of the clients. However, "Execute SQL" does not use the ESS connection. If your solution uses both ESS and Execute SQL, ODBC will have to be set up on both the server and the client. That takes away some of the advantages of ESS, IMHO.
The SQL generated by ESS is for the most part monkey-simple, and it should be easy to have a DBA observe the SQL using admin tools.
DBA's like discipline, control and monitor-ability. Procs are one way to obtain that, but not the only way.
Jonathan Monroe
Actual Technologies - ODBC for Mac OS X
Jonathan --
I agree with you that one of the nice things about ESS is the ability for clients to access ODBC datasources that are setup on FM Server without needing to have those datasources setup locally. I was shocked when I first learned that. It saves a lot of time in that we no longer need to setup ODBC datasources on all of our users' machines...
I was also shocked when I realized that this same benefit hadn't been extended to the "Execute SQL" script step. Every user that uses Execute SQL needs to have the ODBC datasource setup on their local machine. That is, admittedly, a big downside to the Stored Procedure-based strategy that I described in my original post. But in my opinion, the benefits of using stored procedures far outweigh the downside of having to setup the datasources for the users...
And while I also agree with you that DBAs have other tools at their disposal to enforce data integrity, security, and so on, I am not convinced that they are the best way to provide write access.
Imagine a situation where a FileMaker user, with DELETE permission on a SQL table, accidentally deletes all records in the found set, or accidentally performs a Replace Field Contents across records in a SQL table. Things like triggers and account permissions would more than likely not stop these types of things. However, stored procedures, if written properly (and if they allow a write to only one record at a time), most likely would prevent this.
To me, the big benefit of requiring FileMaker developers to use stored procedures is that they allow the DBA to provide a way to write data without actually granting permissions on the underlying tables. Also, they force developers to access the data using the same methods that other applications use. By providing access via stored procedures, the DBA only has to worry about managing a single set of stored procedures. Users accessing the data via the Web, FileMaker, and any other application use that same set. And there are a number of other advantages provided by stored procedures as well, including improved performance, reduced network traffic, etc.
For an interesting blog post that discusses the many benefits of stored procedures (and arguments for requiring that stored procedures be used to access data), see Rob Howard's post Don't use stored procedures yet? Must be suffering from NIHS (Not Invented Here Syndrome).
-- Tim
Tim-
Totally agree with you, in fact I'll extend it further...changes to any database should be made from only one source unless you have an unlimited budget to test every conceivable scenario. We advocate a transaction queue where FM change, insert, and delete requests get stored into a journal and processed by stored procedures. Until we get a full understanding of ESS internals and especially caching mechanism, multiple update paths are a disaster waiting to happen.
Jonathan-
Just because I'm paranoid doesn't mean it's safe to act cavalier.
Regards,
Theo
Theo
We have integrated FmP with MSSQL successfully multiple times. We also use stored procedures via "Execute SQL." However, we're now faced with a stored procedure that seems to be failing in the middle; the only clue we have is size - we're iterating through a recordset using a cursor. If the record count gets too high, bam; it fails (around 60 iterations). Have you had this problem?
Ed --
It sounds like the ODBC call is timing out. When you say that it fails after around 60 iterations, how much time has elapsed?
Also, do you think you could re-write the stored procedure so that the cursor isn't necessary?
-- Tim
Post a Comment