These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 2
  [ 22 posts ]  Go to page 1, 2  Next
  Previous topic | Next topic 
Author Message
caryp
 Post subject: HELP NEWBIE! Postgres and stored procedures
PostPosted: Wed Apr 18, 2007 3:35 pm 
Newbie

Joined: Wed Apr 18, 2007 12:36 pm
Posts: 12
Is anyone able to perform an <sql-insert> using a stored procedure in PostrgeSQL 8.2? I am using Hibernate 3.2.3.GA and just about stumped.

1) With the following mapping definition:
Code:
<sql-insert callable="true" >
          {call insert_data(cast(? as timestamp without time zone), cast(? as timestamp without time zone), cast(? as real), cast(? as real), ?, ?) }       
</sql-insert>

On a session.flush() I am getting a
Quote:
A result was returned when none was expected.
exception. See end of this message for full stack trace.

2) When I try to add a parameter for a resultset as in the following mapping:
Code:
<sql-insert callable="true" >
          {call [b]? =[/b] insert_data(cast(? as timestamp without time zone), cast(? as timestamp without time zone), cast(? as real), cast(? as real), ?, ?) }       
</sql-insert>

The following error occurs:
Quote:
11:25:16,081 INFO [STDOUT] Hibernate: {call ? = insert_sensor_data(cast(? as timestamp without time zone), cast(? as timestamp without time zone), cast(? as real), cast(? as real), ?, ?) }
11:25:16,097 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: 22023
11:25:16,097 ERROR [JDBCExceptionReporter] No value specified for parameter 7.
11:25:16,097 ERROR [AbstractFlushingEventListener] Could not synchronize database state with session
org.hibernate.exception.DataException: Could not execute JDBC batch update



3) After days of searching and reading I found the following jira issue http://opensource.atlassian.com/projects/hibernate/browse/HHH-1745
That proposes a patch to add some callbacks registerResultSetOutParameter() and getResultSet().

This didn't seem to help. After tracing through the hibernate code I've found that the QueryParameters passed to the Loader class is returning false for the parameter's isCallable() method. This prompts the loader to call the Batcher's prepareQueryStatement() method and not the Batcher's prepareCallableQueryStatement() method, and so, the callbacks added from the patch are never even called (hence error #2 above)

Like I said I am totally stumped at this point. Any help would be greatly appreciated.

Thank you for your time!

-Cary P



Full Stack Trace:
11:02:47,378 INFO [STDOUT] Hibernate: {call insert_data(cast(? as timestamp without time zone), cast(? as timestamp without time zone), cast(? as real), cast(? as real), ?, ?) }
11:02:47,394 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: null
11:02:47,394 ERROR [JDBCExceptionReporter] Batch entry 0 select * from insert_data(cast(2007-04-18 18:02:47.331000 -0700 as timestamp without time zone), cast(2007-04-18 17:46:30.000000 -0700 as timestamp without time zone), cast(0.0 as real), cast(0.0 as real), 5, 94225) as result was aborted. Call getNextException to see the cause.
11:02:47,394 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: 0100E
11:02:47,394 ERROR [JDBCExceptionReporter] A result was returned when none was expected.
11:02:47,394 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: 0100E
11:02:47,394 ERROR [JDBCExceptionReporter] A result was returned when none was expected.
11:02:47,394 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: 0100E
11:02:47,394 ERROR [JDBCExceptionReporter] A result was returned when none was expected.
11:02:47,394 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: 0100E
11:02:47,394 ERROR [JDBCExceptionReporter] A result was returned when none was expected.
11:02:47,394 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: 0100E
11:02:47,394 ERROR [JDBCExceptionReporter] A result was returned when none was expected.
11:02:47,394 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: 0100E
11:02:47,394 ERROR [JDBCExceptionReporter] A result was returned when none was expected.
11:02:47,394 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: 0100E
11:02:47,394 ERROR [JDBCExceptionReporter] A result was returned when none was expected.
11:02:47,394 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: 0100E
11:02:47,394 ERROR [JDBCExceptionReporter] A result was returned when none was expected.
11:02:47,394 ERROR [AbstractFlushingEventListener] Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:253)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:235)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
at com.ssi.listener.ejb.CListenerManagerBean.LogSensorData(CListenerManagerBean.java:822)
at sun.reflect.GeneratedMethodAccessor312.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.invocation.Invocation.performCall(Invocation.java:359)
at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:237)
at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:158)
at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:169)
at org.jboss.ws.server.ServiceEndpointInterceptor.invoke(ServiceEndpointInterceptor.java:64)
at org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallValidationInterceptor.java:63)
at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:121)
at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:350)
at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:181)
at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:168)
at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:205)
at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:136)
at org.jboss.ejb.SessionContainer.internalInvoke(SessionContainer.java:648)
at org.jboss.ejb.Container.invoke(Container.java:954)
at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invoke(BaseLocalProxyFactory.java:430)
at org.jboss.ejb.plugins.local.StatelessSessionProxy.invoke(StatelessSessionProxy.java:103)
at $Proxy55.LogSensorData(Unknown Source)
at com.ssi.listener.handler.CLogSensorData.process2(CLogSensorData.java:59)
at com.ssi.listener.handler.AHandler.process(AHandler.java:41)
at com.ssi.listener.servlet.CGetRequestType.doGet(CGetRequestType.java:78)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:697)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:175)
at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:74)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:112)
at java.lang.Thread.run(Thread.java:595)
Caused by: java.sql.BatchUpdateException: Batch entry 0 select * from insert_sensor_data(cast(2007-04-18 18:02:47.331000 -0700 as timestamp without time zone), cast(2007-04-18 17:46:30.000000 -0700 as timestamp without time zone), cast(0.0 as real), cast(0.0 as real), 5, 94225) as result was aborted. Call getNextException to see the cause.
at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2530)
at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleResultRows(AbstractJdbc2Statement.java:2494)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1258)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:350)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2592)
at org.jboss.resource.adapter.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:517)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)
... 49 more


