Hibernate Community • View topic - 17.3 Custom SQL

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

17.3 Custom SQL - documentation old and/or wrong

Page 1 of 1

[ 4 posts ]

Previous topic | Next topic

Author

Message

xerces8

Post subject: 17.3 Custom SQL - documentation old and/or wrong

Posted: Wed Sep 29, 2010 3:28 am

Regular

Joined: Thu Oct 19, 2006 12:07 pm
Posts: 75

See:

http://docs.jboss.org/hibernate/stable/ ... erysql-cud

This is either incomplete or plain wrong.

For example, the hbm example shows this:

Code:

 <sql-delete callable="true">{? = call deletePerson (?)}</sql-delete>
 <sql-update callable="true">{? = call updatePerson (?, ?)}</sql-update>

They both do not work, as there are too many parameters. The error is:

Code:

Hibernate: {? = call deletePerson (?)}
WARN  o.h.util.JDBCExceptionReporter - SQL Error: 0, SQLState: 22023
ERROR o.h.util.JDBCExceptionReporter - No value specified for parameter 2.

Update reports "no value for parameter 3.

The working code is like this:

Code:

 <sql-delete callable="true">{call deletePerson (?)}</sql-delete>
 <sql-update callable="true">{call updatePerson (?, ?)}</sql-update>

But that still does not work, it reports:

Code:

org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1
    at org.hibernate.jdbc.Expectations$BasicExpectation.checkBatched(Expectations.java:85)

The check attribute is needed.
The actually working mapping file is (from actual project):

Code:

  <sql-insert check="none" callable="true">{call mydb.createFooHB (?, ?, ?, ?, ?, ?, ?, ?)}</sql-insert>
  <sql-update check="none" callable="true">{call mydb.updateFooHB(?, ?, ?, ?, ?, ?, ?, ?)}</sql-update>
  <sql-delete check="none" callable="true">{call mydb.deleteFooHB (?)}</sql-delete>

Please someone update or clarify the documentation.

What would be required to have it work without check="none" ?

Currently I use hibernate-core-3.3.2.GA, I will check if the latest version behaves differently.

Regards,
David

Top

xerces8

Post subject: Re: 17.3 Custom SQL - documentation old and/or wrong

Posted: Thu Sep 30, 2010 2:57 am

Regular

Joined: Thu Oct 19, 2006 12:07 pm
Posts: 75

I tried 3.5.4-Final and it is the same.

I found the closest thing to documentation here:
http://opensource.atlassian.com/project ... e/HHH-1792

Question: what is the default value for check attribute ??

Top

xerces8

Post subject: Re: 17.3 Custom SQL - documentation old and/or wrong

Posted: Thu Sep 30, 2010 8:43 am

Regular

Joined: Thu Oct 19, 2006 12:07 pm
Posts: 75

For <loader>, it is even worse. I could not make (or find on internet) a working example.

I tried also simple named sql queries, called by getNamedQuery(). It does not work.

I tried:

Code:

<sql-query name="selectAllFoo" callable="true">
        <return alias="foo" class="Foo">
            <return-property name="id" column="id" />
            <return-property name="name" column="name" />
            <return-property name="address" column="address" />
        </return>
        {call mydb.getAllFoosHB()}
    </sql-query>

This returns on execution:

Code:

14:03:09.296 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
14:03:09.312 [main] DEBUG o.h.util.JDBCExceptionReporter - could not execute query [{call mydb.getAllFoosHB()}]
org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.
   at org.postgresql.core.v3.SimpleParameterList.registerOutParameter(SimpleParameterList.java:46) ~[postgresql-8.4-701.jdbc4.jar:na]
   at org.postgresql.jdbc2.AbstractJdbc2Statement.registerOutParameter(AbstractJdbc2Statement.java:1824) ~[postgresql-8.4-701.jdbc4.jar:na]
   at org.postgresql.jdbc3.AbstractJdbc3Statement.registerOutParameter(AbstractJdbc3Statement.java:1513) ~[postgresql-8.4-701.jdbc4.jar:na]
   at org.hibernate.dialect.PostgreSQLDialect.registerResultSetOutParameter(PostgreSQLDialect.java:335) ~[hibernate-core-3.5.6-Final.jar:3.5.6-Final]
   at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1629) [hibernate-core-3.5.6-Final.jar:3.5.6-Final]
   at org.hibernate.loader.Loader.doQuery(Loader.java:717) [hibernate-core-3.5.6-Final.jar:3.5.6-Final]

If I change the SQL line to : {? = call mydb.getAllFoosHB()}
then it gets one line further (see stack line at org.hibernate.loader.Loader.doQuery):

Code:

14:38:49.484 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
14:38:49.484 [main] DEBUG o.h.util.JDBCExceptionReporter - could not execute query [{? = call mydb.getAllFoosHB()}]
org.postgresql.util.PSQLException: A CallableStatement was executed with an invalid number of parameters
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:387) ~[postgresql-8.4-701.jdbc4.jar:na]
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:360) ~[postgresql-8.4-701.jdbc4.jar:na]
    at org.hibernate.dialect.PostgreSQLDialect.getResultSet(PostgreSQLDialect.java:341) ~[hibernate-core-3.5.6-Final.jar:3.5.6-Final]
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:215) ~[hibernate-core-3.5.6-Final.jar:3.5.6-Final]
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1866) [hibernate-core-3.5.6-Final.jar:3.5.6-Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:718) [hibernate-core-3.5.6-Final.jar:3.5.6-Final]

