• EN
Solved

Expose SDE archived table fields GDB_FROM_DATE and GDB_TO_DATE

B
Badge

Is there a way with an ArcSDE Reader with an archived clause to expose the attributes GDB_FROM_DATE and GDB_TO_DATE ?

Thanks

icon

Best answer by fmelizard 30 May 2016, 16:24

View original

10 replies

fmelizard
Rank
Userlevel 4
Badge +13

Hi @biboba There are a couple ways that I was able to gain access to the gdb_to_date and gdb_from_date attributes.

First option: Add the ArcSDE Geodb reader and include the archived where clause. Attach an AttributeExposer to the reader and expose gdb_to_date and gdb_from_date (the case of the attributes you expose will depend on the database that underlays your SDE (i.e. lowercase for Postgres)).

Second option: When adding the ArcSDE Geodb reader and picking the feature class to read in the Table List, type in _h after the table name. For example, I have an feature class with archiving enabled called Parcels. I pick the Parcels table in the Table List when adding the reader and then add _h to the table name (so it appears as Parcels_h). That will read in the entire archive table and gdb_to_date and gdb_from_date should appear as attributes in the User schema. In this option, you would not need to include an archive where clause.

B
Badge

Hello HeatherAtSafe,

Thanks for your reply.

Unfortunately, neither of your options work.

By the way, I am using FME 2016.0 with an Oracle Database (SDE 10.3) and my table is only archived and not versionned.

1 . I do not have the mentionned attributes in the attribute exposer (neither upper/lower case). I even tried exposing everything suggested but nothing interesting came out.

2. The historical table (_H) is not suggested. It should be noticed that it is a bit logical because this table does not exist in Oracle as it is my table real table which has the gdb_to_date and gdb_from_date fields. The only other "thing" created when enabling archiving is the ESRI view xxxxx._EVW.

Am I missing something ?

Thanks,

Nicolas

fmelizard
Rank
Userlevel 4
Badge +13

@biboba Hi Nicolas,

When using the AttributeExposer you would need to type in GDB_TO_DATE and GDB_FROM_DATE, those attributes would not automatically be available to you. You would also need to have an Archive WHERE clause in place so that FME knows that you are trying to access the archive info. The screen shot below shows a simple workflow of how I was able to expose GDB_TO_DATE and GDB_FROM_DATE from an archived (non-versioned) feature class in an Oracle SDE table.

