iODBC update needed for ODBC in MBS Plugin on macOS

Recently a few clients run into a problem with using ODBC databases:

IM004 [iODBC][Driver Manager]Driver's SQLAllocEnv() failed

The ODBC manager (called iODBC) can't load the ODBC driver for the database you like to connect to. By default on macOS we load "/usr/lib/libiodbc.2.dylib" as the ODBC manager, but this library seems to be outdated. 

For some time now we tried to load various ODBC drivers like Actual Tech's one directly with the plugin by putting it into the connection options for our SQL functions. But loading drivers directly doesn't always work well as the ODBC manager does some text encoding work and translates between older and newer API calls.

Solution

The so far best solution is to install a newer iODBC library. You find one on iODBC.org website:

iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/Downloads

Here is the link for the current version:

sourceforge.net/projects/iodbc/files/iodbc/3.52.16/iODBC-SDK-3.52.16-macOS11.dmg/download

You install it, you may go through and only need the runtime files:

After the installation, you can use the new framework in FileMaker:

Set Variable [ $result ; Value: MBS( "SQL.SetConnectionOption"; $Connection; 
  "ODBC.LIBS"; "/Library/Frameworks/iODBC.framework/Versions/Current/iODBC") ]

Now you should be able to load the driver without the SQLAllocEnv failure.

Question remaining is when Apple may include a newer iODBC version or whether this iODBC.framework works if we just copy it to another machine and skip the installer.

Please try this and let us know if this fixes the issue.