Question

sql server dynamic table name for reader


Hey all,

 

 

Had a browse around on the internet for this but nothing clear popped up so asking the group.

 

 

I have a SQL Server (non spatial) reader and I need to be able to specify the table name in the database to read at runtime.

 

 

I've tried setting the Feature Types to Read parameter  within the SS reader but all I get is that error message stating "some features were read that did not match reader feature type"  - I cannot seem to parameterize the Feature Type Name. I know you can do this but I'm hitting my head against a brick wall on this. I've got potentially hundres of tables to download so I need this to work so I can let it run overnight in batch mode.

 

 

I've got the writer part parameterized nicely but the read keeps defaulting to the table selection I made when I added the reader and throws that error (mentioned above) when I try to parameterize it

 

 

Any ideas what I'm missing? I know it's simple but just can't work it out.

3 replies

Userlevel 4
Hi,

 

 

here are two options:

 

 

1) Publish the parameter "Feature types to read". Create another workspace that uses the "Schema (any format)" reader to retrieve a list of all available tables, then use a WorkspaceRunner to pass this list into the published parameter of the other workspace.

 

 

2) This option avoids the WorkspaceRunner, but requires working knowledge of Python (or TCL, if that's your thing): create a scripted parameter that uses the fmeobjects API to connect to SQL Server and return a list of all available tables. Link this scripted parameter to "Feature types to read".

 

 

In my opinion it is unfortunate that this is so complicated. It would be much better if it was allowed to use wildcards for "Feature types to read".

 

 

David
Darn, it's tricky :) Ok thank you for the quick response.

 

 

I had considered using a SQLExecutor that ran the "Select table_name from information_schema.tables" SQL to get a list of tables and then loop through that to output all the data and use that in a list, but then I'm not sure how the list will work? If it's just a parameter then why won't a simple text box work? I can't differentiate between a list and a single piece of text?

 

 

Might be easier just to use Mapinfo Pro for this - less hassle it seems. Pity: I really needed FME for this, I'm integrating a few functions into it for geometry building/checking but the thing holding me back is the dynamic reader.

 

 

All the schemas are the same - can I merge the features in the Reader and split at the end based on feature_type_name?
Userlevel 4
Hi,

 

 

the parameter value for "Feature types to read" is simply a string of tablenames (with or without schema identifier), separated by spaces. So technically you could construct this string using almost any tool, or even type it in by hand.

 

 

David

Reply