How to use custom Datasource?

Need Help
1

My Application need to retrieve data from both MySQL and MSSQL servers.
Most of tables are in MySQL so I make it default datasourse.
I also configure a custom datasourse as below

        'fam' => [
            'className' => 'Cake\Database\Connection',
            'driver' => 'Cake\Database\Driver\Sqlserver',
            'host' => 'hse-db\ihrp',
            'username' => 'user',
            'password' => 'password',
            'database' => 'db_name',
            'encoding' => PDO::SQLSRV_ENCODING_UTF8,
            'timezone' => 'Asia/Ho_Chi_Minh',
            'cacheMetadata' => true,
        ],

In my table class, I initialise like below, but it does not work. What is the correct way of initialise custom datasource?

class TestFAMsTable extends Table
{
    /**
     * Initialize method
     *
     * @param array $config The configuration for the Table.
     * @return void
     */
    public function initialize(array $config): void
    {
        parent::initialize($config);

        $this->setTable('dbo.AD.tbl_status');
        $this->setDisplayField('Status_Name');
        $this->setPrimaryKey('Status_ID');
        $this->setConnection('fam');
               
    }

Thank you very much.

2

What does “does not work” mean? What error do you get? And exactly what version of CakePHP are you using?

3

As a wild guess, the host name looks weird, but then it is MS-SQL. Haven’t worked with that in quite some time now.

4

I am using CakePHP 4.2.8, PHP v7.4.23.
The hostname follow by the Instance name. MSSQL can have multiple Instances running on the same host, and each instance can be turned off/of separately.

I have tested the hostname, user, password, and DB name with another PHP simple script, connection can be established using PDO_SqlSrv driver.

$serverName = "hse-db\\ihrp"; //serverName\instanceName

// Since UID and PWD are not specified in the $connectionInfo array,
// The connection will be attempted using Windows Authentication.
$connectionInfo = array( "Database"=>"db_name", "UID"=>"user", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

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

The thing does not work is I got error message in IDE, it says expecting type Cake\Database\Connection but found String at the line $this->setConnection(‘fam’);

5

Right. Well, the error you’re getting is because the function is expecting a connection object, not a string. Try
$this->setConnection(ConnectionManager::get('fam'));

6

Another option, in your Table class, you can define the connection name.

https://book.cakephp.org/4/en/orm/table-objects.html#configuring-connections

<?php

class TestFAMsTable extends Table
{
    /**
     * @return string
     */
    public static function defaultConnectionName(): string
    {
        return 'fam';
    }

    /**
     * Initialize method
     *
     * @param array $config The configuration for the Table.
     * @return void
     */
    public function initialize(array $config): void
    {
        parent::initialize($config);

        $this->setTable('dbo.AD.tbl_status');
        $this->setDisplayField('Status_Name');
        $this->setPrimaryKey('Status_ID');
        $this->setConnection('fam');
    }
}
7

Thank you Zuluru and Brent,