Skip to main content
Question

update script doesn't work

R
Forum|alt.badge.img

Hi,

I've got the following script in an SQL Executor:

update tbx.ext_compare_person  
set   gesnam_d = tbx.diakriet_cip(gesnam_d,sourcekey,'Gesnam')
,     gesvor = tbx.diakriet_cip(gesvor,sourcekey,'Vornam')
,     gesnam_d_partner = tbx.diakriet_cip(gesnam_d_partner,sourcekey,'GesnamPartner')
where organisation = '1234'
and   applicationcode = 'ABC'

Parameters are:

When I execute the script via "Run" (test in SQL Statement) it runs perfectly. It updates the correct rows. But when I run the translation complete, it doesn't perform as tested (it updates nothing).

What is the best solution here? Am I doing something wrong?

Thanx in advance, Erwin

13 replies

david_r
Evangelist
  • david_rEvangelist
  • October 30, 2018

From the screenshot it looks like you have some comments inside the SQL statement editor. Can you try removing all the comments and see if that makes a difference?

R
Forum|alt.badge.img
  • reijnen
  • Author
  • October 30, 2018
david_r wrote:

From the screenshot it looks like you have some comments inside the SQL statement editor. Can you try removing all the comments and see if that makes a difference?

Nope, doesn't make a difference.

 

 

david_r
Evangelist
  • david_rEvangelist
  • October 30, 2018

Another thing to try would be to insert a SQLExecutor just before with the following statement and see if anything exits the Result port:

select *
from tbx.ext_compare_person  
where organisation = '1234'
and   applicationcode = 'ABC'

If nothing comes out the Result port, it's an indication that there's simply nothing in the database to update at that point of execution.

R
Forum|alt.badge.img
  • reijnen
  • Author
  • October 30, 2018
david_r wrote:

Another thing to try would be to insert a SQLExecutor just before with the following statement and see if anything exits the Result port:

select *
from tbx.ext_compare_person  
where organisation = '1234'
and   applicationcode = 'ABC'

If nothing comes out the Result port, it's an indication that there's simply nothing in the database to update at that point of execution.

As I said in my Question, the script runs fine if I perform a testrun. But when I start the translation it does nothing.

 

 

david_r
Evangelist
  • david_rEvangelist
  • October 30, 2018
reijnen wrote:
As I said in my Question, the script runs fine if I perform a testrun. But when I start the translation it does nothing.

 

 

I know, I did read your question. But are you 100% certain that the state of your database table is identical when you test the script and during the translation?
david_r
Evangelist
  • david_rEvangelist
  • October 30, 2018
david_r wrote:
I know, I did read your question. But are you 100% certain that the state of your database table is identical when you test the script and during the translation?
For example: is it possible that you're trying to update a table entry that's in an uncommitted transaction when the SQLExecutor is triggered?
ebygomm
Influencer
Forum|alt.badge.img+31
  • ebygommInfluencer
  • Influencer
  • October 30, 2018

What is triggering the sql executor?

R
Forum|alt.badge.img
  • reijnen
  • Author
  • October 30, 2018
ebygomm wrote:

What is triggering the sql executor?

Nothing :-)

 

 

nielsgerrits
VIP
Forum|alt.badge.img+52
reijnen wrote:
Nothing :-)

 

 

I think @egomm is correct. Maybe you just need to trigger the transformer to run it. A Creator Transformer can do this. This is the way FME workspaces are built; a feature hits a Transformer and then it starts to run.
R
Forum|alt.badge.img
  • reijnen
  • Author
  • October 30, 2018
nielsgerrits wrote:
I think @egomm is correct. Maybe you just need to trigger the transformer to run it. A Creator Transformer can do this. This is the way FME workspaces are built; a feature hits a Transformer and then it starts to run.
I added the trigger "SQL Creator" with the following script:

 

 

select sourcekey
,      gesnam_d
,      gesvor
,      gesnam_d_partner
from   tbx.ext_compare_person 
where  organisation = '1234'
and    applicationcode = 'ABC'
The given columns are selected in the Attributes to expose (SQL Creator Parameters).

 

 

If I connect the SQL Creator (result) with the SQL Executor (initiator) it runs. But very very slow. Is there a way to improve the speed of the translation?

 

 

 

nielsgerrits
VIP
Forum|alt.badge.img+52
reijnen wrote:
I added the trigger "SQL Creator" with the following script:

 

 

select sourcekey
,      gesnam_d
,      gesvor
,      gesnam_d_partner
from   tbx.ext_compare_person 
where  organisation = '1234'
and    applicationcode = 'ABC'
The given columns are selected in the Attributes to expose (SQL Creator Parameters).

 

 

If I connect the SQL Creator (result) with the SQL Executor (initiator) it runs. But very very slow. Is there a way to improve the speed of the translation?

 

 

 

How many Features flow from the SQLCreator to the SQLExecutor? See the number on the Connection between the SQLCreator and the SQLExecutor. If there is more then one, your SQLExecutor script is run that number of times. I guess you need it to be run only once? A Sampler Transformer will let only one Feature past when set to 1 First N Features.

 

david_r
Evangelist
  • david_rEvangelist
  • October 30, 2018
reijnen wrote:
I added the trigger "SQL Creator" with the following script:

 

 

select sourcekey
,      gesnam_d
,      gesvor
,      gesnam_d_partner
from   tbx.ext_compare_person 
where  organisation = '1234'
and    applicationcode = 'ABC'
The given columns are selected in the Attributes to expose (SQL Creator Parameters).

 

 

If I connect the SQL Creator (result) with the SQL Executor (initiator) it runs. But very very slow. Is there a way to improve the speed of the translation?

 

 

 

Try using the regular Creator + SQLExecutor, not the SQLCreator + SQLExecutor.
david_r
Evangelist
  • david_rEvangelist
  • October 30, 2018
reijnen wrote:
I added the trigger "SQL Creator" with the following script:

 

 

select sourcekey
,      gesnam_d
,      gesvor
,      gesnam_d_partner
from   tbx.ext_compare_person 
where  organisation = '1234'
and    applicationcode = 'ABC'
The given columns are selected in the Attributes to expose (SQL Creator Parameters).

 

 

If I connect the SQL Creator (result) with the SQL Executor (initiator) it runs. But very very slow. Is there a way to improve the speed of the translation?

 

 

 

Another thing to check regarding speed is to see if the fields "organisation" and "applicationcode" have an index in the database.

Reply


Most helpful members this week

Community Stats

30,847
Posts
116,941
Replies
38,670
Members
Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.