Function: SQL Functions

The SQL functions in ACF come in two flavors. The first one is ExecuteSQL, which is similar to FileMaker's ExecuteSQL. While ACF does not use placeholders like "?," you can use the format function to create SQL statements with placeholders. ExecuteSQL returns a text result with column and row separators, similar to the FileMaker version. You can specify these separators as optional parameters (2nd and 3rd), with the default being a tab character for column separation and a carriage return (CR) for row separation.

New in Plugin version 1.7.0: You can instead of returning the data as a CSV, you can return a set of arrays, one for each column in your dataset. In this way, its easier than ever to work with the result set of exequteSQL. End the SQL with the keyword "INTO", and a comma separated list of declared arrays, each array prefixed with a colon.

Palceholders in SQL

(1.7.0 feature)

Sometimes when performing SQL queries, you need to have parameters in your SQL. Especially in the "WHERE" clause, but also in the "VALUES" part of an "INSERT" statement.

Instead of merging those values, checking that they are correctly formatted, you can simply use a variable prefixed with colon directly in the text of the SQL query. The variable type determines the formatting applied to the value when the plugin replace the placeholder in your SQL. Like this: (Also look at the Import data from Kimai articel, chapter 2 for illustrated use of this Placeholder logic and also the INTO statement

float salary = 10000.0; 
$res = executeSQL ( "SELECT Name, Nr, Salary FROM Emplyees WHERE Salary > :salary");

One important difference: The ExecuteSQL function supports SELECT, UPDATE, INSERT, and DELETE operations. It's ideal for cases where you want to select a single value or perform UPDATE, INSERT, or DELETE operations.

Working with the result set one record at a time

The second flavor consists of four functions:

A handle is an integer that identifies the SQL result set. It's important to understand that it's the integer value that identifies the result set, not the handle variable itself. You can return handles to FileMaker and use them in other functions or script steps. Always remember to close the handle when you're finished with it to avoid exceptions.

Prototypes:

string resultset = ExecuteSQL (string sql {, string column_sep {, string row_sep}});
int handle = SQL_query(string sql);
int number_of_rows = SQL_getRowCount(int handle);
array string row = SQL_getRow(int handle, row_number);
SQL_close(handle);

Definition of Parameter Types:

Parameter name Type Description
sql string The SQL string to be executed.
handle int The identifier of the result set.
row_number int The row number, starting from 0.

Example:

package SQLtest1 "Test SQL Functions"

/*
    In this test, we query the Contacts table for all rows, then loop through the recordset to 
    update the "x" column with the loop counter. 
    Then, we print to the console the ID, FirstName, LastName, the update statement, 
    and the result from the update statement. 
*/
function test_listitems()

    // SQL_query executes the SQL and provides us with a handle to the result.
    // This handle must be closed at the end.
    int x = SQL_query("SELECT ID, FirstName, LastName FROM Contacts");

    int num_rows = SQL_getRowCount(x);

    print format("Number of Contacts: %d\n", num_rows);

    int i;
    string res, upd;
    array string row;
    // We avoid declaring variables inside the loop.

    for (i = 1, num_rows, 1)

        // SQL_getRow returns an array, allowing us to access each column in the recordset individually.
        // The first parameter is the handle, and the second is the row number, starting with "0".
        row = SQL_getRow(x, i - 1);

        // Create the update SQL statement with placeholders using the format function.
        upd = format("UPDATE Contacts SET x=%d WHERE ID=%s", i, row[1]);

        res = ExecuteSQL(upd); // Execute the UPDATE; no need to close with this function.
        print format("ID: %s, First name: %s, Last name: %s - SQL='%s',  Result: %s\n", row[1], row[2], row[3], upd, res);

    end for

    // Close the handle.
    SQL_close(x);

    return "OK";
end

/* Test Result:
Number of Contacts: 2032
ID: 1000, First name: Ole Kristian Ek, Last name: Hornnes - SQL='UPDATE Contacts SET x=1 WHERE ID=1000',  Result:
ID: 1001, First name: Roffe, Last name: Hansen - SQL='UPDATE Contacts SET x=2 WHERE ID=1001',  Result:
ID: 10001, First name: Trond Harald, Last name: Hansen - SQL='UPDATE Contacts SET x=3 WHERE ID=10001',  Result:
...
...
*/

NOTE: When updating large datasets in a loop, the process can be time-consuming, and FileMaker may appear unresponsive until finished. To improve user experience, consider using a progress bar function before starting this function. This allows you to display a progress indicator during the operation. Typically, it's better to run the progress bar in a separate script step outside the ACF function. Starting the progress bar outside of ACF allows the script engine to control its display properly.

Getting the Insert-ID of Newly Inserted Records

To retrieve the insert-ID of a newly inserted record (useful when inserting related records into another table), consider the following methods:

The first method may have concurrency issues if two processes insert into the same table simultaneously, so consider using the second or third method to avoid such problems.

References: