360Works JDBC Plugin/Documentation
Line 328: | Line 328: | ||
The XML data returned at this URL is formatted as FileMaker XML data, and can be imported directly into your FileMaker table. | The XML data returned at this URL is formatted as FileMaker XML data, and can be imported directly into your FileMaker table. | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | + | <h3>Example Usage: Importing a query result</h3> | |
− | </ | + | 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 <code>jdbcLastError</code> 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 <strong>Import Records</strong> dialog specifies an <code>XML Data</code> data source. The import location is determined by calling the [[#jdbcXmlImportUrl|jdbcXmlImportUrl]] function. | ||
+ | The import dialog should look like this:<br/> | ||
+ | <img src="images/jdbc_import_dialog.png" alt="jdbc import dialog"/><br/> | ||
− | + | <div class="see"><strong>Returns:</strong> a URL string for XML data import. | |
− | + | </div> |
Revision as of 16:39, 2 April 2013
360Works JDBC Plugin 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:
- Register the plugin using jdbcRegister
- Load the driver using jdbcLoadDriver
- Open a connection to the database using jdbcOpenDatabase
Once these are called (see the <a href="#error_reporting">Error Reporting section</a> 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.
</p>
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 <a href="#jdbcNextRow">jdbcNextRow</a>) , or you can import the result of an SQL query into FileMaker (see <a href="#jdbcXmlImportUrl">jdbcXmlImportUrl</a>). </p>
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 <a href="https://woof.dev.java.net/">WooF WebObjects Plugin for FileMaker</a>. 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, <a href="mailto:ssb_jdbc@360works.com">let us know</a>!
360Works Plugin Setup Guides
See Plugins_101 for Error reporting, installation, registration, and more.
Function Summary
- jdbcCloseDatabase ( jdbcURL ) — Close a database connection which was opened previously.
- jdbcFieldNames ( table ) — Returns the field names in a table in their natural order, separated by line breaks.
- jdbcGeneratedKey ( columnName ) — Retrieves an auto-generated key created as a result of the previous
INSERT
statement. - jdbcGetContainerValue ( key ; filename ) — Gets the value of a BLOB column in the current row of a SELECT query result set.
- jdbcGetNumberValue ( key ) — Gets the numeric value of a column in the current row of a SELECT query result set.
- jdbcGetTimestampValue ( key ) — Gets the timestamp value of a column in the current row of a SELECT query result set.
- jdbcGetValue ( key ) — Gets the value of a column in the current row of a SELECT query result set.
- jdbcLastError — Returns detailed information about the last error generated by this plugin.
- 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.
- jdbcNextRow — Advances the current result set to the next row.
- jdbcOpenDatabase ( jdbcURL { ; username ; password ; autoCommit} ) — Establish a connection to a JDBC database.
- jdbcPerformQuery ( sql{; timeout=numberOfMilliseconds; ... } ) — Execute a query on the last opened JDBC database.
- jdbcRegister ( licenseKey ; registeredTo ) — Registers the Plugin.
- jdbcTableNames ( ) — Returns the tables in the database, separated by line breaks.
- jdbcVersion ( ) — Returns the version number of the JDBC plugin.
- jdbcXmlImportUrl ( ) — Returns a URL where the last executed query can be imported into FileMaker as an XML data source.
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.
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.
table
- The name of the table to get the fields 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.
columnName
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.
key
- Either the name (case-sensitive!) or position of the column to retrieve the value of.
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.
key
- Either the name (case-sensitive!) or position of the column to retrieve the value of.
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.
key
- Either the name (case-sensitive!) or position of the column to retrieve the value of.
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.
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.
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 common JDBC driver classNames:
- FileMaker Pro 7
- com.ddtek.jdbc.sequelink.SequeLinkDriver
- MySQL
- com.mysql.jdbc.Driver
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.
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.
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.
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
.
jdbcPerformQuery ( sql{; timeout=numberOfMilliseconds; ... } )
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:
- timeout: Specify a number of milliseconds before the plugin should give up and return an error.
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" )
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.
jdbcRegister ( licenseKey ; registeredTo )
Registers the Plugin.
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.
jdbcTableNames ( )
Returns the tables in the database, separated by line breaks.
jdbcVersion ( )
Returns the version number of the JDBC plugin.
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:
<img src="images/jdbc_import_dialog.png" alt="jdbc import dialog"/>