force table creation if table is empty

Hi

Since FME is feature-oriented, it needs a feature entering the output feature type to trigger the creation of the table. So there is no very obvious way to work around this.

For SQL-based writers I have been able to work around it using a NoFeaturesTester which I set to write one dummy feature to each output table if no other feature arrives. This triggers the creation / truncation of the target feature types. On the writer, I set the "SQL statement to execute after translation" to something like 

delete from my_table where my_primary_key = 'dummy_feature'

to delete any dummy features before the translation ends.

Unfortunately, it seems the SDF writer does not have the necessary functionality to implement this.

David

Hi Rob,

Generally FME cannot create empty tables, but can delete records if the writer (format) supports database operation. Fortunately the SDF3 writer (Autodesk MapGuide Enterprise SDF Writer) supports database operation. Note: the SDF writer - Autodesk MapGuide (Version 6.5 and older) doesn't support that.

The strategy is: write a single feature and then delete it immediately with the same writer feature type, if the source Oracle table contains no records.

1. Connect the NoFeaturesTester (FME Store transformer) to the Oracle reader feature type. This transformer will output a single feature through the NOINPUT port, only if no features have been input.
2. Connect the Cloner to the NOINPUT port and set 2 to the "Number of Copies" parameter.
3. Add the AttributeCreator and create these attributes. fme_db_operation: If _copynum = 0 Then "INSERT" Else "DELETE", fme_where: "PRIMARYINDEX=1"
4. Send the feature to the SDF3 writer feature type.
5. Set "UPDATE" to the "Writer Mode" of the destination SDF3 writer or writer feature types.
6. To every Oracle feature (those will be output from the INPUT port of the NoFeaturesTester), add fme_db_operation attribute (value = "INSERT").

See also these links.

• Autodesk MapGuide Enterprise SDF Reader/Writer >  Mapping File Directives >  Writer Directives
• Database Writer Mode

Hope this helps.

Takashi

If you have set the Dynamic Properties referring to the schema of the source Oracle database in the SDF3 writer feature type, a bit of contrivance may be necessary.

In this case, since the source dataset is a database, you can retrieve the number of records for each source table with a SQL statement. If you created initiator features containing "fme_feature_type" attribute which stores a source table name for each, the SQLExecutor with this SQL statement creates features, each of them will contain an attribute called "_num_records" storing the number of records in the specified source table.

select count(*) as _num_records from @Value(fme_feature_type) 

In the parameters dialog of the SQLExecutor, expose "_num_records" with the "Attributes to Expose" parameter; set "Keep Initiator Attributes if Conflict" to the "Combine Attributes" parameter, so that "fme_feature_type" of the initiator feature will be propagated to the resulting feature.

Then, send only features of "_num_records = 0" to the Cloner (step 2) in the data flow I described in the previous post. The step 3 - 6 are the same.

The Dynamic writer can identify the source table name (feature type name) with the "fme_feature_type".

@takashi

 

Hi Takashi,

many thanks for your suggestion - I'm nearly there. I don't use dynamic properties so I followed your description in your intial reply.

Basically - it seems to work as the empty table I did the testing on is now present in the SDF file and it does not contain any records. Which is good and the desired result. But - I get the following error message in FME:

Creating FdoFeatureClass 'WT_AW_INFO:W_WW_V_DSP_BASIN_POL'

 

class FdoFilterException exception: String does not represent a valid filter.

 

class FdoFilterException exception: String incorrectly formatted.

 

A fatal error has occurred. Check the logfile above for details

 

... Last line repeated 2 times ...

 

...

 

...

 

A fatal error has occurred. Check the logfile above for details

As mentioned - the SDF is created and has all tables, including the one without any data. But I'm concerned about the error message as I don't understand why it is there. As we run the WB as ScheduledTask daily I would like to avoid any kind of error message. Not sure if my script will be executed anymore if the WB returns a TRANSLATION FAILED.

It seems the message has to do with the AttributeCreator. If I use:

 

fme_db_operation - If _copynum = 0 Then INSERT Else DELETE

 

fme_where - PRIMARYINDEX=1

then the WB is executed without issues but the table in question has two records.

If I use

 

fme_db_operation - If _copynum = 0 Then "INSERT" Else "DELETE"

 

fme_where - "PRIMARYINDEX=1"

then I get the desired result but the error message as well.

Any ideas how to prevent the error message?

Thank you, Rob

Hi Takashi,

many thanks! I made mistakes and your screenshots helped me to set up the WB properly. Everything is working fine now.

Regards, Rob