PHP odbc support issue

Shared by PB - a 3-day exchange of emails with Nettigritty support for the following:

(our site) which runs on PHP 5.6.40 has x86 architecture with ODBC Library Win32
(our beta site) which runs on PHP 7.4.3 has x64 architecture with ODBC Library as Win32 (should have been Win64)

We request you to install either of the following options:

 

1. Win64 version of the ODBC library for PHP 7.4.3 (64 bit)

        or

2. Win32 version of the ODBC library for PHP 7.4.3 (32-bit)

We get the error odbc_connect(): SQL error: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application, SQL state IM014 in SQLConnect

PB also shared that we are using a connect-string like
$db_connection_variable = odbc_connect("Driver={Microsoft Access Driver (*.mdb)};Dbq=$dbq", '', '', SQL_CUR_USE_ODBC);

and that Plesk logs for our domain show the error

odbc_connect(): SQL error: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application, SQL state IM014 in SQLConnect

Nettigritty support replied, "Please try with a DSN-less connection. You can refer the link below for an example 
https://webcheatsheet.com/php/odbc_connection.php "

PB - We tried using a DSN-less connection as referred to you in the example link (https://webcheatsheet.com/php/odbc_connection.php)

We are getting the following error in the logs of (our beta site)

Code:

//create an instance of the  ADO connection object
$conn = new COM ("ADODB.Connection")
  or die("Cannot start ADO");

Error:

PHP Fatal error: Uncaught Error: Class 'COM' not found in C:\inetpub\vhosts\path\open_connection.inc:13
Stack trace: etc

You would need COM  to be enabled in the PHP configuration

When we use the DSN connection we get the a similar error as described here

https://stackoverflow.com/questions/26290532/data-source-name-not-found-and-no-default-driver-specified

Code:

$db_connection = odbc_connect("Driver={Microsoft Access Driver (*.mdb)};DBQ=$dbq", '', '', SQL_CUR_USE_ODBC);

Error:

PHP Warning: odbc_connect(): SQL error: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified, SQL state IM002 in SQLConnect in C:\inetpub\vhosts\radiosai.org\httpdocs\tfd7\open_connection.inc on line 16

PHP Warning: odbc_connect(): SQL error: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application, SQL state IM014 in SQLConnect in C:\inetpub\vhosts\radiosai.org\httpdocs\tfd7\open_connection.inc on line 16

Kindly configure the system as per the suggestion provided here:

https://stackoverflow.com/questions/26290532/data-source-name-not-found-and-no-default-driver-specified

 Nettigritty - ... please find various types of connection strings for accessing Access details at https://www.connectionstrings.com/access/ and https://www.php.net/manual/en/function.odbc-connect.php

 Please note that the server already has ODBC drivers already installed and a screenshot for the same was already shared you to confirm the same. Please note.

 We recommend even switching your MS Access database to MSSQL database for better security and compatibility with newer scripts. ...

PB replied that we have already tried all the links etc. Finally, Nettigritty replied, 

we have enabled COM class for you for PHP and you could see the error for your application directly from https://beta.radiosai.org/tfd7/login.php URL itself now.

 

On checking further, we find the DSN created through Plesk is being created as 32bit only.

 

Either it is required to use the application in 32bit or we can delete and try adding the DSN directly from server under 64 bit to see if that helps. But we recommend doing the application in 32bit as creating the DSN through plesk only will be active and migrate when there is any OS upgrade at a later point of time.

Regarding installing 64-bit ODBC driver, they said, 
This is a pending feature requested to Plesk on Windows.

https://plesk.uservoice.com/forums/184549-feature-suggestions/suggestions/39056515-add-ability-to-select-64-bit-drivers-in-odbc-data

 

Presently, please share details to manually recreate your DSN on 64 bit ODBC on the server.

They manually created the system DSN, and then the connection worked for PB.