I want to connect to an external DB

user_11514294 0
Viewed 606

I want to connect to SQLserver that I want to use for reference separately from MYSQL which is the main DB
To do that, set

.env

#mysql
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=hogedb
DB_USERNAME=hoge
DB_PASSWORD=password

#SQLserver
DB_CONNECTION=sqlsrv
DB_HOST=localhost
DB_DATABASE=databasename
DB_USERNAME=username
DB_PASSWORD=password

user

protected $connection = 'sqlsrv';

I geted error

could not find driver (SQL: select * from [user] where [user].[deleted_at] is null)

laravel sql-server
2 Answers

.env:

#mysql
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=hogedb
DB_USERNAME=hoge
DB_PASSWORD=password

#SQLserver
DB_SQL_HOST=localhost
DB_SQL_DATABASE=databasename
DB_SQL_USERNAME=username
DB_SQL_PASSWORD=password

And config/database.php:

 'connections' => [
    'mysql' => [
        'driver'    => 'mysql',
        'host'      => env('DB_HOST', 'localhost'),
        'database'  => env('DB_DATABASE', 'forge'),
        'username'  => env('DB_USERNAME', 'forge'),
        'password'  => env('DB_PASSWORD', ''),
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => '',
        'strict'    => false,
    ],
    'sqlsrv' => [
        'driver'   => 'sqlsrv',
        'host'     => env('DB_SQL_HOST', 'localhost'),
        'database' => env('DB_SQL_DATABASE', ''),
        'username' => env('DB_SQL_USERNAME', ''),
        'password' => env('DB_SQL_PASSWORD', ''),
        'prefix'   => '',
    ],
],

Then use in user - protected $connection = "sqlsrv";
Finally run php artisan config:cache.

user_70144310

Have you configured your config/database.php file?

Try to configure it like this

'mysql' => [
        'driver'        => 'mysql',
        'host'          => env('DB_HOST', '127.0.0.1'),
        'port'          => env('DB_PORT', '3306'),
        'database'      => env('DB_DATABASE', 'database_1'), //forge
        'username'      => env('DB_USERNAME', 'root'), // forge
        'password'      => env('DB_PASSWORD', ''),
         .......
    ],

    'sqlsrv' => [
        'driver'        => 'sqlsrv',
        'host'          => env('DB_HOST_SQLSRV', '127.0.0.1'), // Notice that we change the default so that it'll refer to another variable which will be DB_HOST_SQLSRV in the .env file
        'port'          => env('DB_PORT_SQLSRV', '1433'),
        'database'      => env('DB_DATABASE2_SQLSRV', 'database_2'),
        'username'      => env('DB_USERNAME', 'root'), // Remains as is if you won't change the username and password, change it if you need to
        'password'      => env('DB_PASSWORD', ''),
        .......
   ],

In your .env file it should look like this

// Mysql
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=hogedb
DB_USERNAME=hoge
DB_PASSWORD=password

// SQL Server
DB_CONNECTION=sqlsrv // We call in the 'sqlsrv' that we configured in database.php
DB_HOST_SQLSRV=localhost
DB_PORT_SQLSRV=1433 // DON'T FORGET THIS, IT'S VERY IMPORTANT!
DB_DATABASE2_SQLSRV=databasename // We change 'DB_Database' to 'DB_Database2'
DB_USERNAME=root // We may not add this to the second one unless you configured for another username & password in database.php
DB_PASSWORD=

So basically what you've configured in the database.php will fetch the data from your .env, if you notice it has the word env env('DB_PORT_SQLSRV', '1433')

Don't forget to run php artisan config:cache after changing something in the config folder

user_65796230
Related