-->
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 1
  [ 2 posts ] 
  Previous topic | Next topic 
Author Message
mjensen75
 Post subject: Error when tring to use stored procedure for insert
PostPosted: Mon Feb 11, 2008 5:44 pm 
Newbie

Joined: Mon Feb 11, 2008 5:38 pm
Posts: 1
I'm trying to get Hibernate to call a stored procedure on insert, and I am running into the following error:

Hibernate: {exec virtual_currency.test_proc(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}
13:18:33,244 WARN [org.hibernate.util.JDBCExceptionReporter] SQL Error: 0, SQLState: null
13:18:33,244 ERROR [org.hibernate.util.JDBCExceptionReporter] Batch entry 0 exec virtual_currency.test_proc(1, 17, 0, 0, 0.0, 0, 0, 100.0, 1, 2008-02-11 13:18:32.994000 -08:00:00, 35) was aborted. Call getNextException to see the cause.
13:18:33,244 WARN [org.hibernate.util.JDBCExceptionReporter] SQL Error: 0, SQLState: 0100E
13:18:33,244 ERROR [org.hibernate.util.JDBCExceptionReporter] A result was returned when none was expected.

I'm not sure what the problem is, since the procedure is not returning anything. Any help is much appreciated.

Environment:

Hibernate 3
DB: Postgres 8.2
Driver: PostgreSQL 8.2 JDBC4 with SSL (build 508)
Java 6

Here is the sql-insert from my Hibernate mapping file:

<sql-insert callable="true">{exec virtual_currency.test_proc(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}</sql-insert>


And here is the procedure definition:

PROCEDURE test_proc (
p_account_id IN integer,
p_virtual_currency_id IN integer,
p_character_id IN integer,
p_game_id IN integer,
p_balance IN double precision,
p_virtual_currency_balance_id IN integer,
p_event_action_id IN integer,
p_trans_amt IN double precision,
p_modified_by IN integer,
p_last_update_date IN timestamp,
p_id IN integer
)
IS
DECLARE
BEGIN

return;

END;


I also tried it as a function and got a different error:

Hibernate: select nextval ('virtual_currency_balance_audit_id_seq')
Hibernate: {call virtual_currency.test_proc(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}
13:25:13,372 ERROR [org.hibernate.jdbc.AbstractBatcher] Exception executing batch:
org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1


Mapping:

<sql-insert callable="true">{call virtual_currency.test_proc(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}</sql-insert>

Function defintion:

FUNCTION test_proc (
p_account_id IN integer,
p_virtual_currency_id IN integer,
p_character_id IN integer,
p_game_id IN integer,
p_balance IN double precision,
p_virtual_currency_balance_id IN integer,
p_event_action_id IN integer,
p_trans_amt IN double precision,
p_modified_by IN integer,
p_last_update_date IN timestamp,
p_id IN integer
)
RETURN integer
IS
DECLARE
BEGIN

return 1;

END;
Top
 Profile  
 
shawnC
 Post subject:
PostPosted: Fri Mar 07, 2008 5:54 pm 
Newbie

Joined: Mon Nov 05, 2007 12:12 pm
Posts: 5
I am also getting the same error as you. Were you ever able to resolve it?

Here's my code/problem
Code:
<sql-insert callable="true" check="rowcount">{call insertVerChainFile (?::character varying,
                                                            ?::character varying,
                                                            ?::bigint,
                                                            ?::bigint,
                                                            ?::boolean,
                                                            ?::integer,
                                                            ?::bigint,
                                                            ?::bigint,
                                                            ?::double precision,
                                                            ?::double precision,
                                                            ?::timestamp with time zone,
                                                            ?::character varying)}</sql-insert>


Code:
CREATE OR REPLACE FUNCTION insertVerChainFile(v_cust_id varchar,
                                              v_gridfid varchar, v_sequence bigint,
                                              v_txn_id bigint, v_ishead boolean,
                                              v_comp_type integer, v_comp_size bigint,
                                              v_uncomp_size bigint, v_comp_ratio double precision,
                                              v_comp_percent double precision, v_comp_time timestamp with time zone,
                                              v_id varchar )
RETURNS INTEGER AS $$
DECLARE
   num_rows integer;
   new_cust_id varchar;
BEGIN
   new_cust_id = replace(v_cust_id, '-','_');
   EXECUTE 'insert into ver_chain_files_'
      || new_cust_id           || ' values( '
      || quote_literal(v_id)        || '::varchar,'
      || quote_literal(v_cust_id)     || '::varchar,'
      || quote_literal(v_gridfid)     || '::varchar,'
      || quote_literal(v_sequence)     || '::bigint,'
      || quote_literal(v_txn_id)     || '::bigint,'
      || v_ishead::integer        || '::boolean,'
      || quote_literal(v_comp_type)    || '::integer,'
      || quote_literal(v_comp_size)    || '::bigint,'
      || quote_literal(v_uncomp_size)  || '::bigint,'
      || quote_literal(v_comp_ratio)   || '::double precision,'
      || quote_literal(v_comp_percent) || '::double precision,'
      || quote_literal(v_comp_time)    || '::timestamp with time zone)';

   GET DIAGNOSTICS num_rows = ROW_COUNT;
   return num_rows;
END;
$$
LANGUAGE plpgsql;


Code:
WARNING 03/07/08 21:48:57.595 ceptionReporter - SQL Error: 0, SQLState: null
SEVERE  03/07/08 21:48:57.595 ceptionReporter - Batch entry 0 select * from insertVerChainFile (986e4156-cd7d-01db-39f8-001517106ba9::characte
r varying, 7df4ae6f-c876-49fe-b9e1-4c8bc1979e7a::character varying, 3321888774::bigint, 10025::bigint, 0::boolean, 2::integer, 10528768::bigin
t, 10528768::bigint, 1.0::double precision, 0.0::double precision, 2007-06-22 03:08:45.037000 -0500::timestamp with time zone, 7df4ae6f-c876-4
9fe-b9e1-4c8bc1979e7a.3321888774.1182498740.388373::character varying) as result was aborted.  Call getNextException to see the cause.
WARNING 03/07/08 21:48:57.596 ceptionReporter - SQL Error: 0, SQLState: 0100E
SEVERE  03/07/08 21:48:57.596 ceptionReporter - A result was returned when none was expected.
WARNING 03/07/08 21:48:57.596 ceptionReporter - SQL Error: 0, SQLState: 0100E
SEVERE  03/07/08 21:48:57.596 ceptionReporter - A result was returned when none was expected.
WARNING 03/07/08 21:48:57.596 ceptionReporter - SQL Error: 0, SQLState: 0100E
SEVERE  03/07/08 21:48:57.596 ceptionReporter - A result was returned when none was expected.
WARNING 03/07/08 21:48:57.597 ceptionReporter - SQL Error: 0, SQLState: 0100E
SEVERE  03/07/08 21:48:57.597 ceptionReporter - A result was returned when none was expected.
WARNING 03/07/08 21:48:57.597 ceptionReporter - SQL Error: 0, SQLState: 0100E
SEVERE  03/07/08 21:48:57.597 ceptionReporter - A result was returned when none was expected.
WARNING 03/07/08 21:48:57.597 ceptionReporter - SQL Error: 0, SQLState: 0100E
SEVERE  03/07/08 21:48:57.597 ceptionReporter - A result was returned when none was expected.
WARNING 03/07/08 21:48:57.597 ceptionReporter - SQL Error: 0, SQLState: 0100E
SEVERE  03/07/08 21:48:57.598 ceptionReporter - A result was returned when none was expected.
WARNING 03/07/08 21:48:57.598 ceptionReporter - SQL Error: 0, SQLState: 0100E
SEVERE  03/07/08 21:48:57.598 ceptionReporter - A result was returned when none was expected.
WARNING 03/07/08 21:48:57.598 ceptionReporter - SQL Error: 0, SQLState: 0100E
SEVERE  03/07/08 21:48:57.598 ceptionReporter - A result was returned when none was expected.
WARNING 03/07/08 21:48:57.598 ceptionReporter - SQL Error: 0, SQLState: 0100E
SEVERE  03/07/08 21:48:57.598 ceptionReporter - A result was returned when none was expected.
WARNING 03/07/08 21:48:57.599 ceptionReporter - SQL Error: 0, SQLState: 0100E
SEVERE  03/07/08 21:48:57.599 ceptionReporter - A result was returned when none was expected.
WARNING 03/07/08 21:48:57.599 ceptionReporter - SQL Error: 0, SQLState: 0100E
SEVERE  03/07/08 21:48:57.599 ceptionReporter - A result was returned when none was expected.
WARNING 03/07/08 21:48:57.599 ceptionReporter - SQL Error: 0, SQLState: 0100E
SEVERE  03/07/08 21:48:57.599 ceptionReporter - A result was returned when none was expected.
WARNING 03/07/08 21:48:57.600 ceptionReporter - SQL Error: 0, SQLState: 0100E
SEVERE  03/07/08 21:48:57.600 ceptionReporter - A result was returned when none was expected.
WARNING 03/07/08 21:48:57.600 ceptionReporter - SQL Error: 0, SQLState: 0100E
SEVERE  03/07/08 21:48:57.600 ceptionReporter - A result was returned when none was expected.
WARNING 03/07/08 21:48:57.600 ceptionReporter - SQL Error: 0, SQLState: 0100E
SEVERE  03/07/08 21:48:57.600 ceptionReporter - A result was returned when none was expected.
WARNING 03/07/08 21:48:57.600 ceptionReporter - SQL Error: 0, SQLState: 0100E
SEVERE  03/07/08 21:48:57.600 ceptionReporter - A result was returned when none was expected.
WARNING 03/07/08 21:48:57.600 ceptionReporter - SQL Error: 0, SQLState: 0100E
SEVERE  03/07/08 21:48:57.600 ceptionReporter - A result was returned when none was expected.
WARNING 03/07/08 21:48:57.600 ceptionReporter - SQL Error: 0, SQLState: 0100E
SEVERE  03/07/08 21:48:57.601 ceptionReporter - A result was returned when none was expected.
WARNING 03/07/08 21:48:57.601 ceptionReporter - SQL Error: 0, SQLState: 0100E
SEVERE  03/07/08 21:48:57.601 ceptionReporter - A result was returned when none was expected.
SEVERE  03/07/08 21:48:57.601 ngEventListener - Could not synchronize database state with sessionorg.hibernate.exception.GenericJDBCException:
could not insert: [com.exagrid.triage.dbstor.txninfo.VersionChainFile]


Please if anyone knows what the problem is, this has been driving me nuts for hours now. I tried different check conditions, returning void from the function and nothing has made a difference.
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 1
  [ 2 posts ] 

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.