Solved

Dynamic writer and database schemas ?

  • 5 January 2023
  • 5 replies
  • 39 views

Userlevel 1
Badge +22

Hi all.

I'm using a dynamic writer to output a series of features as tables in an SQL Server database, adding a fixed schema name in the "Table Qualifier" parameter.

However, FME completely ignores this setting, and outputs all features to the default "dbo" schema.

I've then tried to add it to the "Table Name" parameter (with "fme_feature_type_name" in "Schema Definition Name"), but then it just errs because the data features "fme_feature_type" now contains my added schema.

Is this really WAD ?

Why doesn't the dynamic writer accept and use the setting in "Table Qualifier" ??

 

icon

Best answer by lifalin2016 18 January 2023, 13:51

View original

5 replies

Badge +4

Hi @Lars I Nielsen​ ,

 

Edit: Elaborated a bit more and added some other info that might be relevant.

 

So first things first, if you're using Schemas from a Schema Feature, these have to arrive at the Writer Feature Type FIRST. Especially with Feature Caching, this sometimes goes wrong. (Easiest way to fix this is by using a Sorter, sort by fme_feature_type_name, alphabetical descending).

 

Dynamic Writers can be a bit tricky, especially when you're using the Table Qualifier option. The easiest way, and this also gives the most flexibility, is to leave the Table Qualifier blank and just prefix the fme_feature_type_name and fme_feature_type attributes with the right Table Qualifier. This is also much easier to test, because you can very easily see what goes into the Writer Feature Type by inspecting the features before they're written.

 

Just make sure that fme_feature_type_name on your Schema Feature equals the fme_feature_type on the corresponding features and then you should be good to go.

 

 

Hope this helps!

Userlevel 1
Badge +22

Hi @Lars I Nielsen​ ,

 

Edit: Elaborated a bit more and added some other info that might be relevant.

 

So first things first, if you're using Schemas from a Schema Feature, these have to arrive at the Writer Feature Type FIRST. Especially with Feature Caching, this sometimes goes wrong. (Easiest way to fix this is by using a Sorter, sort by fme_feature_type_name, alphabetical descending).

 

Dynamic Writers can be a bit tricky, especially when you're using the Table Qualifier option. The easiest way, and this also gives the most flexibility, is to leave the Table Qualifier blank and just prefix the fme_feature_type_name and fme_feature_type attributes with the right Table Qualifier. This is also much easier to test, because you can very easily see what goes into the Writer Feature Type by inspecting the features before they're written.

 

Just make sure that fme_feature_type_name on your Schema Feature equals the fme_feature_type on the corresponding features and then you should be good to go.

 

 

Hope this helps!

Yes I did try to prefix "fme_feature_type_name". Unfortunately that didn't work either.

 

I have done something similar earlier, but this time around it fails.

Badge +4

Yes I did try to prefix "fme_feature_type_name". Unfortunately that didn't work either.

 

I have done something similar earlier, but this time around it fails.

Did you prefix both fme_feature_type_name on your schema feature(s) and prefix the fme_feature_type attribute on the "regular" features?

 

Also, does the database schema you want to write to already exist?

And if you write one of your tables to that schema, (without using the dynamic properties) does that work? If so, we can exclude any database user rights/access limitations.

 

And would you be able to share the log / error you're getting?

Userlevel 1
Badge +22

So, I ran into much the same problem again. And again it erred beyond my comprehension.

BUT, I found a work-around.

 

I used the FeatureMerger to merge the schema feature attributes (attribute{} et.al.) onto every single one of my data features, and remembered to delete the illusive "fme_schema_handling", which make FME skip the (first?) feature.

 

And then I ran into another problem: a single writer apparently cannot have both a "normal" (automatic/manual) feature output and a dynamic feature output at the same time.

So I had to create a clone of my writer, and move my dynamic feature to this. And then the workspace ran as intended.

 

Why are dynamic features so troublesome ? They've been around for a long time, and still there are plenty of issues, it seems.

Userlevel 1
Badge +22

Yes I did try to prefix "fme_feature_type_name". Unfortunately that didn't work either.

 

I have done something similar earlier, but this time around it fails.

As per my recent troubles (see seperate answer), I ended up having the FQN of my output table in both "Table Name" and "Schema Definition Name" in a single attribute. I.e. completely identical. And it still didn't work.

 

And Kailin hinted at in another thread, that "Schema Definition Name" was a data attribute, not a schema attribute, so I'm very confused at the moment. I hope I'm misunderstanding what she said.

 

The general problem is that FME just logs "not matched", but never tells you what it tried to match against.

 

I'm sure there's a bug hiding in there somewhere. Only the simplest examples works, and they never include schemas.

Reply