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.
|
1.
|
|
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 external 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.
|
Note If you want to include other types, deselect all the Filter by Type options.
|
7.
|
Click OK.
|
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.
|
|
2.
|
. |
3.
|
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.
|
•
|
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.
|
|
•
|
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.
|
|
•
|
You cannot change the schema of external ODBC data sources. However, you can add supplemental fields to do calculations and summaries on data from ODBC tables. See Using supplemental fields.
|
|
•
|
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.
|
|
•
|
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 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 cannot control validation and other options that ODBC administrators set. See Updating data between FileMaker and ODBC data sources for information on how field options are affected in Sync operations.
|
|
•
|
FileMaker files linked to ODBC data sources don’t inherent relationships that are created in the ODBC data sources.
|
|
•
|
|
•
|
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.”)
|
|
•
|
|
•
|
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.
|