Pictures in SQL server

[einhugur]

Hello, does anyone know how to store and retrive pictures in SQL server ?

Thanks

[Chris Eastwood]

There are several links from the main CodeGuru/VB site, look under http://codeguru.developer.com/vb/Database/ADO/index.shtml - there's a link to an MSDN article on using GetChunk / AppendChunk using ADO.


Chris Eastwood

CodeGuru - the website for developers
http://codeguru.developer.com/vb

[Allen Noakes]

This is a great question.

The special thing about working with image data in SQL is that the data is not stored with the rest of the record. Instead a 16-byte pointer is used. There are 3 functions that can be used with image/text "or called BLOB data".

WRITETEXT
READTEXT
UPDATETEXT

Because a pointer is used, try to always use a protected image/text pointer. Here is an example of WRITETEXT with a properly protected text pointer.

BEGIN TRAN
DECLARE @mytextptr varbinary(16)
SELECT @mytextptr=TEXTPTR(pr_info)
FROM pub_info (UPDLOCK) WHERE pub_id='9999'
IF @mytextptr IS NOT NULL
WRITETEXT pub_info.pr_info @mytextptr WITH LOG 'Test of pointer'
COMMIT TRAN

Here is an example of READTEXT with a properly protected pointer:

BEGIN TRAN
DECLARE @mytextptr varbinary(16), @sizeneeded int, @pat_offset int
SELECT @mytextptr=TEXTPTR(pr_info),
@pat_offset=PATINDEX('%Washington%',pr_info) - 1,
@sizeneeded=DATALENGTH(pr_info) -
PATINDEX('%Washington%',pr_info) - 1
FROM pub_info (HOLDLOCK) WHERE pub_id='0877'

IF @mytextptr IS NOT NULL AND @pat_offset >= 0 AND
@sizeneeded IS NOT NULL
READTEXT pub_info.pr_info @mytextptr @pat_offset @sizeneeded

COMMIT TRAN


These 2 examples are taken right out of "Inside Microsoft SQL Server 7.0" page 258-259. READTEXT will return only a portion of the text which you don't want to do with image data, as you know, so just modify it to suit your needs.

Let me know how it works for you as I have used this method also with perfect results.

Allen Noakes
VB Programmer/Analyst
Dames & Moore
misan@dames.com

[czimmerman]

See http://www.freevbcode.com/ShowCode.Asp?ID=485 for one possible solution to this problem. It loads picture data from a database to a picture box, and saves data from a picture box to a database.

[einhugur]

Thanks, this one solved my problem and was real easy to use