mySQL Functions

Available in version 1.6.3.0 and up. Rewritten in 1.7.x for Windows.

The mySQL functions are similar to the SQL functions but connect to an external MySQL database instead of the internal FileMaker database. Those functions also come in two flavors. The first one is ExecuteMySQL, which is similar to the ExecuteSQL. The same parameters as ExecuteSQL, except an additional first parameter which is the connection handle returned from "mysql_connect". The other flavor is the four functions used for working on the SQL result set described below.

Both flavors have full support for SELECT, INSERT, UPDATE, and DELETE along with other MySQL commands. It uses these commands for connection:

• mySQL_Connect: Open a connection to an external MySQL database. Return a DB handle.
• mySQL_close (DB-handle): Closes the result set and Database connections and frees up memory.

The second flavor consists of four functions:

• mySQL_query(DB-Handle, sql): Executes the query and returns a handle to the result set.
• mySQL_getRowCount (handle): Retrieves the number of rows found, typically used in a for-loop.
• mySQL_getRow (handle): Retrieves a specific row from the result set as an array, allowing you to access individual columns.
• mySQL_close (DB-handle): Closes the result set and Database connections and frees up memory.

A DB-handle is an integer to identify the connection itself and is neccesary for the query. It is also the first parameter of the "ExecuteMySQL" function. The other handle that is named only "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 handles when you're finished with it to avoid exceptions.

The mySQL_getRow function differs a bit from the corresponding SQL_GetRow used for FileMaker database queries: It has no record number as the second parameter, as the records are fetched from start to end through a series of calls. For this command, you only supply the result-set variable.

Prototypes:

int DB_Handle = mysql_connect (string host, string port, string user, string password, string database_name );
string resultset = ExecuteMySQL (int DB_handle, string sql {, string column_sep {, string row_sep}});
int handle = mySQL_query(int DB_handle, string sql);
int number_of_rows = MySQL_getRowCount(int handle);
array string row = MySQL_getRow(int handle);
MySQL_close(DB_handle);

Definition of Parameter Types:

Example:

// One function to do the connection, so we don't have to scatter database credentials all over the source code. 

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

// Testing just prints to console some content from a Support DB. 

package MySQLtest1 "Test MySQL Functions"

function print_support_emails ()
    int db; 
    db = connect_support_db (); // The function at the top 
    
    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 

References:

• MySQL reference found on Oracle's website.
• See the example section for "MySQL connection from ACF" for additional examples.