[AccessD] Retrieving the binary data from a BLOB (SQL 2000)

Jim:
To answer your questions:
1) You are only getting 1 record because you are selected TOP 1 which will
only return a single record. Change that to TOP 20 and you will get 20
records
2) If you just want the size of your field, you can use: 
SELECT TOP 20 DATALENGTH(imagefield) FROM tbldocs WHERE imagefield IS NOT
NULL ORDER BY NewID()
That should give you the size of the field for 20 random records. If you
need the actual binary data, you'll probably need to use a cursor to loop
though the records one at a time.

Hope that helps.

James Barash

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim DeMarco
Sent: Tuesday, April 26, 2005 10:42 AM
To: AccessD (E-mail)
Subject: [AccessD] Retrieving the binary data from a BLOB (SQL 2000)

List,

We are having an issue with uploading SQL data across our WAN.  The network
speed from our remote sites is 384 kBs and we're sending across numerous
data fields plus a images stored in a BLOB field.  The upload takes over 3
minutes per record with images but is instantaneous without them.  This
upload time is unacceptable as you can imagine.  It ties up the server and
no work can be done until the process runs it's course.  For the curious
we're using ADO recordsets to pass the data from the client to the server.

I'm trying to determine the actual size of the images as they're stored in
the SQL db (image field).  I have the following code that retrieves binary
data for one record but I can't get it to show more than one.

USE myDB
GO
DECLARE @ptrval varbinary(16)
SELECT top 1  @ptrval = TEXTPTR(imagefield) 
   FROM tbldocs pr where imagefield IS NOT NULL
READTEXT tbldocs.imagefield @ptrval 1 25
GO

My questions are:
1. How to show more than one record?
2. How to get the complete data from the field?  The second parameter in
@ptrval can be increased but all the data should be different sizes and I'd
just like to be able to pull it without a parameter.

I need to pull about 20 images in binary format to get an estimate of a
typical data send (one record can contain up to 20 images sometimes more).

TIA

Jim DeMarco
Director of Application Development
Hudson Health Plan




****************************************************************************
*******
"This electronic message is intended to be for the use only of the named
recipient, and may contain information from Hudson Health Plan (HHP) that is
confidential or privileged.  If you are not the intended recipient, you are
hereby notified that any disclosure, copying, distribution or use of the
contents of this message is strictly prohibited.  If you have received this
message in error or are not the named recipient, please notify us
immediately, either by contacting the sender at the electronic mail address
noted above or calling HHP at (914) 631-1611. If you are not the intended
recipient, please do not forward this email to anyone, and delete and
destroy all copies of this message.  Thank You".
****************************************************************************
*******

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com