Script Steps Reference

Chapter 13
|
Miscellaneous script steps 149
•Click Specify to display the Specify SQL dialog box, where you can set the following
options.
• Specify 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 selected Perform without
dialog, or users may be unable to access your data source.
• Calculated SQL text lets you create a calculation to use as the SQL query.
• SQL text lets you manually enter a SQL query statement.
Web compatibility
This script step is not web-compatible.
Description
Executes any SQL statement. You can use this script step to manage data in a foreign
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 FileMaker Pro Help for information about error handling.
• Get(LastODBCError) function returns the latest error state returned by ODBC.
• Get(LastError) function returns the last FileMaker Pro error.
Examples
The following example of a manual SQL statement entry executes once each time you run
the Execute SQL script step.
INSERT INTO Employees (EmployeeID, FirstName, LastName, Title,
WorkPhone, Salary) VALUES (100, 'Joe', 'Smith', 'Software
Engineer', '987-7000', 100000)
UPDATE Employees SET Title = 'Manager' WHERE EmployeeID = 103
DELETE FROM Employees WHERE EmployeeID = 103
The following example of a field-based SQL statement shows a calculation field that
concatenates text with a field that can be used to insert or update a number of records in
the found set
.
"INSERT INTO Employees (EmpID, LastName) Values (" & EmpID & ","
& "'" & Last Name & )"
With EmpID of 103 and Lastname of "Smith" the calculation of the field above is translated
to:
INSERT INTO Employees (EmpID, LastName) Values (103,'Smith')