CloverETL + Postgresql

CloverDX Platform
1

Hi, I have a problem with database postgresql
When using db_output_table component if query fails, all the next record fails with this error code:

current transaction is aborted, commands ignored until end of transaction block

I’ve tried to use savepoints in a custom copy of db_output_table but results are the same.

Can anyone help me?

I’ve tried postgres 8.3 and 9.0

Thank you.
Giampiero.

2

Hello Giampiero,
problem is not in DBOutputTable component, but in DefaultConnection object (see http://bug.cloveretl.org/view.php?id=5711).
As a workaround you can write your connection object, that extends org.jetel.connection.jdbc.specific.conn.PostgreConnection. In this class you need to add the methods:


	@Override
	public void commit() throws SQLException {
		connection.commit();
	}
	
	@Override
	public void rollback() throws SQLException {
		connection.rollback();
	}
	
	@Override
	public void rollback(Savepoint savepoint) throws SQLException {
		connection.rollback(savepoint);
	}

To make the graph to work properly, you also need to set atomicSQL=true or commit=1 on the DBOutputTable component to restore the transaction after the invalid record.

3

To make working such connection with CloverETL, you need to create new plug-in, that contains:

  • Connection extending org.jetel.connection.jdbc.specific.conn.DefaultConnection. in your case:
package com.agata.connection;  
import java.sql.SQLException;  
import java.sql.Savepoint;  
import org.jetel.connection.jdbc.DBConnection;  
import org.jetel.connection.jdbc.specific.JdbcSpecific.OperationType;  
import org.jetel.connection.jdbc.specific.conn.PostgreConnection;  
import org.jetel.exception.JetelException;  
public class MyPostgreConnection extends PostgreConnection {  
	public MyPostgreConnection(DBConnection dbConnection,  
			OperationType operationType) throws JetelException {  
		super(dbConnection, operationType);  
	}  
	@Override  
	public void commit() throws SQLException {  
		connection.commit();  
	}  
	  
	@Override  
	public void rollback() throws SQLException {  
		connection.rollback();  
	}  
	  
	@Override  
	public void rollback(Savepoint savepoint) throws SQLException {  
		connection.rollback(savepoint);  
	}  
}
  • JdbcSpecific extending org.jetel.connection.jdbc.specific.impl.AbstractJdbcSpecific, that creates above Connection object:
package com.agata.connection;  
import java.sql.Connection;  
import org.jetel.connection.jdbc.DBConnection;  
import org.jetel.connection.jdbc.specific.impl.PostgreSpecific;  
import org.jetel.exception.JetelException;  
public class MyPostgreJdbcSpecific extends PostgreSpecific {  
	private static final MyPostgreJdbcSpecific INSTANCE = new MyPostgreJdbcSpecific();  
	  
	public static MyPostgreJdbcSpecific getInstance() {  
		return INSTANCE;  
	}  
	/* (non-Javadoc)  
	 * @see org.jetel.connection.jdbc.specific.impl.AbstractJdbcSpecific#createSQLConnection(org.jetel.connection.jdbc.DBConnection, org.jetel.connection.jdbc.specific.JdbcSpecific.OperationType)  
	 */  
	@Override  
	public Connection createSQLConnection(DBConnection connection, OperationType operationType) throws JetelException {  
		return new MyPostgreConnection(connection, operationType);  
	}  
}
  • and plugin.xml, that informs CloverETL about the above object:
<plugin  
	id="com.agata.connection"  
	version="0.0.0.devel"  
	provider-name="Javlin a.s.">  
	<runtime>  
		<library path="connection.jar"/>  
   	</runtime>  
	<requires engine-version="3.0">  
		<import plugin-id="org.jetel.connection"/>  
		<import plugin-id="org.jetel.jdbc"/>  
	</requires>  
	<extension point-id="jdbcSpecific">  
		<parameter id="database" value="MY_POSTGRE"/>  
		<parameter id="name" value="MyPostgreSQL"/>  
		<!--parameter id="majorVersion" value="x"/-->  
		<parameter id="class" value="com.agata.connection.MyPostgreJdbcSpecific"/>  
	</extension>  
	<extension point-id="jdbcDriver">  
		<parameter id="database" value="MY_POSTGRE"/>  
		<parameter id="name" value="MyPostgreSQL"/>  
		<parameter id="dbDriver" value="org.postgresql.Driver"/>  
		<parameter id="driverLibrary" value="../org.jetel.jdbc/lib/postgre/postgresql-8.3-603.jdbc3.jar"/>  
		<parameter id="urlHint" value="jdbc:postgresql://hostname/database"/>  
		<parameter id="jdbcSpecific" value="MY_POSTGRE"/>  
	</extension>  
</plugin>

The java files should be packed to the connection.jar file and placed with the plugin.xml to other CloverETL plug-ins (something like "c:\Program
Files\eclipse\plugins\com.cloveretl.gui_3.0.1\lib\plugins\) in theirs own folder.