Question

SQL Executor for creating unique IDs

  • 6 October 2014
  • 4 replies
  • 0 views
A
Badge +1
Hi,

 

 

I am using the SQL Executor transformer followed by Counter transformer to create unique IDs for new features that I am adding to an existing Oracle table.

 

 

The SQL Executor transformer fetches the maximum ID number in the existing table, and then the Counter transformer adds 1 to it.

 

 

However when I run the workbench the new features in the Oracle table have a geometry of NULL and in the FME log it says "Spatial Column 'GEOM' is NULL. Geometry will be updated to NULL."

 

 

I am wondering if it is something to do with the settings in the "combine attributes" or "combine geometry" options in the SQL Transformer. But I have tried a number of combinations of settings and cannot get it to work.  I only have the "Result" section of the SQL Executor transformer linked to the "Counter" if this is also part of the problem...

 

 

Does anyone have any ideas?

4 replies

david_r
Rank
Userlevel 4
Hi,

 

 

I suspect you're on the right track about the "combine" settings. It should work if you set it up like this:

 

 

 

 

Tip: consider using an oracle sequence rather than the SELECT MAX(...) and Counter idiom. It is more robust and possibly also quicker.

 

 

David
A
Badge +1
Hi David, 

 

 

Thanks for your reply.  Did you post a screen grab into your response with the set-up? If so, I can't see it due to browser issues.  Are you able to describe the set-up in words?

 

 

Thanks,
david_r
Rank
Userlevel 4
Hi,

 

 

try passing your features through one or two GeometryValidators.

 

 

David
A
Badge +1
Hi David,

 

 

I tried that but it came up with no errors.  I ended up just using a Sequencer and Trigger in Oracle instead..  Still not sure what was wrong with the FME workbench though. Thanks,

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.