Trouble Connecting to Hyperfile SQL Database from Docker Container via PHP

Question

I'm trying to connect to a Hyperfile SQL database from within a Docker container, with the ultimate goal of making the connection via PHP.

This command returns an error:

root@5b0aa50b8fd8:/tmp# isql -v hyperfile
[I][unixODBC]I
[ISQL]ERROR: Could not SQLConnect

I also tried connecting directly via PHP, but that fails as well:

$hf_hostname = "172.22.1.31";
$hf_port = "4900";
$hf_database = "noyau";
$hf_user = "****";
$hf_password = "****";
$hf_dsn = sprintf("odbc:DRIVER={HFSQL};Server Name=%s;Server Port=%s;Database=%s;UID=%s;PWD=%s;", $hf_hostname, $hf_port, $hf_database, $hf_user, $hf_password);
$hf_dbh = new PDO($hf_dsn);

=> SQLSTATE[0] SQLDriverConnect: 0 I 

Here is my Dockerfile:

# Utiliser une image de base Ubuntu
FROM ubuntu:latest

# Mettre à jour les paquets et installer unixodbc
RUN apt-get update && apt-get install -y unixodbc unixodbc-dev odbcinst unzip libiodbc2-dev

# Copier le pilote HFSQL et les fichiers de configuration ODBC dans le conteneur

COPY wxpackodbclinux64.zip /usr/local/lib/wxpackodbclinux64.zip
RUN cd /usr/local/lib/ \
    && unzip /usr/local/lib/wxpackodbclinux64.zip \
    && chmod 777 -R ./  \
    && ./install.sh

COPY odbcinst.ini /etc/
COPY odbc.ini /etc/

ENV ODBCINI=/etc/odbc.ini
ENV ODBCSYSINI=/etc

# Configurer le pilote ODBC
RUN odbcinst -i -d -f /etc/odbcinst.ini
RUN odbcinst -i -s -l -f /etc/odbc.ini

RUN echo "/usr/local/lib" > /etc/ld.so.conf.d/hfsql.conf && ldconfig

# Votre application ici
COPY . /app
WORKDIR /app
# RUN ...
# CMD ["your-application-start-command"]
CMD ["tail", "-f", "/dev/null"]

And my odbc.ini configuration file :

[ODBC Data Sources]
hyperfile = HFSQL

[hyperfile]
Description=HFSQL
Driver=HFSQL
Server Name=172.22.1.31
Server Port=4900
Database=noyau
UID=admageo
PWD=yu1209*

My odbcinst.ini

[HFSQL]
Description=HFSQL Driver
Driver=/usr/local/lib/wd230hfo64.so

I'm looking for any help or suggestions to solve this issue. Thanks in advance for your support.

---

Edit 2024-02-27

As @VonC suggested, wd230hfo64.so is not compatible with unixODBC.

I have try to use iodbtest, but it's note work. I have try to specify the login and password end not specify

Starting program: /usr/bin/iodbctest Driver=\{HFSQL\}\;Server=172.22.1.31:4900\;Database=noyau\;User=****\;Password=***\*\;
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib/x86_64-linux-gnu/libthread_db.so.1".
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0812.0326
[New Thread 0x7ffff7d61640 (LWP 3120)]

Thread 1 "iodbctest" received signal SIGSEGV, Segmentation fault.
__wcslen_avx2 () at ../sysdeps/x86_64/multiarch/strlen-avx2.S:74
74      ../sysdeps/x86_64/multiarch/strlen-avx2.S: No such file or directory.
(gdb) exit
A debugging session is active.

        Inferior 1 [process 3117] will be killed.



Starting program: /usr/bin/iodbctest DSN=hyperfile
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib/x86_64-linux-gnu/libthread_db.so.1".
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0812.0326
[New Thread 0x7ffff7d61640 (LWP 3100)]
[New Thread 0x7ffff7d50640 (LWP 3101)]
[New Thread 0x7ffff7d3f640 (LWP 3102)]
[Thread 0x7ffff7d50640 (LWP 3101) exited]
[Thread 0x7ffff7d3f640 (LWP 3102) exited]
1: SQLDriverConnect = L'acc (0) SQLSTATE=0800
1: ODBC_Connect = L'acc (0) SQLSTATE=0800
[Thread 0x7ffff7d61640 (LWP 3100) exited]

Have a nice day.[Inferior 1 (process 3097) exited normally]

Answer 1

Your odbc.ini includes Driver=/tmp/wd230hfo64.so.

But, from your Dockerfile, you have copied the driver to /opt/wdhfsql/, so your odbc.ini should reference /opt/wdhfsql/wd230hfo64.so instead of /tmp/wd230hfo64.so.

[hyperfile]
Description=HFSQL
Driver=/opt/wdhfsql/wd230hfo64.so
Server Name=172.22.1.31
Server Port=4900
Database=noyau
UID=*****
PWD=****

The PDO DSN string format looks correct, but make sure the DRIVER name matches exactly what is specified in the odbc.ini or odbcinst.ini file. You have used {HFSQL} in your DSN, but that name should match the driver name defined in your odbcinst.ini file.

The {HFSQL} syntax within the DSN string for a PDO connection in PHP is not a reference to an environment variable, but rather a placeholder for the name of the ODBC driver as it is defined in the ODBC driver configuration files (odbcinst.ini or odbc.ini). In the context of ODBC DSN (Data Source Name) strings, the curly braces {} are used to denote the driver name.

In your case, you should make sure that the driver name HFSQL (or whatever name you have assigned to your Hyperfile SQL ODBC driver) is correctly defined in your odbcinst.ini file. This file is where ODBC drivers are registered on the system. The entry in odbcinst.ini might look something like this:

[HFSQL]
Description = HFSQL ODBC Driver
Driver = /path/to/the/driver.so

But... I do not see any [HFSQL] section in your odbc.ini file (or odbcinst.ini, which you did not provide). I do not know if the [hyperfile] section would work in its place.
You should try and add a [HFSQL] section for your Hyperfile SQL ODBC driver in the odbcinst.ini file, and then use the name from that section in your PDO DSN string.

Note: setting environment variables in Docker using RUN echo 'export ODBCINI="/etc/"' > ~/.bashrc might not work as expected because it sets the variable for shell sessions, but not necessarily for Apache or PHP processes. Try instead to use Docker's ENV instruction to set environment variables globally within the container: ENV ODBCINI=/etc/odbc.ini and ENV ODBCSYSINI=/etc.

# Correct driver path and set environment variables
COPY ./Infrastucture/Docker/wxpackodbclinux64.zip /tmp/wxpackodbclinux64.zip
RUN cd /tmp \
    && unzip /tmp/wxpackodbclinux64.zip \
    && chmod 777 -R ./ \
    && ./install.sh \
    && mkdir /opt/wdhfsql/ \
    && cp wd230hfo64.so /opt/wdhfsql/wd230hfo64.so

ENV ODBCINI=/etc/odbc.ini
ENV ODBCSYSINI=/etc

---

Regarding the wrong path, indeed, I had made mistakes when copying and pasting. I have corrected it and added my odbcinst.ini file.
I've added the environments to my Dockerfile, but it still doesn't work. I checked the dependencies with "ldd /usr/local/lib/wd230hfo64.so", and all is OK.

With the updated details and the corrected paths in the odbcinst.ini and odbc.ini files, given that iodbctest works but isql and direct PHP connections do not, that would suggest the issue might lie with how the connections are being made, or with specific configurations or limitations of the unixODBC driver manager being used.

First, make sure the ODBC driver (wd230hfo64.so) is compatible with the unixODBC driver manager version you are using. Incompatibilities can sometimes lead to unexpected behaviors.

Use isql with the -v flag (as you have), but also consider adding -k to use a connection string directly, or use the -3 flag to force using ODBC 3.x behavior, if not already doing so. That might provide more detailed error messages.

Make sure your PHP PDO DSN string matches the DSN and driver configuration. Since you have confirmed the driver and DSN names, your DSN string in PHP should technically be correct. However, consider simplifying the connection to use the DSN directly, as previously mentioned, to see if there is an issue with the way the connection string is being constructed:

$dsn = "odbc:hyperfile";
try {
    $dbh = new PDO($dsn);
    // Use the connection
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

You have set ODBCINI and ODBCSYSINI in your Dockerfile. Double-check these are correctly pointing to your configuration files and that they are accessible to the PHP process. Sometimes, environment variables set in Docker might not propagate as expected to Apache/PHP processes, especially when using certain base images or custom entrypoints (which could unset or override those variables).

Verify that all files related to ODBC (odbc.ini, odbcinst.ini, driver .so files) have appropriate permissions and are accessible by the user running the PHP process inside the container. Additionally, if you are running on a host with SELinux or AppArmor, make sure these security modules are not blocking access.

You cal also enable ODBC tracing to get more detailed logs on what is happening during the connection attempts. That can be done by setting Trace and TraceFile directives in your odbcinst.ini file:

[ODBC]
Trace=Yes
TraceFile=/tmp/sql.log

After attempting a connection, check /tmp/sql.log for detailed logs that might indicate where the connection is failing.

Since you have checked dependencies with ldd and confirmed they are okay, check there is no environmental issue, like network restrictions (firewalls between the Docker container and the Hyperfile SQL server) or database server configurations that might be rejecting connections from the Docker container.