How to replicate SQL Server geometry datatype

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #8760

    Simon Faas
    Keymaster

    For certain datatypes (like SQL Server geometry types) HVR uses “extended datatype support” instead of native datatype support.

    Here is how to setup replication (including capture and refresh) of such datatypes.

    Note that extra configuration is needed to replicate these columns.

    In this example have a table t1:

    CREATE TABLE [dbo].[t1](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [geom1] [geometry] NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    The table is added the channel. After adding this table to the channel you will see that the column is not present. You will need to add it manually in the Table Properties dialog. It needs to be added with the same name but with datatype clob.

    The following actions are needed for HVR 4;

    { group=SRC table=* DbCapture /LogBased }
    { group=SRC table=t1 ColumnProperties /Name=geom1 /Absent /CaptureExpression="geom1.ToString()" /Context=refresh }
    { group=SRC table=t1 ColumnProperties /Name=geom1 /Absent /CaptureExpression="select t.geom1.ToString() from t1 t where t.id={id}" /Context=!refresh }
    { group=TGT table=* DbIntegrate }
    { group=TGT table=t1 DbIntegrate /DbProc /DbProcDuringRefresh }
    { group=TGT table=t1 ColumnProperties /Name=geom1 /CaptureExpression="geom1.ToString()" /IntegrateExpression="geometry::STGeomFromText({geom1},0)" }

    The actions for HVR 5 are nealy the same;

    { group=SRC table=* Capture }
    { group=SRC table=t1 ColumnProperties /Name=geom1 /Absent /CaptureExpression="geom1.ToString()" /Context=refresh }
    { group=SRC table=t1 ColumnProperties /Name=geom1 /Absent /CaptureExpression="select t.geom1.ToString() from t1 t where t.id={id}" /Context=!refresh }
    { group=TGT table=* Integrate }
    { group=TGT table=t1 Integrate /DbProc }
    { group=TGT table=t1 ColumnProperties /Name=geom1 /CaptureExpression="geom1.ToString()" /IntegrateExpression="geometry::STGeomFromText({geom1},0)" }

    As you see, I have 2 capture expressions on the source; with different context. One will be used during refresh (remember to activate it in the Context tab); the other one is used for replication.

    On the target you need to use integration and refresh with database procedures for table t1

    On the integrate we have an integrate expression and also a capture expression. The latter is needed to make HVR compare work.

    #12269

    Björn Bengtsson
    Participant

    The example shows how to transform a geometry column from a source to a target. How do I setup a bidirectional replication that also replicates from the target to the source? I have tried to create the same actions but the other way around but then HVR shows the following error during “HVR Initialize”:

    F_JR0324: Parameter /CaptureExpression has conflicting values for table ‘t1’ and location ‘rqsrc’. The conflicting actions are ColumnProperties /Name=geom1 /CaptureExpression=geom1.ToString() /IntegrateExpression=geometry::STGeomFromText({geom1},0) in the definition of channel ‘rqm_src_tgt’, group ‘RQMSRC’ and table ‘t1’ and ColumnProperties /Name=geom1 /Absent /CaptureExpression=”select t.geom1.ToString() from t1 t where t.id={id}” /Context=!refresh in the definition of channel ‘rqm_src_tgt’, group ‘RQMSRC’ and table ‘t1’.

    #12271

    Mark
    Keymaster

    Hi Björn,

    Thank you for your contribution. In principle this should work for active/active replication. I wonder whether the error message you are getting is an indication that there is an issue with the validity of the actions e.g. the group(s) that they are active for. Would be happy to discuss further.

    Mark.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.

© 2019 HVR

Live Demo Contact Us