360Works JDBC Plugin 1.86 User Guide

The 360Works JDBC Plugin allows execution of arbitrary SQL statements on one or more JDBC databases, iterating result sets, and importing from a JDBC database table. In addition, the JDBC Plugin can load JDBC drivers from a URL or a container field (the latter requires FileMaker version 8 or higher).

Before executing a query, you must:

Once these are called (see the Error Reporting section to make sure that they are successful), you can then begin executing queries using the jdbcPerformQuery function.

If you are using multiple database, the query will always be executed on the last database you selected with the jdbcOpenDatabase method. The database connection is cached, so calling this multiple times for the same database will execute quickly.

Example Usage: Connecting to a database

 jdbcLoadDriver("com.mysql.jdbc.Driver" ;
     "file:///Library/Java/Extensions/mysql-connector-java-3.0.11-stable-bin.jar") and
 jdbcOpenDatabase("jdbc:mysql://db.example.com/invoices")
 

Note the and clause used between the plugin function invocations. This ensures that all steps are successful. The first one which fails will cause the calculation to exit.

Example Usage: Updating data

In addition to reading or importing data from a JDBC data source, you can also alter the data there as well be performing UPDATE, INSERT, or DELETE SQL queries.

The following example updates a row in a JDBC table to contain values stored in the Filemaker table. Note the use of single-quotes to escape the value of the name field. If there were a possibility that the name field contained single quotes, you would need to place a backslash character before them using FileMaker's Substitute function. See the next section on Using FileMaker merge fields in queries for a much better way of including FileMaker data in your SQL queries.

 Set Field [ g::Result; jdbcPerformQuery("UPDATE MY_TABLE " &
     " SET cost=" & widgets::cost & ", name='" & widgets::name & "'" &
     " WHERE id=" & widgets::id ) ]
 If [ g::Result = "ERROR" ]
     Show Custom Dialog [ "Error while performing query: " & jdbcLastError ]
     Halt Script
 End If
 Show Custom Dialog [ "Updated " & g::Result] & " row(s)" ]
 

Example Usage: invoking a stored procedure

 Set Variable [ $result = jdbcPerformQuery("call myStoredProcedure('myFirstParam', 'mySecondParam')" ) ]
 

Iterating over a found set

There are two ways to get data from a JDBC data source into FileMaker. You can iterate over a row set and access individual rows and columns as needed (see jdbcNextRow) , or you can import the result of an SQL query into FileMaker (see jdbcXmlImportUrl).

Using FileMaker merge fields in queries (mac-only)

The above example has a few drawbacks. First, it's hard to read. Second, if the widgets::name field contains a single-quote character, the resulting SQL will be invalid, or worse, will not do what you intended. If you surround your data with double-quotes you can use FileMaker's Quote function to add backslashes before double-quotes, but there's a better way:
 Set Field [ g::Result; jdbcPerformQuery("UPDATE MY_TABLE " &
     " SET cost=<<widgets::cost>>, name=<<widgets::name>>" &
     " WHERE id=<<widgets::id>>" ) ]
 
You can embed FMP-style merge fields directly in your SQL, and the JDBC plugin will replace those with the values from your database when you make the plugin function call! The plugin creates a java.sql.PreparedStatement behind the scenes for this, so you don't have to worry about escaping quotes and things in the data you use in your SQL statement.

Transaction support

If you are using the JDBC plugin to connect to a database which has transaction support, you can turn off auto-commit mode when connecting to the database. If auto-commit is disabled, you can initiate a new transaction by calling jdbcPerformQuery("BEGIN"), and commit the transaction by calling jdbcPerformQuery("COMMIT"). Named transactions are not currently supported.

The default behavior is for auto-commit to be enabled, which causes each query to be executed as its own transaction.

360Works JDBC Driver

360Works has written a custom JDBC driver for FileMaker, it is available as part of the open-source WooF WebObjects Plugin for FileMaker. If you're experiencing issues with the JDBC driver distributed with FileMaker, download the 360Works driver and see if it fixes the problem. If it doesn't, let us know!

Error Handling/Reporting

When something unexpected happens, the plug-in will pop up a dialog showing what the error message is. This makes it easy to see what went wrong. However, in some cases, you (the developer) may prefer to show your own message to the user, or possibly not show a message at all. In that case, you can call jdbcSetErrorCapture with a parameter of true. That will suppress the error dialog from appearing to the user.

