Laravel change database parameters at run time

Question

I want change in run time the database connection after user login. I use the method set of Config Facade. I know that I can use it only on Middleware or Controller constructor. So I created these

 Route::get("login", "Login_Controller@login");

 Route::group(["middleware" => "test"], function() {
    Route::post("login", "Login_Controller@login");
 });

And then I created the "Test" Middleware called after login submit:

public function handle($request, Closure $next) {

    // Validazione dei dati
    $validator = Validator::make($request->all(), [
                "codice_azienda" => "required",
                "username" => "required",
                "password" => "required"
    ]);

    if ($validator->fails()) {
        return redirect()->back()->withInput()->withErrors($validator);
    }

    // Verifico i dati immessi
    $codice_azienda = $request->get("codice_azienda");
    $username = $request->get("username");
    $password = $request->get("password");

    $objOperatore = new Operatore();
    $cliente = $objOperatore->loginOperatore($codice_azienda, $username, $password);
    if (empty($cliente)) {
        throw new \App\Exceptions\LoginFailedException;
    }

    Config::set("DB_HOST", Crypt::decrypt($cliente->Server));
    Config::set("DB_DATABASE", $cliente->NomeDB);
    Config::set("DB_USERNAME", Crypt::decrypt($cliente->Username));
    Config::set("DB_PASSWORD", Crypt::decrypt($cliente->Password));

    $operatori = Operatore_Model::all();
    \App\Http\Controllers\Log_Controller::debug($operatori, true);

    return $next($request);
}

But the metoed all() of Operatore_Model doesn't return anything

Logs return this error:

"[2017-02-16 16:44:52] local.ERROR: exception 'PDOException' with message 'SQLSTATE[42S02]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name 'Operatori'.' in C:\xampp\htdocs\dashboard\www\e730\vendor\laravel\framework\src\Illuminate\Database\Connection.php:323"

I want use a multi DB connection, one for each customer.

If I may make a suggestion. You could make it easier on yourself if you put this logic into a listener for the event auth.login. In that case, you wouldn't have to worry about throwing a LoginFailedException or validationg usernames and password. — user1669496
– user1669496, Commented Feb 16, 2017 at 17:24

Mintendo · Accepted Answer · 2017-02-16 17:25:35Z

I resolved in this way:

The part of config::set was wrong, to access database configuration, I should use the dot (.) style, in this way:

Config::set("database.connections.sqlsrv.host", Crypt::decrypt($cliente->Server));
Config::set("database.connections.sqlsrv.database", $cliente->NomeDB);
Config::set("database.connections.sqlsrv.username", Crypt::decrypt($cliente->Username));
Config::set("database.connections.sqlsrv.password", Crypt::decrypt($cliente->Password));

And then reconnect to DB:

\Illuminate\Support\Facades\DB::reconnect();

My advise for all that have this problem, is use a double db connection, one for the main db (server db) and one for customer db. In this way you can switch to both db, with this simple code:

Config::set("database.default", "sqlsrvCustomer");
\Illuminate\Support\Facades\DB::reconnect();

With the first command you can choose the customer DB and with the second you can connect to it.

edwardstock · Accepted Answer · 2017-02-16 17:09:04Z

0

Probably you just need to add new database or clone existent object with new credentials and use it. You can write some wrapper like:

function getClienteDb($cliente) {
    $clientDb = clone $someWhereGlobalDbConnection;
    // or just create new connection
    $clientDb = new ClientDbConnection();
    // and if needed - disconnect first db
    $someWhereGlobalDbConnection->disconnect();

    $clientDb->database = $cliente->NomeDB;
    $clientDb->username = Crypt::decrypt($cliente->Username);
    // etc...
    $clientDb->connnect();

    return $clientDb;
}

answered Feb 16, 2017 at 17:09

edwardstock

1782 silver badges10 bronze badges

Collectives™ on Stack Overflow

Laravel change database parameters at run time

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related