Problems with Native SQL update

blackDoggy · **Joined:** Thu Feb 05, 2004 9:32 am **Posts:** 7

What is the proper way of calling native SQL queries? Hibernate complains that "Update queries only supported through HQL"! Am I doing something wrong?

Thanks!

Hibernate version:
3.0rc1

Mapping documents:

Code:

<sql-query name="changeUserPasswordSQL">
        UPDATE USERS SET PASSWORD=? WHERE USER_ID=?
    </sql-query> 

Code between sessionFactory.openSession() and session.close():

Code:

Query query = session.getNamedQuery("changeUserPasswordSQL");
query.setParameter(0, password);
query.setParameter(1, userId);
query.executeUpate();

Full stack trace of any exception that occurs:

Code:

java.lang.UnsupportedOperationException: Update queries only supported through HQL
    at org.hibernate.impl.AbstractQueryImpl.executeUpate(AbstractQueryImpl.java:573)
    at org.blog.model.security.UserManager$2.doInHibernate(UserManager.java:93)
    at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:310)
    at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:287)

max · **Posted:** Tue Mar 22, 2005 10:58 am

eh - the exception message says it all....updates is only supported via hql.

probably makes sense to do it for native query too...but basically not necessary since you can just execute that via sesson.connection().

max · **Posted:** Tue Mar 22, 2005 11:02 am

eh - the exception message says it all....updates is only supported via hql.

probably makes sense to do it for native query too...but basically not necessary since you can just execute that via sesson.connection().

blackDoggy · **Joined:** Thu Feb 05, 2004 9:32 am **Posts:** 7

ok, thnx. stupid question :)

nathanmoon · **Posted:** Tue Aug 16, 2005 3:47 pm

Are there plans to support named SQL update queries anytime in the near future?

I'm using session.connection() now to execute SQL updates, but it sure would be nice to be able to externalize them to named queries.

max · **Posted:** Tue Aug 16, 2005 3:51 pm

i *think* there is a jira for it, but not sure ....add one if you dont find one ;)

nathanmoon · **Posted:** Tue Aug 16, 2005 4:23 pm

I couldn't find one. I created this one: http://opensource.atlassian.com/projects/hibernate/browse/HHH-870

agrharsh · **Joined:** Tue Aug 30, 2005 4:04 am **Posts:** 7

Hi Folks,

I want to use a User defined SQL Procedure while updating a fld but have no clue how to do that. Following is the description of my problem statement:

I want to update a user's password. But before writing it in the DB, want to encrypt it. This encryption is done using a SQL Procedure. So tried:

String strResult = (String) this.getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException {
SQLQuery query = session.createSQLQuery(
"update T_TAXPAYER_ACCOUNT set password=dbo.EncMe(" + password
+ "), reset_flg='Y' where userid='"+user_id+"'");
int iReturn = query.executeUpdate();
return iReturn + "";
}
});

where dbo.EncMe is that SQL Procedure. This gives the UnsupportedOperationException exception.

I tried another (funny) way:

aTaxPayerAccount = (TaxPayerAccount)this.getHibernateTemplate().get(
TaxPayerAccount.class, user_id);
aTaxPayerAccount.setPassword("dbo.EncMe("+password+")");
aTaxPayerAccount.setReset_flg("Y");
this.getHibernateTemplate().update(aTaxPayerAccount);

and it sure didn't work. Can anyone please suggest me how to get this done. I hope I was able to make my problem statement clear.

I would appreciate all inputs.

Thanks!!

max · **Posted:** Thu Sep 15, 2005 3:35 am

as listed in this thread - native sql execution only support querying, not data manipulation.

And in your example using direct jdbc would be a perfect fit.

agrharsh · **Joined:** Tue Aug 30, 2005 4:04 am **Posts:** 7

okay. As you suggest, I have to use Spring JDBC, as I am using Hibernate with Spring MVC framework. Thanks for your reply Max.

max · **Posted:** Thu Sep 15, 2005 4:18 am

not to be poking, but you dont *have* to use Spring JDBC ....

session.connection() works just as well does it not ? :)

/max

agrharsh · **Joined:** Tue Aug 30, 2005 4:04 am **Posts:** 7

Hey Max,

Just when I was getting to explore JDBC support for Hibernate, in section 17.4 of the documentation i found that stored proc and custom insert, update, delete queries are supoprted in Hibernate3. It also states "The SQL is directly executed in your database, so you are free to use any dialect you like."

Code:

<class name="Person">
    <id name="id">
        <generator class="increment"/>
    </id>
    <property name="name" not-null="true"/>
    <sql-insert>INSERT INTO PERSON (NAME, ID) VALUES ( UPPER(?), ? )</sql-insert>
    <sql-update>UPDATE PERSON SET NAME=UPPER(?) WHERE ID=?</sql-update>
    <sql-delete>DELETE FROM PERSON WHERE ID=?</sql-delete>
</class>

Strored procs can be called as

Code:

<class name="Person">
    <id name="id">
        <generator class="increment"/>
    </id>
    <property name="name" not-null="true"/>
    <sql-insert callable="true">{call createPerson (?, ?)}</sql-insert>
    <sql-delete callable="true">{? = call deletePerson (?)}</sql-delete>
    <sql-update callable="true">{? = call updatePerson (?, ?)}</sql-update>
</class>

But what doc does not mention is how is this used in the code. Can you tell me how is this used in the code and if there is anything special I need to use this.

Thanks!

max · **Posted:** Thu Sep 15, 2005 4:58 am

eh no, there is no speciel code. That stuff overrides the sql hibernate uses in normal usage, meaning you just use your good old session.createQuery,load,get,delete etc.

spoons10 · **Joined:** Tue Apr 19, 2005 9:39 am **Posts:** 45

max wrote:

eh no, there is no speciel code. That stuff overrides the sql hibernate uses in normal usage, meaning you just use your good old session.createQuery,load,get,delete etc.

How do you use

Code:

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

in your code. I want this stored procedure to be called when I have to delete a user.

Thanks for any help Brandon.

spoons10 · **Joined:** Tue Apr 19, 2005 9:39 am **Posts:** 45

max wrote:

eh no, there is no speciel code. That stuff overrides the sql hibernate uses in normal usage, meaning you just use your good old session.createQuery,load,get,delete etc.

How do you use

Code:

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

in your code. I want this stored procedure to be called when I have to delete a user.

Thanks for any help Brandon.