[Updated]
I am unable to let NHibernate know that an enumeration value needs to be translated into a CHAR(1) value for the database. Some of the other posts on this forums pointed out that an Enumeration can be converted using an IUserType. However, after attempting to implement this strategy, the named query which was being executed is still mapping the Enumeration value to a INT database value type (this information was found by capturing the SQL statement through SQL Profiler).
Sadly, the database structure was written by a 3rd party software vendor. And, the column types cannot be altered.
Hibernate version:
1.2.0.GA. This is being used in combination with Spring.NET 1.1-RC1. But, I believe the problem I am running into well be best viewed by the eyes of an experienced (and very nice/helpful) NHibernate community member.
Mapping documents:
App.Config
Code:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<sectionGroup name="spring">
<section name="context" type="Spring.Context.Support.ContextHandler, Spring.Core"/>
<section name="parsers" type="Spring.Context.Support.NamespaceParsersSectionHandler, Spring.Core" />
</sectionGroup>
<section name="DaemonGeneralSettings" type="System.Configuration.NameValueSectionHandler" />
<section name="DaemonDataSettings" type="System.Configuration.NameValueSectionHandler" />
</configSections>
<DaemonGeneralSettings>
<add key="debug.enabled" value="true" />
</DaemonGeneralSettings>
<DaemonDataSettings>
<add key="db.dars.datasource" value="xxxx" />
<add key="db.dars.database" value="xxxx" />
</DaemonDataSettings>
<spring>
<context>
<resource uri="assembly://Ucsb.Sa.Registrar.Dars.Daemon.Domain/Ucsb.Sa.Registrar.Dars.Daemon.Domain.Config/SpringObjects.xml" />
<resource uri="assembly://Ucsb.Sa.Registrar.Dars.Daemon.Domain.Tests/Ucsb.Sa.Registrar.Dars.Daemon.Domain.Tests.Config/SpringObjects.xml" />
<resource uri="assembly://Ucsb.Sa.Registrar.Dars.Daemon.Data/Ucsb.Sa.Registrar.Dars.Daemon.Data.Config/Dars.Database.shbc.xml" />
<resource uri="assembly://Ucsb.Sa.Registrar.Dars.Daemon.Data/Ucsb.Sa.Registrar.Dars.Daemon.Data.Config/SpringObjects.xml" />
<resource uri="assembly://Ucsb.Sa.Registrar.Dars.Daemon.Data.Tests/Ucsb.Sa.Registrar.Dars.Daemon.Data.Tests.Config/SpringPlaceholders.xml" />
<resource uri="assembly://Ucsb.Sa.Registrar.Dars.Daemon.Data.Tests/Ucsb.Sa.Registrar.Dars.Daemon.Data.Tests.Config/SpringObjects.xml" />
</context>
<parsers>
<parser type="Spring.Data.Config.DatabaseNamespaceParser, Spring.Data" />
</parsers>
</spring>
</configuration>
Dars.Database.shbc.xml
Code:
<?xml version="1.0" encoding="utf-8" ?>
<objects xmlns="http://www.springframework.net"
xmlns:db="http://www.springframework.net/database">
<description>Ucsb.Sa.Registrar.Dars.Daemon.Data Dars Database connection infomation</description>
<db:provider id="DarsDbProvider"
provider="SqlServer-2.0"
connectionString="Data Source=${db.dars.datasource};Initial Catalog=${db.dars.database};Integrated Security=SSPI;Persist Security Info=False;" />
<object id="DarsSessionFactory" type="Spring.Data.NHibernate.LocalSessionFactoryObject, Spring.Data.NHibernate12">
<property name="DbProvider" ref="DarsDbProvider" />
<property name="MappingAssemblies">
<list>
<value>Ucsb.Sa.Registrar.Dars.Daemon.Data</value>
</list>
</property>
<property name="HibernateProperties">
<dictionary>
<entry key="hibernate.connection.provider" value="NHibernate.Connection.DriverConnectionProvider" />
<entry key="hibernate.dialect" value="NHibernate.Dialect.MsSql2005Dialect" />
<entry key="hibernate.connection.driver_class" value="NHibernate.Driver.SqlClientDriver" />
</dictionary>
</property>
</object>
</objects>
SpringObjects.xml (in Data package)
Code:
<?xml version="1.0" encoding="utf-8" ?>
<objects xmlns="http://www.springframework.net">
<description>Ucsb.Sa.Registrar.Dars.Daemon.Data Objects</description>
<object id="DaemonThreadRepository"
type="Ucsb.Sa.Registrar.Dars.Daemon.Data.DaemonThreadRepository, Ucsb.Sa.Registrar.Dars.Daemon.Data">
<property name="DarsSessionFactory" ref="DarsSessionFactory" />
</object>
</objects>
JobQueueList.hbm.xml
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="Ucsb.Sa.Registrar.Dars.Daemon.Domain"
namespace="Ucsb.Sa.Registrar.Dars.Daemon.Domain">
<class name="JobQueueList" table="job_queue_list">
<composite-id>
<key-property name="InstitutionIdQualifier" column="instidq" />
<key-property name="InstitutionId" column="instid" />
<key-property name="InstitutionCode" column="instcd" />
<key-property name="JobId" column="jobid" />
<key-property name="UserId" column="userid" />
</composite-id>
<property name="ReadSystemIn" column="read_sysin" />
<property name="StartDate" column="startdate" />
<property name="StartTime" column="starttime" />
<property name="ServerName" column="servername" />
<property name="Status" column="status" type="Ucsb.Sa.Registrar.Dars.Daemon.Data.UserTypes.JobRequestStatusMappingUserType, Ucsb.Sa.Registrar.Dars.Daemon.Data" />
<property name="Priority" column="priority" />
</class>
</hibernate-mapping>
GetJobQueueList.hbm.xml
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="Ucsb.Sa.Registrar.Dars.Daemon.Data"
namespace="Ucsb.Sa.Registrar.Dars.Daemon.Data">
<query name="DaemonThread.GetJobQueueList">
from Ucsb.Sa.Registrar.Dars.Daemon.Domain.JobQueueList list
where list.Status = :status
and list.ServerName = :servername
and ( list.StartDate <= :startdate or list.StartDate is null )
and ( list.StartTime <= :starttime or list.StartTime is null )
order by list.Priority, list.JobId
</query>
</hibernate-mapping>
Code between sessionFactory.openSession() and session.close():Code:
public List<IJobQueueList> GetJobQueueList(
JobRequestStatus status,
string serverName,
string startDate,
string startTime
) {
using( ISession session = DarsSessionFactory.OpenSession() ) {
IQuery query = session.GetNamedQuery( "DaemonThread.GetJobQueueList" )
.SetParameter( "status", status )
.SetString( "servername", serverName )
.SetString( "startdate", startDate )
.SetString( "starttime", startTime );
return (List<IJobQueueList>) query.List();
}
}
And, the JobQueueRequestStatusMappingUserType.cs (implements IUserType). Also, JobRequestStatus is an enum.
Code:
using System;
using System.Data;
using NHibernate.SqlTypes;
using NHibernate.UserTypes;
using Ucsb.Sa.Registrar.Dars.Daemon.Core.Domain;
namespace Ucsb.Sa.Registrar.Dars.Daemon.Data.UserTypes
{
public class JobRequestStatusMappingUserType : IUserType {
#region "variables"
private SqlType[] _SqlTypes;
private Type _ReturnedType;
private bool _IsMutable;
#endregion
#region "constructors"
public JobRequestStatusMappingUserType() {
_SqlTypes = new SqlType[] { new SqlType( DbType.String, 1 ) };
_ReturnedType = typeof( JobRequestStatus );
_IsMutable = false;
}
#endregion
#region
///<summary>
///
/// The SQL types for the columns mapped by this type.
///
///</summary>
///
public SqlType[] SqlTypes {
get { return _SqlTypes; }
protected set { _SqlTypes = value; }
}
///<summary>
///
/// The type returned by
///<c>NullSafeGet()</c>
///</summary>
///
public Type ReturnedType {
get { return _ReturnedType; }
protected set { _ReturnedType = value; }
}
///<summary>
///
/// Are objects of this type mutable?
///
///</summary>
///
public bool IsMutable {
get { return _IsMutable; }
protected set { _IsMutable = value; }
}
#endregion
#region "methods"
///<summary>
///
/// Compare two instances of the class mapped by this type for persistent "equality"
/// ie. equality of persistent state
///
///</summary>
///
///<param name="x"></param>
///<param name="y"></param>
///<returns>
///
///</returns>
///
public new bool Equals( object x, object y ) {
if( !( x is JobRequestStatus ) ) throw new ArgumentException( "x is not of type JobRequestStatus" );
if( !( y is JobRequestStatus ) ) throw new ArgumentException( "y is not of type JobRequestStatus" );
JobRequestStatus xJob = (JobRequestStatus) x;
JobRequestStatus yJob = (JobRequestStatus) y;
return xJob == yJob;
}
///<summary>
///
/// Get a hashcode for the instance, consistent with persistence "equality"
///
///</summary>
///
public int GetHashCode( object x ) {
return x.GetHashCode();
}
///<summary>
///
/// Retrieve an instance of the mapped class from a JDBC resultset.
/// Implementors should handle possibility of null values.
///
///</summary>
///
///<param name="rs">a IDataReader</param>
///<param name="names">column names</param>
///<param name="owner">the containing entity</param>
///<returns>
///
///</returns>
///
///<exception cref="T:NHibernate.HibernateException">HibernateException</exception>
public object NullSafeGet( IDataReader rs, string[] names, object owner ) {
int index = rs.GetOrdinal( names[0] );
if( rs.IsDBNull( index ) )
return JobRequestStatus.DegreeAudit;
else {
string s = rs[ index ].ToString();
foreach( JobRequestStatus status in Enum.GetValues( typeof( JobRequestStatus ) ) ) {
if( s.Equals( status.ToString() ) ) return status;
}
}
return JobRequestStatus.DegreeAudit;
}
///<summary>
///
/// Write an instance of the mapped class to a prepared statement.
/// Implementors should handle possibility of null values.
/// A multi-column type should be written to parameters starting from index.
///
///</summary>
///
///<param name="cmd">a IDbCommand</param>
///<param name="value">the object to write</param>
///<param name="index">command parameter index</param>
///<exception cref="T:NHibernate.HibernateException">HibernateException</exception>
public void NullSafeSet( IDbCommand cmd, object value, int index ) {
if( value == null || value.Equals( string.Empty ) )
( (IDbDataParameter) cmd.Parameters[index] ).Value = JobRequestStatusParser.ToString( JobRequestStatus.DegreeAudit );
else
( (IDbDataParameter) cmd.Parameters[index] ).Value = JobRequestStatusParser.ToString( (JobRequestStatus) value );
}
///<summary>
///
/// Return a deep copy of the persistent state, stopping at entities and at collections.
///
///</summary>
///
///<param name="value">generally a collection element or entity field</param>
///<returns>
///a copy
///</returns>
///
public object DeepCopy( object value ) {
return value;
}
///<summary>
///
/// During merge, replace the existing (<paramref name="target" />) value in the entity
/// we are merging to with a new (<paramref name="original" />) value from the detached
/// entity we are merging. For immutable objects, or null values, it is safe to simply
/// return the first parameter. For mutable objects, it is safe to return a copy of the
/// first parameter. For objects with component values, it might make sense to
/// recursively replace component values.
///
///</summary>
///
///<param name="original">the value from the detached entity being merged</param>
///<param name="target">the value in the managed entity</param>
///<param name="owner">the managed entity</param>
///<returns>
///the value to be merged
///</returns>
///
public object Replace( object original, object target, object owner ) {
object o = Disassemble( original );
o = Assemble( o, owner );
return o;
}
///<summary>
///
/// Reconstruct an object from the cacheable representation. At the very least this
/// method should perform a deep copy if the type is mutable. (optional operation)
///
///</summary>
///
///<param name="cached">the object to be cached</param>
///<param name="owner">the owner of the cached object</param>
///<returns>
///a reconstructed object from the cachable representation
///</returns>
///
public object Assemble( object cached, object owner ) {
return DeepCopy( cached );
}
///<summary>
///
/// Transform the object into its cacheable representation. At the very least this
/// method should perform a deep copy if the type is mutable. That may not be enough
/// for some implementations, however; for example, associations must be cached as
/// identifier values. (optional operation)
///
///</summary>
///
///<param name="value">the object to be cached</param>
///<returns>
///a cacheable representation of the object
///</returns>
///
public object Disassemble( object value ) {
return DeepCopy( value );
}
#endregion
}
}
The value 'C' in the error statement is from a CHAR(1) database column. It is an expected value for that column.
Also, this is the DEBUG level log from Spring.NET. Luckily, it does contain the full stack trace.
Code:
DaemonThreadRepositoryTests.GetJobQueueList : FailedUnable to read configuration section common/logging. Using no-op implemenation.
System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value 'C' to data type int.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.HasMoreRows()
at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.Read()
at NHibernate.Driver.NHybridDataReader.Read()
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.ADOException: could not execute query
[ select jobqueueli0_.instidq as instidq1_, jobqueueli0_.instid as instid1_, jobqueueli0_.instcd as instcd1_, jobqueueli0_.jobid as jobid1_, jobqueueli0_.userid as userid1_, jobqueueli0_.read_sysin as read6_1_, jobqueueli0_.startdate as startdate1_, jobqueueli0_.starttime as starttime1_, jobqueueli0_.servername as servername1_, jobqueueli0_.status as status1_, jobqueueli0_.priority as priority1_ from job_queue_list jobqueueli0_ where (jobqueueli0_.status=? )and(jobqueueli0_.servername=? )and((jobqueueli0_.startdate<=? )or(jobqueueli0_.startdate is null ))and((jobqueueli0_.starttime<=? )or(jobqueueli0_.starttime is null )) order by jobqueueli0_.priority , jobqueueli0_.jobid ]
Name: starttime - Value: null
Name: servername - Value: null
Name: status - Value: DegreeAudit
Name: startdate - Value: null
[SQL: select jobqueueli0_.instidq as instidq1_, jobqueueli0_.instid as instid1_, jobqueueli0_.instcd as instcd1_, jobqueueli0_.jobid as jobid1_, jobqueueli0_.userid as userid1_, jobqueueli0_.read_sysin as read6_1_, jobqueueli0_.startdate as startdate1_, jobqueueli0_.starttime as starttime1_, jobqueueli0_.servername as servername1_, jobqueueli0_.status as status1_, jobqueueli0_.priority as priority1_ from job_queue_list jobqueueli0_ where (jobqueueli0_.status=? )and(jobqueueli0_.servername=? )and((jobqueueli0_.startdate<=? )or(jobqueueli0_.startdate is null ))and((jobqueueli0_.starttime<=? )or(jobqueueli0_.starttime is null )) order by jobqueueli0_.priority , jobqueueli0_.jobid]
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
at NHibernate.Hql.Classic.QueryTranslator.List(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Impl.SessionImpl.Find(String query, QueryParameters parameters, IList results)
at NHibernate.Impl.SessionImpl.Find(String query, QueryParameters parameters)
at NHibernate.Impl.QueryImpl.List()
at Ucsb.Sa.Registrar.Dars.Daemon.Data.DaemonThreadRepository.GetJobQueueList(JobRequestStatus status, String serverName, String startDate, String startTime) in DaemonThreadRepository.cs:line 78
at Ucsb.Sa.Registrar.Dars.Daemon.Data.Tests.DaemonThreadRepositoryTests.GetJobQueueList() in DaemonThreadRepositoryTests.cs:line 29
Name and version of the database you are using:SQL Server 2005 with the current Service Pack (SP2??)
The generated SQL (show_sql=true):In lou of the actual SQL (which should be in the error message), this is instead the SQL code transmitted to SQL Server. The error message seems to be explained by this code (which was captured using SQL Profiler)
Code:
exec sp_executesql N'select jobqueueli0_.instidq as instidq1_, jobqueueli0_.instid as instid1_, jobqueueli0_.instcd as instcd1_, jobqueueli0_.jobid as jobid1_, jobqueueli0_.userid as userid1_, jobqueueli0_.read_sysin as read6_1_, jobqueueli0_.startdate as startdate1_, jobqueueli0_.starttime as starttime1_, jobqueueli0_.servername as servername1_, jobqueueli0_.status as status1_, jobqueueli0_.priority as priority1_ from job_queue_list jobqueueli0_ where (jobqueueli0_.status=@p0 )and(jobqueueli0_.servername=@p1 )and((jobqueueli0_.startdate<=@p2 )or(jobqueueli0_.startdate is null ))and((jobqueueli0_.starttime<=@p3 )or(jobqueueli0_.starttime is null )) order by jobqueueli0_.priority , jobqueueli0_.jobid',N'@p0 int,@p1 nvarchar(4000),@p2 nvarchar(4000),@p3 nvarchar(4000)',@p0=0,@p1=NULL,@p2=NULL,@p3=NULL
The parameter list definitions that are transmitted across are describing parameter @p0 as an "int". But, that column is a char(1). Unfortunately, I don't know how to properly describe this to Hibernate.
Debug level Hibernate log excerpt:
It looks like this may be included in the
Full stack trace of any exception that occurs: section.
If I am missing something that should be attached, just let me know and I will try to attach it ASAP. Also, any tips on how to simplify things, or write better code, or best practices that I am missing would be appreciated.
And, thank you for any time you give to help solve this.
[Updated]
I did read through a few posts before adding this one. But, I would love for someone to point out something I missed if the answer lies in one of these:
http://forum.hibernate.org/viewtopic.ph ... rtype+enum
http://www.hibernate.org/272.html
Login
FAQ
Search
