ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeff Butler" <jeffgbut...@gmail.com>
Subject Re: Question using order by
Date Mon, 06 Aug 2007 14:58:00 GMT
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 <davypulinckx@telenet.be> 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:ryee@cruzio.com]
> >Verzonden: maandag, augustus 6, 2007 04:08 PM
> >Aan: user-java@ibatis.apache.org
> >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
> >> l.BeheerExample@1e48fa7].  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:ryee@cruzio.com]
> >>> Verzonden: maandag, augustus 6, 2007 03:36 PM
> >>> Aan: user-java@ibatis.apache.org
> >>> 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
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>
> >>>
> >>
> >>
> >>
> >>
> >
> >
> >
>
>
>

Mime
View raw message