Editing ODBC data sources
An
ODBC data source lets a FileMaker file access data from external ODBC tables. You can view and update ODBC tables interactively in the
relationships graph in much the same way you view and update FileMaker
tables.
FileMaker can connect to many relational database management systems (RDBMSs) that provide ODBC APIs for external use. See
Configuring an ODBC client driver for a list of the data sources that are supported.
Important To work with ODBC data sources, you must:
After you have linked the ODBC data source to a FileMaker Pro file, you can set and edit options.
To edit an ODBC data source:
1. Choose File menu > Manage > External Data Sources.
2. Select the ODBC data source in the list and click Edit.
3. You can change the name of the data source, specify a different DSN, set
authentication options to log in to a named ODBC data source, or set view options.
4. For Authentication, the default option is Prompt user for user name and password. Users must enter a user name and password the first time they access the table.
If you don’t want any user of this FileMaker file to be prompted, select
Specify user name and password (applies to all users) and enter a user name and password. You can also create a calculation to determine user access to the external table. You cannot use variables or fields in these calculations. See
Specify Calculation dialog box for more information on setting up calculations.
If you work with shared database files that access ODBC data from Microsoft SQL Server and you want to enable ODBC data source single sign-on, select
Use Windows Authentication (Single Sign-on) and enter the
SPN (Service Principal Name). See
Enabling ODBC data source single sign-on (Windows only).
Note FileMaker encrypts passwords. However, encryption on data sources depends on whether encryption is supported by the ODBC driver.
5. To enhance performance, you can filter the list of external tables or views that are available for you to add to the relationships graph. The default is to list all the tables from the data source.
•For Catalog name, enter a name. Otherwise, tables from all catalogs are listed.
•For Schema name, enter a name. Otherwise, tables from all schemas are listed.
•For Table name, enter a name to list only that table.
6. You can filter the list of tables by type: Tables, Views, or System table.
Note If you want to include other types, deselect all the Filter by Type options.
Setting up an ODBC table in the relationships graph
After you have added an ODBC data source, you can work with ODBC tables in the relationships graph and on
layouts as you would other FileMaker tables. You can retrieve, add, update, and delete data in the ODBC table interactively, in real time.
1. Choose File menu > Manage > Database.
2. In the
Relationships tab in the Manage Database dialog box, click
.
3. In the Specify Table dialog box, for Data Source, choose ODBC data source.
4. If you are prompted, enter the user name and password for the ODBC data source.
5. You see a list of the external tables for that data source.
6. Select the table you want and click OK.
The external table appears in the relationships graph. The table name appears in italics. For information on linking to and working with tables in the relationships graph, see
Working with the relationships graph.
Tip To identify whether a table in the relationships graph is a FileMaker table or from an ODBC data source, move the pointer over the arrow in the table header. You might want to add a color to ODBC tables in order to identify them more easily.
After you add a table to the relationships graph, FileMaker adds a layout with that table name to the
Layout pop-up menu and the table is listed in the
Tables tab. The table name appears in italics.
Columns from the ODBC table appear in the
Fields tab. The field names appear in italics. You can remove fields if you do not want FileMaker Pro to display them within your solution. These columns are not removed from the external SQL data source table schema, only from the FileMaker Pro representation of that table’s schema. See
Updating data between FileMaker and ODBC data sources for more information.
Considerations before you begin working with ODBC data sources
•Decide whether you want to work with ODBC tables interactively in the relationships graph or through "static" ODBC imports. For an overview of the various ways of working with ODBC data sources, see
Using ODBC and JDBC with FileMaker Pro.
•Determine which tables you want to access, and which data sources they are in.
•If FileMaker cannot automatically determine a table’s primary
key, you will be asked to select the columns that comprise a unique key. FileMaker requires every table to have one or more columns that have a unique value for each row.
Limitations on working with ODBC data sources
•You cannot change the schema of ODBC data sources. However, you can add
supplemental fields to do calculations and summaries on data from ODBC tables.
•Fields from ODBC data sources can be used in
value lists, but character large objects (CLOBs) such as long text strings are not supported.
•For value lists with ODBC data, the
No access privilege and
Limited custom privilege are not supported. To prohibit a user from seeing ODBC data in a value list, you must enforce row-level security in the external SQL database. See
Editing value list privileges.
•The SQL Server Timestamp data type is not supported.
•Binary large objects (BLOBs) such as pictures and sounds are not supported.
•When you’re working with external tables in the
Fields tab of the Manage Database dialog box, the following field options are not supported. See
Setting options for fields for information on field options.
•On the
Auto-Enter tab, you can auto-enter serial numbers when you create records, but not when you commit records. (The external database controls how records are committed.) When a
lookup is to related data in an ODBC data source,
copy next lower value and
copy next higher value are disabled.
•On the Validation tab, Unique value and Existing value are disabled. The Maximum number of characters allowed in a field is determined by the external data source.
•The Storage tab is disabled.
•FileMaker files linked to ODBC data sources don’t inherent relationships that are created in the ODBC data sources.
Notes
•When FileMaker Server hosts databases linked to ODBC data sources, FileMaker clients do not need to set up a DSN to access the external data source. However, the DSN must be set up on the machine running FileMaker Server.
•FileMaker does not use SQL set semantics on queries. FileMaker may execute one query for each related table on a layout. If you want to retain certain SQL join behavior or obtain the results of SQL predicates such as GROUP BY, create views. Then access the data from FileMaker through those views. (Views are sometimes called "virtual tables.")
•Long queries, attempts to scroll the entire ODBC table, or opening a large table can result in slower performance.
•Microsoft SQL Server: By default, DATETIME, DATETIME2, and SMALLDATETIME data from ODBC tables is imported as the timestamp field type. You can change the field type of a timestamp shadow field to either date or time, but the data must be structured in a specific manner. If the imported ODBC table contains timestamp data and you treat the timestamp field as a date field, the time portion must equal 12:00 AM (or midnight) for all records in the table. Likewise, if you import an ODBC table that contains timestamp data and you treat the timestamp field as a time field, the date portion must equal 1900-01-01 (or January 1, 1900) for all records in the table.
As with any ODBC table, if the field is a primary key and the values are not unique, the results may be inconsistent.