Codeigniter 4 not working with sqlsrv

Hey guys, I'm trying to connect codeigniter 4 with sqlsrv driver, but it's not working. I'm using wamp server 64. I did all needed configurations.
a) I downloaded drivers for 7.4 php version and
added the "extension=" lines in php.ini file for these two files (php_pdo_sqlsrv_74_ts_x64.dll, php_sqlsrv_74_ts_x64.dll).
b) I checked the php_info and its everything ok.
c) I did a test using the code below and it worked, but when I use the same setting in codeigniter 4, it doesn't work.

PHP Code:

$serverName = "DSSQLM01"; //serverName\instanceName, portNumber (default is 1433)
$connectionInfo = array( "Database"=>"DW", "UID"=>"SA", "PWD"=>"v8d2Sa5Y&cq%","Encrypt"=>false,"TrustServerCertificate"=>false);
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
    echo "Connection established.<br />";
}else{
echo "<pre>";
    echo "Connection could not be established.<br />";
    die( print_r( sqlsrv_errors(), true));
echo "</pre>";
}

$tsql = "SELECT * FROM DW WHERE dt_ultima_atualizacao IS NOT NULL";

if(($result = sqlsrv_query($conn,$tsql)) !== false){
    while( $obj = sqlsrv_fetch_object( $result )) {
          echo $obj->nome.'<br />';
    }
} 


## setting in codeigniter 4

PHP Code:

public $dw = [
 'DSN'      => '',
'hostname' => 'DSSQLM01'dfasdf,
 'username' => 'SA',
 'password' => 'v8d2Sa5Y&cq%',
 'database' => 'DW',
 'DBDriver' => 'sqlsrv',
 'DBPrefix' => '',
 'pConnect' => false,
 'DBDebug'  => (ENVIRONMENT !== 'production'),
 'charset'  => 'utf8',
 'DBCollat' => 'utf8_general_ci',
 'swapPre'  => '',
 'encrypt'  => false,
 'compress' => false,
 'strictOn' => false,
 'failover' => [],
 'port'    => 1433,
 ]; 


## Configurações ##

I want to ask you to test the following.
In the system/Database/SQLSRV/Connection.php file, add a backslash to the beginning of all functions starting with sqlsrv_.
Like this sqlsrv_configure => \sqlsrv_configure
And then try to connect to the database.

Hello I am using PHP: 8.2.18 — CodeIgniter: 4.5.2, Still getting the same error.
If the use the below php code it works for me.

PHP Code:

$serverName = ''; 
$username = '';
$password = '';
$database = '';


try {
    $conn = new PDO("sqlsrv:server=$serverName;Database=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
} 


I have the below code in Database.php and the values are in .env

PHP Code:

<?php

namespace Config;

use CodeIgniter\Database\Config;

/**
 * Database Configuration
 */
class Database extends Config
{
    /**
    * The directory that holds the Migrations and Seeds directories.
    */
    public string $filesPath = APPPATH . 'Database' . DIRECTORY_SEPARATOR;

    /**
    * Lets you choose which connection group to use if no other is specified.
    */
    public string $defaultGroup = 'default';

    /**
    * The default database connection.
    *
    * @var array<string, mixed>
    */

    public array $default = [
            'DSN'        => '',
          'hostname'  => '',
            'username'  => '',
            'password'  => '',
            'database'  => '',
            'schema'    => '',
          'DBDriver'  => 'SQLSRV',
            'DBPrefix'  => '',
            'pConnect'  => false,
          'DBDebug'    => true,
            'charset'    => 'utf8',
            'swapPre'    => '',
            'encrypt'    => false,
            'failover'  => [],
            'port'      => '',
            'dateFormat' => [
                'date'    => 'Y-m-d',
                'datetime' => 'Y-m-d H:i:s',
                'time'    => 'H:i:s',
            ],
        ];


    //$db = \Config\Database::connect('sql', true); // true parameter enables query builder
    //    /**
    //    * Sample database connection for SQLite3.
    //    *
    //    * @var array<string, mixed>
    //    */
    //    public array $default = [
    //        'database'    => 'database.db',
    //        'DBDriver'    => 'SQLite3',
    //        'DBPrefix'    => '',
    //        'DBDebug'    => true,
    //        'swapPre'    => '',
    //        'failover'    => [],
    //        'foreignKeys' => true,
    //        'busyTimeout' => 1000,
    //        'dateFormat'  => [
    //            'date'    => 'Y-m-d',
    //            'datetime' => 'Y-m-d H:i:s',
    //            'time'    => 'H:i:s',
    //        ],
    //    ];

    //    /**
    //    * Sample database connection for Postgre.
    //    *
    //    * @var array<string, mixed>
    //    */
    //    public array $default = [
    //        'DSN'        => '',
    //        'hostname'  => 'localhost',
    //        'username'  => 'root',
    //        'password'  => 'root',
    //        'database'  => 'ci4',
    //        'schema'    => 'public',
    //        'DBDriver'  => 'Postgre',
    //        'DBPrefix'  => '',
    //        'pConnect'  => false,
    //        'DBDebug'    => true,
    //        'charset'    => 'utf8',
    //        'swapPre'    => '',
    //        'failover'  => [],
    //        'port'      => 5432,
    //        'dateFormat' => [
    //            'date'    => 'Y-m-d',
    //            'datetime' => 'Y-m-d H:i:s',
    //            'time'    => 'H:i:s',
    //        ],
    //    ];

    //    /**
    //    * Sample database connection for SQLSRV.
    //    *
    //    * @var array<string, mixed>
    //    */
    //    public array $default = [
    //        'DSN'        => '',
    //        'hostname'  => 'localhost',
    //        'username'  => 'root',
    //        'password'  => 'root',
    //        'database'  => 'ci4',
    //        'schema'    => 'dbo',
    //        'DBDriver'  => 'SQLSRV',
    //        'DBPrefix'  => '',
    //        'pConnect'  => false,
    //        'DBDebug'    => true,
    //        'charset'    => 'utf8',
    //        'swapPre'    => '',
    //        'encrypt'    => false,
    //        'failover'  => [],
    //        'port'      => 1433,
    //        'dateFormat' => [
    //            'date'    => 'Y-m-d',
    //            'datetime' => 'Y-m-d H:i:s',
    //            'time'    => 'H:i:s',
    //        ],
    //    ];

    //    /**
    //    * Sample database connection for OCI8.
    //    *
    //    * You may need the following environment variables:
    //    *  NLS_LANG                = 'AMERICAN_AMERICA.UTF8'
    //    *  NLS_DATE_FORMAT        = 'YYYY-MM-DD HH24:MI:SS'
    //    *  NLS_TIMESTAMP_FORMAT    = 'YYYY-MM-DD HH24:MI:SS'
    //    *  NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
    //    *
    //    * @var array<string, mixed>
    //    */
    //    public array $default = [
    //        'DSN'        => 'localhost:1521/XEPDB1',
    //        'username'  => 'root',
    //        'password'  => 'root',
    //        'DBDriver'  => 'OCI8',
    //        'DBPrefix'  => '',
    //        'pConnect'  => false,
    //        'DBDebug'    => true,
    //        'charset'    => 'AL32UTF8',
    //        'swapPre'    => '',
    //        'failover'  => [],
    //        'dateFormat' => [
    //            'date'    => 'Y-m-d',
    //            'datetime' => 'Y-m-d H:i:s',
    //            'time'    => 'H:i:s',
    //        ],
    //    ];

    /**
    * This database connection is used when running PHPUnit database tests.
    *
    * @var array<string, mixed>
    */
    public array $tests = [
        'DSN'        => '',
        'hostname'    => '127.0.0.1',
        'username'    => '',
        'password'    => '',
        'database'    => ':memory:',
        'DBDriver'    => 'SQLite3',
        'DBPrefix'    => 'db_',  // Needed to ensure we're working correctly with prefixes live. DO NOT REMOVE FOR CI DEVS
        'pConnect'    => false,
        'DBDebug'    => true,
        'charset'    => 'utf8',
        'DBCollat'    => '',
        'swapPre'    => '',
        'encrypt'    => false,
        'compress'    => false,
        'strictOn'    => false,
        'failover'    => [],
        'port'        => 3306,
        'foreignKeys' => true,
        'busyTimeout' => 1000,
        'dateFormat'  => [
            'date'    => 'Y-m-d',
            'datetime' => 'Y-m-d H:i:s',
            'time'    => 'H:i:s',
        ],
    ];

    public function __construct()
    {
        parent::__construct();

        // Ensure that we always set the database group to 'tests' if
        // we are currently running an automated test suite, so that
        // we don't overwrite live data on accident.
        if (ENVIRONMENT === 'testing') {
            $this->defaultGroup = 'tests';
        }
    }
} 


Please suggest

Can you show the output of the following command?

Code:

$ php spark config:check Database

And what is the exact error message?

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

Hi,

I think you must set the dbdriver using UPPERCASE:
'DBDriver' => 'SQLSRV'

Just my 0.02

Hello,
When I run the check Database command I am getting the below result.

Code:

PS C:\wamp64\www\sfwmd4> php spark config:check Database

Warning: PHP Startup: Unable to load dynamic library 'php_pdo_sqlsrv_82_nts_x64.dll' (tried: c:/wamp64/bin/php/php8.2.18/ext/php_pdo_sqlsrv_82_nts_x64.dll (The specified module could not be found), c:/wamp64/bin/php/php8.2.18/ext/php_php_pdo_sqlsrv_82_nts_x64.dll.dll (The specified module could not be found)) in Unknown on line 0

CodeIgniter v4.5.2 Command Line Tool - Server Time: 2024-07-12 13:58:17 UTC+00:00

Config\Database#35 (4) (
    public 'filesPath' -> string (34) "C:\wamp64\www\sfwmd4\app\Database\"
    public 'defaultGroup' -> string (7) "default"
    public 'default' -> array (16) [
        'DSN' => string (0) ""
        'hostname' => string (24) ""
        'username' => string (7) ""
        'password' => string (9) ""
        'database' => string (9) ""
        'schema' => string (0) ""
        'DBDriver' => string (6) "SQLSRV"
        'DBPrefix' => string (0) ""
        'pConnect' => boolean false
        'DBDebug' => boolean true
        'charset' => string (4) "utf8"
        'swapPre' => string (0) ""
        'encrypt' => boolean false
        'failover' => array (0) []
        'port' => string (4) "1433"
        'dateFormat' => array (3) [
            'date' => string (5) "Y-m-d"
            'datetime' => string (11) "Y-m-d H:i:s"
            'time' => string (5) "H:i:s"
        ]
    ]
    public 'test' -> array (16) [
        'DSN' => string (0) ""
        'hostname' => string (0) ""
        'username' => string (0) ""
        'password' => string (0) ""
        'database' => string (0) ""
        'schema' => string (0) ""
        'DBDriver' => string (6) "SQLSRV"
        'DBPrefix' => string (0) ""
        'pConnect' => boolean false
        'DBDebug' => boolean true
        'charset' => string (4) "utf8"
        'swapPre' => string (0) ""
        'encrypt' => boolean false
        'failover' => array (0) []
        'port' => string (0) ""
        'dateFormat' => array (3) [
            'date' => string (5) "Y-m-d"
            'datetime' => string (11) "Y-m-d H:i:s"
            'time' => string (5) "H:i:s"
        ]
    ]
)

Config Caching: Disabled

And When I change DBDriver to upper case its not detecting.

InvalidArgumentException

You have not selected a database type to connect to.

php_pdo_sqlsrv_82_nts_x64.dll

the nts -> stands for non-thread-safe, ts stands for thread-safe.

So you need to make sure and check to see if your php version is nts or ts


If you are unknown which version of PHP is installed in your system then there is an easy way to know that.
Check the version of installed PHP Thread safe or Non Thread Safe:
Open a phpinfo() and search for the line Thread safety for a thread-safe build you should find enable.

On Windows:
php -i|findstr "Thread"

On *nix:
php -i|grep Thread

In the both cases will display any one
Thread Safety => enabled
//or
Thread Safety => disabled


Installing Drivers: Linux and macOS Installation Tutorial for the Microsoft Drivers for PHP for SQL Server

Drivers: 5.12.0 for PHP Driver for SQL Server

Loading Drivers: Loading the Microsoft Drivers for PHP for SQL Server

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

Hello,

Code:

(The specified module could not be found), c:/wamp64/bin/php/php8.2.18/ext/php_php_pdo_sqlsrv_82_nts_x64.dll.dll

The name of the specified module has 2 ".dll" at the end...  Don't seems correct... Maybe a typo on the PHP.ini...
Here is a old CI3 tutorial that could gave you a hint about the needed steps: 7 Steps to make SQL Server and Codeigniter Works
Make sure you have the latest version of the ODBC driver installed too

Hope this helps!