Before executing a query, you must:
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.
jdbcRegister("YOURLICENSEKEY123" ; "Company Name") and
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
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, or you can import the result of an SQL query into FileMaker.
The following performs a query, then loops through the returned rows, taking the first column from each row and building a return-separated list of values.
Note that the loop is exited if jdbcNextRow returns false.
Also, 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 ] Exit Script End If Loop Exit Loop If [ not jdbcNextRow ] Set Field [ g::Text; g::Text & jdbcGetValue( 1 ) & "\n" ] End Loop
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] Exit Loop If [ not jdbcNextRow ] Set Field [ g::Text; g::Text & jdbcGetValue( 1 ) & "\n" ] End LoopThe 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:
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)" ]
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.
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.
FileMaker version 7 or higher.
Java Virtual Machine (JVM) version 1.4.2 or later. If you are running a JVM earlier than 1.4.2, you should upgrade. Download a JVM from http://www.java.com/en/download/. 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.4 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.
Drag the plugin from the MAC or WIN folder into your FileMaker extensions, and restart FileMaker. You will need to enter your license key before you can use it. 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.
This will also enable the plugin for use with Instant Web Publishing from the FileMaker Pro client software.
If the plugin does not load correctly, double-check that you meet the system requirements.
You do not need to do this step unless you plan on using the plugin with Instant Web Publishing or Custom 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 9 Server or FileMaker Server Advanced, drag the plugin from the MAC or WIN 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.
Note that you must use the registration function to register the plugin, since there is no preferences dialog in the FileMaker Web Publishing Engine to enter the license key and company name.
Note that due to a bug which we and other plugin vendors have reported to FileMaker, web plugins do not work in FileMaker Web Publishing Engine 8.0v4 on Mac OS X. You will need to use a later version, like 9, or an earlier version, like 8.0v3. The Windows FileMaker Server 8.0v4 does not have this bug, and will work correctly.
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.
You do not need to do this step unless you plan on using the plugin with scheduled script triggering, a new feature in FileMaker Server 9. You will need an Enterprise License to use this feature.
FileMaker Server/Database Server/Extensions folder (Server 8 and older versions of server use the path FileMaker Server/Extensions/Plugins).Configuration -> Database Server->Server Plug-ins.
Note that you must use the registration function to register the plugin, since there is no preferences dialog in FileMaker Server to enter the license key and company name.
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!
INSERT statement.jdbcURL - The JDBC url of the connection to close.table - The name of the table to get the fields in.table
INSERT statement.
This should only be called after an INSERT or UPDATE statement on a table with auto-enter values.
TEXT.
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.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.null if there was no error.
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.
jdbcDriverJar parameter.
The URL should be of the form file:///path/to/driver.jar or http://example.org/path/to/driver.jar.
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:
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.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.sql - A valid SQL statement, optionally containing FileMaker-style merge fields in double-angled brackets.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.