Question

How to log output from an SQL excutor

  • 12 April 2017
  • 4 replies
  • 3 views
G
Badge

Hi

I have a SQL executor which fires some sql queries to an oracle DB.

They are very simple update statements where the where constraints come from attributes of the feature.

The strange thing is that when I run a set of ~ 12000 features, I got ~ 10 where the sql executer queries seem to have not been executed. I.e. I expect to see updated records in the DB for all features in the set, but ~10 fail.

Is there a way to log the sql executer transformer so it outputs every single sql that it fires to the DB?

I want to see what the exact sql query is for a feature that fail.

I know it would be a huge log, but I don't see a reason why 12000 will update OK and 10 won't.

4 replies

david_r
Rank
Userlevel 4

The latest versions of the SQLExecutor has a <Rejected> port that will output any statements that fail.

If you're using FME 2016.0 or newer but the SQLExecutor doesn't have the <Rejected> port, you can right-click on the transformer to upgrade.

Concerning UPDATE statements you should carefully check your where-clause to make sure that it matches at least one record. You can do this by rewriting the UPDATE as a SELECT statement with the identical where-clause.

G
Badge

We run 2015.1.0.3. Any chance there?

david_r
Rank
Userlevel 4

You'll need to run FME 2016.0 or newer for the <Rejected> port on the SQLExecutor.

To isolate the problem in earlier versions of FME, look into rewriting your UPDATE statements as SELECTs and check which one's not returning at least one record, that would indicate that you have a where-clause in one of your UPDATE statements that doesn't match any records. A typical example would be case-sensitivity or leading/trailing spaces in text comparisons.

G
Badge

Did a test with select and it returned the expected number of records. Which is even more puzzling why some records don't get updated.

This lead to maybe something to do with the actual update clause where I want to set some table columns to some values (the current date in this format '12-Apr-17' [with quotes]). This date is the same for all 12000 features.

Reply


Community Stats

28,731
Posts
109,530
Replies
36,567
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.