using PHP to store image in MSSQL database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
phpwalker
Forum Commoner
Posts: 81
Joined: Sun Apr 23, 2006 12:18 pm

using PHP to store image in MSSQL database

Post by phpwalker »

Hi all, I'm facing an issue when i'm trying to insert an image into MSSQL database.

fyi, i'm using IIS as web server, PHP, and MSSQL database server.

Error I got is
Warning: mssql_query() [function.mssql-query]: message: Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query. (severity 16) in C:\Inetpub\wwwroot\banner\upload.php on line 65

Warning: mssql_query() [function.mssql-query]: Query failed in C:\Inetpub\wwwroot\banner\upload.php on line 65
Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query
ANyone can help me?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: using PHP to store image in MSSQL database

Post by onion2k »

Images are binary data. You can't put image data into a varchar field. If you're trying to store the filename then there's something very wrong with your code. If you're trying to store the image data then your database schema is wrong. No way to say which it is without knowing more about the code though.
phpwalker
Forum Commoner
Posts: 81
Joined: Sun Apr 23, 2006 12:18 pm

Re: using PHP to store image in MSSQL database

Post by phpwalker »

Thanks for the fast reply Onion, I'm wanted to store the image which is binary file into the database.

Here's part of my code.

Code: Select all

$data = $_FILES["file"]["tmp_name"];
          
          //settype($data, "varbinary");
 
          mssql_query("INSERT INTO alenn_tblrotator VALUES ('". $data . "')") or die(mssql_get_last_message()); 
         
And my database design is like

Code: Select all

CREATE TABLE $myPrefix$myTable (
id  smallint IDENTITY(101,1)PRIMARY KEY,
image varbinary(MAX))";
Perhaps you are right, the image uploaded using form, therefore it's varchar data type. How to covert it to varbinary? I've use settype still not working for me.

THanks again for the reply, Onion.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: using PHP to store image in MSSQL database

Post by onion2k »

$_FILES["file"]["tmp_name"] contains the name of the temporary file, not the contents. You'll have to open the file (with fopen() or file_get_contents() ).
phpwalker
Forum Commoner
Posts: 81
Joined: Sun Apr 23, 2006 12:18 pm

Re: using PHP to store image in MSSQL database

Post by phpwalker »

Oh, now only I realize that is only file name...thanks Onion2k!

However, after I've used the fread() function, I've encountered more errors.

Code: Select all

$data = fopen($uploadfile, "rb");
          
          $contents = addslashes(fread($data, filesize($uploadfile)));
          
          //echo $contents;
          
          $contents = hexdec($contents);
          echo $contents;
           
          $query = "INSERT INTO(imgdata) alenn_tblrotator VALUES ('" . $contents . "')";
          
          echo $query;
          
            if (strlen($data) < 149000) {
 
                mssql_query($query) or die(mssql_get_last_message()); 
          
                echo "image saved into database!";
              
                fclose($data);
                
            }else{
            
                echo "image is too large";
            }
Error I got :
Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near '('. (severity 15) in C:\Inetpub\wwwroot\banner\upload.php on line 85

Warning: mssql_query() [function.mssql-query]: message: The identifier that starts with ' R ? ????R()?( \0;?@??\0P???Pq@????\"t(??1??!?)??o?Y`??Vlh? ???T?/X??jY??&BM1???? C ?F? ?????? ?e?U4?' is too long. Maximum length is 128. (severity 15) in C:\Inetpub\wwwroot\banner\upload.php on line 85

Warning: mssql_query() [function.mssql-query]: message: The identifier that starts with ' Rat3??ScK?Et=?b6/v??k9+?J?d?^@?B??y??*?H??J?2%+h\\?Z??U?? ?r?h???.D??zelBWi????\0?3d`?!? P4?#\0? ??? V?' is too long. Maximum length is 128. (severity 15) in C:\Inetpub\wwwroot\banner\upload.php on line 85 ...
Fyi, I've change the image data type from varbinary to IMAGE in the MSSQL database. What step I have missed out?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: using PHP to store image in MSSQL database

Post by onion2k »

Why are you using hexdec() on the data?
phpwalker
Forum Commoner
Posts: 81
Joined: Sun Apr 23, 2006 12:18 pm

Re: using PHP to store image in MSSQL database

Post by phpwalker »

Sorry, that's for testing purpose.

Now i roughly have an idea, which is I convert the image into hex file and save into MSSQL. For some reasons, I don't know why when I browse the data using MSSQL Server management studio, it was converted into decimal. However, when I pull the data out from database, it's converted back into hex file again.

converting image into hex: [upload.php]

Code: Select all

$dataString = file_get_contents($uploadfile);
 
          //$samething = bin2hex($dataString);           
          $arrData = unpack("H*hex", $dataString);
          $contents = "0x".$arrData['hex'];
          //$contents = base64_encode($contents);
          
          $query = "INSERT INTO alenn_tblrotator VALUES ('" . $contents . "')";
This is the code that the raw image (in hex) pull out from the database and converting back into binary using pack() function

Code: Select all

 
$row = mssql_fetch_assoc($result);
 $db_img = $row['imgdata'];
$db_img = pack("H" . strlen($db_img), $db_img);
header("Content-Type: image/jpeg");
echo $db_img;
it shows the result when i browse the page source: [showimage.php]
?????JFIF???d?d?????Ducky???????????Adobe?d?????????







