如何使用Laravel 5. *将二进制数据插入SQL [英] How to insert binary data to SQL using Laravel 5.*

问题描述

我想将图像保存到MSSQL的varbinary(max)字段中.

控制器中的store方法如下:

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');

    }

我遇到错误:

SQLSTATE [42000]:[Microsoft] [SQL Server的ODBC驱动程序11] [SQL 伺服器]中的隐含变量vvar nvarchar(max)-Datentyp in varbinary(max)ist nichtzulässig.

翻译:

从nvarchar(max)数据类型到varbinary的隐式转换 (最大)是不允许的.

解决方案

我也遇到了同样的问题，但是我想出了一种使其可与Laravel的DB类一起使用的方法.

与其他语言一样，某些类型不能隐式转换，MSSQL的类型也不能隐式转换.这是MSSQL所有类型转换的图像. 请注意，从 nvarchar 到 varbinary ，您只能进行显式转换.

由于在PHP中您没有二进制"类型，而只是一个字符串，因此我们无法将其直接存储到MSSQL中.要解决此问题，我们需要将图像手动/明确地投射到 binary/varbinary 中.从转换的二进制/varbinary ，我们需要在图像上调用 CAST 或 CONVERT .

并且由于我们要执行准备好的语句，因此我们可以说CONVERT(VARBINARY(MAX), ?)，然后将其绑定到图像值.

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))
]);

请注意，我不执行任何打包操作，而只是执行base64_encode字符串，因为据我所知，您无法轻松地将二进制文件从PHP传递到SQL.

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.

解决方案

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.

这篇关于如何使用Laravel 5. *将二进制数据插入SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！