Select in multiline SQLExecutor not returning results

You should be able to use two separate SQLExecutors for this, as long as they use the same database connection. If goto_checkpoint() doesn’t return anything you may have to connect the second SQLExecutor to the Initiator port of the first SQLExecutor.

The first statement is execution of a function. If you change the select to execute, it may work (not sure, not tested). The result (the function being executed) should be the same, but FME might not expect a response so might not return that response.

You could also try it using a CTE, and execute the function inside the CTE, which you can then ignore in the select statement. Or add the results into the select statement anyway, it might be helpful to have that, especially when debugging. So something like this:

with session_function as

(

  SELECT sms_1transact.goto_checkpoint('$(end_transaction_name)')

)

SELECT "A"."fid",

       "A"."theme",

       ST_Envelope("A"."geom");

Exact syntax depends a bit on which database you are using...

It’s a good idea and one that I have been investigating, but the commit in the function call seems to only take place after the query is completed. In the query below, last_ckpt is the temp table that stores the session value, and this isnt ‘updated’ until after the WITH block. I have tried messing about with the order, but I am seeing the same behaviour. I see the same with simple subqueries. 
 

with session_function as
(
	SELECT sms_1transact.goto_checkpoint('topo_202304200010')
), 
last_ckpt as 
(
	select last_ckpt from sms_last_checkpoint
)
select (select last_ckpt from last_ckpt) as ckpt, (select * from session_function)

There’s a commit inside that function? wow. That will most certainly mess with anything you do in that one transaction - it’ll brake it up in multiple transactions, and that in turn causes all sorts of unexpected issues if you use it in a CTE. Not recommended (both commiting inside a function, and using a commit inside a CTE!). If this is commercial software, it’s quite bad practice to do something like that. They likely tried to create their own transactional processing, which means it’s now messing up the databases transactional processing. ouch.

So I think that means your only solution is David’s: Use 2 SQLExecutors.

You could try it with a union all. If you wrap the function call in a dummy select, with the same number and naming of columns of your second select, you may get a result - but it is not guaranteed that the order will be correct, so it might still fail. Personally I think it will fail, because of the commit inside the function, but I’m not familiar enough with PostgreSQL’s transactional rules to be able to tell. In Oracle I’d be 99% sure a union all would also fail, meaning you’ll have to use 2 SQLExecutors.

Sadly not. Sessions in FME arent sticky, so once you move from one sqlexecutor to another you lose the session you had. 

What I really need is for FME to add the “SQL to Run Before Read” function to the sqlexecutor, or allow me to set sticky sessions, which I think is a thing you can do for oracle. 

It’s not an explicit commit, just a standad begin/end block.

 

BEGIN
[...]
   EXECUTE 'CREATE OR REPLACE TEMPORARY VIEW sms_last_checkpoint AS SELECT ' || current_ckpt ||'::INTEGER AS last_ckpt';
   RETURN;
END

That’s interesting. I haven’t tried with PostgreSQL (which I guess you’re using), but sessions are indeed persistent between SQLExecutors when using Oracle.

To maybe rewind this a little bit.  Are you sure you don’t get output from the second SELECT?

FME_SQL_DELIMITER ;
SELECT sms_1transact.goto_checkpoint('$(end_transaction_name)');
SELECT "A"."fid", "A"."theme", ST_Envelope("A"."geom")

I no longer have PostGIS environment to test, but in SQL Server test environment, SQLExecutor returns both sets of rows in the returned Features which means it should parse all rows the API returns back to the Transformer and doesn’t seem to limit itself to just the first SELECT.

I have to manually expose the Attributes, and they will not appear automatically as Attribute data when you do this kind of combined multiple SELECT Schemas being run back-to-back, but I do get the expected number of Features that correspond to the combined count of SQL records/rows.

If wanted to do some further SQL shenanigans to refine, it you could put the Function call inside a SELECT that returns no rows like this (by abusing GROUP BY … HAVING.  This should leave only the rows from the 2nd SELECT being returned as features (but equally this can be done in the Workspace with a Tester)
 

FME_SQL_DELIMITER ;
SELECT dummy_val FROM (SELECT 1 AS dummy_val, <Put Function call here>) AS D
GROUP BY dummy_val
HAVING dummy_val = 0;
....