I also made a little video demonstrating the process I followed which can be viewed here: (http://screencast.com/t/rrBjVgJrdvq5)

If the above still does not work for you, I would suggest filing a case with Safe Support at support@safe.com. We'd be able to take a closer look at your data and possibly offer more suggestions.

B
Badge

Works like a charm ! Thank you very much @HeatherAtSafe

T

Hello HeatherAtSafe,

Thanks for your reply.

Unfortunately, neither of your options work.

By the way, I am using FME 2016.0 with an Oracle Database (SDE 10.3) and my table is only archived and not versionned.

1 . I do not have the mentionned attributes in the attribute exposer (neither upper/lower case). I even tried exposing everything suggested but nothing interesting came out.

2. The historical table (_H) is not suggested. It should be noticed that it is a bit logical because this table does not exist in Oracle as it is my table real table which has the gdb_to_date and gdb_from_date fields. The only other "thing" created when enabling archiving is the ESRI view xxxxx._EVW.

Am I missing something ?

Thanks,

Nicolas

I got this to work with a nonversioned archived ArcSDE Geodatabase feature class. it took me a while to get it going though.

 

 

Make sure that your Archive WHERE Clause is surrounded by double quotes, and you are using the standard FME Datetime Format. There some useful information and examples here:

 

 

https://knowledge.safe.com/questions/20911/reading-unversioned-sde-table-with-archiving-enabl.html

 

 

https://docs.safe.com/fme/html/FME_Desktop_Documentation/FME_ReadersWriters/geodatabase/reader_directives_ent_geodb_feat_classes.htm

 

 

https://docs.safe.com/fme/html/FME_Desktop_Documentation/FME_Workbench/!Transformer_Parameters/Date-Time-Functions.htm

 

 

https://docs.safe.com/fme/html/FME_Desktop_Documentation/FME_Workbench/!Transformer_Parameters/standard_fme_date_time_format.htm

 

 

Note I am using FME 2015.1.1.0, ArcGIS Desktop installed as version 10.3.1. ArcSDE 10.1.

 

T

Hello HeatherAtSafe,

Thanks for your reply.

Unfortunately, neither of your options work.

By the way, I am using FME 2016.0 with an Oracle Database (SDE 10.3) and my table is only archived and not versionned.

1 . I do not have the mentionned attributes in the attribute exposer (neither upper/lower case). I even tried exposing everything suggested but nothing interesting came out.

2. The historical table (_H) is not suggested. It should be noticed that it is a bit logical because this table does not exist in Oracle as it is my table real table which has the gdb_to_date and gdb_from_date fields. The only other "thing" created when enabling archiving is the ESRI view xxxxx._EVW.

Am I missing something ?

Thanks,

Nicolas

If you still can't get this to work, then an alternative would be to use an Oracle aspatial reader in FME to read in the same dataset, then use a where clause to remove all the old historic data from the oracle data (eg keep all rows where "GDB_TO_DATE > 99991230000000"), and then join the archive fields to your spatial data (which is coming from your FME ArcSDE Geodatabase Reader) with the FeatureMerger transformer using the OBJECTID field. A bit messy, but it should work.

 

T

Hi @biboba There are a couple ways that I was able to gain access to the gdb_to_date and gdb_from_date attributes.

First option: Add the ArcSDE Geodb reader and include the archived where clause. Attach an AttributeExposer to the reader and expose gdb_to_date and gdb_from_date (the case of the attributes you expose will depend on the database that underlays your SDE (i.e. lowercase for Postgres)).

Second option: When adding the ArcSDE Geodb reader and picking the feature class to read in the Table List, type in _h after the table name. For example, I have an feature class with archiving enabled called Parcels. I pick the Parcels table in the Table List when adding the reader and then add _h to the table name (so it appears as Parcels_h). That will read in the entire archive table and gdb_to_date and gdb_from_date should appear as attributes in the User schema. In this option, you would not need to include an archive where clause.

Note: take care that you DON'T use a AttributeRemover, AttributeManager, AttributeKeeper transformer in your workflow BEFORE you use the AttributeExposer to expose your GDB_TO_DATE or GDB_FROM_DATE, otherwise these two fields will not be properly exposed. It is best to use the AttributeExposer transformer as the very first transformer in your workflow.

 

B
Badge

This solution is not working anymore in FME 2019.2. Archive clause works but GDB_FROM_DATE and GDB_TO_DATE attributes are not exposed.

Is it a bug ? Has anything changed ?

Thanks !

A
Rank
Badge +10

This solution is not working anymore in FME 2019.2. Archive clause works but GDB_FROM_DATE and GDB_TO_DATE attributes are not exposed.

Is it a bug ? Has anything changed ?

Thanks !

Hi @biboba,

Thanks for letting us know about this!

We've filed an issue for our development team to resolve this.

I'll be sure to update you here when that occurs.

- Andrea

A
Rank
Badge +10

Hi @biboba,

As of FME 2018 and newer, there was a change for reading attributes on the Reader Feature Type.

You will now need to set "Attribute to Read" to "All Attributes" under the Feature Type > User Attributes.

After setting this parameter on the Reader Feature Type, you will be able to use the AttributeExposesr to expose GDB_TO_DATE or GDB_FROM_DATE and see the values as expected.

Hope this helps!

- Andrea

Reply


Community Stats

28,867
Posts
109,967
Replies
36,669
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.