FileMaker 11 ® ODBC and JDBC Guide
© 2004–2010 FileMaker, Inc. All Rights Reserved. FileMaker, Inc. 5201 Patrick Henry Drive Santa Clara, California 95054 FileMaker is a trademark of FileMaker, Inc. registered in the U.S. and other countries. The file folder logo is a trademark of FileMaker, Inc. All other trademarks are the property of their respective owners. FileMaker documentation is copyrighted. You are not authorized to make additional copies or distribute this documentation without written permission from FileMaker.
Contents Chapter 1 Introduction About this guide About ODBC and JDBC Using FileMaker software as an ODBC client application Importing ODBC data Adding ODBC tables to the relationships graph Using a FileMaker database as a data source Accessing a hosted FileMaker Pro database Limitations with third-party tools Networking requirements Updating files from previous versions Installing current drivers 7 7 8 8 8 9 9 10 10 10 10 Chapter 2 Accessing external SQL data sources Importing ODBC data Executing SQL to i
FileMaker ODBC and JDBC Guide Chapter 4 Using ODBC to share FileMaker data About ODBC Using the ODBC client driver Overview of accessing a FileMaker database file Accessing a FileMaker database file from a Windows application Specifying ODBC client driver properties for a FileMaker DSN (Windows) Verifying access via ODBC (Windows) Accessing a FileMaker database file from a Mac OS application Specifying ODBC client driver properties for a FileMaker DSN (Mac OS) Verifying access via ODBC (Mac OS) 21 22 2
| Contents 5 Chapter 7 Supported standards Support for Unicode characters SQL statements SELECT statement SQL clauses FROM clause WHERE clause GROUP BY clause HAVING clause UNION operator ORDER BY clause FOR UPDATE clause DELETE statement INSERT statement UPDATE statement CREATE TABLE statement ALTER TABLE statement CREATE INDEX statement DROP INDEX statement SQL aggregate functions SQL expressions Field names Constants Exponential/scientific notation Numeric operators Character operators Date operators R
FileMaker ODBC and JDBC Guide Chapter 8 Reference Information Mapping FileMaker fields to ODBC data types Mapping FileMaker fields to JDBC data types ODBC and JDBC error messages ODBC error messages JDBC error messages 59 59 60 60 60 Index 61
Chapter 1 Introduction This guide describes how you can use FileMaker® software as an ODBC client application and as a data source for ODBC and JDBC applications. The following table gives an overview of how to use ODBC and JDBC with FileMaker software. What do you want to do? How do you do it? Product 1 Use FileMaker software as an 1. Interactively via the relationships graph 1 ODBC client application. 2. One-time, static via ODBC import or 1 1 Access ODBC data stored in File menu > Open.
FileMaker ODBC and JDBC Guide Using ODBC or JDBC APIs, a client application communicates with a driver manager that identifies the client driver to communicate with a data source. Client Application Driver Manager Client Driver Client Driver FileMaker software can act either as a client application or as a data source. Using FileMaker software as an ODBC client application As an ODBC client application, FileMaker software can access data in external SQL data sources.
Chapter 1 | Introduction 9 Using a FileMaker database as a data source As a data source, FileMaker data is shared with ODBC- and JDBC-compliant applications. The application connects to the FileMaker data source using the FileMaker client driver, constructs and executes the SQL queries using ODBC or JDBC, and processes the data retrieved from the FileMaker database solution.
FileMaker ODBC and JDBC Guide Limitations with third-party tools Microsoft Access: When using Microsoft Access to view data in a FileMaker data source, do not use data from a summary field. The summary field’s data should not be edited in Microsoft Access, and the data value that is displayed in Microsoft Access may not be accurate. Networking requirements You need a TCP/IP network when using FileMaker Server Advanced to host a FileMaker database file as a data source over a network.
Chapter 2 Accessing external SQL data sources As an ODBC client application, FileMaker software can access data in external SQL data sources. FileMaker software connects to the external SQL data source using the client driver for the ODBC data source, and either imports ODBC data or works with ODBC tables in the relationships graph. Whether you are importing ODBC data or working with ODBC tables in the relationships graph, you must configure a driver for the ODBC data source you’re using.
FileMaker ODBC and JDBC Guide 1 In FileMaker Pro, do one of the following: 1 To import into an existing FileMaker Pro file, choose File menu > Import Records > ODBC Data Source. 1 To create a FileMaker Pro file from the data source records, choose File menu > Open. In the Open dialog box, choose ODBC Data Source for Files of type (Windows) or Show (Mac OS). Choose your data source, enter the user name and password (if any), and click OK to open the FileMaker Pro SQL Query builder dialog box.
Chapter 2 | Accessing external SQL data sources 13 When you use FileMaker Pro or FileMaker Server as the host for a solution that includes ODBC tables in the relationships graph, you configure the ODBC client driver for the external SQL data source on the host machine.
FileMaker ODBC and JDBC Guide See FileMaker Pro Help for detailed steps and additional information on configuring an ODBC client driver, connecting to ODBC data sources, editing ODBC data sources, and setting up an ODBC table in the relationships graph.
Chapter 3 Installing FileMaker ODBC client drivers These instructions help you install the ODBC client driver needed to access FileMaker as a data source from third-party and custom applications via ODBC (Open Database Connectivity). The ODBC client driver is available through a separate installation on your FileMaker installation disk or electronic download in the xDBC folder. The latest versions of the client drivers are also available from this URL: www.filemaker.
FileMaker ODBC and JDBC Guide To install the ODBC client driver: 1. Do one of the following: 1 If you received your software electronically, double-click the installation icon (.exe file). 1 If you have an installation disk, insert the disk into the drive. 2. In the FileMaker Pro or FileMaker Server window, double-click the xDBC folder. 3. In the xDBC folder, double-click the ODBC Client Driver Installer folder. 4. In the ODBC Client Driver Installer folder, double-click the FMODBC_Installer_Win32.
Chapter 3 | Installing FileMaker ODBC client drivers 17 7. For Host: 1 If you’re connecting to a database file hosted by FileMaker Pro on your local machine, enter localhost or the IP address 127.0.0.1. 1 If you’re connecting to a database file hosted by FileMaker Server Advanced over a network, enter the IP address of the FileMaker Server. If you have enabled sharing via ODBC/JDBC in the host application, you can select Connect to host to obtain the names of available databases. Click Next.
FileMaker ODBC and JDBC Guide 4. In the ODBC Client Driver Installer folder, double-click FileMaker ODBC.mpkg. The FileMaker ODBC Driver Installer opens. 5. Install the ODBC client driver by following the on-screen instructions. 6. When the installation is complete, click Close. The ODBC client driver will be installed in this folder: /Library/ODBC Note You cannot change the installation folder for the ODBC client driver.
Chapter 3 | Installing FileMaker ODBC client drivers 19 6. For Host: 1 If you’re connecting to a database file hosted by FileMaker Pro on your local machine, enter localhost or the IP address 127.0.0.1. 1 If you’re connecting to a database file hosted by FileMaker Server Advanced over a network, enter the IP address of the FileMaker Server. If you have enabled sharing via ODBC/JDBC in the host application, you can select Connect to host to obtain the names of available databases. Click Continue.
FileMaker ODBC and JDBC Guide
Chapter 4 Using ODBC to share FileMaker data Use the ODBC client driver to connect to a FileMaker data source from another application. The application that uses the ODBC client driver can directly access the data in a FileMaker database file. The FileMaker ODBC client driver is FileMaker ODBC. Note You can also use FileMaker Pro as an ODBC client application, interacting with records from another data source via ODBC using SQL.
FileMaker ODBC and JDBC Guide Using the ODBC client driver You can use the ODBC client driver with any ODBC-compliant application.
Chapter 4 | Using ODBC to share FileMaker data 23 5. Construct and execute an SQL query in the client application. Each FileMaker database file that is open and set up for access is a separate data source (you create a DSN for each FileMaker database file you want to access as a data source). Each database can have one or more tables. FileMaker fields are represented as columns. The complete field name, including any non-alphanumeric characters, displays as the column name.
FileMaker ODBC and JDBC Guide 7. For Database, select a database from the list of available databases, or type the filename of the FileMaker database file you’re using as a data source. Note For database files hosted by FileMaker Server Advanced, the list of databases may be filtered based on the File Display Filter setting. See FileMaker Server Help for information. If you need special handling of non-English text, click Advanced Language. The Advanced Language Options dialog box opens.
Chapter 4 | Using ODBC to share FileMaker data 25 1 Make sure your FileMaker account uses a privilege set with the extended privilege of Access via ODBC/ JDBC. 1 Verify that the FileMaker Pro or FileMaker Server host application has been set up for sharing via ODBC/JDBC. Accessing a FileMaker database file from a Mac OS application Specifying ODBC client driver properties for a FileMaker DSN (Mac OS) Create a DSN for each FileMaker database file you want to access as a data source.
FileMaker ODBC and JDBC Guide 8. For Database, select a database from the list of available databases, or type the filename of the FileMaker database file you’re using as a data source. Note For database files hosted by FileMaker Server Advanced, the list of databases may be filtered based on the File Display Filter setting. See FileMaker Server Help for information. If you need special handling of non-English text, click Advanced Language. The Advanced Language Options dialog box opens.
Chapter 5 Installing FileMaker JDBC client drivers These instructions help you install the client driver needed to access FileMaker as a data source from thirdparty and custom applications via JDBC (Java Database Connectivity). The client driver is available on your FileMaker DVD or electronic download in the xDBC folder. The latest versions of the client drivers are also available from this URL: www.filemaker.
FileMaker ODBC and JDBC Guide 4. Copy the fmjdbc.jar file to the appropriate folder for your operating system: 1 Windows: Copy the fmjdbc.jar file to the folder that includes your Java executable file (java.exe) or to another folder location included in the ClassPath of your Java application. 1 Mac OS: Copy the fmjdbc.jar file to the /Library/Java/Extensions folder or to another folder location included in the ClassPath of your Java application.
Chapter 6 Using JDBC to share FileMaker data If you’re a Java programmer, you can use the JDBC client driver with any Rapid Application Development (RAD) tool to visually create a Java application or applet that connects to a FileMaker data source. The Java application or applet that uses the JDBC client driver can directly access the data in a FileMaker database file. About JDBC JDBC is a Java API for executing SQL statements, the standard language for accessing relational databases.
1 1 1 1 1 1 FileMaker ODBC and JDBC Guide Retrieving and updating the object referenced by a Ref object Updating of columns containing CLOB, ARRAY and REF data types Boolean data type DATALINK data type Transform groups and type mapping Relationship between the JDBC SPI and the Connector architecture For additional details, see www.filemaker.com/support/technologies. The JDBC client driver has been tested against the Java Development Kit (JDK) 1.5 (Mac OS) and 1.6 (Windows).
Chapter 6 | Using JDBC to share FileMaker data 3. Returns error codes. import java.sql.*; class FMPJDBCTest { public static void main(String[ ] args) { // register the JDBC client driver try { Driver d = (Driver)Class.forName("com.filemaker.jdbc.Driver").newInstance(); } catch(Exception e) { System.out.println(e); } // establish a connection to FileMaker Connection con; try { con = DriverManager.getConnection(“jdbc:filemaker://192.168.1.
FileMaker ODBC and JDBC Guide Specifying driver properties in the URL subname Specify the user and password driver properties in the subname of the JDBC URL. These are the properties that could be passed to the connection when calling the DriverManager.getConnection method via the Properties parameter.
Chapter 6 | Using JDBC to share FileMaker data 33 Verifying access via JDBC When verifying access to a FileMaker database file via JDBC, make sure: 1 The FileMaker database file is hosted and available. 1 Your FileMaker account uses a privilege set with the extended privilege of Access via ODBC/JDBC. 1 The FileMaker Pro or FileMaker Server Advanced host application has been set up for sharing via ODBC/JDBC.
FileMaker ODBC and JDBC Guide
Chapter 7 Supported standards This chapter describes the SQL statements and constructs supported by the FileMaker ODBC and JDBC client drivers. Use the client drivers to access a FileMaker database solution from an ODBC- or JDBCcompliant application. The FileMaker database solution can be hosted by either FileMaker Pro or FileMaker Server Advanced. The ODBC client driver supports ODBC 3.5 Level 1 with some features of Level 2. The JDBC client driver provides partial support for the JDBC 3.0 specification.
FileMaker ODBC and JDBC Guide The SELECT statement can use a variety of clauses: SELECT [DISTINCT] {* | column_expression [[AS] column_alias],...} FROM table_name [table_alias], ... [ WHERE expr1 rel_operator expr2 ] [ GROUP BY {column_expression, ...} ] [ HAVING expr1 rel_operator expr2 ] [ UNION [ALL] (SELECT...) ] [ ORDER BY {sort_expression [DESC | ASC]}, ... ] [ FOR UPDATE [OF {column_expression, ...}] ] Items in brackets are optional.
Chapter 7 | Supported standards 37 FROM clause The FROM clause indicates the tables that are used in the SELECT statement. The format is: FROM table_name [table_alias] [, table_name [table_alias]] table_name is the name of a table in the current database. table_alias can be used to give the table a more descriptive name, to abbreviate a longer table name, or to include the same table in the query more than once (for example, in self-joins).
FileMaker ODBC and JDBC Guide The WHERE clause can also use expressions such as these: WHERE expr1 IS NULL WHERE NOT expr2 Note If you use fully qualified names in the SELECT (projection) list, you must also use fully qualified names in the related WHERE clause. GROUP BY clause The GROUP BY clause specifies the names of one or more fields by which the returned values should be grouped. This clause is used to return a set of aggregate values.
Chapter 7 | Supported standards 39 ORDER BY clause The ORDER BY clause indicates how the records are to be sorted. The format is: ORDER BY {sort_expression [DESC | ASC]}, ... sort_expression can be field names, expressions, or the positional number of the column expression to use. The default is to perform an ascending (ASC) sort.
FileMaker ODBC and JDBC Guide Additional examples: Using Sample SQL text constant SELECT 'CatDog' FROM Salespeople numeric constant SELECT 999 FROM Salespeople date constant SELECT DATE '2011-06-05' FROM Salespeople time constant SELECT TIME '02:49:03' FROM Salespeople timestamp constant SELECT TIMESTAMP '2011-06-05 02:49:03' FROM Salespeople text column SELECT Company_Name FROM Sales_Data SELECT DISTINCT Company_Name FROM Sales_Data numeric column SELECT Amount FROM Sales_Data SELECT DI
Chapter 7 | Supported standards 41 To retrieve file reference information from a container field, such as the file path to a file, picture, or Quicktime movie, use the CAST function with a SELECT statement. For example: SELECT CAST(Company_Brochures AS VARCHAR(NNN)) FROM Sales_Data In this example, if you: 1 Inserted a file into the container field using FileMaker Pro but stored only a reference to the file, the SELECT statement retrieves the file reference information as type SQL_VARCHAR.
FileMaker ODBC and JDBC Guide DELETE statement Use the DELETE statement to delete records from a database table. The format of the DELETE statement is: DELETE FROM table_name [ WHERE { conditions } ] Note The WHERE clause determines which records are to be deleted. If you don’t include the WHERE keyword, all records in the table are deleted (but the table is left intact).
Chapter 7 | Supported standards 43 Here's an example of an INSERT statement that uses a SELECT statement: INSERT INTO emp1 (first_name, last_name, emp_id, dept, salary) SELECT first_name, last_name, emp_id, dept, salary from emp WHERE dept = ‘D050’ In this type of INSERT statement, the number of columns to be inserted must match the number of columns in the SELECT statement.
FileMaker ODBC and JDBC Guide CREATE TABLE statement Use the CREATE TABLE statement to create a table in a database file. The format of the CREATE TABLE statement is: CREATE TABLE table_name table_element_list [DEFAULT expr][UNIQUE][NOT NULL] Within the statement, you specify the name and data type of each column. 1 table_name and table_element_list have a 100 character limit. 1 The DEFAULT keyword allows you to set a default value for a column.
Chapter 7 | Supported standards 45 Examples To Sample SQL add columns ALTER TABLE Salespeople ADD C1 VARCHAR remove columns ALTER TABLE Salespeople DROP C1 set the default value for a column ALTER TABLE Salespeople ALTER Company SET DEFAULT ‘FileMaker’ remove the default value for ALTER TABLE Salespeople ALTER Company DROP DEFAULT a column Note SET DEFAULT and DROP DEFAULT do not affect existing rows in the table, but change the default value for rows that are subsequently added to the table.
FileMaker ODBC and JDBC Guide The PREVENT INDEX CREATION attribute is not supported. Example DROP INDEX ON Salespeople.Salesperson_ID SQL aggregate functions Aggregate functions return a single value from a set of records. You can use an aggregate function as part of a SELECT statement, with a field name (for example, AVG(SALARY)), or in combination with a column expression (for example, AVG(SALARY * 1.07)).
Chapter 7 | Supported standards 47 Field names The most common expression is a simple field name, such as calc or Sales_Data.Invoice_ID. Constants Constants are values that do not change. For example, in the expression PRICE * 1.05, the value 1.05 is a constant. Or you might assign a value of 30 to the constant Number_Of_Days_In_June. You must enclose character constants in pairs of single quotation marks (').
FileMaker ODBC and JDBC Guide Exponential/scientific notation Numbers can be expressed using scientific notation. Example SELECT column1 / 3.4E+7 FROM table1 WHERE calc < 3.4E-6 * column2 Numeric operators You can include the following operators in number expressions: +, -, *, /, and ^ or ** (exponentiation). You can precede numeric expressions with a unary plus (+) or minus (-). Character operators You can concatenate characters.
Chapter 7 | Supported standards 49 Relational operators Operator Meaning = Equal <> Not equal > Greater than >= Greater than or equal to < Less than <= Less than or equal to LIKE Matching a pattern NOT LIKE Not matching a pattern IS NULL Equal to Null IS NOT NULL Not equal to Null BETWEEN Range of values between a lower and upper bound IN A member of a set of specified values or a member of a subquery NOT IN Not a member of a set of specified values or a member of a subquery
FileMaker ODBC and JDBC Guide SELECT * FROM Sales_Data WHERE EXISTS (SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Salesperson_ID IS NOT NULL) SELECT * FROM Sales_Data WHERE Sales_Data.Amount = ANY (SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Salesperson_ID = 'SP-1') SELECT * FROM Sales_Data WHERE Sales_Data.Amount = ALL (SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Salesperson_ID IS NULL) Logical operators You can combine two or more conditions.
Chapter 7 | Supported standards Functions that return character strings Functions that return character strings Description Example CHR Converts an ASCII code to a one-character string CHR(67) returns C CURRENT_USER Returns the login ID specified at connect time DAYNAME Returns the name of the day that corresponds to a specified date.
FileMaker ODBC and JDBC Guide SELECT SUBSTR(Salespeople.Salesperson_ID, 2) + SUBSTR(Salespeople.Salesperson_ID, 4) AS agg FROM Salespeople SELECT SPACE(2) + Salespeople.Salesperson_ID AS Salesperson_ID FROM Salespeople SELECT STRVAL('60506') AS agg FROM Sales_Data WHERE Sales_Data.
FileMaker ODBC and JDBC Guide Functions that return numbers Functions that return numbers Description ABS Returns the absolute value of the numeric expression ATAN Returns the arc tangent of the argument as an angle expressed in radians ATAN2 Returns the arc tangent of x and y coordinates as an angle expressed in radians B Returns the decimal equivalent of a binary number CEIL CEILING Returns the smallest integer value that is greater than or equal to the argument DEG DEGREES Returns the n
FileMaker ODBC and JDBC Guide Functions that return numbers Description Example SIGN An indicator of the sign of the argument: -1 for negative, 0 for 0, and 1 for positive.
Chapter 7 | Supported standards 55 The following example shows the importance of precedence: WHERE salary > 40000 OR hire_date > {d ‘2008/01/30’} AND dept = 'D101' Because AND is evaluated first, this query retrieves employees in department D101 hired after January 30, 2008, as well as every employee making more than $40,000, no matter what department or hire date. To force the clause to be evaluated in a different order, use parentheses to enclose the conditions to be evaluated first.
FileMaker ODBC and JDBC Guide For example, the following Create Table statement shows how to use the "OID" keyword as a data element name.
Chapter 7 SESSION_USER SET SIZE SMALLINT SOME SPACE SQL SQLCODE SQLERROR SQLSTATE STRVAL SUBSTRING SUM SYSTEM_USER TABLE TEMPORARY THEN TIME TIMESTAMP TIMESTAMPVAL TIMEVAL TIMEZONE_HOUR TIMEZONE_MINUTE TO TODAY TRAILING TRANSACTION TRANSLATE TRANSLATION TRIM TRUE UNION UNIQUE UNKNOWN UPDATE UPPER USAGE USER USERNAME USING USAGE USER USERNAME USING VALUE VALUES VARBINARY VARCHAR VARYING VIEW WHEN WHENEVER WHERE WITH WORK WRITE YEAR ZONE | Supported standards 57
FileMaker ODBC and JDBC Guide
Chapter 8 Reference Information Mapping FileMaker fields to ODBC data types This table illustrates how FileMaker field types map to the standard ODBC data types. FileMaker field type Converts to ODBC data type About the data type text SQL_VARCHAR The maximum column length of text is 1 million characters, unless you specify a smaller Maximum number of characters for the text field in FileMaker. FileMaker returns empty strings as NULL.
FileMaker ODBC and JDBC Guide FileMaker field type Converts to JDBC SQL type container java.sql.Types.BLOB calculation specified by the data type of the calculation’s result The JDBC client driver converts the FileMaker calculation data type to the JDBC SQL type matching the calculation’s result. For example, the JDBC client driver converts a FileMaker calculation that results in a timestamp data type to java.sql.Types.TIMESTAMP.
Index A ABS function 53 Access via ODBC/JDBC extended privilege 22 accounts and privileges 22 aggregate functions in SQL 46 ALL operator 49 ALTER TABLE (SQL statement) 44 AND operator 50 ANY operator 49 ARRAY data type 30 ATAN function 53 ATAN2 function 53 auto-generated keys 29 B B function 53 BETWEEN operator 49 binary data use in SELECT 40 bitmap files in container fields 41 blank characters 48 blank value in columns 42 BLOB data type use in CREATE TABLE 44 use in SELECT 40 Boolean data type 30 C CAST
FileMaker ODBC and JDBC Guide DEGREES function 53 DELETE (SQL statement) 42 disabling a shared FileMaker database file 9 DISTINCT operator 36 driver properties JDBC client driver 31 ODBC client driver (Mac OS) 25 ODBC client driver (Windows) 23 drivers uninstalling old 10 DROP INDEX (SQL statement) 45 DSNs creating (Mac OS) 25 creating (Windows) 23 one per file 10 E empty string use in SELECT 40 error message formats 60 EXISTS operator 49 EXP function 53 exponential notation in SQL expressions 48 expr
| LOG function 53 logical operators in SQL expressions 50 LOWER function 51 LTRIM function 51 M Mac OS creating a DSN 25 JDBC client driver requirements 27 ODBC client driver requirements 15 verifying ODBC access 26 mapping data types JDBC client driver 59 ODBC client driver 59 MAX function 53 meta data functions for JDBC 55 MIN function 53 MINUTE function 53 MOD function 53 MONTH function 53 MONTHNAME function 51 operator precedence in SQL expressions 54 OR operator 50 ORDER BY (SQL clause) 39 OUTER JOIN
FileMaker ODBC and JDBC Guide Server Data Source 26 sharing, setting up ODBC/JDBC 22 SIGN function 54 SIN function 54 SPACE function 51 SQL aggregate functions 46 SQL expressions 46 character operators 48 constants 47 date operators 48 exponential or scientific notation 48 field names 47 functions 50 literals 47 logical operators 50 numeric operators 48 operator precedence 54 relational operators 49 SQL standards compliance 35 SQL statements ALTER TABLE 44 CREATE INDEX 45 CREATE TABLE 44 DELETE 42 DROP
