Execute SQL
Executes any SQL statement on an ODBC data source.
Options
- With dialog specifies whether to display the Specify SQL dialog box, the Select ODBC Data Source dialog box, and the dialog box for entering a password when the script step is performed.
- ODBC Data Source displays the Select ODBC Data Source dialog box. Select a data source from the list, click OK, and enter a valid user name and password.
Note Select Save user name and password if you set With dialog to Off, or users may be unable to access your data source.
- Calculated SQL text specifies a calculation to use as the SQL query.
- SQL text lets you manually enter an SQL query statement.
Compatibility
| Product | Supported |
| FileMaker Pro | Yes |
| FileMaker Go | No |
| FileMaker WebDirect | Partial |
| FileMaker Server | Partial |
| FileMaker Cloud | Partial |
| FileMaker Data API | Partial |
| Custom Web Publishing | Partial |
Originated in version
6.0 or earlier
Description
You can use this script step to manage data in an ODBC data source through an ODBC SQL interface. This provides more control over the FileMaker Pro interaction with ODBC data sources. You can add multiple Execute SQL steps to a script.
Important See Get functions for information about error handling.
- Get(LastErrorDetail) function returns the latest error state returned by ODBC.
- Get(LastError) function returns the last FileMaker Pro error.
Notes
- SQL statements are limited to a maximum length of 256 K characters (512 KB).
- Server-side scripts, FileMaker WebDirect, the FileMaker Data API, and Custom Web Publishing support this script only if the With dialog option is set to Off.
- If you use the Execute SQL script step to send ODBC data that contains Unicode strings, your ODBC driver must support Unicode. Otherwise, the results may be inconsistent.
- Because Microsoft SQL Server supports both Unicode and non-Unicode field types, you must prefix all Unicode strings with an uppercase "N" (which stands for "National" in the SQL-92 standard). Otherwise, when a Unicode string containing non-English characters is passed to Microsoft SQL Server, you may lose any data that doesn't exist in the Microsoft SQL Server code page.
- The Execute SQL script step performs SQL statements against ODBC data sources, not FileMaker data sources. To query tables in a FileMaker data source using only SQL SELECT statements, use the ExecuteSQL function.
Example 1
Updates records in the Employees table of an external ODBC data source.
Copy
Execute SQL [With dialog: On; SQL Text: INSERT INTO Employees (EmployeeID, FirstName, LastName, Title, WorkPhone, Salary) VALUES (100, N'Joe', N'Smith', N'Software Engineer', '987-7000', 100000) UPDATE Employees SET Title = N'Manager' WHERE EmployeeID = 103 DELETE FROM Employees WHERE EmployeeID = 103]Example 2
Calculates and performs an SQL statement to update records in the Customers table of an external ODBC data source.
Copy
Execute SQL [With dialog: On; Calculated SQL Text: "INSERT INTO Customers (Company, LastName) VALUES (" & Customers::Company & "," & "N'" & Customers::Last & "')"]