Laravel 5.4 - How to set PDO Fetch Mode?

Question

The ability to customize the fetch mode was removed from L5.4 and is defaulted to PDO::FETCH_OBJ.

The upgrade guide states that you can override this by using an event listener:

Event::listen(StatementPrepared::class, function ($event) {
    $event->statement->setFetchMode(...);
});

I can't for the life of me understand how to implement this:

1) Where should I place the code? Should I register it with the EventServiceProvider?
2) When does the StatementPrepared event fire? (I only need to change the Fetch Mode for specific repository functions, not on a global scale).
3) Does the FetchMode revert itself automatically for subsequent queries?

Here's an example of my code:

<?php

namespace App\Repositories\Backend;

use DB;
use PDO;

class SystemRepository
{
    /**
     * Get the connection status variables.
     *
     * @return array
     */
    public function getConnectionStatus()
    {
        DB::connection('backend')->setFetchMode(PDO::FETCH_ASSOC);

        $result = DB::connection('backend')
            ->select(DB::raw("
                SHOW STATUS
                WHERE Variable_name = 'Max_used_connections'
                OR Variable_name = 'Max_used_connections_time'
                OR Variable_name = 'Threads_connected'
            "))
        ;

        DB::connection('backend')->setFetchMode(PDO::FETCH_CLASS);

        return $result;
    }
}

Thank you!

Be are of implications listening to this and change the fetch mode: stackoverflow.com/questions/47637869/… — patriziotomato
– patriziotomato, Commented Dec 11, 2017 at 8:48

Scott · Accepted Answer · 2017-07-05 02:19:16Z

8

Go to: app/Providers/EventServiceProvider.php

Add this to the top of the file:

use Illuminate\Database\Events\StatementPrepared;

In the boot method add:

Event::listen(StatementPrepared::class, function ($event) {
    $event->statement->setFetchMode(\PDO::FETCH_ASSOC);
});

answered Jul 5, 2017 at 2:19

Scott

1161 gold badge1 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Scott Over a year ago

Ah I just realized you did not want it on a global scale. This would do that.

Joshy Francis · Accepted Answer · 2018-06-12 12:15:31Z

0

$dbh=DB::getPdo();
$sth = $dbh->prepare("SHOW STATUS
            WHERE Variable_name = 'Max_used_connections'
            OR Variable_name = 'Max_used_connections_time'
            OR Variable_name = 'Threads_connected' ");
$sth->execute(); 
$result = $sth->fetch(PDO::FETCH_CLASS);
print_r($result);

Try this. worked for me. You require only DB trait(use DB;).

answered Jun 12, 2018 at 12:15

Joshy Francis

3491 gold badge9 silver badges15 bronze badges

1 Comment

wp78de Over a year ago

or simply DB::connection()->getPdo()->query($sql)->fetchAll(\PDO::FETCH_ASSOC);

Tom Dettmar · Accepted Answer · 2020-12-02 04:57:21Z

0

Theres another option I find to by pass it Add env

DB_FETCHMODE=FETCH_ASSOC

In config/database add for connections.mysql

'fetch_mode' => env('DB_FETCHMODE', 'FETCH_ASSOC'),

In illuminate/datbase/connection.php replace prepared function with

protected function prepared (PDOStatement $statement){ 
    $config = $this->config;
    $statement->setFetchMode($config['fetch_mode'] == "FETCH_OBJ" ? 5 : ($config['fetch_mode'] == "FETCH_NUM" ? 3 : 2)); 
    $this->event(new Events\StatementPrepared(
        $this, $statement ));
    return $statement; 
 }

This make default FETCH_ASSOC for your application

Then if you want to change it like before, add

config(['database.connections.mysql.fetch_mode' => 'FETCH_OBJ']);

a replacement of

DB::setFetchMode(PDO::FETCH_ASSOC);

edited Dec 2, 2020 at 4:57

Tom Dettmar

31 silver badge2 bronze badges

answered Oct 1, 2019 at 6:05

iamawesome

6628 silver badges15 bronze badges

Comments

Toby Allen · Accepted Answer · 2022-10-30 11:21:41Z

0

In Laravel < 5.4

Add default fetch mode in config/database.php

return [
    'fetch' => PDO::FETCH_CLASS,
    ...
];

edited Oct 30, 2022 at 11:21

Toby Allen

11.3k12 gold badges80 silver badges132 bronze badges

answered Aug 15, 2019 at 9:04

Emamie

2,8721 gold badge23 silver badges19 bronze badges

1 Comment

fungusanthrax Over a year ago

Per the docs, this works great for Laravel versions below 5.4. Anything equal to or above 5.4 needs to use Scott's recommendation laravel.com/docs/5.4/upgrade

mpen · Accepted Answer · 2023-12-03 22:48:22Z

I gave up on changing the fetch mode back and wrote a helper function to convert collections back into arrays-of-arrays:

/**
 * Converts any iterable to an array-of-arrays. Intended for use with Laravel's {@link \Illuminate\Support\Collection} type.
 * Provided for backwards-compatibility because FETCH_ASSOC is no longer available.
 * Avoid using this in new code to prevent overhead of conversion.
 *
 * @return array[][]
 */
public function collection2array(iterable $collection): array {
    $out = [];
    foreach($collection as $el) {
        $out[] = (array)$el;
    }
    return $out;
}

I wouldn't recommend using it all over the place because it probably reduces performance but to get past some errors I had, this was the easiest.

Collectives™ on Stack Overflow

Laravel 5.4 - How to set PDO Fetch Mode?

5 Answers 5

1 Comment

1 Comment

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related