SQL Plug-in

RATING: Advanced
PLATFORM: Dual Platform
VERSION: FileMaker 4/5
TECHNIQUE FILES: Technique File.fp5


Quick overview

The goal of this article is to help you understand what you can do with the SQL Plug-in and how to get started quickly. The following items will be discussed:

- Installation
- Configuration
- Connecting to a database
- Performing queries
- Calculating queries
- Retrieving results
- Updating data in a SQL database
- Inserting data in a SQL database

We recommend that you read this article next to your computer, working along with the examples, as this will really help you to understand how to use the plug-in.

The SQL Plug-In was first introduced in 1997 for FileMaker Pro 4 and is currently in use by more than 20,000 users worldwide. With the SQL Plug-In you can connect to nearly all SQL based databases on Windows and Macintosh (also OS X). Once you're connected to a SQL database, any supported SQL operation can be performed.

Installation

A free trial version of the SQL Plug-in can be downloaded at < http://www.profdata.nl >. The only limitation of the trial version is that it works for only one hour; after an hour of usage you need to restart FileMaker Pro in order to use it again. Other than that it is fully functional.

The Windows version of the plug-in comes with an installer. Just double click the installer and follow the onscreen instructions. When the installer is done, copy the plug-in file (PDMSQL.fmx) into the System subdirectory inside your FileMaker Pro application directory (usually C:Program FilesFileMaker Pro).

For the Macintosh platform there are two versions, one for Mac OS 'classic' (OS 8 and 9) and one for OS X. Both come in the same download archive.

Mac OS Classic:
1. Copy the plug-in file into the Extensions folder inside the FileMaker Pro folder
2. Copy JDBCPlug-In.jar into the FileMaker Pro folder

Mac OS X:
1. Copy the plug-in file into the Extensions folder inside the FileMaker Pro folder
2. Copy JDBCPlug-In.bundle inside the Shared folder located in Users (/Users/Shared)

Configuration

In order to connect to a database you need to have either an ODBC driver or a JDBC driver for your particular SQL database. Because the SQL Plug-in is written in Java, internally it uses JDBC; if you use ODBC, it does so through Sun's JDBC-ODBC bridge (on Windows - on the Mac it's written in Java and C++). As you can imagine, this extra step does cause some overhead, so whenever possible use JDBC drivers in combination with the plug-in. Another benefit of JDBC drivers is that the same driver (if it is a type 4 driver) will work on all platforms and, in general, JDBC drivers are available for free. More information on this topic and locations where to find a JDBC driver for your database can be found at: < http://www.profdata.nl/pdm/generic.jsp?taxonomy_id=5;26;155 >

To use ODBC make sure you have your ODBC data source configured in your ODBC control panel. You will use the DSN (Data Source Name) you entered when you connect via the plug-in.

If you're using JDBC, then follow these steps to install the JDBC driver in the right place:

    On Windows: put the driver file (usually one.jar or.zip file) in the place of your choice (for example c:jdbc) and make a classpath setting to point to it.

    On Windows XP/2000 you do this by right clicking 'My Computer, Choose Properties, Advanced, Environment variables. If the variable Classpath is already defined add the full file location to it (use a semicolon as separator between multiple classpaths), if it's not defined add it to your environment settings (See picture xp-classpath.gif).

    On Windows 95/98/ME the classpath has to be defined in your AUTOEXEC.BAT. If you already find a classpath there, add the filename to it; otherwise, create an entry: SET CLASSPATH=C:JDBCORACLEDRIVER.ZIP
Do NOT put any additional spaces anywhere as windows is not very fond of that. After editing your autoexec.bat file make sure to reboot in order to load the classpath setting.

    Macintosh classic (Mac OS 8 and 9): put the JDBC driver file in the MRJClasses folder located in System Folder:Extensions:MRJ Libraries

    Mac OS X: put the JDBC driver file into the Extensions folder inside the Java folder inside the Library folder (/Library/Java/Extensions)