Last edited by caryp on Wed Apr 18, 2007 4:15 pm, edited 1 time in total.

Top
 Profile  
 
Ananasi
 Post subject:
PostPosted: Wed Apr 18, 2007 3:47 pm 
Expert
Expert

Joined: Tue Jul 11, 2006 10:21 am
Posts: 457
Location: Columbus, Ohio
Can you post the stored procedure definition. It is supposed to return the number of records inserted, registered as an out parameter.
Top
 Profile  
 
caryp
 Post subject:
PostPosted: Wed Apr 18, 2007 4:14 pm 
Newbie

Joined: Wed Apr 18, 2007 12:36 pm
Posts: 12
Thank you for the reply :)

Here is the postgres stored procedure I am currently using:
Code:
CREATE OR REPLACE FUNCTION "mySchema".insert_data(ntimestamp timestamp without time zone, ngw_timestamp timestamp without time zone, nvalue real, nvalue_converted real, nmeasurement_id bigint, nid bigint ) RETURNS integer AS $$
DECLARE
    rowc integer;
BEGIN
    INSERT INTO "mySchema".my_data_table
               (id,value,measurement_id,value_converted,timestamp,gw_timestamp )                 VALUES
               (nid, nvalue, nmeasurement_id, nvalue_converted,
        ntimestamp, ngw_timestamp );
    GET DIAGNOSTICS rowc = ROW_COUNT;
    RETURN rowc;
END;
$$ LANGUAGE plpgsql;


NOTE: some variable names have been changed to protect the innocent.
Top
 Profile  
 
Ananasi
 Post subject:
PostPosted: Wed Apr 18, 2007 4:25 pm 
Expert
Expert

Joined: Tue Jul 11, 2006 10:21 am
Posts: 457
Location: Columbus, Ohio
Well, first off, my bad. The sql-insert stored procedure should NOT return anything. Strip out the return parameter from the stored procedure. Only sql-delete and sql-update return data.
Top
 Profile  
 
Ananasi
 Post subject:
PostPosted: Wed Apr 18, 2007 4:33 pm 
Expert
Expert

Joined: Tue Jul 11, 2006 10:21 am
Posts: 457
Location: Columbus, Ohio
I also noticed in the DTD (since 3.2) that there is a check attribute on the sql-insert element. Perhaps that is used to define return values. So, you can also try using the stored procedure as written and setting check="rowcount" on the sql-insert element. Could not dig up any documentation about this feature though.
Top
 Profile  
 
caryp
 Post subject:
PostPosted: Wed Apr 18, 2007 4:39 pm 
Newbie

Joined: Wed Apr 18, 2007 12:36 pm
Posts: 12
No worries. I tried that too.

The following SP gives us the same result as explained in #1 of my original post:
Code:
CREATE FUNCTION "mySchema".insert_data(ntimestamp timestamp without time zone, ngw_timestamp timestamp without time zone, nvalue

real, nvalue_converted real, nmeasurement_id bigint, nid bigint ) RETURNS void AS $$
BEGIN
    INSERT INTO "mySchema".my_data_table
               (id,value,measurement_id,value_converted,timestamp,gw_timestamp ) VALUES
               (nid, nvalue, nmeasurement_id, nvalue_converted,
        ntimestamp, ngw_timestamp );
