Hi everybody!

We are getting in trouble with the native sql queries. We need to load data from a not mapped table into an object of a mapped class.

Something like:

table ZipData (zip string , city string )

into an object of:

class Address {
string zipcode;
string city;
}

we are trying this code:

String sql ="select zc.city as addr.City, zc.code as addr.ZipCode, " +
"from ZipCodes zc where addr.code = :zipCode";

session.CreateSQLQuery(sql, "addr", typeof(Address))
.SetString("zipCode", zipCode)
.List();

but it throws a nasty Exception: incorrect sintax near "."

Any suggestions will be very welcome ;-)

thks!

jv

String sql ="select zc.city as addr.City, zc.code as addr.ZipCode, " +
"from ZipCodes zc where addr.code = :zipCode";

addr.City, addr.ZipCode, addr.code are all wrong.
Try :

String sql ="select zc.city as City, zc.code as ZipCode, " +
"from ZipCodes zc where code = :zipCode";


Radu

Thanks for your answer...but this solution give us another error

String sql ="select zc.city as City, zc.code as ZipCode, " +
"from ZipCodes zc where code = :zipCode";

IList addresses = session.CreateSQLQuery(sql, String.Empty,
typeof (Address)).
.SetString(zipCode, "08820")
.List();

Error:
IndexOutOfRangeException: id0_

Someone can help us?

This should work in the CVS version, 0.8.4 doesn't really support SQL queries, since some relevant code wasn't ported yet when it was released.

I have the same error...(((

this code works:
SELECT {price.*}
FROM vw_minprice {price}
WHERE price.id_goods in
(
SELECT id_goods
FROM tb_favorites_list_item
WHERE id_favorites_list=:idFavoritesList
)


but I need get a count of objects, but I don't now how to make this... All my attempts add count() function failed

Why don't you just use a plain SQL query (IDbCommand)? You can obtain a connection from the session and use it to run queries. SQL queries are useful if you want to represent the results of your query as persistent objects.

Are native SQL queries now supported in 0.9? (per Hibernate 2.1.7 docs) I didn't see anything in the release notes. If not, does this need to be posted in JIRA?

Yes, they should be working fully now. If you find something that does not work, report it as a bug.