Databases
StackOverflow2013
Postsdbo
POUsing ODP.Net with NHibernate with .net System.decimal
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing ODP.Net with NHibernate with .net System.decimal
    text
    copied!<p>I am using ODP.net to run the aggregate AVG against an Oracle 10g database. I run this query directly on the database and it works fine:</p> <p>select avg(ANSCHAFFUNGSKST) from IHObjekt</p> <p>it returns: 13.4493973163521</p> <p>Niether the HQL nor the CreateCriteria interfaces successfully execute the query. I recieve an NHibernate 'could not execute query' message. However, I am relatively certain it is an ODP.Net error based on this <a href="http://forums.oracle.com/forums/thread.jspa?threadID=231945&amp;tstart=5460" rel="nofollow">posting</a>.</p> <p>There is a solution for Oracle, simply TRUNC the AVG. However, the TRUNC command is different in Oracle versus SQL Server and I need/want to keep my code from being database specific.</p> <p>Any ideas about how I can reduce the number of decimal points so it fits within a decimal and most importantly, it works on all databases?</p> <h1>Source = NHibernate</h1> <h2>StackTrace</h2> <ul> <li>NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)</li> <li>NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters)</li> <li>NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes)</li> <li>NHibernate.Hql.Ast.ANTLR.Loader.QueryLoader.List(ISessionImplementor session, QueryParameters queryParameters)</li> <li>NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.List(ISessionImplementor session, QueryParameters queryParameters)</li> <li>NHibernate.Engine.Query.HQLQueryPlan.PerformList(QueryParameters queryParameters, ISessionImplementor session, IList results)</li> <li>NHibernate.Impl.SessionImpl.List(String query, QueryParameters queryParameters, IList results)</li> <li>NHibernate.Impl.SessionImpl.List(String query, QueryParameters parameters)</li> <li>NHibernate.Impl.QueryImpl.List()</li> <li>DBTest_NHibernate.MainWindow.ButtonHQLQuery_Click(Object sender, RoutedEventArgs e) in C:\...</li> </ul> <h2>InnerException</h2> <p>[System.OverflowException] = {"Die arithmetische Operation hat einen Überlauf verursacht."} ... The arithmetic operation has caused an overflow.</p> <h1>Source = Oracle.DataAccess</h1> <h2>StackTrace</h2> <ul> <li>Oracle.DataAccess.Types.DecimalConv.GetDecimal(IntPtr numCtx)</li> <li>Oracle.DataAccess.Client.OracleDataReader.GetDecimal(Int32 i)</li> <li>Oracle.DataAccess.Client.OracleDataReader.GetValue(Int32 i)</li> <li>Oracle.DataAccess.Client.OracleDataReader.get_Item(Int32 i)</li> <li>NHibernate.Type.DoubleType.Get(IDataReader rs, Int32 index)</li> <li>NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name)</li> <li>NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner)</li> <li>NHibernate.Hql.Ast.ANTLR.Loader.QueryLoader.GetResultColumnOrRow(Object[] row, IResultTransformer resultTransformer, IDataReader rs, ISessionImplementor session)</li> <li>NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies)</li> <li>NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)</li> <li>NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)</li> <li>NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)</li> </ul> <h2>Some HQL Testing Results</h2> <ul> <li>select TRUNC(AVG(ANSCHAFFUNGSKST),27) from IHObjekt - WORKS (ONLY IN ORACLE)</li> <li>select TRUNC(AVG(ANSCHAFFUNGSKST),28) from IHObjekt - DOES NOT WORK</li> <li>select AVG(ANSCHAFFUNGSKST) from IHObjekt - DOES NOT WORK</li> </ul> <h1>NHibernate Generated SQL</h1> <pre><code>SELECT AVG(IHOBJEKT0_.ANSCHAFFUNGSKST) AS COL_0_0_, COUNT(IHOBJEKT0_.ANSCHAFFUNGSKST) AS COL_1_0_, MAX(IHOBJEKT0_.ANSCHAFFUNGSKST) AS COL_2_0_, MIN(IHOBJEKT0_.ANSCHAFFUNGSKST) AS COL_3_0_, SUM(IHOBJEKT0_.ANSCHAFFUNGSKST) AS COL_4_0_ FROM IHOBJEKT IHOBJEKT0_ </code></pre> <p>Only AVG does not work in the above SQL statement on Oracle using ODP.Net. Using SQL Server or the Oracle client it works.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload