BLOBs, VB and SQL Server 1

[phzero]

Hi All,

Does anyone know how to get an image column from a SQL Server database into an adodb recordset using vb code. Based on the code's logic, I will try to figure out how to write it to the database. Can't have everything on a silver platter, now can you. Many thanks for all your responses. Have a great day. Also, can someone explain why can I cannot do this: (assuming pic is a column of type image):

rsPic.open("select pic from MyTable", conn, ...)
MyPicControl.Picture = rsPic!pic

Thanks again for any responses.

 

[SemperFiDownUnda]

AppendChunk and GetChunck of the field object in the ADODB.recordset.

You can also play with streams.

 

[phzero]

Hi Semper,

Here is what I have done after what you have told me:
I opened a recordset and did the below. I got an error
saying: "Oject Required". What am I missing?

pImage.Picture = rsPic.Fields("Picture&quot.GetChunk( _
rsPic.Fields("Picture&quot.ActualSize)

 

[LazyMe]

You need to write the chunks to a temporary file. And then load that file as the picture property. There might be more than one chunk....


Greetings,
Rick

 

[CCLINT]

See thread709-391834

 

[rorubin]

If you are using ADO 2.5 or greater then use ADO.Stream, it's a lot easier to work with.

Here is the object description:

http://www.w3schools.com/ado/ado_ref_stream.asp

 

[CCLINT]

And that was exactly what was already mentioned in that thread above.
But you still need the GetChunk in order to get the binary data from the db before using it with the Stream object....

 

[strongm]

If you are the one putting the image into the database in the first place then you can avoid both GetChunk and temporary files by using ADO's streams and the fact that the Picture object is persistable

 

[phzero]

Hi All,

I'd like to thank you all for having responded to my plea. You all have helped me a great deal with this problem I was having, although I received the answer I was looking for from CCLINT (many thanks dude). Have a great day. Peace out.

 

[rorubin]

>>But you still need the GetChunk in order to get the binary data from the db before using it with the Stream object

I'm afraid that this is not accurate. Although internally that may be how Microsoft implemented. Stream has a method called Write which takes Buffer, which can be a recordset field value. So maybe I didn't understand what you were trying to say but you certainly don't "need" to use GetChunk.

 

[CCLINT]

Yes, this I am aware of...But you may run into problems with certain systems, providers and servers, and large amounts of binary data sooner or later. It may be risky, especially on older systems.

Even though you can pass the field to the buffer, the amount of data you can pull into the field may be limited to only part of the data. Therefore, it is possible that the amount of data available at the moment may not be the complete amount of data, or, if the data is too large, you may see performance degrade, and therefore you may sooner or later end up using the GetChunk - that is what it is there for....

 

[CCLINT]

If you really want to use it then here is a sample:

Dim rsADO As ADODB.Recordset
Dim strm As ADODB.Stream
Set strm= New ADODB.Stream

rsADO.Open "Select myPicture from Pictures", conn, adOpenKeyset, adLockOptimistic

strm.Type = adTypeBinary
strm.Open
strm.Write rsADO.Collect(0)

strm.SaveToFile "C:\myPictures\myPicture", adSaveCreateOverWrite


I have alot of customers using everything from old machines with W95 upwards, often with limited resources, and the method will therefore sooner or later bomb out.
I have seen that on other systems 5-10 Mb of data works.

Using GetChunk seems to me to be the most reliable method, and I wouldn't recommend otherwise, unless the stage it set differently under certain conditions.

 

[rorubin]

I now understand where you are coming from.