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
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
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.
What is the proper way to accomplish this task ?
