Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

NHibernate version:
1.2

Mapping documents:
<sql-query name="updateTag">
UPDATE TAGS SET TAGID = :tagID, MATRICOLA = :matricola, DATA = GetDate()
FROM TAGS
WHERE TAGID = :oldTagID
</sql-query>


Name and version of the database you are using:
MS SQL SERVER 2005

Hallo. i would like to update a record on a table named Tags via a custom sql query.

As first way i tried to use the
<sql-update>UPDATE PERSON SET NAME=UPPER(?) WHERE ID=?</sql-update>

but in could not found any example on how to use this way from the code.

So I have tried namedQuery as the one above but i cannot find a way to execute it like in Hibernate, the only way to execute is:

IQuery query = session.GetNamedQuery("updateTag");
query.SetParameter("tagID", "9999999999999999");
query.List();

And obviously i get the exception
NHibernate.QueryException: Return types of SQL query were not specified

Any idea which is the simplest way to update a record using an HQL/SQL query?

Kind regards

Massimo

The simplest way to update a record (reads "entity") is to Get() the entity, change the property that you want to update, then Update() it.

Ok, i Know that the simplest way to update an entity is via the nhibernate API (aka SaveOrUpdate() ), but since the property that i want to update is the primary key (I know i should not update the primary key etc. etc.... but this is what i HAVE to do....) i have to update it via a SQL query or an HQL query.

Any idea which is the simplest way? (named query or sql-update in the mapping file..)

kind regards

Massimo

To update the primary key (not recommended, as you noted), the simplest way is to fall back to ADO.NET.

Ok, so your suggestion is go back to ADO.Net.

It's strange because i found on the reference this chapter.

13.3. Custom SQL for create, update and delete, and i thought that was the right place to make my custom update.

...
<sql-update>UPDATE PERSON SET NAME=UPPER(?) WHERE ID=?</sql-update>
...

The problem was tha I didn't know and understand how to call the sql-update from the code.
Is this operation triggered during the normal object Save() ?

Kind regards

Massimo

The <sql-update> element is used so you can use a custom update statement instead of the NHibernate-generated update statement. I think NHibernate only passes "the ID" to the SQL statement; it does not pass an "old ID" and "new ID". Therefore, I don't think you can use <sql-update> to change the primary key. I can be wrong on this though.