[5.0] Special chars and sqlsrv

[henriquebremenkanp]

Problem

Special chars in sqlsrv driver come to the model as "non-utf8" chars which makes Queues, Views and Json output to not work as serialization/json_encode don't support it.

Case

I tried to fetch some data from a legacy SQL Server that contains special chars like áíúéóãõç and when I try to call toJson() I get:

UnexpectedValueException in Response.php line 403:
The Response content must be a string or object implementing __toString(), "boolean" given.

Commands that will be serialized for queues doesn't work:
DoSomething(SpecialCharsModel $foo, User $user)
$foo will be serialized as null.

My connection:

'linx' => [
    'driver'   => 'sqlsrv',
    'host'     => env('DB_LINX_HOST', '192.168.0.2'),
    'database' => env('DB_LINX_DATABASE', 'a'),
    'username' => env('DB_LINX_USERNAME', 'a'),
    'password' => env('DB_LINX_PASSWORD', 'a'),
    'prefix'   => '',
],

Collation is SQL_Latin1_General_CI_AS

Workaround

Overwriting setRawAttributes() made it work.

public function setRawAttributes(array $attributes, $sync = false)
    {
        foreach ($attributes as $index => $attribute)
        {
            $attributes[$index] = utf8_encode($attribute);
        }
        return parent::setRawAttributes($attributes, $sync);
    }

I don't know the complexity of this issue at framework level, but if all other drivers don't break, I think would be interesting to have sqlsrv working out of the box too.

[GrahamCampbell]

legacy SQL Server

We only support newer versions.

[henriquebremenkanp]

It's 2008 R2

[GrahamCampbell]

I think that's too old. Sorry.

[jcarmonamx]

create the following function:

public static utf8_encode_deep(&$input) {
    if (is_string($input)) {
        $input = utf8_encode($input);
    } else if (is_array($input)) {
        foreach ($input as &$value) {
            self::utf8_encode_deep($value);
        }

        unset($value);
    } else if (is_object($input)) {
        $vars = array_keys(get_object_vars($input));

        foreach ($vars as $var) {
            self::utf8_encode_deep($input->$var);
        }
    }
}

try to do this in a function in your model:

    $clientes = DB::select( DB::raw("
        SELECT CUST, CUSTNAME, CUSTDES
        FROM CUSTOMERS
        WHERE
            CUSTDES COLLATE Latin1_General_CI_AI LIKE ?
            OR CUSTNAME COLLATE Latin1_General_CI_AI LIKE ?
    "),array($valor, $valor));

    self::utf8_encode_deep($clientes);

    return $clientes;

[ppoffice]

@henriquebremenkanp I think this may help: https://laracasts.com/discuss/channels/general-discussion/sqlsrv-driver-on-linux/?page=2#reply-14887