???X?????????????????????????????????!1AQaq"???2BR#???br3$??C?Scs4???%?T????!1AQaq"2??#?B3??Rb?r????????Qp?@ ??@ ??@ ??@ !H??
(???DA? (?@ t (?@??@ 6???@ ??@ ??@ ???
(???J ?????@ v?8?@ *???
?*??@UTP@??@ v?@?q??@ ??(???
???@ ??@ Pu*??T??@ ??@ %@?8Pq@
B???@ ??@ ????@ ??@ ??@ P?#?8Pq@
B???@ ??@ ??@ ??@ ??@ ??@ J?
Gj???? ?(?@ ??@ ??@ ??@ ??@ ??@??@ H
@ ??@ ??@ ??@ ??@ ??@ ???@ ???@ ??H?@???@ ??@ ??@ ??@ ??@??@ ??@ ?(? )??@ ??@ ??@ ??@ ??@ ???@ ??R?@)??H????v?X?1??ZW?]??2?6ù[0???????k^|?N+9?/d?;?????Q>??HZ*??????Y???I"_???
3S??j0Y???c???(Rm??<??]QCQ?H8?J?qO
nK;???75?7R???M??<????@ ??@ ??@ T??@ ??@ @ ??g?d?=I?1@?ds?m9???}fj?m?C??n?T?bhc@??h??7?K?'ed?"K?4??)?O%?$?+?U???J\?3-?]?d[^&??/??g??h"_Qn?u1???}??r???3?wE?7?Y?0c???R?4`3???u????6?\c???!?fOk%?2B50??n#??p???^??g?Bo?b??1??n?3?x????_o??e???X?K???c?"Z?;??.\?%??@ ??@ ??@ ???@
@?H ????R??????&?U??)????,?Z????<O??????-?4?_??G?!?Z)??3?????:a?Y?i5????11??s$

y?c???I?Mv_??
?|U???7C??n?'????6???4V?\??M???|???#?[2o{Ks????????dM
p?
???
?????y??Z?Z? ??F?p??O?w,9.K???z????yG?A??/e???@ ??@ ??@ ???@ P @ ?v8?+?q???d???~?G?Z?$???|?w
?????U??t,n?r?S?]Q?S??KhN?q?
I then use this html tag to view the image: [viewimage.php]

Code: Select all

 
<img src="showimage.php" width="100" height="80"/>
 
But, the image is still not showing!! I have no idea at all as I've been doing research for this issue for 3 days already. Do I still miss out something in between???
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: using PHP to store image in MSSQL database

Post by onion2k »

Don't mess with the data. Insert exactly what's in the file into the database table as it is.
phpwalker
Forum Commoner
Posts: 81
Joined: Sun Apr 23, 2006 12:18 pm

Re: using PHP to store image in MSSQL database

Post by phpwalker »

But, it will gives me these errors:
Notice: Undefined variable: dataString in C:\Inetpub\wwwroot\banner\upload.php on line 134

Warning: mssql_query() [function.mssql-query]: message: Unclosed quotation mark after the character string '????'. (severity 15) in C:\Inetpub\wwwroot\banner\upload.php on line 136

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near '????'. (severity 15) in C:\Inetpub\wwwroot\banner\upload.php on line 136

Warning: mssql_query() [function.mssql-query]: Query failed in C:\Inetpub\wwwroot\banner\upload.php on line 136
Incorrect syntax near '????'.
I extract part of the data gonna insert into database:
INSERT INTO alenn_tblrotator VALUES ('?????JFIF??`?`?????C? ???C ???e"???????????????? ???????????????????????????? uP?O??h???u?I6{??w??4o?(??1P6y?(???o????g?0+h?L??Jmj??F%"??R?N?Eu6yZ???u????s$???6?4?_$?6?M3?$??32O3?*ffI??*?#Xt[?9??1U?vb ?? ????? U?^?j??J??h?#9?z???XN?SJ???3????J???H?? 9?p?"?:?L?=EI?? p?j??wd??[Ta?n??P?3 ?f??32L?|??$?O3?=??<?rO???s???qEK? ? ?a?4a*"\5?q@7f?ViNp?XK?.?;?? ??I????G??$?@?c?_?{?:?I????/???b??q??O?_x?R???[?&W?uJ?4?9=W?~X?_-?PE-6??`<??33$?? ????<?$??5?\?d?f?Z??V?1H?=??d?I?#R ?`??N??]x:?B????
The simple code:

Code: Select all

 $contents = file_get_contents($uploadfile);
          
          $query = "INSERT INTO alenn_tblrotator VALUES ('" . $contents . "')";
Therefore I will need to convert it to another data type. Any idea?
phpwalker
Forum Commoner
Posts: 81
Joined: Sun Apr 23, 2006 12:18 pm

Re: using PHP to store image in MSSQL database

Post by phpwalker »

I think the problem is over here, so I open a new topic already.

viewtopic.php?f=2&t=83983&p=466203#p466203
ps0113
Forum Newbie
Posts: 1
Joined: Tue Jan 08, 2013 9:56 am

Re: using PHP to store image in MSSQL database

Post by ps0113 »

hey can you let me know how you have resolved your problem... i do have the same problem to store image in byte format in ms-sql server 2008 using php in iis environment...!!

pleaze help me thankz in advance..

Code: Select all

$fileName = $_FILES['userfile']['name'];
$tmpName = $_FILES['userfile']['tmp_name'];
$fileSize = $_FILES['userfile']['size']; 
$fileType = $_FILES['userfile']['type']; 

$fp = fopen($tmpName, 'r'); 
$content = fread($fp, filesize($tmpName)); 
$content = addslashes($content); 
fclose($fp);

$query = "INSERT INTO upload (name,size, type, content ) VALUES ('$fileName','$fileSize', '$fileType', '$content')";
$res = odbc_exec($conn,$query);
	if(!$res)
	{
	 echo "File uploading failed";	 
	}
	else
	{
	 echo "File $fileName uploaded successfully";
	}	       

    
[text]but it shows sql error.............. have any idea whats wrong here....!![/text]
Post Reply