Solved

Updating a SQL Server geometry directly - howto?

lifalin2016
Rank
Userlevel 1
Badge +22

Hi all.

I'm tasked with updating a table that contains 10+ geometry fields.

I'm opening it as a non-geometric table, extracting one (of two) geometries, and reprojecting this to a variable projection, based on other field data and/or coordinate values.

This all works well, but when I try to update the targeted geometry field in the database table, all hell breaks loose in FME.

I've tried to use the formats "Microsoft SQL Server Binary", "OGC Well Known Binary", and even "OGC Well Known Text".

I usually get an error message about mixing image and geometry field types, but now it just times out all the update commands.

I've tried using a writer with "update", and the DatabaseUpdater, but both errs, so I've ended up with a SqlExecutor (with an UPDATE command), which unfortunately times out.

billedeWhat is the proper way to accomplish this task ?

icon

Best answer by ctredinnick 17 June 2022, 09:00

View original
-- Cheers, Lars I.

4 replies

lifalin2016
Rank
Userlevel 1
Badge +22

Here's the target table definition:

billede

-- Cheers, Lars I.
lifalin2016
Rank
Userlevel 1
Badge +22

Here's the target table definition:

billede

Here's a typical snippet from the latest log file:

2022-06-16 14:00:53|   9.3|  0.0|INFORM|MSSQL_SPATIAL reader: Building features into bulk mode features
2022-06-16 14:01:23|   9.5|  0.1|ERROR |Microsoft SQL Server Spatial Reader: Query failed, possibly due to a malformed statement.  Query Text `UPDATE dbo.Geometri SET
    [S34Sys] = 'S',
    [S34] = 0x000000000105260000007000A722C290F2C0C9BA3648623E0141A90D1980C990F2C0903DA4525F3E0141400018EED690F2C024A760EB593E0141BF705FC2D590F2C042CC0B2E593E0141E6A3C200D690F2C079111616593E0141A00FDA2CD590F2C0048EF792583E01415D0D578CE190F2C051A29F97533E0141CCA13F60E290F2C04A26BE1A543E01411DD5A29EE290F2C0DA6BC802543E0141514888CEE390F2C0D28A1BBE543E014135BDA595FE90F2C0CB059DF9493E01419949C065FD90F2C0B6E6493E493E0141043338A4FD90F2C0F2144824493E0141DFCB67D4FC90F2C0AEEC33A1483E0141FEAAC3A71F91F2C078B92EA13A3E01416A1294772091F2C0E5E142243B3E0141D9FB0BB62091F2C03D10410A3B3E014192B9DCE52191F2C0A646A0C73B3E0141C5A8A01B3091F2C021326A10363E014113DE744F3191F2C086DADFCF363E0141831733CA3291F2C02B7AF737363E0141A58C1EFA3291F2C071182957363E01419F705B003491F2C099CC34ED353E01412482F6F63D91F2C00B39001F3C3E0141FC9DB9F03C91F2C0A484F4883C3E01412413A5203D91F2C06F2226A83C3E01410ABD13AA3B91F2C0CBC60C3E3D3E01412DEFB1444591F2C08B98A337433E01417199EB2F4B91F2C04E0851E4463E0141FC55E0DC9191F2C0B247527A2A3E0141B132A2883D91F2C06BE5510DF63D014175FF2E483C91F2C02B303A8DF63D0141F0DC509E1D91F2C0B47F7FE1023E01418EFEB5193E91F2C038ACFE14173E01412E248A4C1E91F2C01E372CDD233E0141738A9A16F390F2C0D5FE28FF083E01416C7B3E287E90F2C01C178D02383E01417000A722C290F2C0C9BA3648623E01410000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001000000020000000001000000FFFFFFFF0000000003
WHERE
    [ID] = 100064379
    AND
    [KildeID] = 797
    
Provider error `(-2147217871) Query timeout expired'
2022-06-16 14:01:23|   9.5|  0.0|ERROR |A fatal error has occurred. Check the logfile above for details

There are no previous log messages.

-- Cheers, Lars I.
ctredinnick
Rank
Userlevel 3
Badge +16

I think your update script is correct. In the past I've always just used a SQLExecutor for this type of operation. You want the Microsoft SQL Server Binary, you've included the 0x at the start of the geometry, it doesn't need quotes, the database isn't reporting a syntax issue.

 

The update should normally take a fraction of a second, so I would look at if there are any triggers on the table which are recalculating something from the geometry, causing it to hit the default 30 second timeout. Try running the update script in SSMS to see if you get a different error. Maybe try extending the timeout to 60 or 120 seconds to see if it completes in that time. Maybe set a coordinate system on the geometry before extracting it, your example is undefined projection (0x00000000).

 

If you wanted to use WKT, you could do the insert using the SQL function .STPolyFromText(), but it's not necessary

 

 

lifalin2016
Rank
Userlevel 1
Badge +22

I think your update script is correct. In the past I've always just used a SQLExecutor for this type of operation. You want the Microsoft SQL Server Binary, you've included the 0x at the start of the geometry, it doesn't need quotes, the database isn't reporting a syntax issue.

 

The update should normally take a fraction of a second, so I would look at if there are any triggers on the table which are recalculating something from the geometry, causing it to hit the default 30 second timeout. Try running the update script in SSMS to see if you get a different error. Maybe try extending the timeout to 60 or 120 seconds to see if it completes in that time. Maybe set a coordinate system on the geometry before extracting it, your example is undefined projection (0x00000000).

 

If you wanted to use WKT, you could do the insert using the SQL function .STPolyFromText(), but it's not necessary

 

 

Hi @ctredinnick​ 

I tried another approach, but still got timeouts. So I started suspecting that my problems was with the server rather than with my code.

So I made a limited copy of the data on another server, and here it worked as a charm.

So you're right, the code seems to work, and the server was the problem all along.

Thanks for the answer.

-- Cheers, Lars I.

Reply


Community Stats

28,729
Posts
109,527
Replies
36,565
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.