When you call this function, it is set for that plug-in for as long as FileMaker is running, so if you want to do all of your own error handling, you can just set it to true once in your startup script. However, we recommend only turning it on when your script is prepared to check for errors, and then turning it off after finishing that section.

Whether or not you suppress the error dialogs, a plugin function will return the word ERROR if something goes wrong. It's a good idea to put your plugin functions in an 'If' statement so that you don't execute a bunch of script steps after something has gone wrong. If you'd like for your script to get the error message, you can get that by calling the jdbcLastError function.

Here is an example of basic error reporting:

Set Variable [ $result = MyPluginFunction("x" ; "y" ; "z") ]
If [ $result = "ERROR" ]
    Exit Script[ "Error occurred: " & jdbcLastError ]
Else
    ... do more stuff here ...
End If

Chaining Multiple Functions Together

Since the string "ERROR" evaluates to false when evaluated by FileMaker, and most plugin functions return a 1 when successful, you can chain multiple dependent plugin operations together using the "and" operator. However, in this case the result will be a 1 or a 0, not "ERROR". For example:

// chain multiple calls together
// if any of the functions fail, the calculation will
// short-circuit with a result of false,
// and none of the subsequent function calls will be evaluated.
Set Variable [ $success =
    FirstPluginFunction("x") and
    SecondPluginFunction("y") and
    ThirdPluginFunction("z")
]
If [not $success]
    Show Custom Dialog [ "An error occurred: " & jdbcLastError ]
End If

Note: the above only works for plugin functions which return 1 on success! Check the documentation for each function used in this manner.

Additional Error Checking - Plugin not installed

If a plugin is not installed correctly, calls to a plugin function will return "?". As part of your startup script, you should check for this occurrence and display a warning accordingly that the plugin needs to be installed. Note: when treated as a boolean true/false value, FileMaker will treat ? as true.

Installation

Requirements

FileMaker version 11 or higher.

Java Virtual Machine (JVM) version 1.6 or later (32-bit). If you are running a JVM earlier than 1.6, you should upgrade. Download a JVM from http://www.java.com/en/download/. Apple has a 32-bit version of Java 1.6 here http://support.apple.com/kb/DL1572. If you are not sure what version of Java you have installed, you can do java -version on the command line in Windows or OS X.

Windows, or Mac OS X version 10.6 or higher.

Note to intel Mac users: running this plugin under Rosetta is not supported. Upgrade to FileMaker 8.5 to run our plugin in native Intel mode.

Install Steps for FileMaker Pro

Drag the plugin from the MAC or WIN folder into your FileMaker extensions, and restart FileMaker.

If the plugin does not load correctly, double-check that you meet the system requirements.

Install steps for Instant Web Publishing

You do not need to do this step unless you plan on using the plugin with Instant Web Publishing with FileMaker Server Advanced. You will need an Enterprise license to use this feature.

For installing into the Web Publishing Engine with FileMaker Server or FileMaker Server Advanced, drag the plugin from the MAC (.fmplugin file) or WIN (.fmx file) folder into the FileMaker Server/Web Publishing/publishing-engine/wpc/Plugins folder. If there is no Plugins folder inside the wpc folder, then create it manually. Restart FileMaker Web Publishing, and now the plugins should be ready to go.

The easiest way to test whether the plugin is working is to have a calculation which calls the version function of the plugin, and display that on an IWP layout. If it shows "?", then the plugin is not working. If it shows a number, then the plugin has been installed successfully.

Install steps for Custom Web Publishing

If you are using FileMaker Server 12.0v1 or earlier, you can follow the same procedure as detailed above for custom web publishing. However, in FileMaker Server 12.0v2 and later, the custom web publishing now runs as a 64-bit application, and requires a slightly different location. You will need an Enterprise license to use this feature.

For Mac, the single plug-in file in the MAC directory contains both 32-bit and 64-bit versions. For Windows, look for the plug-in that has the extension .fmx64 and use it in exclusively in custom web publishing.

To install 64-bit plug-ins, install either the .fmx64 or the .fmplugin to the following directory:

FileMaker Server / Web Publishing / publishing-engine / cwpc / Plugins

