Question

test if feature exists

  • 16 November 2014
  • 9 replies
  • 54 views
M
Is there any way to test a database table to see if a feature exists before loading new records?

 

 

I am setting up a workspace to load data from a csv file into an existing sql server db table.  I want to check that none of the features in the csv exist in the sql server table before loading. I have been trying adding a reader of the destination sql server table and testing against the csv using the tester transformer. I have the tester set so that a unique attribute from the csv should not equal the same field in the sql server table. When this runs it loads records from the csv AND all the records from the sql table. I just want the features from the csv loaded

9 replies

takashi
Rank
Userlevel 2
Badge +17
Hi,

 

 

I would use the SQLExecutor transformer.

 

Assuming that the table has a field called "recordID" for example, the transformer with this SQL statement creates a feature having an attribute named "numMatched" which stores the number of records matched with the condition specified as the "where" clause. i.e. the recordID matches with one of 1, 2, or 3.

 

-----

 

select count(*) as numMatched from your_table_name

 

where recordID in (1,2,3)

 

-----

 

Expose "numMatched" in the transformer parameter setting, you can then check whether the number is 0.

 

 

In the SQL Statement for the transformer, you can also use attribute values of the input Initiator feature as its components.

 

If you will apply the example above, you can create comma-separated ID list (e.g. 1,2,3) based on the CSV features using the Aggregator (or the ListBuilder) and the ListConcatenator, and then send the resulting feature to the SQLExcecutor as the Initiator. The SQL Statement setting will be:

 

-----

 

select count(*) as numMatched from your_table_name

 

where recordID in (@Value(comma_separated_id_list))

 

-----

 

Just be aware that you will have to quote each value with single quotations (e.g. '1','2','3'), if the data type of the "recordID" is not a numeric type. When you need to quote the values, consider using the StringConcatenator or the AttributeCreator before creating the comma-separated ID list.

 

Hope this helps.

 

 

Takashi
M
Thanks for the answer. I am having trouble with the SQLExcecuter, sorry I'm a noob

 

 

FME keeps telling me the sql statement is invalid and giving this error

 

 "Must declare the scalar variable "@Value" 

 

 

I am using the attributecreater to add single quotes then creating a list with listbuilder and then concatenated a string using the listconcatenator. 

 

 

select count(*) as numMatched from dbo.tbPuddle_Project

 

where Local_Wetland_Name in (@Value(sql_concatenated))

 

 

I used an inspection point and determined sql_concatenated(encoded: utf-8)  =   'Rob Puddle','frog pond','Indian Arm' 

 

 

when I hardcode the list into the sql it works fine 
takashi
Rank
Userlevel 2
Badge +17
The error has been detected by the SQL Server, there could be an issue on the syntax of "@Value()". It have to be interpreted to a character string (comma-separated names list) by FME before the querying.

 

Check:

 

- the function name "@Value" is strictly correct in case-sensitive?

 

- there isn't extra whitespace between "@Value" and the following"("?

 

 

Also try inserting "@Value(...)" to the statement by dragging or double-clicking the attribute name shown on the left pane of the editor.

 

M
I am insering the "@Value(....)" by double clicking. Also no whitespaces. I notice in the inspection point the sql_concatenated attribute has a encoded: utf-8 datatype but all my other attributes are text. could this be the problem? I tried adding

 

declare @list nvarchar(max)

 

set @list = convert(nvarchar(max), @Value(sql_concatenated))
takashi
Rank
Userlevel 2
Badge +17
I don't think the encoding is an issue. But I cannot specify the reason for the error anyway.

 

If you construct whole SQL statement as an attribute value and set it to the "SQL Statement" parameter, what happens?

 

 
takashi
Rank
Userlevel 2
Badge +17
like this.

 

M
This worked perectly! Thanks

 

 

One more stupid question when I create my list using listbuilder, I get a sublist for every attribute in my csv which is about 35 sublists! and no attributes. I have searched for a transformer that will take 1 attribute and create 1 list but haven't had any luck.  The fme documentation says to get rid of unneeded lists as soon as possible not sure why they don't make it easy to avoid making more lists than needed.
takashi
Rank
Userlevel 2
Badge +17
Good to hear it worked fine.

 

To avoid creating unnecessary lists, you can use the AttributeRemover or the AttributeKeeper to remove unnecessary attributes (or to keep only necessary attributes) before creating the list.

 

Alternatively, you can also use the Aggregator to create efficiently a comma-separated attribute values list, instead of the ListBuilder+ListConcatenator.

 

Mode: Attributes Only

 

Keep Input Attributes: No

 

Attributes to Concatenate: <select attribute(s) to concatenate>

 

Separator Character: ,
G
Badge +3
try double quotation on the @Value(attribute)

Reply


Community Stats

28,669
Posts
109,360
Replies
36,531
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.