That's it, we're now ready to open a connection to the database for which we have just installed the jdbc (or configured ODBC) driver.

Connecting to a database

Those of you who have already used plug-ins in the past know that the Plug-in API can only be accessed through the calculation engine. This means that you can only 'call' a plug-in by using SetField, using a calculation field, auto enter options or Replace. In most cases, the Set Field script step is used as that gives you the highest level of control over the actions, and the best possibilities to trap errors. The basic syntax for using SQL Plug-In commands would then look like this:

Set Field [ResultField, External ("SQLPlug-in command", "parameters")]

Because you can only pass a single parameter at once to a plug-in, the pipeline (|) symbol is generally used if more then one parameter has to be passed to a plug-in. This format is supported by all FileMaker Pro plug-ins.

To open a database connection you need to follow the following steps:
1. set the driver you use.
2. set the URL to the database.
3. use the Open command to open the connection.

For your convenience the plug-in ships with a small database called 'connecting' that contains connection scripts for all popular databases. Let's have a look at how to connect to an Oracle database using JDBC:

1. Make sure you have the Oracle JDBC driver installed (see previous chapter)
2. Use the following script steps to connect to oracle:

External ("SQL-setDriver", "oracle.jdbc.driver.OracleDriver")
External("SQL-setURL", "jdbc:oracle:thin:@")
External("SQL-open","192.168.1.2:1521:orcl|scott|tiger)

192.168.1.2 = the IP address of the database server
1521 = the port on which the database is listening for connections
orcl = the database name (also called service name or instance in the oracle world)
scott = the username
tiger = the password of the user connecting to the database

If the connection is opened correctly, the Plug-In returns "OK" to the field you are using in your SetField script step. If an error occurs, "EXCEPTION" is returned. To obtain more details on the occurred error, turn on error reporting with the following command:

External(SQL-enableReporting,"3")

It is good practice to have one global text field (such as, "gStatus") where you perform all your SetField scripts on -- that way you can always check in that same field if errors occur.

EXTREMELY IMPORTANT Note: Make SURE that you TURN OFF "Smart Quotes" on your FileMaker file(s). Do this by choosing Edit -> Preferences -> "Document…" and UNCHECKING the checkbox for 'Use smart quotes.'

Performing queries

Now that we have a connection with the database we can perform queries or other SQL statements. Lets start with executing a query:

SetField [gStatus , External ("SQL-execQuery", "Select customer_number, phonenumber from customers where customername = 'John Smith'")]

In the example above we are asking the database for the fields customer_number and phonenumber from the table "customers," where the customername is John Smith.

Author's Note: Here are a couple of syntax differences between SQL and FileMaker Pro. In FileMaker we commonly use the word 'file' for what is referred to in the SQL world as a 'table'; a 'field' in FileMaker is commonly referred to as a 'column' in SQL; and a 'row' in SQL is the equivalent of a 'record' in FileMaker Pro.

You can also execute a SQL statement that you have typed into a field. For example, if you have typed your SQL statement into the FileMaker field "SQLStatement," your plug-in call would look like this:

SetField [gStatus , External ("SQL-execQuery",SQLStatement)]

Using a FileMaker field to store, compose and calculate your SQL queries makes your scripts more readable and your SQL statements easier to debug.

When you execute a query, you don't get results back directly. This is because the Plug-In API can only return one text string to FileMaker, yet the plug-in has different commands to pull the data into FileMaker, see the 'Retrieving results' section for details.

Author's Note: Note that FileMaker Pro uses double quotes to identify text and SQL uses single quotes. On the downside, combining FileMaker and SQL text in one field can make your calculations difficult to read in that you will often find yourself in a situation where you will have single and double quotes next to each other. The font used in FileMaker's calculation dialog is sometimes very hard to read. I suggest copy-pasting your calculation to an external editor (BBEdit, Ultraedit, Notepad) and using the Courier font to create your SQL statements. Alternatively, you could use a calculation field called "sq" (stands for "Single Quote") with a calculation of a single quote within two double quotes ("'"), and then use the field name in place of single quotes in FileMaker calculations.

Calculating queries

In many cases you will want to combine one or more FileMaker Pro fields to compose the SQL statement you are sending to the SQL database. For example, in FileMaker Pro you have a customerid, and you want to retrieve the address information from a SQL database for that customer.

Let's make a calculation field to 'generate' the correct SQL query:

Sql_calc =
"SELECT ADDRESS, CITY FROM CUSTOMERS WHERE CUSTOMERID = " & customerid

The result of this calculation would be:

SELECT ADDRESS, CITY FROM CUSTOMERS WHERE CUSTOMERID = 1

(assuming the customer id of the currently selected customer in FileMaker Pro is 1 and the datatype in FileMaker is a NUMBER and the datatype in your SQL database is also a number.)

Now we can execute this query with the following command:

SetField [gStatus , External ("SQL-execQuery",Sql_calc)]

Once the query is executed, the results are ready to be pulled into FileMaker Pro.

Retrieving results

As mentioned earlier, the Plug-in API is limited to returning one text string to FileMaker Pro. To retrieve the SQL query results into your FileMaker Pro database, the plug-in offers 4 different commands. Each command has its own particular benefits. Let's look at each of them with an example of when and how you would use them in your own solution:

SQL-getColumn

With getColumn you retrieve one column each time you execute the command. So if you have 4 columns in your resultset, you would have to execute it 4 times to retrieve your results.

Example. First we execute our query using the execQuery command:

Select name, address, city, country from customers where companyname = 'PDM'

If the data in the result is: Jan Aleman, 15 Broadway, Amsterdam, Holland, then the first GetColumn call will return 'Jan Aleman,' the second '15 Broadway,' and so on. GetColumn is useful to retrieve small amounts of data. GetColumn is great if you use a SQL command to count the number of records in a SQL database, or you are calling a stored procedure that returns a success or fail result. It's NOT very good to use on large recordsets because of the numerous SetField script steps necessary to pull out the data.

SQL-getRow

GetRow returns one row at a time. The column data is separated with pipe symbols ('|'), which can then be parsed in FileMaker Pro. Using the same query as in the previous example, getRow would return:

Jan Aleman|15 Broadway|Amsterdam|Holland

SQL-exportRows

ExportRows creates a tab-separated file on disk with all the results in it. After calling exportRows, you can use FileMaker Pro's "regular" import function to import the data. This function is very useful to retrieve larger amounts of data into FileMaker Pro. Speed is only limited to FileMaker Pro's import speed, the speed of your SQL database and your network bandwidth. To illustrate how fast it is: pulling 1000 records into FileMaker Pro takes less than two seconds. Before you import a large number of records, you should always have a close look at what you want to achieve: is it really necessary to pull all of the data into FileMaker Pro? Keep in mind that a lot of aggregation and calculation functions are supplied by SQL databases, and most of them do this much faster than FileMaker Pro.

SQL-directColumn

directColumn is a special version of execQuery as it both performs the SQL query and returns the result(s) in the same call. You can also use it with the switch |R to retrieve all results of your query into one field. Example:

SetField [phonenumber , External ("SQL-directColumn", "Select phonenumber from customers where customername = 'John Smith'")]

The call above will return the phone number directly into the phoneumber field in FileMaker Pro. You can even create an unstored calculation field in FileMaker pro to pull the data into FileMaker without a script:

Phonenumber (unstored calc, result is text)
External ("SQL-directColumn", "Select phonenumber from customers where customername = 'John Smith'")]

