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 #1056309] Wed, 08 May 2013 11:33 Go to next message
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?
Re: create a function via nativeQuery [message #1058579 is a reply to message #1056309] Tue, 14 May 2013 13:50 Go to previous message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Your database does not allow DDL when using batch writes. You need to disable batch writing to perform this operation (you have enabled batch writing in your persistence.xml).

EclipseLink (2.5) defines a query hint "eclipselink.jdbc.batch-writing"="false" to allow disabling batching.

You could also unwrap the JDBC connection and use JDBC directly.


James : Wiki : Book : Blog : Twitter
Previous Topic:[Solved] P2 Repository Download failure
Next Topic:JPA database schema validation case problems in ClassDescriptor#checkDatabase(...)
Goto Forum:
  


Current Time: Fri Oct 31 12:21:09 GMT 2014

Powered by FUDForum. Page generated in 0.10510 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software