AnsweredAssumed Answered

JDBC Anyone, Anyone?

Question asked by fmpdude on Sep 11, 2017

Why would you potentially want to use the free FMP JDBC driver to access FileMaker Pro data from Java?

 

Speed

 

Portability

    Assuming the JDBC code isn't using vendor-specific SQL enhancements, you can usually just modify the connection parameters and your JDBC code will work with another database vendor right away (assuming the same schema, data, and such, on the other database vendor).

 

FREE - FMI includes a free JDBC driver with current FMP installations

 

Full Power of Java

    Create custom data exports

    Email with multiple attachments

    Zip

    Encryption

    RegEx

    (just about ... anything...)

 

Full SELECT, INSERT, UPDATE, and DELETE SQL functionality (no messy ExecuteSQL syntax)

 

DB Metadata capability (for generic sync logic -- programmatically get table and field names)

 

Offload heavy processing to fast Java logic

 

Work with larger data sets than often considered for a micro-service

 

Work with micro-services as a proxy    

     For example, call a micro-service and have the micro-service run JDBC logic

 

Could schedule Java JDBC code to run on any schedule you like

 

Other

 

----

 

JDBC uses ResultSets which are similar to a FileMaker FoundSet, conceptually.  JDBC also supports RowSets (not discussed) for amazing flexibility.

 

JDBC supports PreparedStatements for fast processing. Also, PreparedStatements strongly bind parameters to variable types.

 

JDBC also supports many other advanced features for enterprise databases.

 

JDBC uses SQL extensively and is a low-level (low abstraction), but very powerful, way to work with databases.

 

Modern IDEs have a data pane which let you experiment with SQL against the live database as you write the SQL for the JDBC logic. These IDEs, like IntelliJ, have type assist for SQL, full SQL error messages, and other cool features.

 

-----

 

Using a sample FMP database, we could create a simple two-table database, do a SELECT, do an UPDATE, do a DELETE, and do an INSERT. We could round out the tutorial, by writing an output CSV file complete with headers!

 

As with the micro-services tutorial, you would need to have a Java JDK installed (Java 8 at the time of this writing), and a Java IDE. The JDBC tutorial's screenshots would use IntelliJ IDEA's "Ultimate" edition (though you could use any IDE, including free IDEs like Eclipse and NetBeans, you like).

 

Also, using the FileMaker DB only as a data source, this tutorial would be all Java code and SQL.

 

---

 

If this idea sounds interesting, please post your interest for learning how to use the powerful JDBC driver with FMP.

Outcomes