In what way can I control the behaviour of SqlExecutor concerning transactions. I'd like to perform several inserts and updates as a single transaction (by putting the "commit"-command at the end). But this commit always results in the warning "there is no transaction in progress".
Database = PostgreSQL
Try something like the following in a SQLExecutor:
FME_SQL_DELIMITER @
begin transaction;
insert into ...;
insert into ...;
delete from ...;
delete from ...;
commit transaction;
@
The important bit is to re-define the FME SQL delimiter to a character that isn't used by your SQL commands. If you set FME_SQL_DELIMITER to a semi-colon, then FME will split up your all commands and send them to your backend database in separate transactions, which is why the database says that there's no active transaction when "commit transaction" comes.
Try something like the following in a SQLExecutor:
FME_SQL_DELIMITER @
begin transaction;
insert into ...;
insert into ...;
delete from ...;
delete from ...;
commit transaction;
@
The important bit is to re-define the FME SQL delimiter to a character that isn't used by your SQL commands. If you set FME_SQL_DELIMITER to a semi-colon, then FME will split up your all commands and send them to your backend database in separate transactions, which is why the database says that there's no active transaction when "commit transaction" comes.
Try something like the following in a SQLExecutor:
FME_SQL_DELIMITER @
begin transaction;
insert into ...;
insert into ...;
delete from ...;
delete from ...;
commit transaction;
@
The important bit is to re-define the FME SQL delimiter to a character that isn't used by your SQL commands. If you set FME_SQL_DELIMITER to a semi-colon, then FME will split up your all commands and send them to your backend database in separate transactions, which is why the database says that there's no active transaction when "commit transaction" comes.
@david_r: Very helpfull! Also the link @TiaAtSafe gave: learned a lot.
Thanks!
Try something like the following in a SQLExecutor:
FME_SQL_DELIMITER @
begin transaction;
insert into ...;
insert into ...;
delete from ...;
delete from ...;
commit transaction;
@
The important bit is to re-define the FME SQL delimiter to a character that isn't used by your SQL commands. If you set FME_SQL_DELIMITER to a semi-colon, then FME will split up your all commands and send them to your backend database in separate transactions, which is why the database says that there's no active transaction when "commit transaction" comes.
sql-statement-in-sqlexecutor.txt
The solution presented looks very logical and understandable.
But it didn't work (yet).
I stil get the warning, in fact, now one warning extra.
A part of the logging:
-------------------------------------------------
PostgreSQL 9.3.18, compiled by Visual C++ build 1600, 64-bit
SQLExecutorLoadDepositions(QueryFactory): 5 input features read
WARNING: there is already a transaction in progress
NOTICE: COPY tmp_results FROM 'D:\aerius\M18\Laden calculatieresultaten\03_calculatie_csv_bestanden\NietVerfijnd\Depositie\N2k\er-gcnsector_3811_all_receptors_20171003_calculation_year-2015_substance-NH3_sector-1800_resulttype-deposition.csv' DELIMITER ';' CSV HEADER Starting @ Fri Mar 23 10:40:55.349000 2018 CET
NOTICE: COPY tmp_results FROM 'D:\aerius\M18\Laden calculatieresultaten\03_calculatie_csv_bestanden\NietVerfijnd\Depositie\N2k\er-gcnsector_3811_all_receptors_20171003_calculation_year-2015_substance-NH3_sector-1800_resulttype-deposition.csv' DELIMITER ';' CSV HEADER Done @ Fri Mar 23 10:40:57.427000 2018 CET
WARNING: there is no transaction in progress
WARNING: there is already a transaction in progress
NOTICE: COPY tmp_results FROM 'D:\aerius\M18\Laden calculatieresultaten\03_calculatie_csv_bestanden\NietVerfijnd\Depositie\N2k\er-gcnsector_3811_all_receptors_20171003_calculation_year-2015_substance-NOX_sector-1800_resulttype-deposition.csv' DELIMITER ';' CSV HEADER Starting @ Fri Mar 23 10:42:10.992000 2018 CET
NOTICE: COPY tmp_results FROM 'D:\aerius\M18\Laden calculatieresultaten\03_calculatie_csv_bestanden\NietVerfijnd\Depositie\N2k\er-gcnsector_3811_all_receptors_20171003_calculation_year-2015_substance-NOX_sector-1800_resulttype-deposition.csv' DELIMITER ';' CSV HEADER Done @ Fri Mar 23 10:42:13.178000 2018 CET
WARNING: there is no transaction in progress
:
:
-------------------------------------------------
Try something like the following in a SQLExecutor:
FME_SQL_DELIMITER @
begin transaction;
insert into ...;
insert into ...;
delete from ...;
delete from ...;
commit transaction;
@
The important bit is to re-define the FME SQL delimiter to a character that isn't used by your SQL commands. If you set FME_SQL_DELIMITER to a semi-colon, then FME will split up your all commands and send them to your backend database in separate transactions, which is why the database says that there's no active transaction when "commit transaction" comes.
Try something like the following in a SQLExecutor:
FME_SQL_DELIMITER @
begin transaction;
insert into ...;
insert into ...;
delete from ...;
delete from ...;
commit transaction;
@
The important bit is to re-define the FME SQL delimiter to a character that isn't used by your SQL commands. If you set FME_SQL_DELIMITER to a semi-colon, then FME will split up your all commands and send them to your backend database in separate transactions, which is why the database says that there's no active transaction when "commit transaction" comes.
Hi, anyone found a response on the ticket C135638 of @halo_rivm ?
Hi, anyone found a response on the ticket C135638 of @halo_rivm ?
Hello @Helmoet de Zwijger , unfortunately this hasn’t been resolved yet. The ticket is FMEENGINE-9273 - autocommits after each new SQL statement. If you have a workspace with multiple statements in a SQLExecutor, you could try breaking them up so it only runs a single statement at a time, but it still may commit after each.. Not totally sure if there's a workaround for this as of now.
Is your question regarding postgres or is this regarding a different database? Best, Kailin
Hi @kailinatsafe thanks for answering. This is indeed for our postgres database. However, we also use Oracle and Snowflake databases. 
The main reason why I was wondering is the use of FME_SQL_DELIMITOR. There is obviously a difference between:
FME_SQL_DELIMITER ;
drop table first_table;
create table first_table as select * from other_table;
insert into first_table (colum1) values (value1);
select * from first_table;and
FME_SQL_DELIMITER @
drop table first_table;
create table first_table as select * from other_table;
insert into first_table (colum1) values (value1);
select * from first_table;
@The question is if the table first_table would be there if one of the following statements fails and if this depends on the character following FME_SQL_DELIMITER .
Hi @kailinatsafe thanks for answering. This is indeed for our postgres database. However, we also use Oracle and Snowflake databases. The main reason why I was wondering is the use of FME_SQL_DELIMITOR. There is obviously a difference between:
FME_SQL_DELIMITER ;
drop table first_table;
create table first_table as select * from other_table;
insert into first_table (colum1) values (value1);
select * from first_table;and
FME_SQL_DELIMITER @
drop table first_table;
create table first_table as select * from other_table;
insert into first_table (colum1) values (value1);
select * from first_table;
@The question is if the table first_table would be there if one of the following statements fails and if this depends on the character following FME_SQL_DELIMITER .
Hello @Helmoet de Zwijger , I think the first statement will run one after another, commiting after each. The second statement in your response I think will run as a single 'block' but each line will run individually. The SQL_DELIMITER was originally added for a SQL statement that are too long to be contained on a single line, for example the following would likely fail without SQL_DELIMITER present:
SELECT * from Table 1
INNER JOIN table 2 on
table1.attr1 = table2.attr1
Would you be able to clarify your question for me? Did you have a more specific question about either of those statements? If you haven't come across this article yet, it does a really good job at explaining SQL_DELIMITER. Best, Kailin.