MySQL connection from ACF example

Rolling Up MySQL Connector in the ACF Plugin

Working directly with MySQL databases from the FileMaker application can be highly beneficial. Instead of relying on APIs to communicate with web-based systems, you can interact directly with the web-system's database. This enables you to extract or update data instantly from the FileMaker system without the need for any middleware systems.

While it's possible to use FileMaker's external data sources dialogue to add ODBC connections to tables and then script updates, this might not be the most efficient approach for all scenarios. ODBC also requires the installation of ODBC drivers on client computers for all users. In contrast, working directly with the ACF functions can be more efficient and doesn't necessitate ODBC drivers. This feature will be available when version 1.6.3.0 of the ACF plugin is released.

Let's take a look at an example of a function used to delete spam from a support system. In this case, we're dealing with the osTicket support system, which occasionally receives spam emails. With a relatively simple ACF function, 114 spam entries across four tables in the osTicket system were deleted in less than half a second.

Here's the ACF function used for this purpose:

function connect_support_db ()
    int db = mysql_connect ("<host>", "3306", "<user>", "<password>", "<database name>"); 
    return db; 
end

function delete_support_spam ()
    int db; 
    db = connect_support_db (); 
    
    string sql = 'SELECT tem.id, ti.ticket_id, te.id, th.id, te.title, tem.headers FROM ost_thread_entry_email as tem
left outer join ost_thread_entry as te on  te.id = tem.thread_entry_id
left outer join ost_thread as th on th.id = te.thread_id
left outer join ost_ticket as ti on ti.ticket_id = th.object_id
where tem.headers like "%X-Halon-SPAM: YES%" or te.title ="Undelivered Mail Returned to Sender";'; 

    string saker = ExecuteMySQL ( db, sql, "|*|", "||") ; 
    array string rader, kolonner; 
    int norader, i; 
    array string tem, ti, th, te; 
    if ( saker != "") then 
        rader = explode ( "|*|", saker); 
        norader = sizeof ( rader ); 
        for ( i=1, norader)
            kolonner = explode ( "||", rader[i]); 
            tem[] = kolonner[1]; 
            ti[] = kolonner[2]; 
            te[] = kolonner[3]; 
            th[] = kolonner[4]; 
        end for
    else
        mysql_close ( db ); 
        return "No SPAM found"; 
    end if
    string res;
    res = ExecuteMySQL(db, "DELETE FROM ost_thread_entry_email WHERE id IN (" + implode (",", tem ) + ")");
    res += ExecuteMySQL(db, "DELETE FROM ost_thread_entry WHERE id IN (" + implode (",", te ) + ")");
    res += ExecuteMySQL(db, "DELETE FROM ost_thread WHERE id IN (" + implode (",", th ) + ")");
    res += ExecuteMySQL(db, "DELETE FROM ost_ticket WHERE ticket_id IN (" + implode (",", ti ) + ")");
    
    mysql_close ( db ); 
    return "OK " + norader + " SPAMs deleted.";
end

The process started with SQL development in MySQL Workbench, examining the tables in the osTicket support database, identifying where the tickets are stored, and exploring related tables. After analyzing the patterns in the spam tickets, the SQL query was crafted to fetch the IDs of related records. The SQL was tested to ensure it didn't produce false positives, and then it was integrated into the ACF function.

The result? Over 100 spam tickets removed in less than half a second. To improve the cleanliness of the support system further, an additional condition was added to identify spam tickets generated by the "mail delivery subsystem."

Working with Result Sets

The ExecuteMySQL function in ACF is similar to FileMaker's ExecuteSQL. It returns the result as a delimited text with row and column delimiters. To work with this data, you need to parse it into separate rows and fields. However, in ACF, you have arrays, making parsing more straightforward.

Furthermore, ACF introduces the concept of result sets, allowing you to work with structured data directly. This means you can perform a SQL query and return a handle to a result set. With this handle, you can iterate through rows and columns efficiently.

For MySQL, you can use associative arrays, enabling you to access data using column names as string indexes. In contrast, FileMaker SQL doesn't provide column names in its results, so you must always supply a field list in your SQL queries.

Here's an example of how you can print support emails using result sets:

function print_support_emails ()
    int db; 
    db = connect_support_db (); 
    
    int x = mysql_query ( db, "SELECT * FROM c1support.ost_email;"); 
    int i, y = mysql_getrowcount ( x );
    print

 "row count: " + y + ", res-set id: " + x; 
    array string row; 
    for ( i=1, y)
        row = mysql_getrow ( x ); 
        print  row['email'] + "\n"; 
    end for
    mysql_close ( db ); 
    return "OK";    
end

Ole K System Developer