If it does not exist, create the Plugin folder manually. Restart FileMaker Web Publishing, and then you can then test a script that contains a plug-in and see if it returns the correct values.

Please note that plug-ins do not work with the web publishing engine of the Mac version of FileMaker Server 8.0v4.

Install steps for server scheduled scripts

You do not need to do this step unless you plan on using the plugin with scheduled script triggering. You will need an Enterprise license to use this feature.

  1. Drag the plugin from the MAC (.fmplugin file) or WIN (.fmx file) folder into the FileMaker Server extensions folder. On Mac OS X, this is located at /Library/FileMaker Server/Database Server/Extensions folder. On Windows, this is at C:\Program Files\FileMaker\FileMaker Server\Database Server\Extensions.
  2. In the Server Admin application, restart FileMaker Server by stopping and starting it.
  3. Go to Configuration -> Database Server->Server Plug-ins and check the box that says 'Enable FileMaker Server to use plug-ins', and then check the 'enabled' box for this plugin. Click the 'save' button and wait a few seconds to make sure that the 'enabled' check box stays checked. If it does not, then there was an error loading the plugin and you should contact us for help troubleshooting. You should now be able to write schedules that trigger scripts which use the plugin.

Auto Update

360Works has created an AutoUpdate helper database which makes setting up Auto Update much easier. This file includes pre-configured plugin files which you can place on your server, and an auto-update script for each of our plugins which you can paste into your own solution.

You can get the AutoUpdate360Works file at fmp7://autoupdate.360works.com/AutoUpdate360Works or fmp://autoupdate12.360works.com/AutoUpdate360Works. Follow the instructions included in the file to either host your own Auto Update server or pull the files from ours.

Uninstalling the plugin

Uninstall the plugin by quitting FileMaker Pro or stopping FileMaker Server and removing the plugin file from your Extensions directory.

Demo mode and Registering the plugin

Plugins will run in demo mode until they are registered. While running in Demo mode, the product will run for 2 hours every time you launch FileMaker / FileMaker Server / FileMaker Web Publishing Engine. The 2 hour time limit will reset every time you relaunch FileMaker. There is no expiration date when Demo mode stops working. There are no feature differences between the Demo version and the licensed version.

Once you have purchased the plugin, you can register it with the license key. Once a valid license key is entered, the product will run for as long as FileMaker is running. After FileMaker starts up with the plugin installed, open FileMaker preferences, click on the Plug-ins tab, select the plugin from the list, and click the Configure button. Enter your license key and company name in this dialog. You will only need to do this once on a given machine. Alternately, you can use the registration function to register the plugin during a startup script.

Note that if you are running the plugin with FileMaker Server / FileMaker Web Publishing Engine, you must use the registration function to register the plugin, since there is no preferences dialog on FileMaker Server to enter the license key and company name.

Feedback

We love to hear your suggestions for improving our products! If you are experiencing problems with this plugin, or have a feature request, or are happy with it, we'd appreciate hearing about it. Send us a message on our website, or email us!

Function Summary

Function Detail

jdbcCloseDatabase ( jdbcURL )

Close a database connection which was opened previously. If there is no open connection or the connection was already closed, this returns silently.

Parameters:
jdbcURL - The JDBC url of the connection to close.

jdbcFieldNames ( table )

Returns the field names in a table in their natural order, separated by line breaks.

Parameters:
table - The name of the table to get the fields in.
Returns: newline-delimited list of the columns in table

jdbcGeneratedKey ( columnName )

Retrieves an auto-generated key created as a result of the previous INSERT statement. This should only be called after an INSERT or UPDATE statement on a table with auto-enter values.

Parameters:
columnName - name of the key column
Returns: the auto-entered value as TEXT.

jdbcGetContainerValue ( key ; filename )

Gets the value of a BLOB column in the current row of a SELECT query result set. If there has not been a successful SELECT query executed, this will generate an error. If the jdbcNextRow function has not been called yet, this will automatically advance to the first row of the result set. You may use the jdbcNextRow function to read data from multiple rows.

If the column requested is not a binary (BLOB) column, the regular contents are returned instead.

Parameters:
key - Either the name (case-sensitive!) or position of the column to retrieve the value of.
See also: jdbcNextRow

