Error using DBExecute in CloverETL 3.4

CloverDX Platform
1

Hi Pro,

I use DBExecute to call a procedure against a MSSQL database which will create a recordset.
It ran into the error: "Component [DBExecute:DBEXECUTE] finished with status ERROR. The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. " even I did the following setting:

  1. disabling autocommit in DB connection, the URL is “jdbc:jtds:sqlserver://:/;autoCommit=false”
  2. setting the property "Transaction set"to “Never commit” in DBExecute component.

Could you please shed some light on me?

Thanks!

2

Hi,

Assuming that this is the issue reported by Corwin Hu from Oracle, please find the reply below:

Is it possible to execute the query from another SQL client?

As far as I can tell, the connection and DBExecute settings are correct, the component should not perform explicit commit. As follows from the stack trace, the exception is thrown from java.sql.Statement.executeUpdate() and the stack trace does not contain any calls to java.sql.Connection.commit().

I guess that the problem is in dw.Report_EnplanementDeplanement(). The following link may be helpful:
https://sqlandsql.wordpress.com/2014/05 … ansaction/

How many queries are executed by the component? There is a chance that commit was performed during the execution of a previous query.

Finally, the query looks like a stored procedure call, why is “Call as stored procedure” set to false?

3

Hi again,

I may have managed to reproduce the exception with a custom script. The script creates a table containing source data, another table with a faulty trigger, and a stored procedure that inserts data into the second table (you may find it below for the sake of completeness). But the exception is thrown if the stored procedure is called with disabled autocommit. When I unchecked the highlighted checkboxes, the editors returned the same error as CloverETL.

Could you try disabling the highlighted checkboxes in SQLWorkbench/J and DBeaver to verify my theory?

SQLWorkbench/J
SQLWorkbenchJ.png
DBeaver.png


-- source data table
CREATE TABLE test (
	test_column1 NCHAR(50)
);
INSERT INTO test VALUES ('some value');

CREATE TABLE test2 (
	test2_column1 NCHAR(50)
);

GO
-- failing trigger
CREATE TRIGGER failing_trigger
   ON  test2
   AFTER INSERT
AS 
BEGIN
	SET NOCOUNT ON;

	BEGIN TRANSACTION TranA
		BEGIN TRY
			DECLARE @cond INT;
			SET @cond = 'A';
		END TRY
		BEGIN CATCH
			PRINT 'a'
		END CATCH;
	COMMIT TRAN TranA 
END

GO

CREATE PROCEDURE test_procedure
	@LastName nvarchar(50) 
AS
BEGIN
	SET NOCOUNT ON;
	
	BEGIN TRY
		TRUNCATE TABLE result;
		DROP TABLE result;
		INSERT INTO test2 VALUES ('a');
	END TRY
	BEGIN CATCH
	END CATCH

	SELECT * INTO result FROM test;
	SELECT * FROM result;
END
4

Hi Milan,

I just noticed there is a post here. As I also mentioned in the email previously, customer replied that:
its possible to run this procedure call using the statement:
{ call dw.Report_EnplanementDeplanement ‘2015-05-26’, ‘2015-05-26’, 1, null, 1, null, 0, 9999, ‘’, null}
and the SQL Workbench/J Build 117 with jTDS JDBC Driver and url:
jdbc:jtds:sqlserver://:/;autoCommit=false
So it means customer can run the procedure on SQL Workbench/J with autoCommit to false. Does it give you some clue? Do you still need customer to try what you mentioned in last post?

Customer also mentioned that:
In fact, if i set up SQLWorkbench/J to use a jdbc connection with the property autoCommit=true, i’ll face the same error than DBExecute one.

And customer also provided some details about the code of that procedure:
I did an inspection in the procedure code and it consists only with DROP and TRUNCATE temporary tables and SELECT INTO clauses.
The blocks with the drops and truncates are into BEGIN CATCH and END CATCH.
There are no COMMIT or ROLLBACK statements.
The last statement is a SELECT against the latest temporary table created as result from many others ones and their respective temporaries tables.

This is what customer want to achieve in their graph:
I just need a result set from this procedure to send them to others clover components and load data on mdex(Endeca product). No other database is involved (in this case).

Please let us know if these information is helpful to you and any advice you have.

Best Regards,
Corwin Hu

5

It does not seem to me that setting “autoCommit=false” in the URL has any effect in SQLWorkbench/J. In my test, it doesn’t have any effect at all, the only thing that helps is *enabling* Autocommit using the checkbox.

In CloverETL, autocommit behavior is controlled by the connection and by the component. In version 3.4, autocommit was enabled by default. Since 3.5, autcommit is disabled by default for MSSQL connections and only enabled if “Transaction Set”=“One statement”. So if it’s really the case that autoCommit=false helps, upgrading to version 3.5 might help.

As you can see in the SQL snippet below, my test_procedure doesn’t contain any other statements except for TRUNCATE TABLE, DROP TABLE, INSERT INTO and SELECT. It’s the trigger on one of the tables that contains transaction management that causes the problem:


   BEGIN TRY
      TRUNCATE TABLE result;
      DROP TABLE result;
      INSERT INTO test2 VALUES ('a');
   END TRY
   BEGIN CATCH
   END CATCH

   SELECT * INTO result FROM test;
   SELECT * FROM result;

Also, SET IMPLICIT_TRANSACTIONS command might relate to the problem.

In any case, the stored procedure should be written in such a way that is works regardless of the autocommit mode.

If none of these hints helps, I will need more information in order to reproduce the issue. Ideally, the DDL for all involved tables, triggers and stored procedures.

6

Hi Milan,

Thanks for your feedback. To double confirm my understanding, do you mean in 3.4, the autocommit is enabled always and can’t be changed, even if we set the "Transaction set"to “Never commit” in DBExecute component?

Best Regards,
Corwin Hu

7

Hi Corwin,

I am sorry about the confusion. I really thought that autocommit was always enabled and could not be changed, but I did not notice that DBExecute disables autocommit, unless “Transaction Set”=“One statement”.

To sum it up:

  • if Transaction set = “One statement”, autocommit is enabled

  • if Transaction set = “One set of statements”, “All statements” or “Never commit”, autocommit is disabled and transactions are controlled explicitly by the component

Just for the sake of completeness, could you suggest trying to execute the stored procedure with “Transaction Set”=“One statement”, please?