CodeIgniter 3.1.9: Call to undefined function odbc

UPDATE: This question has been flagged as a question that has been solved already. But when checked, those previous "similar" questions require users to download the corresponding .dll file which is not applicable for macOS users and other answers did not explicitly elaborate on how to install needed extensions like pdo_sqlsrv, sqlsrv, and ODBC for XAMPP-installed PHP.

I'm trying to connect to MS SQL database using CodeIgniter 3.1.9 via ODBC:

$db['default'] = array(
    'dsn'   => '',
    'hostname' => 'x.x.x.x',
    'username' => 'sa',
    'password' => 'xxx',
    'database' => 'xxx',
    'dbdriver' => 'odbc'

But I'm getting this error:

An uncaught Exception was encountered
Type: Error

Message: Call to undefined function odbc_connect()

Filename: /Applications/XAMPP/xamppfiles/htdocs/gpweb/system/database/drivers/odbc/odbc_driver.php

Line Number: 141

PHP is installed via XAMPP. I checked the php.ini file to get information about ODBC and the line below is disabled because of the semi-colon in the beginning:

;extension=php_pdo_odbc.dll

I know it is irrelevant because I'm running on MacOS and .dll files are for Windows (but I still tried enabling it and with more errors):

A PHP Error was encountered
Severity: Core Warning

Message: PHP Startup: Unable to load dynamic library '/Applications/XAMPP/xamppfiles/lib/php/extensions/no-debug-non-zts-20160303/php_pdo_odbc.dll' - dlopen(/Applications/XAMPP/xamppfiles/lib/php/extensions/no-debug-non-zts-20160303/php_pdo_odbc.dll, 0x0009): tried: '/Applications/XAMPP/xamppfiles/lib/php_pdo_odbc.dll' (no such file), '/Applications/XAMPP/xamppfiles/lib/php/extensions/no-debug-non-zts-20160303/php_pdo_odbc.dll' (no such file)

Filename: Unknown

Line Number: 0

Has anyone encountered this issue before and how did you solve it? How can you enable ODBC in MacOS with PHP installed using XAMPP?

CodePudding user response：

I found the solution, missing in php.ini

extension=php_odbc.dll

CodePudding user response：

Unfortunately, macOS users that use XAMPP to install PHP have no way to install extensions like sqlsrv, pdo_sqlsrv, and ODBC, unlike in Windows, where they can download the necessary .dll files and enable extensions in the php.ini.

I've been using CodeIgniter 3.x.x for years to create web applications and MySQL as the database. I wanted to utilize the said PHP library with my other projects requiring MSSQL as the database since CI can do so, provided that you have installed the necessary extensions.

I have scoured the internet and there is really no definite solution to install extensions in XAMPP-installed PHP running macOS.

I gave up and installed PHP using homebrew instead. In your terminal, enter:

$ brew install php

Then installed sqlsrv extension (for M1 ARM64 users)

sudo CXXFLAGS="-I/opt/homebrew/opt/unixodbc/include/" LDFLAGS="-L/opt/homebrew/lib/" pecl install sqlsrv
sudo CXXFLAGS="-I/opt/homebrew/opt/unixodbc/include/" LDFLAGS="-L/opt/homebrew/lib/" pecl install pdo_sqlsrv

Then the ODBC extension:

HOMEBREW_NO_ENV_FILTERING=1 ACCEPT_EULA=Y
brew install msodbcsql17 mssql-tools

Since I cannot use the XAMPP-installed PHP, I have to use the CodeIgniter 4 to use the natively installed PHP:

composer create-project codeigniter4/appstarter project-root

After that, in the /project/app/Config/Database.php of my CodeIgniter 4 project, setup the connection to the database:

 public $default = [
    'DSN'      => '',
    'hostname' => 'x.x.x.x', // IP ADDRESS OF THE SQL SERVER
    'username' => 'xxx', // USERNAME
    'password' => 'xxx', // PASSWORD
    'database' => 'xxx', // NAME OF DATABASE
    'DBDriver' => 'SQLSRV', // DATABASE DRIVER TO BE USED
    'DBPrefix' => '',
    'pConnect' => false,
    'DBDebug'  => (ENVIRONMENT !== 'production'),
    'charset'  => 'utf8',
    'DBCollat' => 'utf8_general_ci',
    'swapPre'  => '',
    'encrypt'  => false,
    'compress' => false,
    'strictOn' => false,
    'failover' => [],
    'port'     => 1433, // DEFAULT PORT FOR SQL SERVER
];

This is not the solution to the original problem (XAMPP-installed PHP), but an alternative solution to connect to MSSQL database using PHP.

REFERENCE:

But still, if you found a way to install sqlsrv and ODBC extensions in XAMPP-installed PHP running macOS, please don't hesitate to post it here.