Solved

Updating data in an ESRI SDE - from an ESRI GDB - using GlobalID

rumle
Badge

I am trying to update an ESRI SDE database with data from an ESRI GDB. The matching key between the two datasets is GlobalID. I use an ArcSDE writer and FME 2019.1.

Settings in the Writer:

When I try to update or delete data in the SDE, I get an error like the following:

No rows matched UPDATE query to table/feature class 'XXX' where upper(GlobalID) = N'{C1BD1C13-4C4F-4C05-B3D8-F051F7B0A33F}'

The GlobalIDs do exist in the SDE database! I have checked this multiple times.

I can update data between two GDBs with the exact same data without problems, so I suspect there is something in the way GlobalIDs is read in GDB vs. SDE databases that do not match. I have tried adding/deleting the brackets {} on the input data using AttributeTrimmer with no luck. The "N" before the GlobalID in the message is not present in the data, and I have no idea why it shows up here.

icon

Best answer by trentatsafe 11 September 2019, 23:12

View original

22 replies

lars_de_vries
Badge +10

When I look at the workspace, it seems that you also try to insert 21 new features in the database. Since these do not yet exist in the SDE, there won't be a matching GlobalId.

rumle
Badge

When I look at the workspace, it seems that you also try to insert 21 new features in the database. Since these do not yet exist in the SDE, there won't be a matching GlobalId.

The "Inserted" features are added to the SDE without problems. FME is clever enough not to try to match these by GlobalID. These features get a new GlobalID from the database in the process.

It is only the "Updated" and "Deleted" features that gives the error. I could have written that more clearly I see.

rumle
Badge

Furthermore:

The ChangeDetector in the same workspace matches the exact same datasets (GDB vs. SDE).

The Change detector has no problems matching the GlobalIDs, as it finds updated objects.

Only the FeatureWriter can't find the match.

 

Looking at the FeatureReaders i can see that they read the SDE GlobalIDs as "globalid" type, and the GDB GlobalIDs as "char" type. This could be part of the problem.

nampreetatsafe
Rank
Userlevel 1
Badge +10

Furthermore:

The ChangeDetector in the same workspace matches the exact same datasets (GDB vs. SDE).

The Change detector has no problems matching the GlobalIDs, as it finds updated objects.

Only the FeatureWriter can't find the match.

 

Looking at the FeatureReaders i can see that they read the SDE GlobalIDs as "globalid" type, and the GDB GlobalIDs as "char" type. This could be part of the problem.

Hi @rumle, I'm wondering why you are using a dynamic writer? Are you able to share your workspace to see if we can spot something obvious?

Also, try inspecting the feature cache of the Updated and Deleted ports on the ChangeDetector and compare it to the GlobalID attribute of the destination you are writing to. You might find some clues there.

dellerbeck
Rank
Badge +6

Out of curiosity, where is the upper(GlobalID) = N coming from? When FME submits to an SDE database it does not cast these as upper case in my experience. I have actually overwritten Esri's GlobalID values with FME's UUID() and FME would still match the lower UPPER combination.

To me it looks like somewhere in your workspace you are updating the GlobalID Column and might have just missed the @upper(), or are trying to make FME match an upper case character and should update it in FME first by using a string case changer.

rumle
Badge

Out of curiosity, where is the upper(GlobalID) = N coming from? When FME submits to an SDE database it does not cast these as upper case in my experience. I have actually overwritten Esri's GlobalID values with FME's UUID() and FME would still match the lower UPPER combination.

To me it looks like somewhere in your workspace you are updating the GlobalID Column and might have just missed the @upper(), or are trying to make FME match an upper case character and should update it in FME first by using a string case changer.

Hi @dellerbeck.

Sorry for the late response. I have been off-grid on fieldwork for the last 3 weeks. I do appreciate your comment.

I have no idea why the log states "upper(GlobalID) = N", but I am pretty sure this has something to do with the error. I have tried to put the GlobalID in upperCase/lowerCase with a StringCaseChanger before the Writer, but with no luck. I was hoping someone knew something about this.

My workspace is very simple. I am not changing the GlobalID in any way. I will try to upload the workspace for anyone who is willing to give it a look.

rumle
Badge

Hi @rumle, I'm wondering why you are using a dynamic writer? Are you able to share your workspace to see if we can spot something obvious?

Also, try inspecting the feature cache of the Updated and Deleted ports on the ChangeDetector and compare it to the GlobalID attribute of the destination you are writing to. You might find some clues there.

Hi @nampreet. Sorry for the late reply. I have been off-grid on fieldwork.

I am using a Dynamic Writer, as i have different geometry types running through the workspace, and going out through the same writer. I have tried to simplify my workspace with just a non-Dynamic Writer but the error persists.

I have inspected the Cache at the GlobalIDs on the Reader and just before the Writer. They are identical, and also identical to what i see in the SDE using ArcGIS Pro. Below is an example copy-pasted from FME Inspector.

  • SDE {411550FB-87DC-4524-917C-5F54B7B83587}
  • FME READER {411550FB-87DC-4524-917C-5F54B7B83587}
  • FME WRITER {411550FB-87DC-4524-917C-5F54B7B83587}

I will upload a cleaned up workspace for you to look into.

rumle
Badge

I have Uploaded a Workspace that errors out as shown. It is even simpler than the original shown, as it updates the SDE with changed data from the same SDE, so there is no external data source involved.:

updatesde.fmw

For some reason i am not permitted to upload the logfile: but the Warnings goes:

No rows matched DELETE query to table/feature class 'DBO.STENBLOKKE_Z20_1' where upper(GlobalID) = N'{B9494BDA-1459-49A4-8E8E-0B90CF272DEE}'

 

