FileMaker 7 ® ODBC and JDBC Developer’s Guide
© 2004-2005 FileMaker, Inc. All Rights Reserved. FileMaker, Inc. 5201 Patrick Henry Drive All persons and companies listed in the examples are purely fictitious and any resemblance to existing persons and companies is purely coincidental. Santa Clara, California 95054 Credits are listed in the Acknowledgements document provided with this software. FileMaker is a trademark of FileMaker, Inc., registered in the U.S.
Contents Chapter 1 Introduction About this guide 7 Using ODBC and JDBC with FileMaker 7 7 Using a FileMaker database file as a data source 8 Networking requirements 8 Using FileMaker Pro as a client application 9 Updating files from previous versions 9 If you previously shared a FileMaker database file as a data source 9 If you previously used FileMaker Pro as a client to access a data source 9 Installing the xDBC plug-in 10 Installing the plug-in for FileMaker Pro (Windows) 10 Installi
FileMaker ODBC and JDBC Developer’s Guide Chapter 4 Supported standards Support for Unicode characters 23 SQL statements and clauses 23 23 SELECT statement 24 DELETE statement 27 INSERT statement 27 UPDATE statement 28 CREATE TABLE statement 29 ALTER TABLE statement 29 CREATE INDEX statement 30 DROP INDEX statement 30 FROM clause 30 WHERE clause 31 GROUP BY clause 31 HAVING clause 31 UNION operator 32 ORDER BY clause 32 FOR UPDATE clause 32 SQL aggregate functions 33
Contents 5 Appendix C ODBC and JDBC error messages ODBC error messages 45 45 ODBC driver error messages 45 ODBC Driver Manager error messages 45 SequeLink Client error messages 45 SequeLink Server error messages 45 Data source error messages 46 JDBC error messages 46 JDBC driver error messages 46 SequeLink Server error messages 46 Data source error messages 46 Index 47
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 Using a FileMaker database file as a data source You can use FileMaker Server to host a FileMaker database file as a data source, sharing your data with other applications using ODBC and JDBC. FileMaker Server supports local access (same computer) and remote access (both for middleware such as web servers, and for remote client access from desktop productivity applications).
Introduction 9 Using FileMaker Pro as a client application You can use FileMaker Pro as an ODBC client application, interacting with data sources on the same computer or over a network. Limitations with third-party tools Microsoft SQL Server 1 When you export decimal, float, money, or numeric data to a FileMaker database file, the auto-enter field option Calculated value is set in the database. The option truncates values to zero decimal places using the calculation Truncate(fieldname;0).
FileMaker ODBC and JDBC Developer’s Guide Important On Mac OS, OpenLink iODBC drivers conflict with the drivers provided by FileMaker. If you install the OpenLink iODBC drivers, you can no longer use the Text, Oracle, or SQL Server drivers included with FileMaker Pro. Additionally, Windows includes client drivers, such as SQL Server driver, with the operating system.
Introduction 11 4. Copy the new xDBC.fmplugin file and /xDBC Support folder into the /Applications/FileMaker Pro 7/ Extensions folder. 5. In the Finder, select both the xDBC.fmplugin file and the /xDBC Support folder in the /Extensions folder. All users must be able to read and write to the file and to the contents of the /xDBC Support folder. 6. Choose File menu > Get Info. 7. Expand the Ownership & Permissions area of the Get Info dialog box, and the Details area, if necessary.
FileMaker ODBC and JDBC Developer’s Guide Installing the plug-in for FileMaker Server (Mac OS) If you have a previous version of the plug-in, delete it before installing the new version. Installing the plug-in 1. In FileMaker Server Admin, choose Server menu > Local FileMaker Server Administration. Stop FileMaker Server and FileMaker Server Helper before you delete the existing plug-in (if you have a previous version) or install the new version. 2. Click the lock. 3.
Chapter 2 Using ODBC to share FileMaker data Use the ODBC client driver, DataDirect 32-BIT SequeLink 5.4, 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. 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.
Using ODBC to share FileMaker data 15 5. Construct and execute a 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 individual 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 Developer’s Guide 6. For Data Source Name, type a name that will be meaningful to others accessing the FileMaker data source. An additional Description is optional. Be sure Use LDAP is cleared. No translator .DLLs are included with the ODBC client driver (so clicking Translate will not allow you to configure an ODBC translator). 7. For SequeLink Server Host, enter the location of your data source.
Using ODBC to share FileMaker data 17 If the connection is OK, you’ll receive the message Connection test was successful. If the connection fails: 1 Make sure the FileMaker database file is hosted and available. 1 Update or correct your connection information. 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.
FileMaker ODBC and JDBC Developer’s Guide Specifying ODBC client driver properties for a FileMaker DSN (Mac OS) Create a DSN for each individual FileMaker database file you want to access as a data source (in previous versions of FileMaker Server and FileMaker Pro, you created only one DSN for all FileMaker database files hosted by the application). The DSN identifies the FileMaker ODBC driver, the location of the host application, and the FileMaker database file you’re accessing as a data source.
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 About the JDBC client driver The JDBC client driver is a JDBC 3.0 API compatible driver designed to work with the Java Development Kit (JDK) 1.4. It is a Type 4 driver — a native protocol, pure Java driver that converts JDBC calls directly into the network protocol used by FileMaker. This type of driver offers all the advantages of Java including automatic installation (for example, downloading the JDBC driver with an applet that uses it).
Using JDBC to share FileMaker data 21 DriverManager.getConnection("jdbc:sequelink://17.184.17.170:2399; user=some user;password=some password",userName,password); } catch(Exception e) { system.out.println(e); } // get connection warnings SQLWarning warning = null; try { warning = con.getWarnings(); if (warning == null) { System.out.println("No warnings"); return; } while (warning != null) { System.out.println("Warning: "+warning); warning = warning.getNextWarning(); } } catch (Exception e) { Sysem.out.
FileMaker ODBC and JDBC Developer’s Guide JDBC URL connection with the database name specified in the URL Format: jdbc:sequelink://:; serverDataSource= Example: jdbc:sequelink://17.184.17.170:2399;serverDataSource=publications If your database name contains spaces, replace them with the escape characters %20. Example: jdbc:sequelink://17.184.17.
Chapter 4 Supported standards FileMaker Pro and FileMaker Server use the DataDirect 32-BIT SequeLink 5.4 drivers when hosting a FileMaker database file as a shared data source. The ODBC client driver, when used with FileMaker Pro or FileMaker Server, supports ODBC 3.5 Level 1 with some features of Level 2. The JDBC client driver, when used with FileMaker Pro or FileMaker Server, supports JDBC 3.0.
FileMaker ODBC and JDBC Developer’s Guide SELECT statement Use the SELECT statement to specify which columns you're requesting. Follow the SELECT statement with the column expressions (similar to field names) you want to retrieve (for example, last_name). Expressions can include mathematical operations or string manipulation (for example, SALARY * 1.05). The SELECT statement can use a variety of clauses: SELECT [DISTINCT] {* | column_expression [[AS] column_alias],...} FROM table_name [table_alias], ..
Supported standards 25 Examples The following example retrieves the names of employees who make at least 20,000: SELECT last_name,first_name FROM emp WHERE salary >= 20000 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
FileMaker ODBC and JDBC Developer’s Guide Retrieving the contents of a container field: CAST() function and GetAs() function You can retrieve binary data, file reference information, or data of a specific file type from a container field. To retrieve binary data, use a standard SELECT statement. For example: SELECT Company_Brochures FROM Sales_Data If file or JPEG data exists, the SELECT statement retrieves the data in binary form; otherwise, the SELECT statement returns .
Supported standards 27 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 } ] 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).
FileMaker ODBC and JDBC Developer’s Guide The SELECT statement is a query that returns values for each column_name value specified in the column name list. Using a SELECT statement instead of a list of value expressions lets you select a set of rows from one table and insert it into another table using a single INSERT statement.
Supported standards 29 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 [NOT NULL] Within the statement, you specify the name and data type of each column. 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.
FileMaker ODBC and JDBC Developer’s Guide CREATE INDEX statement Use the CREATE INDEX statement to speed searches in your database file. The format of the CREATE INDEX statement is: CREATE INDEX [ index_name ][ON] table_name.column_name CREATE INDEX is supported for a single column (multi-column indexes are not supported).
Supported standards 31 If you are joining more than one table, and you want to discard all rows that don’t have corresponding rows in both source tables, you can use INNER JOIN. For example: SELECT * FROM Salespeople INNER JOIN Sales_Data ON Salespeople.Salesperson_ID = Sales_Data.Salesperson_ID Note OUTER JOIN is not currently supported. WHERE clause The WHERE clause specifies the conditions that records must meet to be retrieved.
FileMaker ODBC and JDBC Developer’s Guide UNION operator The UNION operator combines the results of two or more SELECT statements into a single result. The single result is all of the returned records from the SELECT statements. By default, duplicate records are not returned. To return duplicate records, use the ALL keyword (UNION ALL).
Supported standards 33 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'.
Supported standards 35 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
Supported standards 37 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.Amount = ANY (SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Salesperson_ID = 'SP-1') SELECT * FROM Sales_Data WHERE Sales_Data.
FileMaker ODBC and JDBC Developer’s Guide Functions that return character strings Description Example 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 string of blanks STRVAL Converts a value of any type to a character STRVAL('Woltman') returns Woltman string STRVAL(5 * 3) returns 15 STRVAL(4 = 5) returns 'Fa
Supported standards 39 Functions that return numbers Description Example 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) returns 66 POW Raises a number to a power POW(7,2) returns 49 INT Returns the integer part of a number INT(6.
FileMaker ODBC and JDBC Developer’s Guide 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. To force the clause to be evaluated in a different order, use parentheses to enclose the conditions to be evaluated first.
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-technologies.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 For example: [DataDirect] [ODBC SequeLink driver] [SequeLink Server] Only SELECT statements are allowed in this read-only connection.
Index A F aggregate functions in SQL 33 ALTER TABLE (SQL statement) 29 field names in SQL expressions 34 FOR UPDATE (SQL clause) 32 FROM (SQL clause) 30 functions in SQL expressions 37 B blank space in database name 18, 22 C CAST function 26, 41 catalog functions for ODBC 40 character operators in SQL expressions 35 client application, using FileMaker 9 client drivers included 9 configuring a FileMaker data source via JDBC 21 via ODBC (Mac OS) 18 via ODBC (Windows) 15 constants in SQL expressions 34 co
FileMaker ODBC and JDBC Developer’s Guide O ODBC described 13 repeating fields 41 ODBC client driver catalog functions 40 installing (Mac OS) 17 installing (Windows) 15 mapping data types 41 maximum number of FileMaker fields 41 repeating fields 23 Unicode support 23 verifying access (Mac OS) 18 verifying access (Windows) 16 operating systems 8 operator precedence in SQL expressions 39 ORDER BY (SQL clause) 32 OUTER JOIN 31 P plug-in 10 R Rapid Application Development (RAD) tools 19 registering the J
