Home > Reference > Script steps reference > Miscellaneous script steps > Execute SQL

Execute SQL

Purpose 

Executes any SQL statement on an ODBC data source.

Format 

Execute SQL [No Dialog; ODBC: <datasource name>; <native SQL
or calculated SQL>]

Options 

•	Perform without dialog prevents the Specify SQL dialog box, the Select ODBC Data Source dialog box, and the Password dialog box from displaying when the script step executes.

•	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.

Compatibility 

 

Where the script step runs	Supported
FileMaker Pro	Yes
FileMaker Server scheduled script	No
FileMaker Go	No
Custom Web Publishing	No
Instant Web Publishing	No
Runtime solution	No

Originated in 

FileMaker Pro 6.0 or earlier

Description 

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 Get functions 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.

Notes

•	SQL statements are limited to a maximum length of 256 K characters (512 KB).

•	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.

•	ODBC import, the Execute SQL script step, and external SQL data sources are not supported in runtime solutions created with FileMaker Pro Advanced.

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, 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

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 & "," & "N'" & 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,N'Smith')

Note  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.

Some Database Management Systems (DBMS), such as the MySQL 3.51 driver, don’t support the “N” prefix. For more information, see the manual for your DBMS.

Related topics 

Script steps reference (alphabetical list)

Script steps reference (category list)