PHP PDO将映像加载到MS SQL Server 2012中 [英] PHP PDO load image into 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中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
