Hello All,
We have a legacy DB2 database on AS400 and what I'm trying to do is map one of the tables to the class defined. The good thing is, the only operation we'r allowed to perform on this legacy database is SELECT.
But, for some reason I can't get the query to work here! I keep getting a message "invalid column ID" and I know that there is no column ID in that table, but that's the name I'm using for the composite key.
I'm still not quite used to NHibernate, but I would really like to use it for this purpose.
Here are the details that you might need to understand the details...
NHibernate version: 1.0.2
Mapping documents:
Code:
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0" default-cascade="none">
<class
name="AS400.Domain.CoilProcessHistoryImpl, As400.Core"
table="xxxPRD.CMCYCPP"
dynamic-insert="false"
dynamic-update="false"
lazy="true">
<composite-id class="AS400.Domain.CoilProcessHistoryKey, AS400.Core" access="nosetter">
<key-property name="ProcessSequence" type="Int32" column="column1" />
<key-property name="ProcessCode" type="String" column="column2" />
</composite-id>
<property name="ProcessCode" type="String" insert="false" update="false">
<column name="column1" not-null="true" unique="false" sql-type="VARCHAR(255)"/>
</property>
<property name="ProcessSequence" type="Int32" insert="false" update="false">
<column name="column2" not-null="true" unique="false" sql-type="INTEGER"/>
</property>
<property name="ColdMillProduct" type="String">
<column name="column3" not-null="true" unique="false" sql-type="VARCHAR(255)"/>
</property>
<property name="CoilTypeCode" type="String">
<column name="column4" not-null="true" unique="false" sql-type="VARCHAR(255)"/>
</property>
<property name="ColdMillCoilId" type="Double">
<column name="column5" not-null="true" unique="false" sql-type="FLOAT"/>
</property>
<!-- Few more properties follow ... -->
</class>
</hibernate-mapping>
Code:
[serializable]
public class CoilProcessHistoryKey {
private int _processSequence;
private string _processCode;
// Properties for these attributes defined here
// Contructors are defined here
// Equals(), GetHashCode() and ToString() are overriden here
}
public abstract class CoilProcessHistory {
private CoilProcessHistoryKey _id;
private String _processCode;
private int _processSequence;
private String _coldMillProduct;
private String _coilTypeCode;
private double _coldMillCoilId;
// Other private attributes follow ...
// Properties for all these private attributes defined here
// Equals(), GetHashCode() and ToString() are overriden here
// A factory method defined here
}
There is a Dao class that I use to actually run the query. The code for that is here ...
Code:
public virtual Object Load(int transform, int processSequence, string processCode)
{
string hqlQuery = "from CoilProcessHistoryImpl as cphi " +
"where cphi.ProcessSequence = " + processSequence.ToString() + " and " +
" cphi.ProcessCode = " + processCode;
Object entity = Session.Find(hqlQuery);
return TransformEntity(transform, (AS400.Domain.CoilProcessHistory)entity);
}
Full stack trace of any exception that occurs:Code:
NHibernate.ADOException: Could not execute query ---> System.Data.Odbc.OdbcException: ERROR [42S22] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0205 - Column ID not in table table_name in database_name.
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
at NHibernate.Hql.QueryTranslator.List(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Impl.SessionImpl.Find(String query, QueryParameters parameters)
--- End of inner exception stack trace ---
at NHibernate.Impl.SessionImpl.Find(String query, QueryParameters parameters)
at NHibernate.Impl.SessionImpl.Find(String query)
at AS400.Domain.CoilProcessHistoryDaoBase.Load(Int32 transform, Int32 processSequence, String processCode) in C:\Documents and Settings\Sandeep_Joshi\My Documents\Visual Studio 2005\Projects\As400\As400.Core\target\AS400\Domain\CoilProcessHistoryDaoBase.cs:line 38
at Service.GetCoilProcessHistory(Int32 processSequence, String processCode) in c:\inetpub\wwwroot\AS400.Service\App_Code\Service.vb:line 16
Name and version of the database you are using: DB2 (not sure what ver it is)
The generated SQL:Code:
2006-11-16 01:46:46 PM [828] DEBUG NHibernate.Hql.QueryTranslator - HQL: from AS400.Domain.CoilProcessHistoryImpl as cphi where cphi.ProcessSequence = 177 and cphi.ProcessCode = 10
2006-11-16 01:46:46 PM [828] DEBUG NHibernate.Hql.QueryTranslator - SQL: select coilproces0_.CYANNC as CYANNC, coilproces0_.CYA0CE as CYA0CE, coilproces0_.CYAMDT as CYAMDT, coilproces0_.CYACTM as CYACTM, coilproces0_.CYUFNB as CYUFNB, coilproces0_.CYZSNU as CYZSNU, coilproces0_.CYFTDT as CYFTDT, coilproces0_.CYQCCD as CYQCCD, coilproces0_.CYUCNB as CYUCNB, coilproces0_.CYQVCD as CYQVCD, coilproces0_.CYAEVN as CYAEVN, coilproces0_.CYQWCD as CYQWCD, coilproces0_.CYAACE as CYAACE, coilproces0_.CYAEST as CYAEST, coilproces0_.CYBFNC as CYBFNC, coilproces0_.CYACVN as CYACVN, coilproces0_.CYUANB as CYUANB, coilproces0_.CYALDT as CYALDT, coilproces0_.CYABVN as CYABVN, coilproces0_.CYAAND as CYAAND, coilproces0_.CYZRNU as CYZRNU, coilproces0_.CYAXSS as CYAXSS, coilproces0_.CYZ2SS as CYZ2SS, coilproces0_.CYADVN as CYADVN, coilproces0_.CYABTM as CYABTM, coilproces0_.CYAWSS as CYAWSS, coilproces0_.ID as ID, coilproces0_.CYN0CD as CYN0CD from xxxPRD.CMCYCPP coilproces0_ where (coilproces0_.CYANNC=177)and(coilproces0_.CYA0CE=10)
2006-11-16 01:46:46 PM [828] DEBUG NHibernate.Impl.BatcherImpl - Opened new IDbCommand, open IDbCommands :1
2006-11-16 01:46:46 PM [828] DEBUG NHibernate.Impl.BatcherImpl - Building an IDbCommand object for the SqlString: select coilproces0_.CYANNC as CYANNC, coilproces0_.CYA0CE as CYA0CE, coilproces0_.CYAMDT as CYAMDT, coilproces0_.CYACTM as CYACTM, coilproces0_.CYUFNB as CYUFNB, coilproces0_.CYZSNU as CYZSNU, coilproces0_.CYFTDT as CYFTDT, coilproces0_.CYQCCD as CYQCCD, coilproces0_.CYUCNB as CYUCNB, coilproces0_.CYQVCD as CYQVCD, coilproces0_.CYAEVN as CYAEVN, coilproces0_.CYQWCD as CYQWCD, coilproces0_.CYAACE as CYAACE, coilproces0_.CYAEST as CYAEST, coilproces0_.CYBFNC as CYBFNC, coilproces0_.CYACVN as CYACVN, coilproces0_.CYUANB as CYUANB, coilproces0_.CYALDT as CYALDT, coilproces0_.CYABVN as CYABVN, coilproces0_.CYAAND as CYAAND, coilproces0_.CYZRNU as CYZRNU, coilproces0_.CYAXSS as CYAXSS, coilproces0_.CYZ2SS as CYZ2SS, coilproces0_.CYADVN as CYADVN, coilproces0_.CYABTM as CYABTM, coilproces0_.CYAWSS as CYAWSS, coilproces0_.ID as ID, coilproces0_.CYN0CD as CYN0CD from xxxPRD.CMCYCPP coilproces0_ where (coilproces0_.CYANNC=177)and(coilproces0_.CYA0CE=10)
2006-11-16 01:46:46 PM [828] INFO NHibernate.Loader.Loader - select coilproces0_.CYANNC as CYANNC, coilproces0_.CYA0CE as CYA0CE, coilproces0_.CYAMDT as CYAMDT, coilproces0_.CYACTM as CYACTM, coilproces0_.CYUFNB as CYUFNB, coilproces0_.CYZSNU as CYZSNU, coilproces0_.CYFTDT as CYFTDT, coilproces0_.CYQCCD as CYQCCD, coilproces0_.CYUCNB as CYUCNB, coilproces0_.CYQVCD as CYQVCD, coilproces0_.CYAEVN as CYAEVN, coilproces0_.CYQWCD as CYQWCD, coilproces0_.CYAACE as CYAACE, coilproces0_.CYAEST as CYAEST, coilproces0_.CYBFNC as CYBFNC, coilproces0_.CYACVN as CYACVN, coilproces0_.CYUANB as CYUANB, coilproces0_.CYALDT as CYALDT, coilproces0_.CYABVN as CYABVN, coilproces0_.CYAAND as CYAAND, coilproces0_.CYZRNU as CYZRNU, coilproces0_.CYAXSS as CYAXSS, coilproces0_.CYZ2SS as CYZ2SS, coilproces0_.CYADVN as CYADVN, coilproces0_.CYABTM as CYABTM, coilproces0_.CYAWSS as CYAWSS, coilproces0_.ID as ID, coilproces0_.CYN0CD as CYN0CD from xxxPRD.CMCYCPP coilproces0_ where (coilproces0_.CYANNC=177)and(coilproces0_.CYA0CE=10)
2006-11-16 01:46:46 PM [828] DEBUG NHibernate.SQL - select coilproces0_.CYANNC as CYANNC, coilproces0_.CYA0CE as CYA0CE, coilproces0_.CYAMDT as CYAMDT, coilproces0_.CYACTM as CYACTM, coilproces0_.CYUFNB as CYUFNB, coilproces0_.CYZSNU as CYZSNU, coilproces0_.CYFTDT as CYFTDT, coilproces0_.CYQCCD as CYQCCD, coilproces0_.CYUCNB as CYUCNB, coilproces0_.CYQVCD as CYQVCD, coilproces0_.CYAEVN as CYAEVN, coilproces0_.CYQWCD as CYQWCD, coilproces0_.CYAACE as CYAACE, coilproces0_.CYAEST as CYAEST, coilproces0_.CYBFNC as CYBFNC, coilproces0_.CYACVN as CYACVN, coilproces0_.CYUANB as CYUANB, coilproces0_.CYALDT as CYALDT, coilproces0_.CYABVN as CYABVN, coilproces0_.CYAAND as CYAAND, coilproces0_.CYZRNU as CYZRNU, coilproces0_.CYAXSS as CYAXSS, coilproces0_.CYZ2SS as CYZ2SS, coilproces0_.CYADVN as CYADVN, coilproces0_.CYABTM as CYABTM, coilproces0_.CYAWSS as CYAWSS, coilproces0_.ID as ID, coilproces0_.CYN0CD as CYN0CD from xxxPRD.CMCYCPP coilproces0_ where (coilproces0_.CYANNC=177)and(coilproces0_.CYA0CE=10)
Debug level Hibernate log excerpt:After instantialing command object rest of the DEBUG level log says ...
Code:
2006-11-16 01:46:46 PM [828] DEBUG NHibernate.Connection.DriverConnectionProvider - Obtaining IDbConnection from Driver
2006-11-16 01:46:46 PM [828] DEBUG NHibernate.Util.ADOExceptionReporter - SQL Exception
System.Data.Odbc.OdbcException: ERROR [42S22] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0205 - Column ID not in table CMCYCPP in xxxPRD.
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
2006-11-16 01:46:46 PM [828] WARN NHibernate.Util.ADOExceptionReporter - System.Data.Odbc.OdbcException: ERROR [42S22] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0205 - Column ID not in table CMCYCPP in xxxPRD.
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
2006-11-16 01:46:46 PM [828] ERROR NHibernate.Util.ADOExceptionReporter - ERROR [42S22] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0205 - Column ID not in table CMCYCPP in xxxPRD.
2006-11-16 01:46:46 PM [828] DEBUG NHibernate.Impl.BatcherImpl - Closed IDbCommand, open IDbCommands :0
2006-11-16 01:46:46 PM [828] DEBUG NHibernate.Impl.SessionImpl - closing session
2006-11-16 01:46:46 PM [828] DEBUG NHibernate.Impl.SessionImpl - disconnecting session
2006-11-16 01:46:46 PM [828] DEBUG NHibernate.Connection.ConnectionProvider - Closing connection
2006-11-16 01:46:46 PM [828] DEBUG NHibernate.Impl.SessionImpl - transaction completion
2006-11-16 01:46:46 PM [828] DEBUG AndroMDA.NHibernateSupport.DefaultSessionManager - Closed hibernate session
Any help is appreciated
Thanks,
Sandeep
Login
FAQ
Search
