FileMaker version 12 or higher.
When 360Plugins are intialized for the first time, they will automatically download all required support files
Drag the plugin from the MAC or WIN folder into your FileMaker extensions, and restart FileMaker.
If the plugin does not load correctly, please send an email to support@360works.com
Install plug-ins for use with WebDirect by dragging the appropriate plugin to FileMaker Server/Web Publishing/publishing-engine/cwpc/Plugins
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 or WIN(.fmx) 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 call 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.
If you are using FileMaker Server 12.0v1, you can follow the same procedure as detailed above for custom web publishing. However, in FileMaker Server 12.0v2 and later, custom web publishing now runs as a 64-bit application and requires the 64-bit version of the plugin 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.
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.
360Works has created an AutoUpdate helper database which makes setting up AutoUpdate much easier. This file includes pre-configured plugin files which you can place on your server, and an AutoUpdate script for each of our plugins which you can paste into your own solution.
You can get the AutoUpdate360Works file here Follow the instructions included in the file to either host your own Auto Update server or pull the files from ours.
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: 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. You will need to call the register function at the beginning of any script that is going to call plugin functions. This will ensure that you do not get demo mode errors.
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!
When calling plugin functions as script steps, you will handle errors in the same manner you would any other FileMaker script step. Please see the FileMaker documentation for how to handle errors appropriately. Generally, if there is an error, our plugins will return an error code of 1552 when Get(LastError) is called. However, some plugin functions will return different error codes. If a function can return an error code other than a 1552, it will be documented with the description of the function below. In addition to the error code, you can also get the description of the error by calling Get(LastExternalErrorDetail)
You can also call our functions in a calculation dialog. In this case, error handling is done differently. If you decide to call plugin functions in a calculation dialog and you want to capture errors, see this page page for instruction on how to do so
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.
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.
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)" ]
Set Variable [ $result = jdbcPerformQuery("call myStoredProcedure('myFirstParam', 'mySecondParam')" ) ]
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).
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.
jdbcURL
- The JDBC url of the connection to close.table
- The name of the table to get the fields in.INSERT
statement. This should
only be called after an INSERT
or UPDATE
statement on a table with auto-enter values.
columnName
- name of the key columnjdbcNextRow
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.
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.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.
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.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 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. As a best practice, auxillary .jar files should be loaded first, followed by loading the .jar with the driver class, and specifying a value for the jdbcDriverClass parameter. When calling this function with an empty jdbcDriverClass parameter, the .jar file specified will be added to a list of .jar files to load. When calling this function with a populated jdbcDriverClass parameter, all .jar files will be loaded, then the list of .jar files will be reset. This is done to prevent an ever-growing list of .jar files to examine for dependencies.
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,
,.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.
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.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.emailAddress
- The user's email address to allow for support contactproblemDescription
- A description of the current issueerrorCapture
- set to true to suppress the default popups.
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: