Skip to main content
Solved

WHERE Clause and SELECT Statement for reader plugin; feature type - Parameters tab

E
Forum|alt.badge.img

Hello,

I have our own FME plugin which works fine on FME 2016 and 2017.

On FME 2017 I see "Where Clause" and "Select statement" added to Parameters tab of Feature type dialog (dbl click on Reader). See the pict below:

Question: do you know what changes / additions shall I make to my plugin .fmf file (or any other file(s)) to enable these "Where Clause" and "Select statement" settings for my custom plugin as well?

I checked "oracle_spatial.fmf" but do not see what exactly code causes Oracle Spatial plugin to enable these two settings.

Thanks!

Best answer by trentatsafe

Hello @egorbaykovnn,

 

Here are the steps for the parsing of information, in C++ format/documentation.

 

 

1. Make sure the GUI type used on the DEF_LINE_TEMPLATE and WORKBENCH_DEFLINE_PARMS are "encoded".

 

 

An example using the Oracle Spatial format, which can be found in the "oracle_spatialDefSource.fmi" file is:

 

 

For the DEF_LINE_TEMPLATE section:

 

puts {DEF_LINE_TEMPLATE {FME_GEN_GROUP_NAME} \\ }; \\

 

puts { oracle_geom_column \\"\\" \\ }; \\

 

puts { oracle_sql_encoded \\"\\" \\ }; \\

 

puts { oracle_where_clause_encoded \\"\\" \\ }; \\

 

For the WORKBENCH_DEFLINE_PARMS section:

 

"GUI OPTIONAL WHOLE_LINE TEXT_EDIT_SQL_CFG oracle_sql_encoded MODE,SQL SELECT Statement" "" \\

 

"GUI OPTIONAL WHOLE_LINE TEXT_EDIT_SQL_CFG oracle_where_clause_encoded MODE,WHERE WHERE Clause" "" \\

 

 

2. For the actual parsing of the DEF lines, break by spaces as you stated.

 

This would look like:

 

keyword_def

 

feature type name

 

key

 

value

 

(repeat)

 

attr name

 

attr value

 

(repeat)

 

 

Once the value has been pulled out, you will need to decode it.

 

The documentation for decoding can be found in the following folder located in your FME install location.

 

pluginbuilder/cpp/apidoc/classIFMEString.html

 

 

The function would be one of the following:

 

virtual void decodeFromFMEParsableText ()=0

 

virtual void decodeUtf8FromFMEParsableText ()=0

View original
Did this help you find an answer to your question?

18 replies

trentatsafe
Safer
Forum|alt.badge.img+6
Hi @egorbaykovnn,

 

For efficiency, if you wouldn't mind identifying the places you wish to add support with screenshots of other r/w plugins that do what you want. We could explain in more detail how to add that functionality.
R
  • raw925
  • August 3, 2017
@trentatsafe

See screenshot pict.png attached. Trying to add ability to custom plugin to all SELECT and WHERE options under "table" parameters section for "FeatureType".

R
  • raw925
  • August 3, 2017
@trentatsafe

Please see the attached screen shot (pict.org) that shows the "table" parameters section of the FeautureReader. Here is another screen shot for a POSTGIS FeatureReader options.

trentatsafe
Safer
Forum|alt.badge.img+6
Hi @raw925,

 

Those parameters go on 'DEF' lines, and there are 3 sections:

 

1. Add these to the "DEF_LINE_TEMPLATE" in the metafile.

 

2. Add these to the "WORKBENCH-DEFLINE_PARAMS" section in the metafile.

 

3. Add code to your plugin to parse the DEF lines.

 

 

Once you have those parameters, your plug in would have to make the correct database call to handle them(you mentioned Oracle Spatial).

 

 

As a heads up, it is common for database parameters among others, to require encoding any parameters that may include special characters like quotes.

 

 

I hope that helps!

 

trentatsafe
Safer
Forum|alt.badge.img+6
raw925 wrote:
@trentatsafe

See screenshot pict.png attached. Trying to add ability to custom plugin to all SELECT and WHERE options under "table" parameters section for "FeatureType".

Hi @raw925,

 

Those parameters go on 'DEF' lines, and there are 3 sections:

 

1. Add these to the "DEF_LINE_TEMPLATE" in the metafile.

 

2. Add these to the "WORKBENCH-DEFLINE_PARAMS" section in the metafile.

 

3. Add code to your plugin to parse the DEF lines.

 

 

Once you have those parameters, your plug in would have to make the correct database call to handle them(you mentioned Oracle Spatial).

 

 

As a heads up, it is common for database parameters among others, to require encoding any parameters that may include special characters like quotes.

 

 

I hope that helps!

 

E
Forum|alt.badge.img

Hi @trentatsafe,

thanks a lot. Really great help!

I'm able to define these parameters in metafile and see them on UI.

I also see the parameters and values in resulted mapping file which is generated for translation.

So #1 and #2 done.

But for #3 I'm not sure what the best way to parse the def line?

I see the parameters are present in mapping file in the following format:

"CTGENIE_1_DEF CT_CO select_sql select_statement_text where_clause where_clause_text ACAD_ANGLE number(38,14) ACAD_BLOCK varchar2(255) ACAD_COLOR number(38,0) etc <a lot of data in one line>"

I could parse this line - i.e. find "select_sql" which is param name - if it is found - it means the next word will be the value. If param is not found - user didn't specify any values.

 

Is it the right way how to parse it or may be there is better one?

Thanks!

trentatsafe
Safer
Forum|alt.badge.img+6
egorbaykovnn wrote:

Hi @trentatsafe,

thanks a lot. Really great help!

I'm able to define these parameters in metafile and see them on UI.

I also see the parameters and values in resulted mapping file which is generated for translation.

So #1 and #2 done.

But for #3 I'm not sure what the best way to parse the def line?

I see the parameters are present in mapping file in the following format:

"CTGENIE_1_DEF CT_CO select_sql select_statement_text where_clause where_clause_text ACAD_ANGLE number(38,14) ACAD_BLOCK varchar2(255) ACAD_COLOR number(38,0) etc <a lot of data in one line>"

I could parse this line - i.e. find "select_sql" which is param name - if it is found - it means the next word will be the value. If param is not found - user didn't specify any values.

 

Is it the right way how to parse it or may be there is better one?

Thanks!

Hi @egorbaykovnn

 

I will reply in earnest tomorrow morning when I get into the office. Here is an article that may provide you with the answer you are searching for: https://knowledge.safe.com/articles/798/developing-a-new-format-readerwriter-with-the-fme.html

 

 

I'll verify tomorrow morning the exact method of parsing. Which language are you using? C++, Python or Java?

 

E
Forum|alt.badge.img
trentatsafe wrote:
Hi @egorbaykovnn

 

I will reply in earnest tomorrow morning when I get into the office. Here is an article that may provide you with the answer you are searching for: https://knowledge.safe.com/articles/798/developing-a-new-format-readerwriter-with-the-fme.html

 

 

I'll verify tomorrow morning the exact method of parsing. Which language are you using? C++, Python or Java?

 

 

Thanks @trentatsafe!

 

I'm using C++; but you can provide the info based on any language you'd prefer.

 

Thanks!
trentatsafe
Safer
Forum|alt.badge.img+6
  • trentatsafeSafer
  • Safer
  • Best Answer
  • August 4, 2017

Hello @egorbaykovnn,

 

Here are the steps for the parsing of information, in C++ format/documentation.

 

 

1. Make sure the GUI type used on the DEF_LINE_TEMPLATE and WORKBENCH_DEFLINE_PARMS are "encoded".

 

 

An example using the Oracle Spatial format, which can be found in the "oracle_spatialDefSource.fmi" file is:

 

 

For the DEF_LINE_TEMPLATE section:

 

puts {DEF_LINE_TEMPLATE {FME_GEN_GROUP_NAME} \\ }; \\

 

puts { oracle_geom_column \\"\\" \\ }; \\

 

puts { oracle_sql_encoded \\"\\" \\ }; \\

 

puts { oracle_where_clause_encoded \\"\\" \\ }; \\

 

For the WORKBENCH_DEFLINE_PARMS section:

 

"GUI OPTIONAL WHOLE_LINE TEXT_EDIT_SQL_CFG oracle_sql_encoded MODE,SQL SELECT Statement" "" \\

 

"GUI OPTIONAL WHOLE_LINE TEXT_EDIT_SQL_CFG oracle_where_clause_encoded MODE,WHERE WHERE Clause" "" \\

 

 

2. For the actual parsing of the DEF lines, break by spaces as you stated.

 

This would look like:

 

keyword_def

 

feature type name

 

key

 

value

 

(repeat)

 

attr name

 

attr value

 

(repeat)

 

 

Once the value has been pulled out, you will need to decode it.

 

The documentation for decoding can be found in the following folder located in your FME install location.

 

pluginbuilder/cpp/apidoc/classIFMEString.html

 

 

The function would be one of the following:

 

virtual void decodeFromFMEParsableText ()=0

 

virtual void decodeUtf8FromFMEParsableText ()=0

E
Forum|alt.badge.img
trentatsafe wrote:

Hello @egorbaykovnn,

 

Here are the steps for the parsing of information, in C++ format/documentation.

 

 

1. Make sure the GUI type used on the DEF_LINE_TEMPLATE and WORKBENCH_DEFLINE_PARMS are "encoded".

 

 

An example using the Oracle Spatial format, which can be found in the "oracle_spatialDefSource.fmi" file is:

 

 

For the DEF_LINE_TEMPLATE section:

 

puts {DEF_LINE_TEMPLATE {FME_GEN_GROUP_NAME} \\ }; \\

 

puts { oracle_geom_column \\"\\" \\ }; \\

 

puts { oracle_sql_encoded \\"\\" \\ }; \\

 

puts { oracle_where_clause_encoded \\"\\" \\ }; \\

 

For the WORKBENCH_DEFLINE_PARMS section:

 

"GUI OPTIONAL WHOLE_LINE TEXT_EDIT_SQL_CFG oracle_sql_encoded MODE,SQL SELECT Statement" "" \\

 

"GUI OPTIONAL WHOLE_LINE TEXT_EDIT_SQL_CFG oracle_where_clause_encoded MODE,WHERE WHERE Clause" "" \\

 

 

2. For the actual parsing of the DEF lines, break by spaces as you stated.

 

This would look like:

 

keyword_def

 

feature type name

 

key

 

value

 

(repeat)

 

attr name

 

attr value

 

(repeat)

 

 

Once the value has been pulled out, you will need to decode it.

 

The documentation for decoding can be found in the following folder located in your FME install location.

 

pluginbuilder/cpp/apidoc/classIFMEString.html

 

 

The function would be one of the following:

 

virtual void decodeFromFMEParsableText ()=0

 

virtual void decodeUtf8FromFMEParsableText ()=0

Thanks a lot @trentatsafe!

 

decodeFromFMEParsableText() works just fine!

 

You saved tons of my time; really appreciate!

 

Have a nice weekend...

 

 

E
Forum|alt.badge.img

@trentatsafe,

one more question please;

I've completed Reader - it works as expected now.

The next question is about FeatureReader Transformer - please see the attached pict (featureReader.png).

My problem - although I have "Where Clause" available for Reader (FeatureType) - I still have "WHERE clause" dimmed on FeatureReader.

Do you have any ideas how to make "WHERE clause" available on FeatureReader?

Thanks!featurereader.png

trentatsafe
Safer
Forum|alt.badge.img+6
egorbaykovnn wrote:

@trentatsafe,

one more question please;

I've completed Reader - it works as expected now.

The next question is about FeatureReader Transformer - please see the attached pict (featureReader.png).

My problem - although I have "Where Clause" available for Reader (FeatureType) - I still have "WHERE clause" dimmed on FeatureReader.

Do you have any ideas how to make "WHERE clause" available on FeatureReader?

Thanks!featurereader.png

Hello @egorbaykovnn,

 

I will confirm when I get back into office on Tuesday. I believe this is due to the format you selected. I don't believe the Shapefile format supports the WHERE clause. If you try with another format, you should see the WHERE clause light up(Oracle Spatial for example).

 

 

I did a quick test, and Shapefile WHERE clause is not accessible to me.

 

 

I hope that helps.

 

E
Forum|alt.badge.img
trentatsafe wrote:
Hello @egorbaykovnn,

 

I will confirm when I get back into office on Tuesday. I believe this is due to the format you selected. I don't believe the Shapefile format supports the WHERE clause. If you try with another format, you should see the WHERE clause light up(Oracle Spatial for example).

 

 

I did a quick test, and Shapefile WHERE clause is not accessible to me.

 

 

I hope that helps.

 

@trentatsafe,

 

let me please clarify one think:

 

we have our own FME Plugin which is not Shape file based.

 

The pict I attached is just for the reference - to show how Dimmed Where Clause looks like and where it is located.

 

Our custom FME plugin is Oracle based and Where clause in FeatureReader Transformer is applicable to our plugin logic.

 

I see Oracle Spatial Feature Reader Transformer has that Where clause available.

 

So I assume it shall be some setting which tells FME when it is necessary to dim that Where Clause out on FeatureReader Transformer and when not.

 

