FileMaker 8 ® ODBC and JDBC Developer’s Guide
© 2004-2005 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, and ScriptMaker and the file folder logo are trademarks of FileMaker, Inc. All persons and companies listed in the examples are purely fictitious and any resemblance to existing persons and companies is purely coincidental. FileMaker documentation is copyrighted.
Contents Chapter 1 Introduction About this guide Using ODBC and JDBC with FileMaker Using a FileMaker database file as a data source Networking requirements Using FileMaker Pro as a client application Updating files from previous versions If you previously shared a FileMaker database file as a data source If you previously used FileMaker Pro as a client to access a data source Chapter 2 Using ODBC to share FileMaker data About ODBC Using the ODBC client driver Accessing a FileMaker database file - overview
FileMaker ODBC and JDBC Developer’s Guide Chapter 4 Supported standards Support for Unicode characters SQL statements and clauses SELECT statement DELETE statement INSERT statement UPDATE statement CREATE TABLE statement ALTER TABLE statement CREATE INDEX statement DROP INDEX statement FROM clause WHERE clause GROUP BY clause HAVING clause UNION operator ORDER BY clause FOR UPDATE clause SQL aggregate functions SQL expressions Field names Constants and literals Exponential/scientific notation Numeric op
| Appendix C ODBC and JDBC error messages Contents ODBC error messages ODBC driver error messages ODBC Driver Manager error messages SequeLink Client error messages SequeLink Server error messages Data source error messages JDBC error messages JDBC driver error messages SequeLink Server error messages Data source error messages 47 47 47 47 47 48 48 48 48 48 49 Index 51 5
FileMaker ODBC and JDBC Developer’s Guide
Chapter 1 Introduction Welcome to the FileMaker® ODBC and JDBC Developer’s Guide. This guide explains concepts and details to help you share FileMaker data with other applications, using ODBC (Open Database Connectivity) and JDBC (Java Database Connectivity). This guide also documents how the ODBC and JDBC client drivers, when used with FileMaker Pro and FileMaker Server Advanced, support the industry standards for ODBC, JDBC, and SQL (Structured Query Language).
FileMaker ODBC and JDBC Developer’s Guide SQL is passed through the ODBC and JDBC interfaces to the FileMaker host of the data source, performing queries such as SELECT first_name, last_name FROM customers WHERE city='Paris' and making updates such as the creation of a new record with INSERT INTO customers (first_name, last_name) VALUES ('Jane','Smith').
Chapter 1 | Introduction 9 Limitations with third-party tools Microsoft Query Wizard 1 In a FileMaker data source, you cannot access table or column names that use High ASCII or doublebyte characters. Instead, use Microsoft Query and manually enter the characters, enclosed in double quotation marks. Microsoft Access 1 1 In a FileMaker data source, you cannot access table or column names that use High ASCII or doublebyte characters.
FileMaker ODBC and JDBC Developer’s Guide See FileMaker Pro Help for details about sharing via ODBC/JDBC, and for setting up accounts and privilege sets. Note In previous versions of FileMaker Server and FileMaker Pro, you needed to create only one DSN (Data Source Name) for the host application when configuring a driver for access via ODBC. The one DSN allowed you to access any of your FileMaker database files as a data source.
Chapter 2 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. In Windows, the FileMaker ODBC client driver is DataDirect 32-BIT SequeLink 5.4. In Mac OS, the FileMaker ODBC client driver is ivslk18.dylib. You can also use FileMaker Pro as a client application, interacting with records from another data source via ODBC using SQL.
FileMaker ODBC and JDBC Developer’s Guide Using the ODBC client driver You can use the ODBC client driver with any ODBC-compliant application.
Chapter 2 | Using ODBC to share FileMaker data 13 4. Connect to the FileMaker data source. Client applications sometimes use different terminology for accessing a data source via ODBC. Many applications have menu items with names such as Get external data or SQL query. Review the documentation or Help that comes with your application for details. 5. Construct and execute a SQL query in the client application.
FileMaker ODBC and JDBC Developer’s Guide 3. Click Add. If you’re changing the properties of an existing data source, select the data source, click Configure, and skip to step 6. 4. Choose DataDirect 32-BIT SequeLink 5.4 from the list of drivers. If the driver is not listed in the ODBC Data Source Administrator, look for the (Default) entry of the registry key HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers using Regedit (choose Start menu > Run > Regedit).
Chapter 2 | Using ODBC to share FileMaker data 15 Verifying access via ODBC (Windows) To verify that you’ve correctly configured the ODBC client driver to access the FileMaker data source: 1. Open the ODBC Data Source Administrator control panel. In the Windows Control Panel, choose Administrative Tools > Data Sources (ODBC). In Windows XP, Administrative Tools appear in the Performance and Maintenance category. 2. Click the System DSN tab.
FileMaker ODBC and JDBC Developer’s Guide For additional information, see Installing FileMaker ODBC and JDBC Client Drivers, which is available on the FileMaker Pro CD in the folder \xDBC\Electronic Documentation, and in the folder \English Extras\Electronic Documentation where you installed FileMaker Server. Configuring the ODBC client driver (Mac OS) Configure the client driver using the ODBC Administrator bundled with the iODBC driver manager software recommended for your client application.
Chapter 2 | Using ODBC to share FileMaker data 17 Additionally, you’ll need to specify these keyword values for the DSN: Keyword Value Host If you’re connecting to a FileMaker database file hosted by FileMaker Pro on your local machine, type localhost (or 127.0.0.1). If you’re connecting to a FileMaker database file hosted by FileMaker Server over a network, type the IP address or hostname of FileMaker Server. Port Type 2399.
FileMaker ODBC and JDBC Developer’s Guide
Chapter 3 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.
FileMaker ODBC and JDBC Developer’s Guide Installing the JDBC client driver The JDBC client driver is available through a separate installation program on the FileMaker Server Web Publishing CD and the FileMaker Pro CD in the folder \xDBC\JDBC Client Driver Installer. The driver is packaged as a Java archive file (with the .jar filename extension) containing a collection of class files.
Chapter 3 | 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.ddtek.jdbc.sequelink.SequeLinkDriver").newInstance(); } catch(Exception e) { System.out.println(e); } // establish a connection to FileMaker Connection con; try { con = DriverManager.getConnection("jdbc:sequelink://17.184.17.
FileMaker ODBC and JDBC Developer’s 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 3 | Using JDBC to share FileMaker data 23 Note Secure Socket Layer (SSL) encryption is not supported. To create a more secure JDBC solution, set up an environment such as .ASP or .JSP, where the web server is hosting via https and communicating with the FileMaker database file behind a firewall. Verifying access via JDBC When you install the JDBC client driver, you have the option of installing JDBCTest to help verify your connections.
FileMaker ODBC and JDBC Developer’s Guide
Chapter 4 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 FileMaker Pro or FileMaker Server. 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; see www.filemaker.
FileMaker ODBC and JDBC Developer’s 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 4 | Supported standards 27 The following example uses the ORDER BY clause to sort by both last name and first name in ascending order: SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name Additional examples: Using Sample SQL text constant SELECT 'CatDog' FROM Salespeople numeric constant SELECT 999 FROM Salespeople date constant SELECT DATE '2004-06-05' FROM Salespeople time constant SELECT TIME '02:49:03' FROM Salespeople timestamp constant SELECT TIMESTAMP '
FileMaker ODBC and JDBC Developer’s Guide If file or JPEG data exists, the SELECT statement retrieves the data in binary form; otherwise, the SELECT statement returns . To retrieve file reference information (such as the file path), use the CAST function with a SELECT statement.
Chapter 4 | Supported standards 29 An example of a DELETE statement on the Employee table is: DELETE FROM emp WHERE emp_id = 'E10001' Each DELETE statement removes every record that meets the conditions in the WHERE clause. In this case, every record having the employee ID E10001 is deleted. Because employee IDs are unique in the Employee table, only one record is deleted. INSERT statement Use the INSERT statement to create records in a database table.
FileMaker ODBC and JDBC Developer’s Guide In this type of INSERT statement, the number of columns to be inserted must match the number of columns in the SELECT statement. The list of columns to be inserted must correspond to the columns in the SELECT statement just as it would to a list of value expressions in the other type of INSERT statement. For example, the first column inserted corresponds to the first column selected; the second inserted to the second, and so on.
Chapter 4 | Supported standards 31 table_name and table_element_list have a 100 character limit. Defining a column to be NOT NULL automatically selects the Not Empty Validation Option for the corresponding field in the FileMaker database file. The field will be flagged as a Required Value in the Fields tab of the Define Database dialog box in FileMaker Pro.
FileMaker ODBC and JDBC Developer’s Guide Creating an index for any column automatically selects the Storage Option of Automatically create indexes as needed in Indexing for the corresponding field in the FileMaker database file. Example CREATE INDEX myIndex ON Salespeople.Salesperson_ID DROP INDEX statement Use the DROP INDEX statement to remove an index from a database file. The format of the DROP INDEX statement is: DROP INDEX [ON] table_name.
Chapter 4 | Supported standards 33 WHERE clause The WHERE clause specifies the conditions that records must meet to be retrieved. The WHERE clause contains conditions in the form: WHERE expr1 rel_operator expr2 expr1 and expr2 can be field names, constant values, or expressions. rel_operator is the relational operator that links the two expressions. For example, the following SELECT statement retrieves the names of employees who make $20,000 or more.
FileMaker ODBC and JDBC Developer’s Guide When using the UNION operator, the select lists for each SELECT statement must have the same number of column expressions, with the same data types, and must be specified in the same order. For example: SELECT last_name, salary, hire_date FROM emp UNION SELECT name, pay, birth_date FROM person This example has the same number of column expressions, and each column expression, in order, has the same data type.
Chapter 4 | Supported standards 35 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)). You can precede the column expression with the DISTINCT operator to eliminate duplicate values. For example: COUNT (DISTINCT last_name) In this example, only unique last name values are counted.
FileMaker ODBC and JDBC Developer’s Guide Field names The most common expression is a simple field name, such as calc or Sales_Data.Invoice_ID. Constants and literals 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. A literal is another kind of constant; but instead of having an assigned value, the literal itself is the value, such as 'Paris' or '14:35:10'.
Chapter 4 | Supported standards Exponential/scientific notation You can include exponential 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.
FileMaker ODBC and JDBC Developer’s Guide 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 su
Chapter 4 | Supported standards SELECT COUNT(Sales_Data.Invoice_ID) AS agg FROM Sales_Data WHERE Sales_Data.INVOICE_ID NOT IN (50,250,100) SELECT COUNT(Sales_Data.Invoice_ID) AS agg FROM Sales_Data WHERE Sales_Data.INVOICE_ID NOT IN (SELECT Sales_Data.Invoice_ID FROM Sales_Data WHERE Sales_Data.Salesperson_ID = 'SP-4') 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.
FileMaker ODBC and JDBC Developer’s Guide Functions that return character strings Description Example LEFT Returns leftmost characters of a string LEFT('Mattson',3) returns Mat RIGHT Returns rightmost characters of a string RIGHT('Mattson',4) returns tson SUBSTR Returns a substring of a string, with SUBSTR('Conrad',2,3) returns onr parameters of the string, the first character SUBSTR('Conrad',2) returns onrad to extract, and the number of characters to extract (optional) SPACE Generates a s
Chapter 4 | Supported standards Functions that return numbers Description Example MONTH Returns the month part of a date MONTH({01/30/2004}) returns 1 DAY Returns the day part of a date DAY({01/30/2004}) returns 30 YEAR Returns the year part of a date YEAR({01/30/2004}) returns 2004 MAX Returns the larger of two numbers MAX(66,89) returns 89 DAYOFWEEK Returns the day of week (1-7) of a date expression DAYOFWEEK({05/01/2004}) returns 7 MIN Returns the smaller of two numbers MIN(66,89)
FileMaker ODBC and JDBC Developer’s Guide Precedence Operator 6 Not 7 AND 8 OR The following example shows the importance of precedence: WHERE salary > 40000 OR hire_date > {01/30/2004} AND dept = 'D101' Because AND is evaluated first, this query retrieves employees in department D101 hired after January 30, 2004, as well as every employee making more than $40,000, no matter what department or hire date.
Appendix A 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 Developer’s Guide
Appendix B Mapping FileMaker fields to JDBC data types The JDBC client driver uses the following mappings when converting FileMaker data types to JDBC SQL types. (For information about these types, see the JDK 1.4 documentation web pages at www.javasoft.com.) FileMaker field type Converts to JDBC SQL type text java.sql.Types.VARCHAR number java.sql.Types.DOUBLE date java.sql.Types.DATE time java.sql.Types.TIME timestamp java.sql.Types.TIMESTAMP container java.sql.Types.
FileMaker ODBC and JDBC Developer’s Guide
Appendix C ODBC and JDBC error messages Here are the basic formats of error messages you receive when working with FileMaker and ODBC/JDBC. For a listing of error numbers and explanations, see www.datadirect.com. For more information about working with errors in FileMaker, see the Get(LastError) or Get(LastODBCError) functions described in FileMaker Pro Help.
FileMaker ODBC and JDBC Developer’s Guide SequeLink Server error messages An error reported by the SequeLink Server has the following format: [DataDirect] [ODBC SequeLink driver] [SequeLink Server] message For example: [DataDirect] [ODBC SequeLink driver] [SequeLink Server] Only SELECT statements are allowed in this read-only connection.
Appendix C | ODBC and JDBC error messages 49 Data source error messages An error that occurs in the data source includes the data source name, in the following format: [DataDirect] [SequeLink JDBC Driver] [data_source] message For example, you might get the following message from your FileMaker data source: [DataDirect] [SequeLink JDBC Driver] [FileMaker] Invalid Username/Password If you get this type of error, you did something incorrectly with the database system.
FileMaker ODBC and JDBC Developer’s Guide
Index A E aggregate functions in SQL 35 ALTER TABLE (SQL statement) 31 escape character 17, 22 exponential notation in SQL expressions 37 expressions in SQL 35 B blank space in database name 17, 22 C CAST function 28, 43 catalog functions for ODBC 42 character operators in SQL expressions 37 client application, using FileMaker 9 configuring a FileMaker data source via JDBC 22 via ODBC (Mac OS) 16 via ODBC (Windows) 13 constants in SQL expressions 36 container field 27 CREATE INDEX (SQL statement) 31 CR
FileMaker ODBC and JDBC Developer’s Guide M mapping data types JDBC client driver 45 ODBC client driver 43 meta data functions for JDBC 42 Microsoft Access client application 9 Microsoft Query Wizard 9 Microsoft SQL Server 9 N network requirements 9 numeric operators in SQL expressions 37 O ODBC described 11 repeating fields 43 ODBC client driver catalog functions 42 installing (Mac OS) 15 installing (Windows) 13 mapping data types 43 maximum number of FileMaker fields 43 repeating fields 25 Unicode
