Re: Mac and IBM i + ODBC

HI Patrick,

That Mac and Filemaker wiz asked me to pass on this new information.

We've successfully tested the Actual DB2 driver on two M1 machines now, but only in FileMaker 19.0. We've determined that upgrading to 19.4 causes the driver to fail (maybe due to changes in the minimum OS for 19.4 having an impact on whether a 32-bit driver will run?). We assume it would still work in 19.3, but we haven't tested that yet. Either way, we're still planning to phase out our use of the ODBC driver in favor of IWS.

Regards,
Bobby

On 5/16/2022 13:24, Bobby Adams wrote:

Hi Patrick,

I forwarded the questions to our Mac and Filemaker wiz.  He is not technically an IBM i developer but he is getting closer and closer.

Here is his response:

My machine for local development is an Intel Core i9 running Monterey 12.3.1. The driver (Actual DB2.bundle) continues to work fine, although ODBC Manager can't open the DSN's settings anymore because it's 32-bit, which may be the reason Actual said it's not supported since Catalina. But I just manually modify the odbc.ini and odbcinst.ini files in text editor nowadays, and it works fine.

The driver is also still working on older Mac Minis running FileMaker Server 16. We've ordered a new M1 Mac Studio to run Server 19 on, and come to think of it, I don't know for sure that the driver will work on it. Won't that be a fun surprise!

Incidentally, the driver doesn't work with native date fields (they just come in as question marks unless you convert the dates to a character format FileMaker can read, such as MM/DD/YYYY), and any SQL objects created through the driver (such as views or UDFs with calculated dates) will be subject to a 1940-2039 date limit no matter what environment you subsequently run them in, which is also a pain. The driver doesn't seem to have any options for what date format it should use.

For the reasons above, I've started phasing out ODBC imports in favor of using Insert From URL to contact SQL-driven REST services I've set up in IBM Web Administration for i. If you go that route, JSON works well for smaller result sets, but for importing many thousands of records, I'd recommend returning XML data from the IWS service and using an XML import. I wrote a universal XSLT file to facilitate these imports:
https://community.claris.com/en/s/question/0D53w00005jff1sCAA/universal-xslt-for-ibm-i-web-api-results-solution-enclosed

Regards,
Bobby

On 5/16/2022 11:57, Patrik Schindler wrote:

Hello Bobby,

Am 16.05.2022 um 15:50 schrieb Bobby Adams <bobby_adams@xxxxxxx>:

We are using the Actualtech ODBC driver with Monterey with Filemaker.

Which one exactly?

It is not listed on their website but they do have it.  We have had it for a while so maybe they don't sell it any longer.  I don't know.  But it still works for us.

Interesting. My inquiry made them respond with "We used to have a product for connecting FileMaker to AS400 and DB2/LUW on Mac, but it is only compatible with Mac OS Mojave and earlier. Catalina introduced changes which made our driver no longer work correctly, and we have not been able to develop a new approach."

So I'm wondering what exactly you're running on Monterey. And, equally interesting, on which CPU architecture (Intel, or M1)?

We can't use the ACS ODBC driver because it uses unixODBC and Filemaker on the Mac works with iODBC.

Thanks, this is a very helpful hint. So, there are (at least) two incompatible ODBC implementations on the Mac, driver-wise. Here are some more details on this:

https://stackoverflow.com/questions/7548825/what-are-the-functional-differences-between-iodbc-and-unixodbc

I haven't found any "cross library" compatibility shlibs, so drivers written and compiled for each implementation could be used with each other. But then, maybe the Mac market is too small for such a translation agent.

Who's to blame? Apple for breaking compatibility with the existing iODBC drivers for OS/400? IBM for not providing iODBC compatible drivers for the Mac? FileMaker for not supporting both iODBC and UnixODBC APIs?

:wq! PoC