How to insert binary data to SQL using Laravel 5.*

Question

I would like to save an image into a varbinary(max) field in MSSQL.

The store method in my controller looks like this:

public function store(Request $request)
    {

        $vorname = $request->input('vorname');

        $nachname = $request->input('nachname');

        $file = $request->file('avatar');

        $extension = $file->clientExtension();

        $fullname = $nachname . "_" . $vorname . "." . $extension;

        $file->storeAs('avatars' , $fullname);

        $path = storage_path() . "\app\avatars\\". $fullname;

        $imageData = unpack("H*", file_get_contents($path));

        Mitarbeiter::create([

            'PersonalNr' => request('personalnr'),
            'Mitarbeiterschluessel' => request('mitarbeiterkey'),
            'Vorname' => $vorname,
            'Familienname' => $nachname,
            'Bild' => $imageData[1]

        ]);

        return view('welcome');

    }

I'm getting an error:

SQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Die implizite Konvertierung vom nvarchar(max)-Datentyp in varbinary(max) ist nicht zulässig.

translated:

Implicit conversion from the nvarchar (max) data type to varbinary (max) is not allowed.

An off-topic question, why are you trying to store the image in the database? Laravel offers a great Storage API, why don't you use it? Any particular reason? — Rehmat
– Rehmat, Commented Apr 26, 2020 at 5:26

Lim Socheat · Accepted Answer · 2018-08-13 16:58:32Z

2

this code work for me

if($request->input('Image')){
    $datastring = file_get_contents($request->input('Image'));
    $data         = unpack("H*hex", $datastring);
    $data         = '0x'.$data['hex'];
    $photo = \App\TraineeImage::where('ID', 13)->first();
    $photo->update(['Image' =>  \DB::raw("CONVERT(VARBINARY(MAX), $data) ") ]);
}

answered Aug 13, 2018 at 16:58

Lim Socheat

7253 gold badges12 silver badges26 bronze badges

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

1 Comment

Charlie Over a year ago

This worked for me after weeks of struggling to CAST the binary data

miken32 · Accepted Answer · 2022-01-10 18:52:22Z

2

I recently came across similar requirement and did it like this. (Assuming $base64String is your string that contains image in base64 format)

$sql = "CAST(N'' AS xml).VALUE('xs:base64Binary(\"".$base64String."\")', 'VARBINARY(max)')";

UserProfilePicture::updateOrCreate(
    ['user_id' => $this->user_id],
    [
        'type' => $type,
        'size' => $size,
        'data' => DB::raw($sql),
    ],
);

edited Jan 10, 2022 at 18:52

miken32

42.5k16 gold badges127 silver badges177 bronze badges

answered Nov 18, 2019 at 18:02

Yogesh Gupta

1701 silver badge15 bronze badges

2 Comments

Benjamin Ini Over a year ago

after more than a week... this is the best solution

Yogesh Gupta Over a year ago

Thanks @BenjaminIni

GogromaT · Accepted Answer · 2018-02-08 16:26:46Z

I am faced with the same problem, but I figured out a way to make it work with Laravel's DB class.

Basically just like in any other language certain types cannot be implicitly converted, so are the types of MSSQL. Here is an image of all of the type conversion for MSSQL. Notice that From nvarchar To varbinary you can only do Explicit conversion.

Since in PHP you don't have "binary" type and simply a string, we cannot store it directly into MSSQL. To solve this problem, we need to manually/explicitly cast our image into a binary/varbinary. Reading from conversions of binary/varbinary we need to call CAST or CONVERT on our image.

And since we want to do prepared statements, we can say CONVERT(VARBINARY(MAX), ?) and then bind it to the image value.

DB::statement(
    "INSERT INTO files (PersonalNr, Mitarbeiterschluessel, Vorname, Familienname, Bild) VALUES (?,?,?,?,CONVERT(VARBINARY(MAX), ?));", [
        request('personalnr'),
        request('mitarbeiterkey'),
        $vorname,
        $nachname,
        base64_encode(file_get_contents($path))
]);

Notice that I don't do any pack/unpack, but simply base64_encode'ed string, because as I understand you cannot pass binary from PHP to SQL that easily.

Chevelle · Accepted Answer · 2020-04-26 05:21:37Z

0

i know that this topics is very old but, this was my fix

$doc = file_get_contents($path);
$data = unpack("H*hex", $doc);
DB::connection("sqlsrv")->statement("INSERT INTO tb_docs (id, doc) VALUES (?,0x".$data['hex'].");",[$id]);

the field "doc" have as data type varbinary(max) without attribute FILESTREAM, in this field i stored files like JPG, PNG, PDF and work very well

answered Apr 26, 2020 at 5:21

Chevelle

2585 silver badges14 bronze badges

Collectives™ on Stack Overflow

How to insert binary data to SQL using Laravel 5.*

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related