This is very interesting, because according to this old idea, this feature hasn't been implemented yet: https://community.safe.com/s/bridea/a0r4Q00000HbreZQAR/sqlexecutor-support-for-bind-variables

 

@mark2atsafe​ @steveatsafe​  Would you guys happen to know anything about this?

Hi @arthur_bazin​ , Thank you for filing the case. We'll be sure to follow up here when we know more.

@david_r​ , you'll be happy to know we are working on bind variables in Oracle and getting return data from Stored Procedures... (this will spread to other DB formats) but I don't think the above issue is related to that work. We'll be in touch.

Hi all,

I've got the answer from the support team !! 😄

 

Some changes have been made between the 2021.1 and the 2022.1 version in the way these transformers work 🚧

 

When "feature caching" is on AND into these transformers the "Features to read" parameter is set to either Schema and Data Features or Schema Features

Thus, these SQL transformers will attempt to retrieve the Schema of the query by wrapping it with a PREPARE command.

 

🏁 So, to resolve this case you just have to disable the feature caching or the "Schema reading" or both of them🏁

 

I've asked the support to add this information into the documentation of theses transformers as it can be an issue for lots of developpers.

Thanks for sharing these details!

Thanks for the great news, Steve!

I'm not sure if this was also the case with @arthur_bazin​ 's original issue, but I just ran into this problem on FME 2023.0 with a Postgres SQLExecutor trying to run a procedure (using CALL) to create some partitions followed by multiple other statements (using FME_SQL_DELIMITER), and FME considered that all of them had run successfully, even though the Postgres logs (and the final database state) showed otherwise.

 

As you can see from the original SQL:

FME_SQL_DELIMITER ;
 
CALL "$(POSTGRES_INSERTION_SCHEMA)".create_heatmap_partitions(timestamptz '$(DATETIME_LOW_BOUND_ISO)', timestamptz '$(DATETIME_HIGH_BOUND_ISO)');
 
INSERT INTO "$(POSTGRES_INSERTION_SCHEMA)".heatmap_report_data (...) (
    SELECT ...
    FROM @Value(_feature_types{0}.name) AS report_lines
) ON CONFLICT (report_id, period_start_time)
DO NOTHING;
 
DROP TABLE @Value(_feature_types{0}.name)

From the Postgres logs:

2023-07-20 11:18:11.143 EDT <postgres%Heatmaps> <12612] ERROR:  syntax error at or near "CALL" at character 39
2023-07-20 11:18:11.143 EDT <postgres%Heatmaps> Â12612] STATEMENT:  PREPARE schema_from_query_prepared AS CALL "heatmaps".create_heatmap_partitions(timestamptz '2023-07-20 10:17:00-04:00', timestamptz '2023-07-20 11:17:00-04:00')
2023-07-20 11:18:11.171 EDT <postgres%Heatmaps> s12612] ERROR:  syntax error at or near "DROP" at character 39
2023-07-20 11:18:11.171 EDT <postgres%Heatmaps> ;12612] STATEMENT:  PREPARE schema_from_query_prepared AS DROP TABLE fme_fake_pg_temp.context_id_63da3b96_5bfa_4a63_99ad_396c4735ace3_report_data

328	2023-7-20 11:18:11 | Creating reader for format: PostgreSQL
329	2023-7-20 11:18:11 | Trying to find a DYNAMIC plugin for reader named `POSTGRES'
330	2023-7-20 11:18:11 | Performing query against POSTGRES dataset `Heatmaps@PG14'
331	2023-7-20 11:18:11 | Getting schema from query 'CALL "heatmaps".create_heatmap_partitions(timestamptz '2023-07-20 10:17:00-04:00', timestamptz '2023-07-20 11:17:00-04:00')'. If query has no schema, there may be warnings in the log below that can be safely ignored
332	2023-7-20 11:18:11 | Opening POSTGRES reader for dataset 'Heatmaps'
333	2023-7-20 11:18:11 | PostGIS client encoding: 'UTF8'
334	2023-7-20 11:18:11 | PostgreSQL 14.3, compiled by Visual C++ build 1914, 64-bit
 
...
 
341	2023-7-20 11:18:11 | Getting schema from query 'INSERT INTO "heatmaps".heatmap_report_data (...) (
342	2023-7-20 11:18:11 |     SELECT ...
346	2023-7-20 11:18:11 |     FROM fme_fake_pg_temp.context_id_63da3b96_5bfa_4a63_99ad_396c4735ace3_report_data AS report_lines
347	2023-7-20 11:18:11 | ) ON CONFLICT (report_id, period_start_time)
350	2023-7-20 11:18:11 | DO NOTHING'. If query has no schema, there may be warnings in the log below that can be safely ignored
351	2023-7-20 11:18:11 | Getting schema from query 'DROP TABLE fme_fake_pg_temp.context_id_63da3b96_5bfa_4a63_99ad_396c4735ace3_report_data'. If query has no schema, there may be warnings in the log below that can be safely ignored
352	2023-7-20 11:18:11 | Successfully closed POSTGRES database reader
353	2023-7-20 11:18:11 | Successfully closed POSTGRES database reader