The calling code is simple:

Code:

Query namedQuery = session.getNamedQuery("selectAllFoos");
List<Foo> list = namedQuery.list();

The DB is PostgreSQL v 8.4, hibernate is version 3.5.6.

The stored procedure is:

Code:

CREATE OR REPLACE FUNCTION mydb.getallfooshb()
  RETURNS SETOF foo AS
$BODY$

BEGIN
 RETURN QUERY SELECT *
    FROM foo;
END;
$BODY$
  LANGUAGE 'plpgsql' ;

Is this due to poor PostgreSQL support in hibernate? Or am I doing something wrong?

Regular hibernate operation works fine (create/load/update/delete entities).

Regards,
David

Top

xerces8

Post subject: Re: 17.3 Custom SQL - documentation old and/or wrong

Posted: Thu Sep 30, 2010 3:24 pm

Regular

Joined: Thu Oct 19, 2006 12:07 pm
Posts: 75

OK, I found a solution/workaround for named sql queries.

Non-working code, as in docs*:

Code:

<sql-query name="selectAllFoos" callable="true">
  <return alias="foo" class="Foo">
    <return-property name="id" column="id" />
    <return-property name="name" column="name" />
    <return-property name="address" column="address" />
    <return-property name="city" column="city" />
    <return-property name="postCode" column="post_code" />
  </return>
        {? = call mydb.getAllFoosHB()}
</sql-query>

By trial, error and googling, I discovered that by making a couple of small changes, it works:

Code:

<sql-query name="selectAllFoos" > <!-- callable="true"  does not work   -->
  <return alias="foo" class="Foo">
    <return-property name="id" column="id" />
    <return-property name="name" column="name" />
    <return-property name="address" column="address" />
    <return-property name="city" column="city" />
    <return-property name="postCode" column="post_code" />
  </return>
        <!-- {? = call mydb.getAllFoosHB()}  this way does not work -->
        select * from mydb.getAllFoosHB()
</sql-query>

* - this docs: http://docs.jboss.org/hibernate/stable/ ... l#sp_query

For good measure, here is my hibernate.cfg.xml:

Code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC   "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
          "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
   <session-factory>
      <property name="connection.url">jdbc:postgresql://localhost/postgres</property>
      <property name="connection.username">postgres</property>
      <property name="connection.driver_class">org.postgresql.Driver</property>
      <property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>
      <property name="connection.password">SooperSeekrit</property>
      <property name="transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
      <property name="current_session_context_class">thread</property>
      <property name="hibernate.show_sql">true</property>
      <mapping resource="FooSP.hbm.xml" />
   </session-factory>
</hibernate-configuration>

And the mapping file:

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>
   <class name="Foo" table="mydb.t_foo">
      <id name="id" column="id">
         <generator class="sequence">
            <param name="sequence">mydb.foo_seq</param>
         </generator>
      </id>
      <property name="name" column="name" />
      <property name="address" column="address" />
      <property name="city" column="city" />
      <property name="postCode" column="post_code" />
<!-- custom SQL for database access: -->
<loader query-ref="loadFooSP"/>
<!--loader query-ref="loadFoo"/-->
      <sql-insert  check="none" callable="true">{call mydb.createFooHB (?, ?, ?, ?, ?, ?, ?, ?)}</sql-insert>
      <sql-update check="none" callable="true">{call mydb.updateFooHB(?, ?, ?, ?, ?, ?, ?, ?)}</sql-update>
      <sql-delete check="none" callable="true">{call mydb.deleteFooHB(?)}</sql-delete>
   </class>

<sql-query name="loadFoo">
    <return alias="foo" class="Foo" lock-mode="upgrade"/>
    SELECT 
      NAME AS {foo.name},
      ID AS {foo.id},
      address AS {foo.address},
      city AS {foo.city},
      post_code AS {foo.postCode},
    FROM mydb.t_foo
    WHERE ID=?
    FOR UPDATE
</sql-query>

<!-- try loading Foo by using a stored procedure -->
   <sql-query name="loadFooSP" > <!-- callable="true"  does not work   -->
      <return alias="foo" class="Foo">
         <return-property name="employee" column="EMPLOYEE" />
         <return-property name="id" column="id" />
         <return-property name="name" column="name" />
         <return-property name="address" column="address" />
         <return-property name="city" column="city" />
         <return-property name="postCode" column="post_code" />
      </return>
      <!--{? = call mydb.loadFooHB(?)} this way does not work -->
      select * from mydb.loadFooHB(?)
   </sql-query>

<!-- custom query that returns a list of Foo objects  -->
   <sql-query name="selectAllFoos" > <!-- callable="true"  does not work   -->
      <return alias="foo" class="Foo">
         <return-property name="id" column="id" />
         <return-property name="name" column="name" />
         <return-property name="address" column="address" />
         <return-property name="city" column="city" />
         <return-property name="postCode" column="post_code" />
      </return>
      <!-- {? = call mydb.getAllFoosHB()}  this way does not work -->
      select * from mydb.getAllFoosHB()
   </sql-query>
   
</hibernate-mapping> 

Top

Page 1 of 1

[ 4 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