I should also mention that JDBC is terribly named. It really should be something like Programmable SQL. If you can do SQL, you can do JDBC.
Think for a minute about how ExecuteSQL() SHOULD work…
You issue a query (ExecuteSQL(), unlike JDBC, limited to only Select).
You get back a “FoundSet” in FileMaker so you can do whatever.
Unfortunately, 2. above isn’t how ExecuteSQL() works. Baffling. Instead, you just get back simple text data to do … something with.
With JDBC, you enter your SQL and you do IN FACT get back a “FoundSet”, but it’s called a ResultSet (same idea). You can loop through the ResultSet hundreds of times faster than FMP’s slow looping.
With JDBC, you also can do, INSERT, DELETE, and UPDATE statements.
With JDBC, you can also do DDR statements like CREATE TABLE or ALTER TABLE.
With JDBC, you can also use database transactions. FileMaker’s transaction support seems limited, but it’s there.
Best yet, perhaps, if/when you move your database from FMP to another vendor (MySQL, SQL Server, etc.), your JDBC code will still work. Just change the connection string and any FMP-specific SQL syntax and you’re good to go with the new database – assuming the same schema, of course.
JDBC doesn’t force you into the Data API and FMS; JDBC works with plain old FMP.
Note that frameworks “abstract” JDBC so you don’t even need to worry about SQL at all. You just issue “save()” or “update()” method calls and the framework takes care of everything for you.
Just wanted to add a bit more detail.
“Adding manpower to a late software project makes it later.”
- Fred Brooks (Brooks’ law)