Be careful if you use calculation fields to call the plug-in!! For example, if you add the above calculation to an existing FileMaker Pro database with 100,000 records and you use a stored calculation, FileMaker Pro will do a plug-in call 100,000 times. You also don't have control over errors that occur. If you use it with unstored calcs, avoid list views and make sure you have an open connection before displaying a layout with a calculated field. However, using an unstored calculation field is an excellent option for displaying 'live' data from SQL servers.

Updating data

To perform any other SQL command (other than searching data using the "SELECT" verb), use the plug-in function doSQL. With doSQL you can perform any SQL command that is supported by your backend database, including some very powerful commands like creating and deleting tables, databases, columns and data.

The updating of data in SQL is accomplished using the UPDATE command. Keep in mind that the approach in SQL is quite different from FileMaker Pro. The closest thing in FileMaker is the "Replace" command. The UPDATE command consists of the following items:

UPDATE TABLENAME SET FIELD=value[,field2=value…] [WHERE wherestatement]

Where:
Tablename is the name of the table in which you want to update rows
Field is the name of the field you want to update
Value is the value you want it to change to
Wherestatement is to select which row(s) you want to update

For example: if in the customers table we want to update the address of pdm from 15 Broadway to 16 Broadway, we would use this UPDATE statement:

UPDATE CUSTOMERS SET ADDRESS='16 Broadway' where companyname = 'PDM'

