FeatureReader and formats like Microsoft SQL Server - beware of 2 places to enter Where Clause

lenaatsafe wrote:

Hi @tim_wood

thank you for bringing this up. FeatureReader Where Clause parameter does indeed duplicate - and override - the Reader Where Clause parameter. I have filed a bug regarding this issue. The FeatureReader parameter should be removed.

Thanks @LenaAtSafe. I'll make sure my Where Clauses are in the Reader parameters ready for the update. I guess it might be tricky to move the Where Clause from the FeatureReader into the Reader parameter when the user upgrades the transformer after the bug is fixed...?

 

lenaatsafe wrote:

Hi @tim_wood

thank you for bringing this up. FeatureReader Where Clause parameter does indeed duplicate - and override - the Reader Where Clause parameter. I have filed a bug regarding this issue. The FeatureReader parameter should be removed.

@LenaAtSafe can you explain what this means? Is it offering me the option of having different output ports based on different Where Clause values?

 

It appeared when I set the SQL Server Where Clause in my FeatureReader to come from an attribute value rather than being hard coded. Leaving it blank and clicking OK seems to work - I still have the SQL Server table name as an output port. If I click cancel, I don't get the table name output port.

 

The attribute value is used for the Where Clause in the Reader itself rather than the FeatureReader Where Clause. The FeatureReader Where Clause is blank. Is this dialog box something that will not appear when the bug is fixed?

 

tim_wood wrote:

@LenaAtSafe can you explain what this means? Is it offering me the option of having different output ports based on different Where Clause values?

 

It appeared when I set the SQL Server Where Clause in my FeatureReader to come from an attribute value rather than being hard coded. Leaving it blank and clicking OK seems to work - I still have the SQL Server table name as an output port. If I click cancel, I don't get the table name output port.

 

The attribute value is used for the Where Clause in the Reader itself rather than the FeatureReader Where Clause. The FeatureReader Where Clause is blank. Is this dialog box something that will not appear when the bug is fixed?

 

Hi @tim_wood

 

I believe we now fixed this issue in 2019 beta. Let me explain what was happening.

 

Some of the Reader/FeatureReader parameters affect/define the schema. If they are set dynamically (from an attribute value) we don't know their values till run-time and therefore don't know what the schema will be. If you attempt to create an output port for every feature type, FeatureReader will ask you to provide values for all dynamically set parameters to be able to generate the schema.

 

With Where Clause, if you click OK an empty Where Clause (i.e. no Where Clause) will be used to generate the schema. If you click Cancel, you refuse to provide the value for the Where Clause and as a result the schema will not be generated and all features will be output through <Generic> port.

 

The confusing part is that Where Clause can not alter the list of feature types to read and can't even affect their schemas. I.e. this dialog was unnecessary (for the Where Clause) - and we removed it. You will still see similar dialogs for other dynamically set parameters that do indeed affect the schema.

 

 

lenaatsafe wrote:

Hi @tim_wood

 

I believe we now fixed this issue in 2019 beta. Let me explain what was happening.

 

Some of the Reader/FeatureReader parameters affect/define the schema. If they are set dynamically (from an attribute value) we don't know their values till run-time and therefore don't know what the schema will be. If you attempt to create an output port for every feature type, FeatureReader will ask you to provide values for all dynamically set parameters to be able to generate the schema.

 

With Where Clause, if you click OK an empty Where Clause (i.e. no Where Clause) will be used to generate the schema. If you click Cancel, you refuse to provide the value for the Where Clause and as a result the schema will not be generated and all features will be output through <Generic> port.

 

The confusing part is that Where Clause can not alter the list of feature types to read and can't even affect their schemas. I.e. this dialog was unnecessary (for the Where Clause) - and we removed it. You will still see similar dialogs for other dynamically set parameters that do indeed affect the schema.

 

 

Thanks, I understand. I like the idea of FME getting grumpy: "well if you won't provide a Where Clause, I won't generate a schema so there!".

 

 

I've re-engineered my Workspaces with Parameters so they can operate on Dev or Live data depending on the parameter value. This saves having to edit lots of hard-coded values when migrating an updated Workspace from Dev to Live. At the moment I'm using two FeatureWriters for the part that triggered this post, one FeatureWriter points at the Dev data and the other at Live. A Tester controls which one is used. I want to avoid hard-coding the attributes in the FeatureWriter so at some point I'll investigate the funky dynamic schema stuff you can do and see if I can get it down to one FeatureWriter...

 

gio wrote:

@LenaAtSafe

..you ..just noticed that? (after just XX years?)

We do try hard but sometimes we're so close to things we can't seem them ourselves. That's why we appreciate this community so much -- you have so many more eyeballs on it than us and see things we miss. Deeply appreciated.