I'm wrestling with NHibernate named query and PostgreSQL but can't get it
to work. I have a function in PostgreSQL database:
create or replace function x(results refcursor, id_ bigint)
returns refcursor as
$$
begin
open results for
select id, name, number from table_name where id = id_;
return results;
end;
$$ language plpgsql;
Then I made a mapping in NHibernate:
<sql-query name="SqlQueryForTesting">
<return-scalar column="id" type="long" />
<return-scalar column="name" type="string" />
<return-scalar column="number" type="string" />
<![CDATA[
begin;
select x('table_name_cursor', :id);
fetch all in table_name_cursor;
commit;
]]>
</sql-query>
And finally made a method that calls this named query.
public IList MethodForNamedQuery(int id)
{
var query = Session.GetNamedQuery("SqlQueryForTesting");
query.SetInt32("id", id);
return query.List();
}
The problem is that this code gives me an error.
System.IndexOutOfRangeException
Field not found
at Npgsql.NpgsqlRowDescription.FieldIndex(String fieldName)
at NHibernate.Driver.NHybridDataReader.GetOrdinal(String name)
at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name)
at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name,
ISessionImplementor session, Object owner)
at
NHibernate.Loader.Custom.CustomLoader.ScalarResultColumnProcessor.Extract(Object[]
data, IDataReader resultSet, ISessionImplementor session)
at
NHibernate.Loader.Custom.CustomLoader.ResultRowProcessor.BuildResultRow(Object[]
data, IDataReader resultSet, Boolean hasTransformer, ISessionImplementor
session)
at NHibernate.Loader.Custom.CustomLoader.GetResultColumnOrRow(Object[] row,
IResultTransformer resultTransformer, IDataReader rs, ISessionImplementor
session)
at NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet,
ISessionImplementor session, QueryParameters queryParameters, LockMode[]
lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[]
keys, Boolean returnProxies)
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session,
QueryParameters queryParameters, Boolean returnProxies)
at
NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor
session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.DoList(ISessionImplementor session,
QueryParameters queryParameters)
NHibernate.Exceptions.GenericADOException
could not execute query
[ begin;
select x('table_name_cursor', :p0);
fetch all in table_name_cursor;
commit; ]
Name:id - Value:2
If I run my query through pgAdmin it works perfectly and returns me one
row. So I'm guessing the problem is somewhere in mapping. Can anybody
say what is wrong with my mapping?
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/nhusers.
For more options, visit https://groups.google.com/d/optout.
