matthes's avatar

SQL Server uniqueidentifier generated by newsequentialid()

Hello,

I've run into a problem when using uniqueidentifier generated by the SQL Server function newsequentialid() as primary key. In SQL Management Studio, the UUID looks like this:

825B076B-44EC-E511-80DC-00155D0ABC54

When using App\Mymodel::first()->id and parsing the received bytes using either Ramsey/Uuid or webpatser/laravel-uuid I get this UUID:

6B075B82-EC44-11E5-80DC-00155D0ABC54

The first 8 bytes seem to be interchanged. My app/database.php looks like this:

'sqlsrv' => [
            'driver'   => 'sqlsrv',
            'host'     => env('DB_HOST', 'localhost'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset'  => 'utf8',
            'prefix'   => '',
            'datetimeformat' => 'Y-m-d H:i:s.u0'
        ],

Am I missing something?

Edit: That's my raw result:

>>> App\Mymodel::first()->id
=> b"""
   k\x07[‚ìD\x11å€Ü\x00]\n
   ¼T
   """

Also: When parsing the uuid and converting it back to bytes, I get the same value:

>>> App\Deployment::first()->id->getBytes()
=> b"""
   k\x07[‚ìD\x11å€Ü\x00]\n
   ¼T
   """

Only the string conversion seems to fail. Sadly I cannot use the byte array like this:

>>> App\Deployment::find(App\Deployment::first()->id->getBytes())
Illuminate\Database\QueryException with message 'SQLSTATE[HY000]: General error: 20018 Incorrect syntax near 'k[�ìDå�Ü'. [20018] (severity 15) [(null)] (SQL: select top 1 * from [deployments] where [deployments].[id] = k[▒▒D▒▒]
▒T)'

>>> App\Deployment::find(App\Deployment::first()->id->toString())
null

Edit 2: Some more googling led me to this thread: http://dba.stackexchange.com/questions/121869/sql-server-uniqueidentifier-guid-internal-representation Still I have no clue how properly fix this.

0 likes
3 replies
matthes's avatar

I was able to create a pretty dirty workaround by following this thread: http://dba.stackexchange.com/questions/121869/sql-server-uniqueidentifier-guid-internal-representation

public function getIdAttribute($value)
{
        $data = bin2hex($value);

        // reverse the order of the first 3 blocks of the hex presentation of the uuid
        $first8 = hex2bin(strrev(substr($data, 0, 8)) . strrev(substr($data, 8, 4)) . strrev(substr($data, 12, 4)));
        // change from low nibble to high nibble first
        $first8 = pack('H*', implode('', unpack('h*', $first8)));

        $last8 = hex2bin(substr($data, 16, 16));

        $uuid = Uuid::fromBytes($first8.$last8);
        return $uuid->toString();
}

I would still appreciate if anyone has an idea how to implement this fix properly.

1 like
matthes's avatar

I got a little bit further. PDO seems to send @@IDENTITY to SQL Server, which would return the last inserted ID if you're using Int as your identity column. Sadly you cannot make a uniqueidentifier column an identity column.

There is a workaround here: http://php.net/manual/en/pdo.lastinsertid.php#105580

// Assume $dbh connection handle is already established
$sql = "INSERT INTO product (product_name) OUTPUT INSERTED.product_id VALUES (?)";
$sth = $dbh->prepare($sql);
$sth->execute(array('widgets'));
$temp = $sth->fetch(PDO::FETCH_ASSOC);

Is there any way to implement this with my Mymodel::create() methods?

Please or to participate in this conversation.