How to change pdo_odbc.ini extension setting to libodbc.so

Question

Installed LAMP on an offline Ubuntu 22.04 box to test odbc-mdbtools setup ready for an online server which needs to read and write to MS Access for sending/accepting with Microsoft-based tablets (before anyone says why do such a thing).

Put unixODBC on first, then odbc-mdbtools (before realising it would add it anyway). Edited php.ini to enable the pdo-odbc extension. Getting no response from tests, looked at phpinfo and spotted pdo-odbc wasn't installed with php8.1. Installed it with

suo apt-get install php-odbc

Now got odbc showing in phpinfo output but test script still not connecting but throwing as error showing it was trying to use mysql. So, did a sudoedit of pdo-odbc.ini:

#; configuration for php odbc module
; priority=20
extension=pdo_odbc.so

Tried altering the extension to 'libodbc.so' which is the unixODBC substitution but that produced an output from the test script saying there was 'no driver found'. The extension is certainly available './usr/lib/x86_64-linux-gnu/libodbc.so'

How can I get php-pdo to work with it (even tried purging unixodbc and odbc-mbtools and reinstalling just odbc-mdbtools in the hope it would pop the extension inot the ini file itself).

Answer 1

UPDATE 1

To make it work with PDO, it looks like there's an issue with libodbccr that needs a work-around.

Here's an updated Dockerfile:

FROM ubuntu:22.04
RUN apt update
# tzdata is a pre-requisite for PHP
# this install tzdata in a non-interactive fashion
RUN ln -fs /usr/share/zoneinfo/America/New_York /etc/localtime; DEBIAN_FRONTEND=noninteractive apt-get install -y tzdata; dpkg-reconfigure --frontend noninteractive tzdata
RUN apt install -y php php-odbc mdbtools mdbtools-dev

# This will need to be updated per your installation.
# To find the right directory run "(cd / && find -name 'libodbccr.so*')"
RUN ln -s /usr/lib/aarch64-linux-gnu/libodbccr.so.2 /usr/lib/aarch64-linux-gnu/libodbccr.so

And a trivial DB access script via PDO:

<?php
$db = "/working/MYDB.mdb";
$conn = new PDO("odbc:Driver=MDBTools;DBQ=//$db;UID=;PWD=;",'','');
$data = $conn->prepare("SELECT * FROM my_table");
$data->execute();
$done = $data->fetch();
echo "foo = " . $done['AName'] . "\n";
echo "DONE\n";
?>

ORIGINAL

Have you installed mdbtools-dev?

I've booted it up in docker using the following Dockerfile and it "Just Works", without having to edit any php ini files:

FROM ubuntu:22.04
RUN apt update
# tzdata is a pre-requisite for PHP
# this install tzdata in a non-interactive fashion
RUN ln -fs /usr/share/zoneinfo/America/New_York /etc/localtime; DEBIAN_FRONTEND=noninteractive apt-get install -y tzdata; dpkg-reconfigure --frontend noninteractive tzdata
RUN apt install -y php php-odbc mdbtools mdbtools-dev

And this is the PHP I hacked together to make it read (and it is a hack job since I don't speak PHP):

<?php
$db = "/working/MYDB.mdb";
$conn = odbc_connect("Driver=MDBTools;DBQ=//$db;UID=;PWD=;",'','');
$rs = odbc_exec($conn,"SELECT * FROM my_table");
odbc_fetch_row($rs);
$foo = odbc_result($rs, "AName");
echo "foo = $foo\n";
echo "DONE\n";
odbc_close($conn);
?>

This works with raw PHP from the command line, there may be other peculiarities working with a LAMP stack. If so ping me and I'll take another look.