It works, really thanks a lot Jeff and Richard! May I ask why the query (order by, substr) did not work when it is written in the xml file? Is this a bug in DB2, wrong drivers??
However I have 1 question, the query works perfectly, but when it displays the information to the user, my begindate now displays for example (Mon May 14 14:42), in my previous code (not ibatis) it displays this (2007-05-14 14:42). Now, in my database this is a Timestamp field, in my code I have written this. RowBeheer[i].setBegindate(String.value(beheer2.getBegindate())); Is this the problem why my timestamp is not correct displayed (2007-05-14 14:42). ________________________________________ From: Jeff Butler [mailto:[EMAIL PROTECTED] Sent: maandag 6 augustus 2007 16:58 To: [email protected] Subject: Re: Question using order by This DB2 error is related to an invalid token in the SQL string. The DB2 type 4 driver does not like semicolons in SQL strings - that's probably the issue. However, you should be able to accomplish this without modifying anything that Abator generates. Here is an example of how it should work: OfficeExample example = new OfficeExample(); example.createCriteria().andRefnumberLike("%some_string%"); example.setOrderByClause("substr(refnumber,16,12) DESC, refnumber, begindate DESC"); List answer = officeDao.selectByExample(example); Jeff Butler On 8/6/07, davy <[EMAIL PROTECTED]> wrote: I have also searched in the db2 documentation for nr 42601. What is the best way to solve this problem? When i use my old code (not ibatis) it works fine, when i use ibatis it also works fine, but there must be a order by and a substr for getting the correct information to the user, and there is the problem, the order by and substr. >----- Oorspronkelijk bericht ----- >Van: Richard Yee [mailto:[EMAIL PROTECTED] >Verzonden: maandag, augustus 6, 2007 04:08 PM >Aan: [email protected] >Onderwerp: Re: Question using order by > >It seems that the where clause is where the problem is. > >-Richard > > >translate to as an error message in the DB2 documentation? > >-Richard > > > >davy wrote: >> You are right, sorry. >> >> Yes i have tried that : >> ORDER BY substr(refnumber,16,12) DESC, refnumber, begindate DESC >> >> I get the following error message in my stacktrace. >> >> com.ibatis.dao.client.DaoException: Failed to queryForList - id >> [BEHEER.selectByBeheerList1], parameterObject [mode >> [EMAIL PROTECTED] Cause: >> com.ibatis.common.jdbc.exception.NestedSQLException: >> --- The error occurred while applying a parameter map. >> --- Check the BEHEER.selectByBeheerList1-InlineParameterMap . >> --- Check the statement (query failed). >> --- Cause: com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -104, >> SQLSTATE: 42601, SQLERRMC: where;DER BY ENDDATE;<space> >> Caused by: com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -104, >> SQLSTATE: 42601, SQLERRMC: where;DER BY ENDDATE;<space> >> >> >> >> >> >>> ----- Oorspronkelijk bericht ----- >>> Van: Richard Yee [mailto:[EMAIL PROTECTED] >>> Verzonden: maandag, augustus 6, 2007 03:36 PM >>> Aan: [email protected] >>> Onderwerp: Re: Question using order by >>> >>> Have you tried putting >>> >>> select refnumber, rownumber, begindate, enddate, officenumber >>> from ${schema1}.office >>> <isParameterPresent> >>> <include refid="office.abatorgenerated_Example_Where_Clause"/> >>> <isNotNull property="orderByClause"> >>> </isNotNull> >>> </isParameterPresent> >>> ORDER BY substr(refnumber,16,12) DESC, refnumber, begindate DESC >>> >>> </select> >>> >>> in your SQL map? You haven't really explained what error or problem you are >>> having. That is probably why no one is responding. Are you getting any >>> errors? >>> >>> -Richard >>> >>> >>> >>> >>> >>> Jdev wrote: >>> >>>> Is there really nobody who can help me using an order by? >>>> I have searched on the Internet but I can find nothing useful. >>>> I have post this question a couple of days ago, but I have got still no >>>> answer. >>>> >>>> -------------------------------------------- >>>> >>>> >>>> I have the following query which must be converted to Ibatis. >>>> But I don't know how I must write the SUBSTR and DESC in Ibatis >>>> >>>> private final String select1 = >>>> "SELECT refnumber, rownumber, begindate, enddate, officenumber, " + >>>> FROM " + ConnectDB.schema1 + ".office " + >>>> "WHERE refnumber LIKE ? " + >>>> "ORDER BY substr(refnumber,16,12) DESC, refnumber, begindate DESC >>>> "; >>>> >>>> Abator has everything generated and everything works perfectly, except I >>>> don't know how to write the query in the Ibatis xml file for SUBSTR and >>>> DESC. >>>> Below a small query what Abator has generated and the query mentioned >>>> below must be extended to what above stands. >>>> Can someone explain me how or what is the best way to process the above >>>> query to my new Ibatis query mentioned below. >>>> >>>> <select id="………etc…. >>>> select refnumber, rownumber, begindate, enddate, officenumber >>>> from ${schema1}.office >>>> <isParameterPresent> >>>> <include refid="office.abatorgenerated_Example_Where_Clause"/> >>>> <isNotNull property="orderByClause"> >>>> order by $orderByClause$ >>>> </isNotNull> >>>> </isParameterPresent> >>>> </select> >>>> >>>> >>>> Thank you in advance, >>>> Davy >>>> >>>> >>>> >>>> >>>> >>> >>> >> >> >> >> > > >
