Inserting binary string at an offset to exsting data in a Text type field

  • Good morning,

    I am supporting an application which stores Geometry values in tables for use with Spatialware this was before SQL Server 2008 and the inbuilt Spatial functions - I am using:

    Microsoft SQL Server 2005 - 9.00.5000.00 (X64)

    Dec 10 2010 10:38:40

    Copyright (c) 1988-2005 Microsoft Corporation

    Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

    We have moved between different versions of a program called "Mapinfo" and have found that something has changed between these two versions which has caused the spatial data written back to SQL Server (to a Text column) to change. This has had a knock-on affect on another process, causing it to fail.

    So, my question is - given binary data in a Text type field in SQL server, is it possible to "Fix" a changed (I assume header) in the record; i.e.

    In this order – original shape, modified in mapinfo 9.5, modified in mapinfo 10.5

    0x01070000000100000001030000000100000005000000 B3B0EEFF370F234117045FF6EF990141DF4B2900C81 - WORKS OK

    0x01070000000100000001030000000100000004000000 B3B0EEFF370F234117045FF6EF990141DF4B2900C81 - WORKS OK

    0x0107000000010000000 4000000 B3B0EEFF370F234117045FF6EF990141DF4B2900C81 - FAILS

    I hope the above is clear. The missing section in the 3rd line appears to be "103000000010000000" which is the only change in the binary representation of the data between the two software versions - it is this missing data which is causing other processes to fail.

    Is it possible to insert binary data in an existing text column, into existing binary data (text type) stored in there, to an offset, to make the 3rd line above look like the 2nd line?

    I have looked at the .write and updatetext commands but don't seem to be getting anywhere fast.. Any pointers would be much appreciated.

    thank you

    Duncan.

  • What about STUFF? Not sure it works with the text data type.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Text is a deprecated datatype - are you able to consider a datatype change? Varchar(max) or, even better, Varchar(nnn) if your column width won't exceed 8000.

    If yes, this update should be fairly trivial using STUFF().

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Am trying this...

    -- Use a table variable to cast the trace

    -- header from image to varbinary(max)

    DECLARE @header TABLE (

    header varbinary(max)

    )

    -- insert the trace header into the table

    INSERT INTO @header

    SELECT AD.SW_GEOMETRY

    FROM Abstract_DPB AD

    WHERE AD.SW_MEMBER=40967

    -- update the byte at offset 390 with version 10 (SQLServer 2008)

    -- instead of version 11 (SQLServer 2012)

    UPDATE @header

    SET header = STUFF(header,10,0,CONVERT(varbinary(max),0x103000000010000000))

    -- write the header back to the trace table

    UPDATE Abstract_DPB

    SET SW_GEOMETRY = (SELECT header FROM @header)

    WHERE SW_MEMBER=40967

    but getting this error... "SQL.sql: Error (36,1): Implicit conversion from data type varchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query."

    I've added the convert in the update above..?

  • Phil Parkin (5/6/2014)


    Text is a deprecated datatype - are you able to consider a datatype change? Varchar(max) or, even better, Varchar(nnn) if your column width won't exceed 8000.

    If yes, this update should be fairly trivial using STUFF().

    Thanks Phil, unfortunately the TEXT type is mandatory for the Spatialware software we are using, hence we are stuck on SQL Server 2005 until we get rid of it and move to SQL Server spatial types...

    D.

  • UPDATE Abstract_DPB

    SET SW_GEOMETRY = STUFF(SW_GEOMETRY,20,0,0x103000000010000000)

    WHERE SW_MEMBER=40967

    "SQL.sql: Error (38,1): Argument data type image is invalid for argument 1 of stuff function."

    ah...

  • No error from this:

    -- header from image to varbinary(max)

    DECLARE @header TABLE (

    header varbinary(max)

    )

    INSERT INTO @header

    SELECT AD.SW_GEOMETRY

    FROM Abstract_DPB AD

    WHERE AD.SW_MEMBER=40967

    UPDATE @header

    SET header = CAST(STUFF(header,19,0,103000000010000000) AS varbinary(max))

    -- write the header back to the trace table

    UPDATE Abstract_DPB

    SET SW_GEOMETRY = (SELECT header FROM @header)

    WHERE SW_MEMBER=40967

    just need to work out where the correct data needs to be inserted to.. so working now, thanks for all comments.

    D.

  • Glad you got it solved and thanks for posting back.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply