Question

Access SQL Creator Info in FME Server PostgreSQL Repository?

  • 20 September 2017
  • 3 replies
  • 3 views
R

I wasn't able to find any schema documentation for the FME Server repository, is there any public details I might have missed? 

Essentially just trying to query source and targets for each workspace. Was able to query this information for readers and writers but we often use "SQL Creators" and/or "Joiners" to pull in tabular information. Is there a way to access those from the repository database? To list out the connection name would be great or even grab the SQL?

Here is what we have so far:

SELECT 
  fme_item.name AS "Workspace", 
  fme_item.title AS "Workspace Title", 
  fme_item.username AS "Username", 
  fme_item.lastpublishdate AS "Publish Date", 
  fme_dataset.location AS "Data Location", 
  fme_dataset.format AS "Data Format", 
  fme_feature_type.name AS "Table/Feature Name", 
  fme_repositoryinfo.name AS "Repository", 
  fme_dataset.is_source AS "Is Source"
FROM 
  public.fme_item, 
  public.fme_dataset, 
  public.fme_feature_type, 
  public.fme_repositoryinfo
WHERE 
  fme_item.item_id = fme_dataset.item_id AND
  fme_item.repositoryinfo_id = fme_repositoryinfo.repositoryinfo_id AND
  fme_dataset.dataset_id = fme_feature_type.dataset_id AND
  fme_repositoryinfo.name != 'Dashboards'
ORDER BY
  fme_repositoryinfo.name ASC, 
  fme_item.name ASC;

Any ideas on how to query all the sources and targets would be great. Thanks a lot!

3 replies

stalknecht
Rank
Userlevel 1
Badge +18

I suggest to use a "Directory and File Pathnames" - reader and point to the repository folder. Then you can use a featureReader in combination with a "FME Workspace (FMW)" reader. You can then filter the TransformersParameters featureclass and look for the SQL statement.

david_r
Rank
Userlevel 4

I don't think there's a way to extract data sources from the SQLCreator/SQLExecutor in the fmeserver database, at least that I know of.

If you're using FeatureReaders and FeatureWriters that would also be another challenge.

You could use the FMW reader to analyze workspaces, but of course that will only give you the static definitions and not take run-time values into account. In particular look for the feature types Connections and TransformerParameters (where transformer_parameter_name is DATASET, SQL_STATEMENT, etc).

R

Thanks @david_r and @stalknecht. We built an FME job to extract this info as suggested and it seems to solve the issue. The database was almost a great solution but this is probably the better approach anyway. Thanks again for the info!

Reply


Community Stats

28,706
Posts
109,466
Replies
36,549
Members
Terms & ConditionsCookie 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.