note 43160 deleted from function.mssql-bind by nlopess
| From: | nlopess@php.net | Date: | Fri, 11 Jun 2004 13:40:11 +0000 |
| Subject: | note 43160 deleted from function.mssql-bind by nlopess | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-70931@lists.php.net to get a copy of this message | ||
Note Submitter: lb@subset.de
----
I tried to upload images (passfoto.jps's) to a MSSQL 2000 Server (SP3) using datatype
"IMAGE". To do this at all, of cause I know ... never ever. To to this against a Mysql DB
with datatype "BLOB" no problem.
I was not able to use the same way uploading images to a MSSQL as I did in a MySQL. Infact, I needed
a Stored Procedure on the MSSQL Server (AddMemberImage.txt) and using the PHP function mssql_bind to
get the fotos up.
Bad, the mssql_bind seem's not support the datatype "IMAGE" I tried instead with
"VARBINARY".
To find the supported datatypes I find http://www.phpbuilder.com/manual2/ref.mssql.php
= Predefined Constants
on the MSSQL Server
Procedure: AddMemberImage.txt
CREATE PROCEDURE AddUDMemberImage
(
@initial varchar(8),
@ImageType int,
@ImageContentType nvarchar(50),
@ImageFileName nvarchar(150),
@ImageData varbinary
)
AS
if isnull(@ImageContentType,'') = ''
begin
set @ImageContentType = 'jpg'
end
if isnull(@ImageFileName,'') = ''
begin
set @ImageFileName = 'tmp'
end
IF @ImageType = 1 -- Member image
BEGIN
if exists ( select initial from passfotos where initial = @initial)
UPDATE passfotos
SET
PhotoPicture = @ImageData,
PhotoPictureContentType = @ImageContentType,
PhotoPictureFileName = @ImageFileName
WHERE passfotos.initial = @initial
else
insert into passfotos (initial, PhotoPictureFileName, PhotoPictureContentType, PhotoPicture) values
(@initial, @ImageFileName, @ImageContentType, @ImageData)
END
GO
(there you can also identify the tabledesign)
and the php-script that worked:
<?php
// Database connect
// mssql server hostname
$dbhost = "localhost";
// mssql server username
$dbuser = "sa";
// mssql server password
$dbpasswd = "xxPasswordxx";
// mssql server connect
$db=mssql_connect("$dbhost","$dbuser","$dbpasswd");
// mssql database name
$db_name="members";
mssql_select_db($db_name,$db);
$Folder = @$_GET['Folder'];
if ($Folder=="") {
$Folder="./";
}ELSE{
if ($Folder=="..") {
$Folder="./";
}
}
if ($handle = opendir($Folder)) {
while (false !== ($file = readdir($handle))) {
$fileArray[$j] = $file;
$j++;
}
arsort($fileArray);
closedir($handle);
}
for( $i=1; $i<count($fileArray);$i++ )
{
if ($fileArray[$i] != "index2.php" AND $fileArray[$i] != "." &&
$fileArray[$i] != "..")
{
if (is_dir($Folder . $fileArray[$i])) { }
{
$form_description = substr("$fileArray[$i]", 0, -4);
$data = fread(fopen($fileArray[$i],"rb"), filesize($fileArray[$i]));
$form_data_size = filesize($fileArray[$i]);
$data = "bin2hex($data)";
$ContentType = '';
$ImageType = 1;
// Annahme, es existiert eine Stored PRocedure sp_StorePicture(filename, binaerdaten)
$stmt = mssql_init("AddUDMemberImage");
mssql_bind($stmt, "@ImageFileName", $fileArray[$i], SQLVARCHAR, false);
mssql_bind($stmt, "@ImageData", $data, SQLBIT, false);
mssql_bind($stmt, "@initial", $form_description, SQLVARCHAR, false);
mssql_bind($stmt, "@ImageContentType", $ContentType, SQLVARCHAR, false);
mssql_bind($stmt, "@ImageType", $ImageType, SQLINT1, false);
$result=mssql_execute($stmt);
echo "added - $i = $form_description - $fileArray[$i] - $form_data_size <br>";
}
}
}
$j=0;
MSSQL_CLOSE();
?>
the jpg's had been in the same directory as the php scripts and all had a format xzy.jpg - can
be hundreds.
If someone find how to support datatype IMAGE ... give me an email (lb@subset.de)
By the way, I'am NOT a programmer ... just an network admin... sorry about the coding ... and
of cause I had some help to.