Solved

FME 2022 prepare statement in SQL Executor.

  • 4 October 2022
  • 7 replies
  • 33 views
arthur_bazin
Rank
Badge +1

This is not a real question, it's more an assesment.

 

In FME 2022, it seams that every query run with SQL Executor/Creator is run as a prepared statement (see here for more information on PostgreSQL https://www.postgresql.org/docs/current/sql-prepare.html).

 

The problem is that only certain query can be prepared, not every one...

Some of my queries can not work anymore like every "CREATE" queries that can not be prepared into PostgreSQL.

 

Is there a way to disable this behaviour ?

 

For me it's a critical problem because some times we need the queries to be run as we write them, not like the database want to redefine them.

icon

Best answer by arthur_bazin 6 October 2022, 09:37

View original

7 replies

david_r
Rank
Userlevel 4

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?

steveatsafe
Rank
Badge +11

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.

SteveatSafe
arthur_bazin
Rank
Badge +1

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.

david_r
Rank
Userlevel 4

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!

david_r
Rank
Userlevel 4

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.

Thanks for the great news, Steve!

vlroyrenn
Rank
Userlevel 1
Badge +9

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> [12612] 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.

arthur_bazin
Rank
Badge +1

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> [12612] 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.

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.

Reply


Community Stats

28,670
Posts
109,366
Replies
36,531
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.