Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » create a function via nativeQuery
create a function via nativeQuery [message #1056852] Wed, 08 May 2013 07:35
Luiz E. is currently offline Luiz E.
Messages: 97
Registered: September 2010
Member
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?
Previous Topic:Incrementing generated primary key in @PrePersist
Next Topic:Foreign key constraint with create_or_extend_table is used
Goto Forum:
  


Current Time: Tue Jul 29 13:00:22 EDT 2014

Powered by FUDForum. Page generated in 0.01973 seconds