Hi,

I have been trying to delete a few records from the database using the following named query :

[b]<sql-query name="Delete_Express">
DELETE SEC_LOG WHERE TIME between convert(smalldatetime, ? ) and convert(smalldatetime, ? )
</sql-query> [/b]


Now to call this query, i use [b]query.executeUpdate();[/b], but I get an exception :
[b]java.lang.UnsupportedOperationException: Update queries only supported through HQL
org.hibernate.impl.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:607)[/b].

Could anyone tell me how to call such a query from hibernate? Any help is appreciated.

[quote="adil"]Hi,

I have been trying to delete a few records from the database using the following named query :

[b]<sql-query name="Delete_Express">
DELETE SEC_LOG WHERE TIME between convert(smalldatetime, ? ) and convert(smalldatetime, ? )
</sql-query> [/b]


Now to call this query, i use [b]query.executeUpdate();[/b], but I get an exception :
[b]java.lang.UnsupportedOperationException: Update queries only supported through HQL
org.hibernate.impl.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:607)[/b].

Could anyone tell me how to call such a query from hibernate? Any help is appreciated.[/quote]

Yo have to specifc parameters names in you sql-query code, like this:
DELETE SEC_LOG WHERE TIME between convert(smalldatetime, :param1 ) and convert(smalldatetime, :param2 )



Then, you have to set parameters values in your hibernate code:

Query q = session.getNameQuery("Delete_Express");
q.setParameter("param1", object1);
q.setParameter("param2", object2);

I guess that will help you to solve your problem.

Requests for comments

Thanks for the reply , however when I tried adding this to the query :


[b]DELETE SEC_LOG WHERE TIME between convert(smalldatetime,:param1) and convert(smalldatetime,:param2)[/b]

but when i try this code :

[b]query = getSession().getNamedQuery("Delete_Express");
query.setParameter(":param1",startDate);
query.setParameter(":param2",endDate);[/b]


java.lang.IllegalArgumentException: Parameter :param1 does not exist as a named parameter in [DELETE SEC_LOG WHERE TIME between convert(smalldatetime,:param1) and convert(smalldatetime,:param2)]

Any idea why ??

I'm not familiar with your use of convert here, but Hibernate appears to expect that your query use mapped class names and properties, not column names. For example,

<class name="SecLog" table="sec_log">
...
<property name="someTime" column="time" ...>
</class>

then

<query name="Delete_Express">
delete from SecLog where someTime between ? and ?
</query>

In the code,
Query q = session.getNamedQuery("Delete_Express");
Date d1 = new Date(1,0,12);
q.setDate(0, d1);
Date d2 = new Date(1,0,15);
q.setDate(1, d2);
q.executeUpdate();

If you want to use functions, you may need to add formula-type properties to the mapping.

adil,

but when i try this code :

query = getSession().getNamedQuery("Delete_Express");
query.setParameter(":param1",startDate);
query.setParameter(":param2",endDate);


java.lang.IllegalArgumentException: Parameter :param1 does not exist as a named parameter in [DELETE SEC_LOG WHERE TIME between convert(smalldatetime,:param1) and convert(smalldatetime,:param2)]


The 1st parameter pass into the setParameter shouldn;t include the ":", if u have a param like :sample, you should only pass in "sample",
e.g query.setParameter("sample", value);

Hope this help.