-2

I want to save an uploaded image to database. Now my question is:

  1. What should be the data type for SQL Server 2000 for image? a. image b. varbinary

  2. what is the code for saving image in database?

  3. How to retrieve the image from database and show?

Please refer me any tutorial or guidline. Or if you can please share with me.

Thanks in advance.

Termedi
  • 3
  • 4

4 Answers4

4

The data type should be text because the best way to save an image to a database is to save its path. Let your OS do the job of storing the actual files.

simoncpu
  • 578
  • 5
  • 9
  • While I agree that this is generally the best method, this is not what he is asking for. - And to say conclusively that this is the **best** method is wrong. It is *generally* the best method, but certainly not in always. – Atli Mar 31 '10 at 12:58
  • You may have a point, but I will say conclusively that storing an image to a database is wrong. :) – simoncpu Apr 27 '10 at 12:23
1

Typically on SQL Server, you would use a BLOB, Binary Large OBject, to store images. We used it for Word documents on a previous project, and it worked just fine. See this article on Database Journal for more info, although a quick Google for the BLOB type will throw up lots more examples.

Matt Gibson
  • 37,886
  • 9
  • 99
  • 128
  • I didn't think SQL Server had a BLOB type. Or do you perhaps use it more as a general term for all binary types? – Atli Mar 31 '10 at 13:30
0

I wrote this article a while back on this subject. It should help you with #2 and #3. It uses MySQL, but the basics are the same, you just need to replace the MySQL calls with the MSSQL calls.

As to #1, I would go with the obvious choice: "image".
I am not 100% sure of the differences between the two, however. It just seems obvious :)

Edit, according to this, it appears that the image datatype is deprecated. It will be removed in future versions. Not sure how much this affects you, seeing as you are using a 10 year old version, but it is worth keeping in mind.

Roberto Aloi
  • 30,570
  • 21
  • 75
  • 112
Atli
  • 7,855
  • 2
  • 30
  • 43
  • Not necessarily an answer but whenever I upload images to a db, I base64 encode it and then store it in a clob. When I want to show it, i'll just decode it. – Flukey Mar 31 '10 at 11:34
  • 1
    @Jamie why is this useless encoding? – Your Common Sense Mar 31 '10 at 11:37
  • @Col. Shrapnel - It's not neccessarily useless. There's a good argument for it here: http://www.stoimen.com/blog/2009/04/23/when-you-should-use-base64-for-images/ – Flukey Mar 31 '10 at 11:45
  • @Jamie But this is completely different issue discussed in the article you linked to! It does encode at displaying, not at save time! – Your Common Sense Mar 31 '10 at 12:17
  • @Jamie. Col. Shrapnel is right, these two scenarios are in no way comparable. - The reason you use Base64 for inline `` tags is because the character representation of some of the data may mess up the HTML parsing. - That is not the case when inserting into a database. Even when inserted via a string (like a PHP script creating a SQL query) the small list of special characters that may mess up the query can easily be matches against the data and escaped. – Atli Mar 31 '10 at 12:47
  • If you write a tutorial, at least learn them to send it to the db-server as hex; it would increase the performance. – Jacco Mar 31 '10 at 12:54
  • @Jacco How so? I write what I know, and I know no reason why converting the data to hex before inserting it would increase performance. (Wouldn't mind being corrected, though. Increased performance is always good :]) – Atli Mar 31 '10 at 13:02
  • 1
    @Atli It is considered bad-practice to use mysql_real_escape_string on binary data. The unescaping can take up considerable resources in MySQL, because of how thing are handled. (because escaped data is handled as string, the whole string is read into memmory). Since MySQL can use stream operators on a HEX-coded data, sending data as `x'".bin2hex( $blob )."'` lessens the load on the server. (Some people advacate using base64 encoding to sidestep the unescape problem, but this inflates the stored data by aprox 33%, where the hex stream is stored as the original binary data). – Jacco Mar 31 '10 at 14:39
  • @Jacco Ahh, I see. I was not aware of that issue. Thanks for pointing that out. - I wonder if using a prepared statements would bypass this as well, or if the driver escapes it in the same way mysql_real_escape does. – Atli Mar 31 '10 at 16:54
  • @Jacco I'm having a hard time confirming this. I've been running tests and it seem that using HEX-coded queries uses more resources in both PHP and MySQL than the binary-string equivalents. Memory usage goes up to 24% higher and CPU timing up to 86% higher. (Granted, these are rough tests, but still.) - You wouldn't happen to have a reference to any resource on this topic? – Atli Apr 01 '10 at 04:06
  • The URL that documented the differences has died, I've been looking for other info on the subject but can't find any good benchmarks. If my memory serves me right, the differences started to show from 2MB blobs and larger. Looking at my example, it might be the `'(...)'` still makes it a string. I guess using `x".bin2hex( $blob )."` could make the difference as could the db-engine (InnoDB vs MyISAM) – Jacco Apr 01 '10 at 16:38
  • @Jacco OK, thanks. I've so far only been testing files up to ~1.5MiB, so that may be the reason. I'll be testing it further. Interested to find out the specifics of this - I created a new question about this, by the way. http://stackoverflow.com/questions/2558453/why-use-bin2hex-when-inserting-binary-data-from-php-into-mysql – Atli Apr 01 '10 at 18:13
0

In SQLServer IMAGE or VARBINARY are allmost the same. IMAGE is 2GB but VARBINARY() needs a length argument. It's not good idea to store images in a database, the size increases a lot, with each Backup you need to save all images, with increasing size also increases the time to perform a Backup and Restore. You also need to change the network packet size (at the server properties, advanced, network, network packet size) In recents SQLServer versions the most adecuate datatype is varbinary(MAX)

Cristian Solervicéns
  • 69
  • 2