0

I'm trying to insert a function on database via native query, like this

EntityManagerImpl entityManagerFunctionConta = (EntityManagerImpl) GermantechEntityManager.getEntityManager();
EntityTransaction transactionFunctionConta = entityManagerFunctionConta.getTransaction();
String functionConta = "CREATE OR REPLACE FUNCTION saldo_anterior_conta(dt_inicial date, id_conta bigint, id_empresa bigint) " +
 "RETURNS numeric AS " +
 "$BODY$ " +
 "declare " +
 "saldo_anterior numeric(14,2);" +
 "begin " +
 "select coalesce(sum(valor), 0) into saldo_anterior " +
 "from saldoinicialconta " +
 "where data < dt_inicial and empresa_id = id_empresa and conta_id = id_conta; " +
 "return saldo_anterior;" +
 "end;" +
 "$BODY$ " +
 "LANGUAGE plpgsql VOLATILE COST 100;" +
 "ALTER FUNCTION saldo_anterior_movimentacao(date, bigint, bigint) OWNER TO postgres;";

transactionFunctionConta.begin();
entityManagerFunctionConta.createNativeQuery(functionConta).executeUpdate();
transactionFunctionConta.commit();

but the following exception is thrown

Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.4.1.v20121003-ad44345): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.BatchUpdateException: Entrada em lote 1 <unknown> foi abortada. Chame getNextException para ver a causa.
Error Code: 0
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:324)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeJDK12BatchStatement(DatabaseAccessor.java:882)
    at org.eclipse.persistence.internal.databaseaccess.DynamicSQLBatchWritingMechanism.executeBatchedStatements(DynamicSQLBatchWritingMechanism.java:144)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.writesCompleted(DatabaseAccessor.java:1714)

is there something wrong with the function? can we run a command like this via nativeQuery?

Luiz E.
  • 6,769
  • 10
  • 58
  • 98
  • 1
    Please try disabling batch writing and post the exception that occurs. – Chris May 08 '13 at 12:26
  • 1
    My guess is you cannot issue DDL in a JTA transaction. You might need to unwrap the connection and issue the statement using JDBC on it outside of a transaction – Chris May 08 '13 at 12:29
  • Well, disabling `eclipselink.jdbc.batch-writing` did the trick! What exactly is batch writing? – Luiz E. May 08 '13 at 12:31

0 Answers0