Unanswered: Unpacking image data using a stored procedure
I have integer data stored in an image field. Every 2 bytes in the image field is a data point.
Currently this data is extracted from within a VB6 application, converted to an ADO recordset and passed in to Crystal Reports. I'd like to find a way to unpack the data through a stored procedure and report from the store procedure instead (bypassing the VB6 application).
Can some one point me in the right direction please?
How many points can be stored as a single image? If there are always less than 4000 points in an image, you could use something like:
CREATE PROCEDURE p_decompose
DECLARE @c VARCHAR(8000)
SELECT @c = Cast(GraphData AS VARCHAR(8000)
WHERE BottleID = @piBottle
CREATE TABLE #points (
pointID INT IDENTITY(1, 1) NOT NULL
, point_value INT
WHILE 0 < Len(@cWork)
INSERT INTO #points (point_value)
SELECT Ascii(@c) + 256 * Ascii(SubString(@c, 2, 1)) -- byte sex?
@c = SubString(@c, 3, 8000)
ORDER BY pointID
Depending on the byte sex of the values in the image, you may need to change which value gets multiplied by 256.
You could enhance that snippet using READTEXT to enable it to deal with arbitrary sized IMAGE data. Just as an FYI, it may or may not perform as you'd like using Transact-SQL... If you need to speed it up, the only option I see would be to use an extended stored procedure.
I can tell from this that byte2 should be the most significant byte. I tried 256 * byte2 + byte1 but sometimes get an overflow (for values that should be negative). Any suggestions for performing the byte swap?
This seems to work, but it is a little slow for BottleIDs with > 4,000 data points (about 4 secs on a 1.6GHz P4 notebook running MSDE). Good enough for now, but I'd still love to know a faster way to switch bytes in a SMALLINT if anyone can think of it.
DECLARE @BottleID INT
SET @BottleID = 200
DECLARE @DataLength INT
DECLARE @Offset INT
DECLARE @Identity INT
DECLARE @ptrval VARBINARY(16)