CloverETL + Postgresql

gsavina

• January 17, 2011 00:00

• Answered

Follow

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.

• 

  avackova

  • January 18, 2011 11:45

  0

  Comment actions Permalink

  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.
• 

  avackova

  • January 18, 2011 15:52

  0

  Comment actions Permalink

  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.

Please sign in to leave a comment.