Currently, we don't have the <return-scalar> for <sql-query>, that is Hibernate 3.x feature. HQL can return scalar values, but our group really need native query return one, cause:

1. we have to use native query for some oracle native feature
2. the query return 10,000 records, which we don't want NHibernate to cache or even use reflection to get the results into objects, cause it's slow with reflection.
3. But we do like the query cache can handle the 2nd same SQL, just return the result set from cache immediatly. No need to return objects.

I found solving 1 & 2 is easy by modifying SqlLoader.cs:


But, besides using NHibernate's session and DB conenction, independant with different DB driver, it's really not THAT useful. I can just use session.Connection.CreateCommand() to call the native query. So, what I hope is , for query cache, if the return is scalar values, automatically cache the result set or the collection of data.

Is it easy to modify the query cache part?

Even modifing the query cache cannot be done very soon, I recommend to add that the feature for sql-query supporting scalar return value. It's just one little modification in SqlLoader.cs, as described above.

For using it:


This will allow us to use other IQuery easy to use methods: query.SetMaxResults(), SetFirstResutl(), etc. Just the return scalar values are not in correct types, i.e. all number return as decimal for Oracle. If NHibernate1.0 can allow we put scalar types in session.CreateSQLQuery(), it will be better. Is this very hard to implement? It dosenot seem like that hard. :)

Will there be a patch for NHibernate 1.0, can this feature get included? Or you guys are just concentrating migrating Hibernate3 from now on?

Thanks

Please create a JIRA issue for this. In the meantime, you can either implement it in your copy of NHibernate and use that, or implement your own SQL query cache, this should not be hard.