PHP PDO将映像加载到MS SQL Server 2012中

问题描述：

我正在尝试使用PDO将图像上传到SQL Server中，就像我将图像上传到MySQL中一样，但没有成功.

I'm trying to upload image into SQL Server using PDO with the same code as I do it into MySQL with no success.

我认为这将像在MySQL中一样简单，但是Microsoft确保我会受苦.

I thought it will be simple as in MySQL but Microsoft makes sure I'll suffer.

我收到的错误是: SQLSTATE [IMSSP]:将输入参数3的字符串转换为UCS-2时发生错误:目标多字节代码页中不存在Unicode字符的映射. /strong>

the error I'm receiving is: SQLSTATE[IMSSP]: An error occurred translating string for input param 3 to UCS-2: No mapping for the Unicode character exists in the target multi-byte code page.

我已经创建了这个小的可运行示例(2个文件"upload.php"和"test.html"). 为了使用MySQL，参数应为 $ db ='mysql'

I've create this small runnable example (2 files 'upload.php' and 'test.html'). In order to use MySQL the parameter should be $db = 'mysql'

名为"upload.php"的php文件内容:

php file content named 'upload.php':

<?php

$db = 'mssql'; // $db = 'mysql';

$config = array(
    'mysql' => array(
        'dsn'       => 'mysql:host=localhost;dbname=',
        'user'      => 'root',
        'password'  => ''
    ),

    'mssql' => array(
        'dsn'       => 'sqlsrv:Server=localhost;Database=',
        'user'      => '',
        'password'  => ''
    )
);

try {
    // connect to database
    $pdoTest = new PDO($config[$db]['dsn'].'test', $config[$db]['user'], $config[$db]['password']);
    $pdoTest->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // insert image to table (with additional some info)
    $queryIn = $pdoTest->prepare("
        INSERT INTO     tbl_image (username, type, image)
        VALUES          (:username, :type, :image);
    ");
    $queryIn->bindValue(':username',  $_POST['username']);
    $queryIn->bindValue(':type',      $_FILES['image']['type']);
    $queryIn->bindValue(':image',     file_get_contents($_FILES['image']['tmp_name']));
    $queryIn->execute();

    // retrieve image and type from table
    $queryOut = $pdoTest->prepare("
        SELECT          type, image
        FROM            tbl_image
        WHERE           username = :username
    ");
    $queryOut->bindValue(':username',  $_POST['username']);
    $queryOut->execute();
    $row = $queryOut->fetch(PDO::FETCH_ASSOC);
    if ($row) {
        // send image back to browser
        header('content-type: '.$row['type']);
        echo $row['image'];
    }
}
catch (PDOException $e) {
    echo $e->getMessage();
}

名为"test.html"的html文件内容:

html file content named 'test.html':

<!DOCTYPE html>
<html class="no-overflow">
    <head>
        <meta charset="UTF-8">

        <title>Load image to SQL Server 2012</title>
    </head>

    <body>
        <form enctype="multipart/form-data" action="upload.php" method="POST">
            <input type="text" name="username" autofocus="">
            <input type="file" name="image" accept="image/jpeg">
            <input type="submit" name="submit">
        </form>
    </body>
</html>

MySQL定义:

database name     'test'
table name        'tbl_image'
field #1          'username varchar(255)'
field #2          'type varchar(255)'
field #3          'image blob'

SQL Server 2012定义:

SQL Server 2012 definition:

database name     'test'
table name        'tbl_image'
field #1          'username nvarchar(255)'
field #2          'type nvarchar(255)'
field #3          'image varbinary(MAX)'

我还尝试了一些较旧的SQL Server示例，但均未成功:

I also tried some older examples for SQL Server with no success:

$datastring = file_get_contents($_FILES['image']['tmp_name']);
$data       = unpack("H*hex", $datastring);
$image      = '0x'.$data;

有什么主意吗?

答

这是mssql的解决方案代码

Hi this is the solution code for mssql

<?php

$db = 'mssql';

$config = array(
    'mysql' => array(
        'dsn'       => 'mysql:host=localhost;dbname=',
        'user'      => 'root',
        'password'  => ''
    ),

    'mssql' => array(
        'dsn'       => 'sqlsrv:Server=localhost;Database=',
        'user'      => '',
        'password'  => ''
    )
);

try {
    // connect to database
    $pdoTest = new PDO($config[$db]['dsn'].'test', $config[$db]['user'], $config[$db]['password']);
    $pdoTest->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // insert image to table (with additional some info)
    $queryIn = $pdoTest->prepare("
        INSERT INTO     tbl_image (username, type, image)
        VALUES          (:username, :type, :image);
    ");
    $queryIn->bindParam(':username',  $_POST['username']);
    $queryIn->bindParam(':type',      $_FILES['image']['type']);
    $queryIn->bindParam(':image',     file_get_contents($_FILES['image']['tmp_name']), PDO::PARAM_LOB, 0, PDO::SQLSRV_ENCODING_BINARY);
    $queryIn->execute();

    // retrieve image and type from table
    $queryOut = $pdoTest->prepare("
        SELECT          type, image
        FROM            tbl_image
        WHERE           username = :username
    ");
    $queryOut->bindParam(':username',  $_POST['username']);
    $queryOut->execute();
    $queryOut->bindColumn(2, $image, PDO::PARAM_LOB, 0, PDO::SQLSRV_ENCODING_BINARY);
    $row = $queryOut->fetch(PDO::FETCH_ASSOC);
    if ($row) {
        // send image back to browser
        header('content-type: '.$row['type']);
        echo $row['image'];
    }
}
catch (PDOException $e) {
    echo $e->getMessage();
}

PHP PDO将映像加载到MS SQL Server 2012中

相关推荐