No rows matched UPDATE query to table/feature class 'DBO.STENBLOKKE_Z20_1' where upper(GlobalID) = N'{411550FB-87DC-4524-917C-5F54B7B83587}'

If I use OBJECTID as "Match Column" (and not GlobalID) the updates/deletes works fine. That is just not useful in my original setup.

Can anyone find anything obvious wrong? @nampreet, @dellerbeck

Thanks a lot for your time.

mmccart
Rank
Badge +13

@rumle, did you ever get this solved? I am seeing the same thing in 2019.1 trying to update/delete records in SDE based on GlobalIDs.

rumle
Badge

@rumle, did you ever get this solved? I am seeing the same thing in 2019.1 trying to update/delete records in SDE based on GlobalIDs.

@mmccart, no not yet. I have tried to push the questions towards some of the experts in the forum, but with no luck.

Glad to hear I am not the only one have this issue. Your comment has led me to report it as a bug to Safe today. I will update here, when I get some comments from them.

mmccart
Rank
Badge +13

@mmccart, no not yet. I have tried to push the questions towards some of the experts in the forum, but with no luck.

Glad to hear I am not the only one have this issue. Your comment has led me to report it as a bug to Safe today. I will update here, when I get some comments from them.

Great. Thanks @rumle. I tried a myriad of things like stripping brackets out and re-adding them; leveraging a WHERE statement in the writer to get around the issue. No luck. I'm curious to see why this 'Upper(GlobalID)' is coming from as I am seeing it as well.

rumle
Badge

Great. Thanks @rumle. I tried a myriad of things like stripping brackets out and re-adding them; leveraging a WHERE statement in the writer to get around the issue. No luck. I'm curious to see why this 'Upper(GlobalID)' is coming from as I am seeing it as well.

Hi @mmccart.

Looks like we have been through all the same steps. GlobalID in UpperCase, LowerCase, brackets, adding N' to the GlobalID. etc. etc. :-)

 

As you are the first to confirm the issue, I haven't filed a bug report before now. It is not easy for others to recreate the problem when working with local SDE databases, so I am glad you commented.

T
Rank
Badge +6

Update 2: I have filed an issue to add this functionality to FME, and will post here when a fix becomes available. The Issue # is: FMEENGINE-61477.

__________

 

Update 1: To give a quick update. I was able to reproduce this issue. I will be filing an issue to track this and will post here once I do.

After I speak with our Development team, I will post any updates to this post including when a fix has been applied.

 

rumle
Badge

Great. Thanks @rumle. I tried a myriad of things like stripping brackets out and re-adding them; leveraging a WHERE statement in the writer to get around the issue. No luck. I'm curious to see why this 'Upper(GlobalID)' is coming from as I am seeing it as well.

Hi @mmccart.

@trentatsafe from FME Support is on the case. I will let him post updates on the issue, but as you can see from his reply it is most likely a bug.

mmccart
Rank
Badge +13

Update 2: I have filed an issue to add this functionality to FME, and will post here when a fix becomes available. The Issue # is: FMEENGINE-61477.

__________

 

Update 1: To give a quick update. I was able to reproduce this issue. I will be filing an issue to track this and will post here once I do.

After I speak with our Development team, I will post any updates to this post including when a fix has been applied.

 

Thanks @trentatsafe for filing this issue with the Dev Team. I look forward to seeing this resolved. As I am seeing the same issue in FME 2019.1 Build 19608 while attempting to Update/Delete to a 10.7 SDE SQL Server DB. We're hoping to automate this workbench with FME Server soon. Thanks!

-Mark

M

Update 2: I have filed an issue to add this functionality to FME, and will post here when a fix becomes available. The Issue # is: FMEENGINE-61477.

__________

 

Update 1: To give a quick update. I was able to reproduce this issue. I will be filing an issue to track this and will post here once I do.

After I speak with our Development team, I will post any updates to this post including when a fix has been applied.

 

Is there any update on this issue? I have run into the exact same problem and I'm not having any luck with a work around. The problem as far as I can tell is this mysterious N that appears at the beginning of the Global ID value when executing the SQL where statement. We need a solution to this issue which appears to have been reported at least two weeks ago.

H
Badge +2

Update 2: I have filed an issue to add this functionality to FME, and will post here when a fix becomes available. The Issue # is: FMEENGINE-61477.

__________

 

Update 1: To give a quick update. I was able to reproduce this issue. I will be filing an issue to track this and will post here once I do.

After I speak with our Development team, I will post any updates to this post including when a fix has been applied.

 

This issue has been fixed for FME 2020 and is available to download from safe.com/beta

rumle
Badge

This issue has been fixed for FME 2020 and is available to download from safe.com/beta

Thanks for the update @hollyatsafe. I have tested 2020.0 with my original workspace and SDE database. Everything worked.

M
Badge +2

This issue has been fixed for FME 2020 and is available to download from safe.com/beta

Fix is also available in 2019.2

M

I'm using the ArcGIS Data Interoperability Extension. I had the same problem, but I've solved it simply by using the "WHERE Clause" option instead of the "Match Column" option, see the image below:

 

E

I'm using the ArcGIS Data Interoperability Extension. I had the same problem, but I've solved it simply by using the "WHERE Clause" option instead of the "Match Column" option, see the image below:

 

Thanks a lot! Worked perfectly with "GlobalId" = '@Value(GlobalId)'

E
Badge

I'm using the ArcGIS Data Interoperability Extension. I had the same problem, but I've solved it simply by using the "WHERE Clause" option instead of the "Match Column" option, see the image below:

 

Thanks so much. This operation works great and saved my bacon!

Reply


Community Stats

28,731
Posts
109,530
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.