END;
$$ LANGUAGE plpgsql;


Thanks again for your ideas!!

-CP
Top
 Profile  
 
Ananasi
 Post subject:
PostPosted: Wed Apr 18, 2007 4:41 pm 
Expert
Expert

Joined: Tue Jul 11, 2006 10:21 am
Posts: 457
Location: Columbus, Ohio
Use that SP and set check="none" on the sql-insert element.
Top
 Profile  
 
caryp
 Post subject:
PostPosted: Wed Apr 18, 2007 4:52 pm 
Newbie

Joined: Wed Apr 18, 2007 12:36 pm
Posts: 12
Ananasi wrote:
I also noticed in the DTD (since 3.2) that there is a check attribute on the sql-insert element. Perhaps that is used to define return values. So, you can also try using the stored procedure as written and setting check="rowcount" on the sql-insert element. Could not dig up any documentation about this feature though.

No luck with the check="rowcount" attribute either.
Top
 Profile  
 
caryp
 Post subject:
PostPosted: Wed Apr 18, 2007 4:56 pm 
Newbie

Joined: Wed Apr 18, 2007 12:36 pm
Posts: 12
Ananasi wrote:
Use that SP and set check="none" on the sql-insert element.

I tried that too. However, my mapping file DOCTYPE is still defined at 3.0:
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>

This should be OK -- no?
Top
 Profile  
 
caryp
 Post subject:
PostPosted: Wed Apr 18, 2007 6:18 pm 
Newbie

Joined: Wed Apr 18, 2007 12:36 pm
Posts: 12
Since I need something working yesterday, I tried using straight JDBC calls.

This worked for me! Hopefully it will help someone else out.

Here's what I did:
Code:
      Connection con = hsession.connection();           
           
      // get next my_data_table id
      String sql = "SELECT nextval('my_data_table_sequence')";          
           ResultSet set = con.createStatement().executeQuery(sql);
           if (!set.next()) {
              throw new HibernateException("Could not get next my_data_table id");
           }
           sd.setId(set.getLong("nextval"));
          
           // execute insert          
           String pStr = "{call insert_data(" +
                    "cast(? as timestamp without time zone), " +
                    "cast(? as timestamp without time zone), " +
                    "cast(? as real), cast(? as real), ?, ?)}";
           PreparedStatement st = con.prepareCall(pStr);

           st.setTimestamp(1, sd.getTs());
           st.setTimestamp(2, sd.getGwTs());
           st.setDouble(3, sd.getValue());
           st.setDouble(4, sd.getValueConverted());
           st.setLong(5, sd.getMeasurement().getId());
           st.setLong(6, sd.getId());
           st.execute();


If anyone knows of a cleaner way PLEASE let me know. There are other SQL and HQL queries performed in the same transaction as this JDBC call.

Is it OK to mix JDBC and hibernate queries/calls like this??
Top
 Profile  
 
max
 Post subject:
PostPosted: Thu Apr 19, 2007 3:24 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 6:10 am
Posts: 8615
Location: Neuchatel, Switzerland (Danish)
try with check="none" and let us know if it still fails.

_________________
Max
Don't forget to rate

Top
 Profile  
 
caryp
 Post subject:
PostPosted: Thu Apr 19, 2007 11:09 am 
Newbie

Joined: Wed Apr 18, 2007 12:36 pm
Posts: 12
max wrote:
try with check="none" and let us know if it still fails.


It does still fail.

Thanks for the reply :)
Top
 Profile  
 
max
 Post subject:
PostPosted: Thu Apr 19, 2007 12:57 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 6:10 am
Posts: 8615
Location: Neuchatel, Switzerland (Danish)
does it fail with a different trace/error ?

_________________
Max
Don't forget to rate

Top
 Profile  
 
caryp
 Post subject:
PostPosted: Thu Apr 19, 2007 1:03 pm 
Newbie

Joined: Wed Apr 18, 2007 12:36 pm
Posts: 12
Yes. The same trace/error occurs with or without the check="none" attribute. It's doesn't seem to have any effect.
Top
 Profile  
 
max
 Post subject:
PostPosted: Thu Apr 19, 2007 1:58 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 6:10 am
Posts: 8615
Location: Neuchatel, Switzerland (Danish)
weird

_________________
Max
Don't forget to rate

Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 2
  [ 22 posts ]  Go to page 1, 2  Next

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.