And from the FME logs.

 

The SQLExecutor was set to "Features to read: Schema features". The problem went away when I switched it to "Data features", as suggested above.

 

As it turns out, even though none of the partitions had been created and none of the data had been inserted (or temporary tables deleted), FME didn't raise any errors and actually marked the translation as having completed successfully. I'm not 100% sure of whether or not this is a result of the interplay between this issue and FME_SQL_DELIMITOR, but I'm pretty confident that there should have been an error, here.

FME_SQL_DELIMITER ;
 
CALL "$(POSTGRES_INSERTION_SCHEMA)".create_heatmap_partitions(timestamptz '$(DATETIME_LOW_BOUND_ISO)', timestamptz '$(DATETIME_HIGH_BOUND_ISO)');
 
INSERT INTO "$(POSTGRES_INSERTION_SCHEMA)".heatmap_report_data (...) (
    SELECT ...
    FROM @Value(_feature_types{0}.name) AS report_lines
) ON CONFLICT (report_id, period_start_time)
DO NOTHING;
 
DROP TABLE @Value(_feature_types{0}.name)

2023-07-20 11:18:11.143 EDT <postgres%Heatmaps> <12612] ERROR:  syntax error at or near "CALL" at character 39
2023-07-20 11:18:11.143 EDT <postgres%Heatmaps> Â12612] STATEMENT:  PREPARE schema_from_query_prepared AS CALL "heatmaps".create_heatmap_partitions(timestamptz '2023-07-20 10:17:00-04:00', timestamptz '2023-07-20 11:17:00-04:00')
2023-07-20 11:18:11.171 EDT <postgres%Heatmaps> s12612] ERROR:  syntax error at or near "DROP" at character 39
2023-07-20 11:18:11.171 EDT <postgres%Heatmaps> ;12612] STATEMENT:  PREPARE schema_from_query_prepared AS DROP TABLE fme_fake_pg_temp.context_id_63da3b96_5bfa_4a63_99ad_396c4735ace3_report_data

328	2023-7-20 11:18:11 | Creating reader for format: PostgreSQL
329	2023-7-20 11:18:11 | Trying to find a DYNAMIC plugin for reader named `POSTGRES'
330	2023-7-20 11:18:11 | Performing query against POSTGRES dataset `Heatmaps@PG14'
331	2023-7-20 11:18:11 | Getting schema from query 'CALL "heatmaps".create_heatmap_partitions(timestamptz '2023-07-20 10:17:00-04:00', timestamptz '2023-07-20 11:17:00-04:00')'. If query has no schema, there may be warnings in the log below that can be safely ignored
332	2023-7-20 11:18:11 | Opening POSTGRES reader for dataset 'Heatmaps'
333	2023-7-20 11:18:11 | PostGIS client encoding: 'UTF8'
334	2023-7-20 11:18:11 | PostgreSQL 14.3, compiled by Visual C++ build 1914, 64-bit
 
...
 
341	2023-7-20 11:18:11 | Getting schema from query 'INSERT INTO "heatmaps".heatmap_report_data (...) (
342	2023-7-20 11:18:11 |     SELECT ...
346	2023-7-20 11:18:11 |     FROM fme_fake_pg_temp.context_id_63da3b96_5bfa_4a63_99ad_396c4735ace3_report_data AS report_lines
347	2023-7-20 11:18:11 | ) ON CONFLICT (report_id, period_start_time)
350	2023-7-20 11:18:11 | DO NOTHING'. If query has no schema, there may be warnings in the log below that can be safely ignored
351	2023-7-20 11:18:11 | Getting schema from query 'DROP TABLE fme_fake_pg_temp.context_id_63da3b96_5bfa_4a63_99ad_396c4735ace3_report_data'. If query has no schema, there may be warnings in the log below that can be safely ignored
352	2023-7-20 11:18:11 | Successfully closed POSTGRES database reader
353	2023-7-20 11:18:11 | Successfully closed POSTGRES database reader

I need to check to see if I was having an error retrieved by FME or not, but it seems to me that there was no error in FME.

Maybe FME doesn't consider the exception return by the failure of the preparation of the statement.

I will do some tests.