Database Connections

Direct database connections provide a powerful way to integrate FileMaker with other database systems without intermediary files or web services.

Database Connection Overview

Database connections allow FileMaker to directly read from and write to external database systems, enabling:

• Real-time data access
• Bi-directional data flow
• Complex query capabilities
• Transaction support

Connection Types

ODBC (Open Database Connectivity)

ODBC is a standard API for accessing database management systems:

• Widespread support: Works with most database systems
• Cross-platform: Available on Windows, macOS, and Linux
• FileMaker as client: Connect to external databases from FileMaker
• FileMaker as server: Allow other applications to connect to FileMaker

JDBC (Java Database Connectivity)

Java-specific API for database connections:

• Java integration: Ideal for Java applications
• FileMaker JDBC driver: Available for connecting Java apps to FileMaker
• Not used in FileMaker: FileMaker uses ODBC, not JDBC, to connect to other databases

Native Drivers

Some systems offer direct, native drivers for specific databases:

• Performance: Often faster than ODBC
• Features: May support database-specific features
• FileMaker: FileMaker has it's own native protocol to connect from client to server.

FileMaker as a Data Source

FileMaker can act as a data source for other applications:

FileMaker ODBC Driver

Allows external applications to connect directly to FileMaker:

1. Install the FileMaker ODBC driver on the client computer
2. Configure the ODBC DSN
3. Enable ODBC/JDBC in FileMaker Server
4. Set up proper privileges in the FileMaker file

-- Example SQL query to FileMaker from external application
SELECT "FirstName", "LastName", "Email"
FROM "Customers"
WHERE "State" = 'CA'

FileMaker JDBC Driver

Similar to ODBC but specifically for Java applications:

// Example Java code connecting to FileMaker via JDBC
Connection conn = DriverManager.getConnection(
    "jdbc:filemaker://server.example.com/database",
    "username", "password"
);

FileMaker Accessing External Data

FileMaker can connect to external databases in two primary ways:

External SQL Sources (ESS)

ESS allows FileMaker to integrate with external SQL databases directly:

1. Setup: In FileMaker, go to File > Manage > External Data Sources
2. Add Source: Add an ODBC data source
3. Usage: Once connected, external tables appear as if they were local tables

Benefits of ESS:

• Native FileMaker interface to external data
• Ability to create relationships between FileMaker and external tables
• Support for find operations and scripting

Supported databases include:

• MySQL
• MariaDB
• SQL Server
• Oracle
• PostgreSQL

Execute SQL Script Step

For more advanced operations, the Execute SQL script step provides direct SQL access:

Execute SQL [No dialog; ODBC Data Source: "MySQL_Source";
  "INSERT INTO customers (first_name, last_name, email) VALUES (?, ?, ?)";
  Parameters: $firstName; $lastName; $email]

This approach allows:

• Complex queries beyond what ESS supports
• Transaction control
• Stored procedure execution
• Bulk operations

Implementation Patterns

Shadow Tables

Create mirror tables in FileMaker that synchronize with external databases:

1. Create tables in FileMaker matching external structure
2. Set up scheduled scripts to sync data
3. Configure conflict resolution rules

Live Connections

Work directly with external data in real-time:

1. Connect via ESS
2. Create relationships between FileMaker and external tables
3. Build interfaces that read/write directly to external data

Hybrid Approach

Combine local and external data tactically:

1. Import frequently used reference data into FileMaker
2. Keep large transaction tables in external databases
3. Sync on a schedule or on-demand

Performance Considerations

When using database connections:

1. Connection pooling: Reuse connections when possible
2. Indexing: Ensure proper indexes on both systems
3. Query optimization: Write efficient SQL queries
4. Transaction size: Batch operations for better performance
5. Network latency: Consider the impact of network speed on integration

Security Best Practices

1. Least privilege: Use accounts with minimal required permissions
2. Credential management: Securely store and manage database credentials
3. Connection encryption: Use SSL/TLS when possible
4. Audit trails: Log database activities for security monitoring
5. Network security: Restrict database servers to specific IP addresses