jdbcGetNumberValue ( key )

Gets the numeric value of a column in the current row of a SELECT query result set. If the column with the given name/index cannot be converted into a number, returns "ERROR" (use jdbcLastError for details on the cause).

If there has not been a successful SELECT query executed, this will generate an error. If the jdbcNextRow function has not been called yet, this will automatically advance to the first row of the result set. You may use the jdbcNextRow function to read data from multiple rows.

Parameters:
key - Either the name (case-sensitive!) or position of the column to retrieve the value of.
See also: jdbcGetValue
Returns: the numeric value of the key in question.

jdbcGetTimestampValue ( key )

Gets the timestamp value of a column in the current row of a SELECT query result set. If the column with the given name/index cannot be converted into a timestamp, returns "ERROR" (use jdbcLastError for details on the cause). If you only need the date or time portion of this value, use the GetAsDate or GetAsTime function in FileMaker.

If there has not been a successful SELECT query executed, this will generate an error. If the jdbcNextRow function has not been called yet, this will automatically advance to the first row of the result set. You may use the jdbcNextRow function to read data from multiple rows.

Parameters:
key - Either the name (case-sensitive!) or position of the column to retrieve the value of.
See also: jdbcGetValue
Returns: the timestamp value of the key in question.

jdbcGetValue ( key )

Gets the value of a column in the current row of a SELECT query result set.

If there has not been a successful SELECT query executed, this will generate an error. If the jdbcNextRow function has not been called yet, this will automatically advance to the first row of the result set. You may use the jdbcNextRow function to read data from multiple rows.

If the column contains binary data, use the jdbcGetContainerValue function instead, which allows you to specify a filename for the container data.

Parameters:
key - Either the name (case-sensitive!) or position of the column to retrieve the value of.

jdbcLastError ( )

Returns detailed information about the last error generated by this plugin. If another plugin function returns the text "ERROR", call this function to get a user-presentable description of what went wrong.

Returns: Error text, or null if there was no error.

jdbcLicenseInfo ( )

Retrieve information about the jdbc plugin licensing and version.


jdbcLoadDriver ( jdbcDriverClass {; location} )

Load the JDBC driver for the JDBC source you are about to connect to. This must be called before calling jdbcOpenDatabase. Each different database product uses its own JDBC driver. If you are connecting to multiple types of database, you will need to load the JDBC driver for each one. Calling this multiple times with the same driver should not cause any problems, each driver will only be loaded once.

The JDBC drivers are generally available from the website of the database company in the form of a .jar file.

There are two methods of loading the driver .jar file: via URL, or via container field.

Loading JDBC drivers from a URL

You can load a JDBC driver which is on the local file system or accessible on the network by passing a URL parameters as the jdbcDriverJar parameter. The URL should be of the form file:///path/to/driver.jar or http://example.org/path/to/driver.jar.

Loading JDBC drivers from a container field

As an alternative to specifying a URL, you can embed a JDBC driver .jar file into a container field in your FileMaker solution, and load the driver from there. Be sure that the driver is not stored as a reference, or the JDBC plugin will not be able to read the information.

Some JDBC drivers (e.g., Pervasive) are split into multiple .jar files, where one of those files contains the the driver class referenced by the jdbcDriverClass parameter. In these instances you should call jdbcLoadDriver by specifiying the jdbcDriverClass parameter for the .jar file that is supposed to contain the driver class. For the auxiliary .jar files, you should pass in an empty placeholder ("") for this parameter.

Some common JDBC driver classNames:

FileMaker Pro 7
com.ddtek.jdbc.sequelink.SequeLinkDriver
MySQL
com.mysql.jdbc.Driver

Parameters:
driverName - the fully qualified class name of the JDBC driver.
jdbcDriverJar - jdbc driver location or data. This can be a URL pointing to a .jar file, or an actual container field containing a .jar file.
Returns: 1 if the driver was loaded successfully, or an error message if the driver could not be loaded.

jdbcNextRow ( )

Advances the current result set to the next row. This is used if you wish to loop through a query result. If jdbcGetValue is called immediately after performing a query, jdbcNextRow is called automatically. Looping can be more efficient than importing if working with large result sets, since the JDBC data is not written to your FileMaker file, and you can start accessing the query result data immediately after performing the query. It can also be convenient when you only need one or two pieces of data from the JDBC database, and don't want to set up a designated table and script for importing purposes.