NOTE that if we would leave out the WHERE clause we would update ALL rows in the entire table with 16 Broadway. So ALWAYS BE SURE to use a WHERE clause to perform updates against a SQL database. If you are not sure if your update statement is correct, use a transaction (supported by most SQL databases). Transactions allow your SQL database to "undo" the last command(s). There are plug-in commands to initiate and commit a transaction if transactions are available in your SQL database. Short Example:

Begin transaction
Perform update you're not sure of
Do a query to check if it went right
Rollback because update was wrong (original data is restored), or perform commit if the update was "OK."

Until you end a transaction (by issuing the "commit" command), your changes are not visible to other users (this behavior can be changed on most SQL databases). The DEFAULT mode of the plug-in is set to automatically issue a "commit" command after each doSQL command.

You can also perform an update using a FileMaker Pro calculation field. Let's assume we have changed the address in the example above and we want to calculate the correct insert statement in FileMaker:

Update_calc =
'UPDATE CUSTOMERS SET ADDRESS='" & fmpaddressfield & "' WHERE COMPANYNAME = '" & fmpcompanyfield & "'"

Note in the calculation above that we have to generate the single quote SQL text qualifiers around our concatenated FileMaker Pro data.

Now you can execute this to your database by using:

SetField [phonenumber , External ("SQL-doSQL", Update_Calc)]

The plug-in will return OK if the update went well.

Inserting data

The syntax of an insert statement looks like this:

INSERT INTO TABLENAME (COLUMN-NAMES) VALUES (VALUES)

Where:
Tablename is the name of the table into which you want to insert data
Column-names are the names of the columns you want to insert data into (you can leave this out, but then you have to provide values for all columns)
Values are the data you want to insert into the table

Example:

INSERT INTO CUSTOMERS (COMPANYNAME, CITY, COUNTRY) VALUES ('ClickWare','Los Angeles','USA')

You can calculate and execute statements the same way as update statements.


Author's Note: To see more advanced insert and update calculations have a look at the 'transparent' example file that comes with the SQL Plug-in.


Conclusion

This article only scratches the surface of the possibilities of the plug-in and SQL. The SQL Plug-In combined with FileMaker Pro make an excellent choice for developing front-ends for SQL databases. With FileMaker Pro you have maximum control over your front-end look and feel, data entry and validation, while a SQL database gives you enterprise speed, scalability and availability.

Call for feedback: Would you be interested in more SQL articles? Which topic should they cover? Send your feedback to <clint@filemakermagazine.com>. Your feedback will be used for further SQL articles.

Happy FileMaking!

Jan Aleman, Professional Data Management <jaleman@profdata.nl>
Jan Aleman is a Java programmer, Oracle and PostgreSQL dba and provides support for SQL Plug-In developers.