Will very appreciate if you let me know which setting manages this logic

 

Thanks!

 

 

trentatsafe
Safer
Forum|alt.badge.img+6

Hello @egorbaykovnn

The FeatureReader GUI will enable the WHERE clause based on the results of the following. If the properties returned by the getProperties call for 'fme_all_features' contains 'fme_where'. As part of that contract, the reader should support fme_where for setConstraints.

 

So in short, I believe setContraints() and getProperties() are what will return the 'fme_where' within the 'fme_all_features'.

 

 

I hope that helps.
E
Forum|alt.badge.img
trentatsafe wrote:

Hello @egorbaykovnn

The FeatureReader GUI will enable the WHERE clause based on the results of the following. If the properties returned by the getProperties call for 'fme_all_features' contains 'fme_where'. As part of that contract, the reader should support fme_where for setConstraints.

 

So in short, I believe setContraints() and getProperties() are what will return the 'fme_where' within the 'fme_all_features'.

 

 

I hope that helps.
It works, @trentatsafe,

 

thanks a lot!
E
Forum|alt.badge.img
egorbaykovnn wrote:
It works, @trentatsafe,

 

thanks a lot!
@trentatsafe

 

need you help again please for this topic.

 

FME_Boolean MyReader::getProperties(const char* propertyCategory, IFMEStringArray& values) {
    values.append("fme_all_features");
    values.append("fme_where");
    return FME_TRUE;
} 
works ok and Where clause is enabled on FeatureReader transformer.

 

But when I started testing that Where clause with some real values I got the following error:

 

"UniversalReader: Unable to emulate search type 'fme_all_features' because fme_where is requested"

 

 

If I remove "values.append("fme_where");" from code - where clause gets disabled on UI

 

 

If I remove "values.append("fme_all_features");" - another error occurs: "Error - reader does not support search type 'fme_all_features' via setConstraints()"

 

 

I believe I shall set both "fme_all_features" and "fme_where" in getProperties.

 

But do not know how to fix that UnversalReader error; so I'm stuck with that and will very appreciate if any ideas how to proceed further.

 

Thank you!

 

trentatsafe
Safer
Forum|alt.badge.img+6
  • trentatsafeSafer
  • Safer
  • August 10, 2017

Hello @egorbaykovnn

 

 

I think your call may be slightly off. The code below is approximately how our readers perform the getproperties call. If you take a look at the api documentation located in the following folder: fme\pluginbuilder\cpp\apidoc\index.html . This will demonstrate how to perform the approximate call. In your case, I believe performing the kFMERead_AllFeatures will achieve the correct results. A short example is listed below. I have edited it to match your definition up above. I hope that helps. Please let me know if it works or not.
FME_Boolean MyReader::getProperties(const char* propertyCategory, IFMEStringArray& values) {


// fme_search_type = fme_all_features
if (strcmp(propertyCategory, kFMERead_AllFeatures) == 0 ||
strcmp(propertyCategory, kFME_ReaderPropAll) == 0)
{
values.append(kFMERead_AllFeatures);
values.append(kFMERead_FeatureType);
values.append(kFMERead_AllFeatures);
values.append(kFMERead_Where);
foundPropertyCategory = FME_TRUE;
}

return foundPropertyCategory;
}

E
Forum|alt.badge.img
trentatsafe wrote:

Hello @egorbaykovnn

 

 

I think your call may be slightly off. The code below is approximately how our readers perform the getproperties call. If you take a look at the api documentation located in the following folder: fme\pluginbuilder\cpp\apidoc\index.html . This will demonstrate how to perform the approximate call. In your case, I believe performing the kFMERead_AllFeatures will achieve the correct results. A short example is listed below. I have edited it to match your definition up above. I hope that helps. Please let me know if it works or not.
FME_Boolean MyReader::getProperties(const char* propertyCategory, IFMEStringArray& values) {


// fme_search_type = fme_all_features
if (strcmp(propertyCategory, kFMERead_AllFeatures) == 0 ||
strcmp(propertyCategory, kFME_ReaderPropAll) == 0)
{
values.append(kFMERead_AllFeatures);
values.append(kFMERead_FeatureType);
values.append(kFMERead_AllFeatures);
values.append(kFMERead_Where);
foundPropertyCategory = FME_TRUE;
}

return foundPropertyCategory;
}

@trentatsafe, it works for me!

 

Thanks a lot!

 

 

Reply


Helpful Members This Week

Community Stats

30,921
Posts
117,180
Replies
38,725
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.