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.
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.
-
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. -
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. - Connection extending org.jetel.connection.jdbc.specific.conn.DefaultConnection. in your case:
Please sign in to leave a comment.
Comments 2