Question

SQLExecutor not returning results on FME Server

  • 2 November 2021
  • 7 replies
  • 38 views
nic_ran
Rank
Badge +11

I have a SQLExecutor that runs fine on Desktop but does not output any results on FME Server. The Server log file indicates that the SQLExecutor created results but that they are not being output to the downstream workspace. Any idea why Server might be behaving like this?

 

Here's some log output from Desktop:

2021-11-02 12:06:53| 492.3|  0.2|STATS |SQLExecutor_4_RESULT Feature Counter -1 361 (TeeFactory): Cloned 132885 input feature(s) into 132885 output feature(s)
2021-11-02 12:06:53| 492.3|  0.0|STATS |SQLExecutor_4_INITIATOR Feature Counter -1 2147680433 (TeeFactory): Cloned 1 input feature(s) into 0 output feature(s)
2021-11-02 12:06:53| 492.3|  0.0|STATS |SQLExecutor_4_<REJECTED> Feature Counter -1 2147745969 (TeeFactory): Cloned 0 input feature(s) into 0 output feature(s)

Note that it says "Cloned 132885 input feature(s) into 132885 output feature(s)"

 

And here's the equivalent section from the Server log:

806	2021-11-2 04:10:34 | SQLExecutor_4 RESULT Transformer Output Nuker (TeeFactory): Cloned 132885 input feature(s) into 0 output feature(s)
807	2021-11-2 04:10:34 | SQLExecutor_4 INITIATOR Transformer Output Nuker (TeeFactory): Cloned 1 input feature(s) into 0 output feature(s)
808	2021-11-2 04:10:34 | SQLExecutor_4_<Rejected> (TeeFactory): Cloned 0 input feature(s) into 0 output feature(s)

And here it says "Cloned 132885 input feature(s) into 0 output feature(s)"

 

So the features are being read from SQL, they're just not being passed downstream.

 

Desktop and Server are both using the same workspace and are both running FME 2020.1.

The Server version has worked in the past and only recently stopped working....

7 replies

hkingsbury
Rank
Userlevel 5
Badge +29

My interpretation of that line in a log file is that 132885 features were passed into a transformer (ie the SQLExecutor was triggered 132885 time) and that resulted in 0 of the sql requests returning anything.

 

Are your connection credentials correct and does FME Server have access through the network to the database?

 

You say its worked in the past, has there been any infrastructure changes or password changes recently?

Check out the Abley FME Firesides on Youtube: https://www.youtube.com/@ableyltd
nic_ran
Rank
Badge +11

My interpretation of that line in a log file is that 132885 features were passed into a transformer (ie the SQLExecutor was triggered 132885 time) and that resulted in 0 of the sql requests returning anything.

 

Are your connection credentials correct and does FME Server have access through the network to the database?

 

You say its worked in the past, has there been any infrastructure changes or password changes recently?

Thanks very much for your answer but it's not quite working as you've interpreted it.

What's happening is the SQLExecutor is being passed a single input feature. The resulting query that is executed in the SQLExecutor is returning 132885 features from that single input feature. The SQLExecutor then should output those 132885 features on the "Result" output port but nothing is coming out (on FME Server). The key piece of information here in the log file is the "(TeeFactory): Cloned...". This is FME telling us that it's trying to route the resulting features downstream from the SQLExecutor but for some reason it's deciding not to (0 features cloned)...

The strange thing is the exact same workspace is behaving correctly on FME Desktop.

 

hkingsbury
Rank
Userlevel 5
Badge +29

Thanks very much for your answer but it's not quite working as you've interpreted it.

What's happening is the SQLExecutor is being passed a single input feature. The resulting query that is executed in the SQLExecutor is returning 132885 features from that single input feature. The SQLExecutor then should output those 132885 features on the "Result" output port but nothing is coming out (on FME Server). The key piece of information here in the log file is the "(TeeFactory): Cloned...". This is FME telling us that it's trying to route the resulting features downstream from the SQLExecutor but for some reason it's deciding not to (0 features cloned)...

The strange thing is the exact same workspace is behaving correctly on FME Desktop.

 

Without know how complex your workflow is, can you isolate the sql porition into its own workbench and check that the SQLExecutor by itself is working?

 

I have occasionally come across scenarioswere transformers become 'bugged' and require a fresh workbench to be created to fix the bug (dont copy and paste the SQLExecutor to the new workbench)

Check out the Abley FME Firesides on Youtube: https://www.youtube.com/@ableyltd
nic_ran
Rank
Badge +11

Thanks very much for your answer but it's not quite working as you've interpreted it.

What's happening is the SQLExecutor is being passed a single input feature. The resulting query that is executed in the SQLExecutor is returning 132885 features from that single input feature. The SQLExecutor then should output those 132885 features on the "Result" output port but nothing is coming out (on FME Server). The key piece of information here in the log file is the "(TeeFactory): Cloned...". This is FME telling us that it's trying to route the resulting features downstream from the SQLExecutor but for some reason it's deciding not to (0 features cloned)...

The strange thing is the exact same workspace is behaving correctly on FME Desktop.

 

I think the whole issue might have been just a glitch in FME Server. The workspace seems to be working now that it's been re-uploaded to Server.

 

Thanks for taking the time to help out with this @hkingsbury​ 

virtualcitymatt
Rank
Userlevel 4
Badge +26

I just started seeing the same behaviour - very strange indeed. (FME Server 2020.0)

 

I just have a Creator going into the SQLExcecuter and nothing coming out of any of the 3 ports. I coped the combo into a new workspace and it worked fine.

 

I also created a new SQLExcecuter in the same (not working) worksapce with the same settings and it also didn't give a result.

 

Personally I suspect it's something to do with the Database connection somehow. I'm going to play around to see what makes it start working again.

nic_ran
Rank
Badge +11

I just started seeing the same behaviour - very strange indeed. (FME Server 2020.0)

 

I just have a Creator going into the SQLExcecuter and nothing coming out of any of the 3 ports. I coped the combo into a new workspace and it worked fine.

 

I also created a new SQLExcecuter in the same (not working) worksapce with the same settings and it also didn't give a result.

 

Personally I suspect it's something to do with the Database connection somehow. I'm going to play around to see what makes it start working again.

Good luck tracking this one down! I don't think I'd be able to reproduce it now. It always worked on Desktop for me but somehow the upload to Server caused the issue. A re-upload fixed it.

I'd definitely be interested in hearing if you do manage to figure it out though.

virtualcitymatt
Rank
Userlevel 4
Badge +26

Good luck tracking this one down! I don't think I'd be able to reproduce it now. It always worked on Desktop for me but somehow the upload to Server caused the issue. A re-upload fixed it.

I'd definitely be interested in hearing if you do manage to figure it out though.

Yeah super strange. In the end what seemed to work for me was embedding the connection details in the SQLExecuter. After that switching back to the database connection worked again.

I tried publishing and republishing a bunch of times but it wasn't until I made this change that all of a sudden it started working again​

Reply


Community Stats

28,721
Posts
109,501
Replies
36,560
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.