Menu
I'm trying to upload image into SQL Server using PDO with the same code as I do it into MySQL with no success.
I thought it will be simple as in MySQL but Microsoft makes sure I'll suffer.
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.
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'
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();
}
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 definition:
database name 'test'
table name 'tbl_image'
field #1 'username varchar(255)'
field #2 'type varchar(255)'
field #3 'image blob'
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)'
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;
Any idea?
928
asked Sep 16 '25 06:09
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();
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With