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.
What is the proper way to accomplish this task ?
Best answer by ctredinnick
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
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
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
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
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
What is the proper way to accomplish this task ?
