ORA-01830: date format picture ends before converting entire input string.

[kumpfi]

Hello,

i have the following problem:

When i want to get objects between a special date i get the error,
that nhibernate could not execute query with the ORA-01830: date
format picture ends before converting entire input string.

When i copy the generated SQL string into SQL developer and excecute
query, the query runs without errors.

Here the data of the project:

Hibernate version:

2.0

Mapping documents:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-
lazy="false">

<class name="Flexline.Server.Data.Flexline.Entity.ProductsEntity,
Flexline.Server" table="PRODUCTS" >

<id name="Id" column="MATRIX_CODE_ID" type="System.String"><generator
class="assigned"/></id>

<property name="Aufnr" column="AUFNR" access="field.pascalcase-
underscore" not-null="false" type="System.String" insert="true"
update="true"/>
<property name="State" column="STATE" access="field.pascalcase-
underscore" not-null="false" type="System.String" insert="true"
update="true"/>
<property name="TimeFinished" column="TIME_FINISHED"
access="field.pascalcase-underscore" not-null="false"
type="System.DateTime" insert="true" update="true"/>
<property name="TimeMainlineDuration" column="TIME_MAINLINE_DURATION"
access="field.pascalcase-underscore" not-null="false"
type="System.Decimal" insert="true" update="true"/>

</class>
</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():



public IList GetByHQL(string hqlString)
{
IList items = null;
ISession session = SessionProvider.Instance.GetSession();

try
{
IQuery query = session.CreateQuery(hqlString);
items = query.List();

return items;
}
catch (Exception ex)
{
log.Fatal(ex.ToString());
//Logger.WriteToLog(Logger.GenerateDefaultLogFileName
("Waferhandler"), ex.ToString(), true, "error");
throw new DataAccessException("Error getting items by
HQL", ex);
}
finally
{
SessionProvider.Instance.CloseSession();
}
}




Full stack trace of any exception that occurs:



ex.ToString() "NHibernate.ADOException: could not execute query\r\n
[ select productsen0_.MATRIX_CODE_ID as MATRIX1_1_, productsen0_.AUFNR
as AUFNR1_, productsen0_.CHARGE as CHARGE1_,
productsen0_.COUNT_LIGHTUP as COUNT4_1_, productsen0_.COUNT_REWORK as
COUNT5_1_, productsen0_.DISCRETE_JOB_ID as DISCRETE6_1_,
productsen0_.EXPOSURE_NR_FIRST as EXPOSURE7_1_,
productsen0_.EXPOSURE_NR_LAST as EXPOSURE8_1_, productsen0_.IC as
IC1_, productsen0_.LED_COLOR as LED10_1_, productsen0_.ROW_NR as
ROW11_1_, productsen0_.STATE as STATE1_, productsen0_.STATE_NEXT as
STATE13_1_, productsen0_.TIME_FINISHED as TIME14_1_,
productsen0_.TIME_LIGHTUP as TIME15_1_,
productsen0_.TIME_MAINLINE_DURATION as TIME16_1_,
productsen0_.TIME_MAINLINE_END as TIME17_1_,
productsen0_.TIME_MAINLINE_START as TIME18_1_,
productsen0_.TIME_PLANNING as TIME19_1_, productsen0_.TIME_PRELINE as
TIME20_1_, productsen0_.TIME_REWORK as TIME21_1_ from PRODUCTS
productsen0_ where (productsen0_.TIME_MAINLINE_DURATION is not null )
and(productsen0_.TIME_FINISHED<'29.11.2008 14:32:02' )and
(productsen0_.TIME_FINISHED>'27.11.2008 14:32:02' ) ]\r\n[SQL: select
productsen0_.MATRIX_CODE_ID as MATRIX1_1_, productsen0_.AUFNR as
AUFNR1_, productsen0_.CHARGE as CHARGE1_, productsen0_.COUNT_LIGHTUP
as COUNT4_1_, productsen0_.COUNT_REWORK as COUNT5_1_,
productsen0_.DISCRETE_JOB_ID as DISCRETE6_1_,
productsen0_.EXPOSURE_NR_FIRST as EXPOSURE7_1_,
productsen0_.EXPOSURE_NR_LAST as EXPOSURE8_1_, productsen0_.IC as
IC1_, productsen0_.LED_COLOR as LED10_1_, productsen0_.ROW_NR as
ROW11_1_, productsen0_.STATE as STATE1_, productsen0_.STATE_NEXT as
STATE13_1_, productsen0_.TIME_FINISHED as TIME14_1_,
productsen0_.TIME_LIGHTUP as TIME15_1_,
productsen0_.TIME_MAINLINE_DURATION as TIME16_1_,
productsen0_.TIME_MAINLINE_END as TIME17_1_,
productsen0_.TIME_MAINLINE_START as TIME18_1_,
productsen0_.TIME_PLANNING as TIME19_1_, productsen0_.TIME_PRELINE as
TIME20_1_, productsen0_.TIME_REWORK as TIME21_1_ from PRODUCTS
productsen0_ where (productsen0_.TIME_MAINLINE_DURATION is not null )
and(productsen0_.TIME_FINISHED<'29.11.2008 14:32:02' )and
(productsen0_.TIME_FINISHED>'27.11.2008 14:32:02' )] --->
System.Data.OracleClient.OracleException: ORA-01830:
Datumsformatstruktur endet vor Umwandlung der gesamten
Eingabezeichenfolge\n\r\n bei
System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle
errorHandle, Int32 rc)\r\n bei
System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle
statementHandle, CommandBehavior behavior, Boolean needRowid,
OciRowidDescriptor& rowidDescriptor, ArrayList&
resultParameterOrdinals)\r\n bei
System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle
statementHandle, CommandBehavior behavior, ArrayList&
resultParameterOrdinals)\r\n bei
System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior
behavior)\r\n bei
System.Data.OracleClient.OracleCommand.ExecuteDbDataReader
(CommandBehavior behavior)\r\n bei
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()\r
\n bei NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd)
\r\n bei NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean
autoDiscoverTypes, Boolean callable, RowSelection selection,
ISessionImplementor session)\r\n bei NHibernate.Loader.Loader.DoQuery
(ISessionImplementor session, QueryParameters queryParameters, Boolean
returnProxies)\r\n bei
NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections
(ISessionImplementor session, QueryParameters queryParameters, Boolean
returnProxies)\r\n bei NHibernate.Loader.Loader.DoList
(ISessionImplementor session, QueryParameters queryParameters)\r\n ---
Ende der internen Ausnahmestapelüberwachung ---\r\n bei
NHibernate.Loader.Loader.DoList(ISessionImplementor session,
QueryParameters queryParameters)\r\n bei
NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor
session, QueryParameters queryParameters)\r\n bei
NHibernate.Loader.Loader.List(ISessionImplementor session,
QueryParameters queryParameters, ISet`1 querySpaces, IType[]
resultTypes)\r\n bei NHibernate.Hql.Classic.QueryTranslator.List
(ISessionImplementor session, QueryParameters queryParameters)\r\n bei
NHibernate.Engine.Query.HQLQueryPlan.PerformList(QueryParameters
queryParameters, ISessionImplementor session, IList results)\r\n bei
NHibernate.Impl.SessionImpl.List(String query, QueryParameters
queryParameters, IList results)\r\n bei
NHibernate.Impl.SessionImpl.List(String query, QueryParameters
parameters)\r\n bei NHibernate.Impl.QueryImpl.List()\r\n bei
Flexline.Server.Interface.BaseDAO.GetByHQL(String hqlString) in C:\
\NConstructProjects\\Flexline\\Src\\Flexline\\Flexline.Server\
\Interface\\HandleStorage.cs:Zeile 189." string




Name and version of the database you are using:

Oracle 10g

The generated SQL (show_sql=true):


{select productsen0_.MATRIX_CODE_ID as MATRIX1_1_, productsen0_.AUFNR
as AUFNR1_, productsen0_.CHARGE as CHARGE1_,
productsen0_.COUNT_LIGHTUP as COUNT4_1_, productsen0_.COUNT_REWORK as
COUNT5_1_, productsen0_.DISCRETE_JOB_ID as DISCRETE6_1_,
productsen0_.EXPOSURE_NR_FIRST as EXPOSURE7_1_,
productsen0_.EXPOSURE_NR_LAST as EXPOSURE8_1_, productsen0_.IC as
IC1_, productsen0_.LED_COLOR as LED10_1_, productsen0_.ROW_NR as
ROW11_1_, productsen0_.STATE as STATE1_, productsen0_.STATE_NEXT as
STATE13_1_, productsen0_.TIME_FINISHED as TIME14_1_,
productsen0_.TIME_LIGHTUP as TIME15_1_,
productsen0_.TIME_MAINLINE_DURATION as TIME16_1_,
productsen0_.TIME_MAINLINE_END as TIME17_1_,
productsen0_.TIME_MAINLINE_START as TIME18_1_,
productsen0_.TIME_PLANNING as TIME19_1_, productsen0_.TIME_PRELINE as
TIME20_1_, productsen0_.TIME_REWORK as TIME21_1_ from PRODUCTS
productsen0_ where (productsen0_.TIME_MAINLINE_DURATION is not null )
and(productsen0_.TIME_FINISHED<'29.11.2008 14:32:02' )and
(productsen0_.TIME_FINISHED>'27.11.2008 14:32:02' )}

[Fabio Maulo]

You are using something to generate the mapping.

In the class or in the HQL there is something wrong.

Check the type of this two properties:

TimeFinished

TimeMainlineDuration

Ensure you are using query.SetDateTime("parameterName", value).

P.S. The query you sent us is not related to the mapping you sent.

2009/3/9 kumpfi <tobias.ku...@googlemail.com>

--
Fabio Maulo