Storing and retrieving an image in SQL Server 2000

Tags:
Microsoft SQL Server 2000
SQL Server Image Store
Visual Basic 6
How do I store and retrieve an image in SQL Server 2000, using Visual Basic 6.0 on the front end?

Answer Wiki

Thanks. We'll let you know when a new response is added.

[kccrosser] I have to disagree with mrdenny – using SQL database table fields to store image blobs works quite well, and there are a lot of positive reasons to use this technique – not the least of which is the consistency of the database transactions and backups.

As far as how to manipulate the data from VB, assuming you are using ADO 2.5 or later, here is the Microsoft article which describes the appropriate code methods:

Microsoft article on VB and SQL blob data

[mrdenny] This is not recommended as it will slow the database down, and case database bloat. It is much better to store the image on the file system somewhere and store a pointer to the file in the database.

If you must store them in the database, then create a table with a column using the datatype IMAGE. The IMAGE data type allows you to store up to 2 Gigs of data in the field (per row).

Create a stored procedure with an input parameter using the IMAGE data type.

In the VB code read the image file into a binary variable and pass it to the stored procedure.

When you see the image, select it from the database and put it into a variable, then render it from the variable, or save it as needed.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    I never said that it didn't work. As the system grows storing blob data within the database will cause performance issues as the SQL Server has to spend more and more time managing requests for these large objects instead of handling its normal OLTP processes. Not to mention the SQL Server's buffer cache will now be full of blob objects that don't need to be requested that often which are taking away a lot of space from storing row level data within the buffer. In a small application with a few hundred or a few thousand, even up to tens of thousands of blobs the system probably won't notice that much of an issue (depending on the amount of RAM, and the speed of the disks). But as you get into the millions, tens of millions+ range performance will nose dive as building SQL Servers with hundreds of Gigs of RAM to store objects which don't need to be cached gets very cost prohibitive. As an example, the system I currently manage has a ~500 Gig OLTP database (SQL 2005 so FILESTREAM isn't an option) with about 2 TB of blob data sitting on the file system. I would never want to load that blob data into the database. Database server performance would slow to a crawl as I wouldn't be able to cache much if any OLTP data into the buffer cache. As for consistent backups the fact that the backups might be a few minutes out of sync with the database is deamed as acceptable. Now if I was using SQL Server 2008 I could look into the option of using FILESTREAM in order to write the files into the SQL Server, but store them as file system objects on the file system, then use the Win32 API to read the files when needed. This would allow me to have my consistent backups without having to fill my SQL Server's buffer cache with blob data that is read occasionally.
    66,360 pointsBadges:
    report
  • Kccrosser
    I think one consideration would be the use of the image data and SQL Server. Is the intention to store a lot of structured information with perhaps one or two images associated with a given structured object? Or is this a catalog of many blobs? If the former, then the simplicity of storing the data directly as a blob in the database probably outweighs the complexity of external storage management. If you are intending to store terabytes of images, with the database primarily a set of reference data to organize the images, then you should consider storing the data externally. In this case, you might look at the RBS libraries, as these are designed to provide for SQL Server management of externally stored blob data. You can find the references at: Codeplex link to RBS As far as disk storage goes - disk is disk, whether managed by SQL Server or the file system. Whether the blobs are stored in the native file system or in SQL Server will not appreciably change the amount of storage required. I haven't seen problems with the buffer cache in systems using blob objects. However, the systems I work with usually have a high percentage of structured data relative to the images themselves, and the images tend to be in the 70 Kbyte to 200 Kbyte size range (e.g., mug shots). If you are trying to manage lots of multi-megabyte images, that may perform differently.
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following