Getting cropped BLOBs when using a Select Statement

[Jalr2003]

Hello,

I am using SQL Server 2005 with a table that looks like this:

create table dbo.Display_Images (
pk_Image_ID int Identity(1,1) primary key,
Description varchar(50) not null,
Picture varbinary(max) not null,
DateAdded datetime default getdate(),
FileName varchar(50) null
)

Using Borland Components I was able to upload jpegs to the "Picture" field, and I was also able to view the pictures by using a TTable component (linked to the table above). The problems start when I try to see the pictures from a dataset resulting from a Select Statement, for I only get a portion of the pictures.

At first I thought it was a Borland problem when using TQuery, so I created a view that uses the same SQL statement and I tried to access the view using a TTable Component, but the results were the same. Then, I used a "Select Into" statatement to copy some of the records into a new table and I still get partial pictures.

It looks to me that whenever I use a Select statment the lenght of the jpegs get shortened somehow. I tried to use

SET TEXTSIZE 50000

but it did not work.

my pictures are 47KB average.

Any help on this is highly appreciated.

Thanks

 

[ESquared]

I would ask in the appropriate forum for Borland components.

In ADO, you can't access long columns in the regular way. You have to do use a special GetChunk method (and for updating, AppendChunk). Just because two libraries give the same result doesn't necessarily mean it's a SQL problem. I haven't tried reading long column data with SQL 2005 but I wouldn't be surprised if it's just the same as before.

 

[Jalr2003]

Hi,

Thaks for your answer. The problem I see with your statement is that I was able to retrieve the complete pictures straight from the original table using Borland components, more specifically, the TTable. The same TTable fails when accesing a second table created by using a Select Into statement.

Any other ideas?

Thanks again,

 

[ESquared]

oh... hmmm.

Do your select into and then query the length of the column's rows in the new table. If the lengths are right, then I wouldn't know what to suggest.

I seem to recall that when working with long data in SQL 2000, you couldn't do a simple select into, as the destination table would have everything but the out-of-row data. You had to use text pointers and loop through and update one row at a atime.

I'm sure SQL 2005 is different, but if your TTable works for one situation then it should work for another. I would validate that the data is actually IN the table correctly, then I would use SQL Profiler to run a trace while you're doing both a working and a non-working data access. What you learn from the trace may be all you need to find out what's wrong. Simply copying the exact statements submitted to the server and using them in your own QA or SSMS session can often be very, very enlightening.