Handling SQL in the ACF-plugin for FileMaker version 1.7.0

I am delighted to share more information about handling SQL queries in the ACF-Plugin version 1.7.0 for FileMaker. The focus is on placeholders and managing result data from both FileMaker SQL queries and MySQL database queries.

Placeholders in SQL Construction

Originally, FileMaker's ExecuteSQL function lacked a concept of placeholders in the query. This changed later, with the function now using question marks as placeholders in the SQL sentence, along with additional parameters to replace in the SQL sentence before execution—a useful feature.

While the approach is beneficial for correctly formatting parameter values into the SQL, it can be error-prone due to the need for careful ordering of variables in the parameter list. To address this, we took inspiration from 4th Dimension and introduced placeholders as variable names directly in the SQL, eliminating the need for additional parameters.

To use local variables in the SQL sentence, prefix them with a colon (:), allowing the plugin to pull those variables and replace the placeholders in the SQL before execution. Here's an example for a MySQL database query, applicable to FileMaker SQL:

function TestMySQL_Execute ()

    int db = Mysql_Connect ("localhost", "3306", "root", "ACDF1234", "boost_mysql_examples"); 

    float minSalary = float(Example::nPar1); 
    float maxSalary = float(Example::nPar2); 
    
    string result = ExecuteMySQL ( db, 
        "SELECT * FROM employee WHERE Salary BETWEEN :minSalary AND :maxSalary", "\n", ";"); 

    mysql_close ( db ); 
    return result; 

end 

This gave the output from my test database:

// For nPar1 and nPar2 = 0 and 500000 ( all records ) 
1;Efficient;Developer;30000;AWC;2021-03-13;2022-12-31 10:04:13.000000;
2;Lazy;Manager;80000;AWC;;;
3;Good;Team Player;35000;HGS;;;
4;Enormous;Slacker;45000;SGL;;;
5;Coffee;Drinker;30000;HGS;;;
6;Underpaid;Intern;15000;AWC;;;

// For nPar1 = 30 000 and nPar2 = 50 000
1;Efficient;Developer;30000;AWC;2021-03-13;2022-12-31 10:04:13.000000;
3;Good;Team Player;35000;HGS;;;
4;Enormous;Slacker;45000;SGL;;;
5;Coffee;Drinker;30000;HGS;;;

This approach simplifies the construction of SQL queries, improving human readability and enabling developers to write better code more efficiently.

Handling the Result Set

Building on our efforts to enhance SQL result handling, we introduce a new INTO statement at the end of SQL statements, inspired by 4th Dimension.

In the last line of the SQL, we have "INTO" and a comma-separated list of arrays, each prefixed with a single ":" as you see in the example below.

Example:

function TestMySQL_Execute ()

    int db = Mysql_Connect ("localhost", "3306", "root", "ACDF1234", "boost_mysql_examples"); 
    array string aFirstNames, aLastNames; 
    array float aSalaries; 


    float minSalary = float(Example::nPar1); 
    float maxSalary = float(Example::nPar2); 
    
    string result = ExecuteMySQL ( db, 
        "SELECT first_name, last_name, salary FROM employee 
        WHERE Salary BETWEEN :minSalary AND :maxSalary
        INTO :aFirstNames, :aLastNames, :aSalaries"); 

    int rows = sizeof ( aFirstNames ); 
    int i;
    if ( result == "OK") then
        // Print a little report !!
        // Heading
        print format "Emplyees with salary between %.0f and %.0f\n", minSalary, maxSalary); 
        print format ("%-3s %-20s %-20s %-20s\n", "row", "First Name", "Last Name",  "Salary");  
        // Data
        for (i = 1, rows) 
            print format ("%-3d %-20s %-20s %20.2f\n", i, 
                aFirstNames[i], aLastNames[i],  aSalaries[i]); 
        end for
    else
        return result; // Some error. 
    end if
    mysql_close ( db ); 
    return result; 

end 

And the result printed on the plugin console:

Emplyees with salary between 30000 and 50000
row First Name           Last Name            Salary              
1   Efficient            Developer                        30000.00
2   Good                 Team Player                      35000.00
3   Enormous             Slacker                          45000.00
4   Coffee               Drinker                          30000.00

This feature eliminates the need for result parsing, providing ready-to-use data in arrays directly.