See also: jdbcGetValue
Returns: 1 if there was a next row to go to, otherwise null.

jdbcOpenDatabase ( jdbcURL { ; username ; password ; autoCommit} )

Establish a connection to a JDBC database. Once you connect with the plugin, the connection is cached, so further calls to this method execute very quickly. If you are not going to be using the connection again, you should close it with the jdbcCloseDatabase function. An example URL for MySQL might be jdbc:mysql://127.0.0.1/myDB

The fourth (optional) parameter to this function indicates whether to connect in auto-commit mode. If auto-commit is explicitly disabled, SQL statements are grouped into transactions that are terminated by a call to either the method commit or the method rollback. Auto-commit is enabled by default.

Parameters:
jdbcURL - The JDBC url of the database to connect to.
username - The optional username to use for connecting to the database.
password - The optional password to use for connecting to the database.
autoCommit - Optional parameter indicating whether each SQL statement is executed and committed as an individual transaction. The default value is true.
Returns: 1 if the database was opened successfully, or connection was cached.

jdbcPerformQuery ( sql{; timeout=numberOfMilliseconds; disableGeneratedKeys=true; ... } )

Execute a query on the last opened JDBC database. If the SQL statement begins with "SELECT" and the query does not fail, this function will return 1, and you may use the jdbcNextRow and jdbcGetValue functions to read data from the query result.

If the SQL statement does not begin with SELECT, the function will return the number of rows affected by the query.

Flags

The following optional flags are valid for this function:

An example of using this function with a foreign key pulled from a FileMaker field named '_k_empId' with a 30 second timeout would look like this:

 jdbcPerformQuery ( "SELECT firstName, lastName FROM employees WHERE employeeId=<<_k_empId>>"; "timeout=30" )
 

Parameters:
sql - A valid SQL statement, optionally containing FileMaker-style merge fields in double-angled brackets.
flags - See documentation above for optional flags. You can safely omit flag parameters.
Returns: 1 if a SELECT query was executed successfully, or the number of rows affected for an INSERT, UPDATE, or DELETE statement.

jdbcRegister ( licenseKey ; registeredTo )

Registers the Plugin.

Parameters:
licenseKey - a valid license key string, or the literal string "DEMO" to run in demo mode.
registeredTo - the company name for the license key used.
Returns: 1 on success, or "ERROR" on failure.

jdbcSetErrorCapture ( errorCapture )

Toggles error dialogs on or off. When something unexpected happens, the plug-in will pop up a dialog displaying the error message. This makes it easy to see what went wrong. However, in some cases, you (the developer) may prefer to show your own message to the user, or possibly not show a message at all. In that case, you can call jdbcSetErrorCapture with a parameter of true. That will suppress the error dialog from appearing to the user.

Parameters:
errorCapture - set to true to suppress the default popups.

jdbcTableNames ( )

Returns the tables in the database, separated by line breaks.

Returns: newline-delimited list of the tables in the current database.

jdbcVersion ( )

Returns the version number of the JDBC plugin.

Returns: a text version number

jdbcXmlImportUrl ( )

Returns a URL where the last executed query can be imported into FileMaker as an XML data source. As a security measure, a password is assigned to every SQL query that is executed by the JDBC plugin. This function embeds that password into a URL, ensuring that no outside party can access your SQL data by importing from a URL. The XML data returned at this URL is formatted as FileMaker XML data, and can be imported directly into your FileMaker table.

Example Usage: Importing a query result

The following performs a query, then imports the query data into a FileMaker table. Note: it is always good practice to check for errors using the jdbcLastError function.
     Set Field [ g::result; jdbcPerformQuery( "SELECT * FROM user") ]
     If [ g::result = "ERROR" ]
         Show Custom Dialog [ "Error while performing query: " & jdbcLastError ]
         Halt Script
     End If
     Go to Layout["JDBC_Data"]
     Import Records[jdbcXmlImportUrl]
 
The Import Records dialog specifies an XML Data data source. The import location is determined by calling the jdbcXmlImportUrl function. The import dialog should look like this:
jdbc import dialog

Returns: a URL string for XML data import.