+5Hi,
is there a way to find the last OBJECTID in an ESRI FileGeoDatabase and return it?
I can do this using SQLCreator > VariableSetter > VariableRetriever > Counter transformers for an Oracle Spatial Database but a FileGeoDatabase is not listed in the Reader Gallery within SQLCreator transformer.
My knowledge of FME is pretty basic and would appreciate a 'how to' (if it's possible).
Thank you
+60What I do is use a FeatureReader (Esri Geodatabase (File Geodb)) and put
OBJECTID = (select MAX(OBJECTID) from <featureclassname>)in the whereclause. This returns the feature with the highest objectid. You can initiate a FeatureReader with a Creator transformer.
This works in a classic reader as well. You can find the WhereClause under the Readers Parameters in the Navigator, on the left side of your screen.
+5What I do is use a FeatureReader (Esri Geodatabase (File Geodb)) and put
OBJECTID = (select MAX(OBJECTID) from <featureclassname>)in the whereclause. This returns the feature with the highest objectid. You can initiate a FeatureReader with a Creator transformer.
This works in a classic reader as well. You can find the WhereClause under the Readers Parameters in the Navigator, on the left side of your screen.
Thanks @nielsgerrits
I don't have Esri Geodatabase available as an option, only File Geodb Open API. Not sure if that causes a problem as the WHERE Clause in the window then greys out. I tried entering clause first then selecting the Format and Dataset but it reverted Where to being greyed out again.
+47Thanks @nielsgerrits
I don't have Esri Geodatabase available as an option, only File Geodb Open API. Not sure if that causes a problem as the WHERE Clause in the window then greys out. I tried entering clause first then selecting the Format and Dataset but it reverted Where to being greyed out again.
The where clause should become available to select once you've chosen a feature type to read
+5Thanks @nielsgerrits
I don't have Esri Geodatabase available as an option, only File Geodb Open API. Not sure if that causes a problem as the WHERE Clause in the window then greys out. I tried entering clause first then selecting the Format and Dataset but it reverted Where to being greyed out again.
No, I'm afraid not. Unless I'm missing something?
+47Thanks @nielsgerrits
I don't have Esri Geodatabase available as an option, only File Geodb Open API. Not sure if that causes a problem as the WHERE Clause in the window then greys out. I tried entering clause first then selecting the Format and Dataset but it reverted Where to being greyed out again.
Can you specify a single feature type to read. Currently it's set to read all feature types
+5Thanks @nielsgerrits
I don't have Esri Geodatabase available as an option, only File Geodb Open API. Not sure if that causes a problem as the WHERE Clause in the window then greys out. I tried entering clause first then selecting the Format and Dataset but it reverted Where to being greyed out again.
Apologies, I had uploaded my 1st image again by mistake. Screengrab should have "General_Housing_Stock" populated.
+47Thanks @nielsgerrits
I don't have Esri Geodatabase available as an option, only File Geodb Open API. Not sure if that causes a problem as the WHERE Clause in the window then greys out. I tried entering clause first then selecting the Format and Dataset but it reverted Where to being greyed out again.
Ahhh that's too bad, I've just looked at this link: https://community.safe.com/s/idea/0874Q000000TksnQAC/detail
It seems like if you add in a normal FILEGDB reader (not feature reader) you can specify a where clause on each Feature Type. Not at computer computer I can't check
+5Thanks @nielsgerrits
I don't have Esri Geodatabase available as an option, only File Geodb Open API. Not sure if that causes a problem as the WHERE Clause in the window then greys out. I tried entering clause first then selecting the Format and Dataset but it reverted Where to being greyed out again.
@nielsgerrits is quite correct in his post, if you hover over the WHERE window it specifies it's not available. Well spotted @virtualcitymatt
So, I've done my bit and voted the suggestion up.
Is adding File Geodb Open API as a reader an option for me here to return the max OBJECTID then?, WHERE Clause will let me populate. Don't know if syntax is OK though!
+60Thanks @nielsgerrits
I don't have Esri Geodatabase available as an option, only File Geodb Open API. Not sure if that causes a problem as the WHERE Clause in the window then greys out. I tried entering clause first then selecting the Format and Dataset but it reverted Where to being greyed out again.
If you change the where clause to
OBJECTID = (select MAX(OBJECTID) from General_Housing_Stock)it works. Just checked.
+5Thanks @nielsgerrits
I don't have Esri Geodatabase available as an option, only File Geodb Open API. Not sure if that causes a problem as the WHERE Clause in the window then greys out. I tried entering clause first then selecting the Format and Dataset but it reverted Where to being greyed out again.
Thanks @nielsgerrits
File Geodb Open API Reader set with syntax kindly provided
How do I go about passing the max OBJECTID?
I thought it might be a case of adding a VariableSetter as that works when it's writing to Oracle. However as you can see it's failing along the way (this of course may not be the correct approach!)
+47Thanks @nielsgerrits
I don't have Esri Geodatabase available as an option, only File Geodb Open API. Not sure if that causes a problem as the WHERE Clause in the window then greys out. I tried entering clause first then selecting the Format and Dataset but it reverted Where to being greyed out again.
You need to make sure that the FILGDB reader is read first. You can do this by reordering the Readers in the Navigator to make sure that the FILEGDB reader is on top of the Oracle reader
+5Thanks @nielsgerrits
I don't have Esri Geodatabase available as an option, only File Geodb Open API. Not sure if that causes a problem as the WHERE Clause in the window then greys out. I tried entering clause first then selecting the Format and Dataset but it reverted Where to being greyed out again.
Thanks @virtualcitymatt OK I have reordered and have had a degree of success, looks like the max OBJECTID is passing OK.
Appears something isn't correct with the VariableRetriever?
+47Thanks @nielsgerrits
I don't have Esri Geodatabase available as an option, only File Geodb Open API. Not sure if that causes a problem as the WHERE Clause in the window then greys out. I tried entering clause first then selecting the Format and Dataset but it reverted Where to being greyed out again.
What is the rejection message in the Counter. Note that when using VariableSetter/Receivers with feature caching on it can have unexpected side-effects. Make sure to run the workspace fully each time you want to do a test
+5Thanks @nielsgerrits
I don't have Esri Geodatabase available as an option, only File Geodb Open API. Not sure if that causes a problem as the WHERE Clause in the window then greys out. I tried entering clause first then selecting the Format and Dataset but it reverted Where to being greyed out again.
Yes, been re-running entire workspace each time. Errors not very helpful.
Translation log - 4 Errors
The below feature caused the translation to be terminated
Counter_<Rejected> (TeeFactory): Counter_<Rejected>: Termination Message: 'Counter output a <Rejected> feature. To continue translation when features are rejected, change 'Workspace Parameters' > Translation > 'Rejected Feature Handling' to 'Continue Translation''
Counter (CounterFactory): Counter_<Rejected>: Termination Message: 'Counter output a <Rejected> feature. To continue translation when features are rejected, change 'Workspace Parameters' > Translation > 'Rejected Feature Handling' to 'Continue Translation''
A fatal error has occurred. Check the logfile above for details
+47Thanks @nielsgerrits
I don't have Esri Geodatabase available as an option, only File Geodb Open API. Not sure if that causes a problem as the WHERE Clause in the window then greys out. I tried entering clause first then selecting the Format and Dataset but it reverted Where to being greyed out again.
If you click on the feature coming out of the counter is there an attribute called REJECTION_REASON or something? how have you configured the counter?
+5Thanks @nielsgerrits
I don't have Esri Geodatabase available as an option, only File Geodb Open API. Not sure if that causes a problem as the WHERE Clause in the window then greys out. I tried entering clause first then selecting the Format and Dataset but it reverted Where to being greyed out again.
No errors as such when you expand the Output for VariableRetriever but the Counter has added a field.
+47Thanks @nielsgerrits
I don't have Esri Geodatabase available as an option, only File Geodb Open API. Not sure if that causes a problem as the WHERE Clause in the window then greys out. I tried entering clause first then selecting the Format and Dataset but it reverted Where to being greyed out again.
Yeah, right and if you inspect the feature that gets rejected is there anything inside the fme_rejection_code?
I think you should try and use an ExpressionEvaluator before the counter to evaluate the expression you have there in the counter. I suspect FME is treating that as a string and not actually evaluating your expression.
+5Thanks @nielsgerrits
I don't have Esri Geodatabase available as an option, only File Geodb Open API. Not sure if that causes a problem as the WHERE Clause in the window then greys out. I tried entering clause first then selecting the Format and Dataset but it reverted Where to being greyed out again.
Upon expanding the table it doesn't seem happy.
Added an ExpressionEvaluator
Ran through with Counter disconnected or else it fails.
The ExpressionEvaluator_Output isn't populating OBJECTID - I would expect it to?
+47Thanks @nielsgerrits
I don't have Esri Geodatabase available as an option, only File Geodb Open API. Not sure if that causes a problem as the WHERE Clause in the window then greys out. I tried entering clause first then selecting the Format and Dataset but it reverted Where to being greyed out again.
Right, here it looks like the variable is not getting picked up from the VariableRetriever - This explains why your result is <null>.
You need to make sure that the variable is getting picked up.
If you find the VariableRetriever to be too annoying you can also use a FeatureMerger - use the MAXCOUNT feature as the supplier and set the merge conditions to be 1 = 1. This will merge the single MAXCOUNT feature onto all of the features coming from Oracle.
The VariableSetter/Retriever should also work though but with the feature caching it does make it annoying to work with sometimes.