Question

Get Schema into attribute from SQLExecutor

  • 24 August 2018
  • 6 replies
  • 15 views
mferwerda1111
Badge +1

Is there a way to get a list of exposed attributes/schema from a SQLExecutor into an attribute value or as a list? I know I can write out a record with a featurewriter and sue a schema reader to get at it, but that is a convoluted way to go. I guessing ti can be done with python, but my python skills are limited

6 replies

ebygomm
Rank
Userlevel 1
Badge +21

The python to get a comma separated list of attribute names is fairly straightforward, just send one of the features to a pythoncaller with the code

def processFeature(feature):
    attrNames = ','.join(feature.getAllAttributeNames())
    feature.setAttribute('attrNames', attrNames)
stalknecht
Rank
Userlevel 1
Badge +18

The SchemaSetter will create a list of all attributes.

takashi
Rank
Userlevel 2
Badge +17

Why not use the FeatureReader to read both Schema feature and Data features, rather than using the SQLExecutor?

mferwerda1111
Badge +1

Thanks, I'll try these out, I am using SQL just because it allows me to combine multiple tables together and also use subqueries to deal with a complex data source.

takashi
Rank
Userlevel 2
Badge +17

Thanks, I'll try these out, I am using SQL just because it allows me to combine multiple tables together and also use subqueries to deal with a complex data source.  

Just an idea. If the database user has the privilege to create a view in the database, possibly you could create a view from the SELECT statement with the SQLExecutor and read schema feature and data features from the view with the FeatureReader. Finally you could drop the view with another SQLExecutor.

 

This may not work as expected depending on database format, but at least worked fine with a PostgreSQL database in my test.

 

0684Q00000ArM6lQAF.png

SQL Statement parameter in the first SQLExecutor (Example)

 

FME_SQL_DELIMITER ;

-drop view $(TEMP_VIEW);

create view $(TEMP_VIEW) as
select a.*, b.value
from table1 as a inner join table2 as b
on b.id = a.id; 
mferwerda1111
Badge +1

Thanks all, I used the python solution, worked great, the days of typical user having any privs on the database to create stuff are long gone. In case anyone is interested, I am doing this to create a source to target column mapping, I get the column names into the flow, then run therm through an AttributeValueMapper to crosswalk them to the source table.column_name, and query the Oracle All_tab_columns table to get the column comments, so I get a source table name.column_name, target column_name and an attribute definition as a result in an xls, along with the real output in a personal geodatabase

Reply


Community Stats

28,688
Posts
109,427
Replies
36,543
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.