Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2005
    Location
    Avon Lake, OH
    Posts
    112

    Question 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?

    CREATE TABLE [dbo].[tblBottleGraphData] (
    [BottleID] [int] NOT NULL ,
    [GraphData] [image] NULL ,
    [TimeOffset] [int] NULL ,
    [ConcurrencyID] [int] NULL
    ) ON [SECONDARY] TEXTIMAGE_ON [SECONDARY]

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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:
    Code:
    CREATE PROCEDURE p_decompose
      @piBottle INT
    AS
    
    DECLARE @c VARCHAR(8000)
    
    SELECT @c = Cast(GraphData AS VARCHAR(8000)
       FROM tblBottleGraphData
       WHERE BottleID = @piBottle
    
    CREATE TABLE #points (
       pointID INT IDENTITY(1, 1) NOT NULL
    ,  point_value INT
       )
    
    WHILE 0 < Len(@cWork)
       BEGIN
          INSERT INTO #points (point_value)
             SELECT Ascii(@c) + 256 * Ascii(SubString(@c, 2, 1)) -- byte sex?
    
          @c = SubString(@c, 3, 8000)
       END
    
    SELECT point_value
       FROM #points
       ORDER BY pointID
    
    RETURN
    Depending on the byte sex of the values in the image, you may need to change which value gets multiplied by 256.

    -PatP

  3. #3
    Join Date
    Jan 2005
    Location
    Avon Lake, OH
    Posts
    112
    The theoretical max right now is 10,080 data points (84 days, 120 points per day), but 80% of the data are <= 600, with the rest <= 5,040.


    I'll look for a suitable dataset with a mix of <= 4000 and > 4,000 to try your solution.

    Many thanks,


    Barry

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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.

    -PatP

  5. #5
    Join Date
    Jan 2005
    Location
    Avon Lake, OH
    Posts
    112
    Thank you Pat,

    That was very helpful. I now have the following which gets close to the solution:

    DECLARE @BottleID INT
    SET @BottleID = 100


    DECLARE @DataLength INT
    DECLARE @Offset INT


    SET @DataLength = 0

    SELECT @DataLength = DATALENGTH(GraphData)
    FROM dbo.tblBottleGraphData
    WHERE BottleID = @BottleID

    CREATE TABLE #points (
    pointID INT IDENTITY(1, 1) NOT NULL
    , byte1 tinyint
    , byte2 tinyint
    , word smallint
    )


    BEGIN TRAN
    SET @Offset = 1
    WHILE @Offset < @DataLength
    BEGIN
    INSERT INTO #points (byte1, byte2, word)
    SELECT SUBSTRING (GraphData, @Offset, 1), SUBSTRING (GraphData, @Offset + 1, 1),
    SUBSTRING (GraphData, @Offset, 2)
    FROM dbo.tblBottleGraphData
    WHERE BottleID = @BottleID
    SET @Offset = @Offset + 2
    END
    COMMIT


    SELECT *
    FROM #points
    ORDER BY pointID


    DROP TABLE #points

    This gives me results such as this:

    9 0 2304 65
    1 0 256 66
    250 255 -1281 67
    245 255 -2561 68

    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?

    Best regards,

    Barry

  6. #6
    Join Date
    Jan 2005
    Location
    Avon Lake, OH
    Posts
    112

    Smile

    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)

    DECLARE @Byte1 TINYINT
    DECLARE @Byte2 TINYINT
    DECLARE @Var1 VARBINARY(1)
    DECLARE @Var2 VARBINARY(1)

    SET @DataLength = 0
    SELECT @DataLength = DATALENGTH(GraphData)
    FROM dbo.tblBottleGraphData
    WHERE BottleID = @BottleID

    CREATE TABLE #points (
    pointID INT IDENTITY(1, 1) NOT NULL
    , byte1 tinyint
    , byte2 tinyint
    , blob image -- overkill since data is only 2 bytes
    )
    CREATE UNIQUE INDEX #i_points ON #points (pointID ASC)

    BEGIN TRAN
    SET @Offset = 1
    WHILE @Offset < @DataLength
    BEGIN
    SELECT @Byte2 = SUBSTRING (GraphData, @Offset, 1), @Byte1 = SUBSTRING (GraphData, @Offset + 1, 1),
    @Var2 = SUBSTRING (GraphData, @Offset, 1), @Var1 = SUBSTRING (GraphData, @Offset + 1, 1)
    FROM dbo.tblBottleGraphData
    WHERE BottleID = @BottleID

    INSERT INTO #points (byte1, byte2, blob)
    VALUES (@Byte1, @Byte2, @Var1)

    SELECT @ptrval = TEXTPTR(blob)
    FROM #points
    WHERE pointID = @@IDENTITY

    UPDATETEXT #points.blob @ptrval 1 0 @Var2
    SET @Offset = @Offset + 2
    END
    COMMIT

    SELECT PointID, DataPoint = CAST(SUBSTRING(blob, 1, 2) AS SMALLINT)
    FROM #points
    ORDER BY pointID

    DROP TABLE #points

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •