ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Yee, Richard K CTR DMDC" <Richard.Yee....@osd.pentagon.mil>
Subject RE: Question using order by
Date Mon, 06 Aug 2007 15:52:29 GMT
Davy,
I suggest turning on the debugging logs for java.sql so that you can
output the prepared statement. It appears to me that your WHERE clause
is where the problem is. Do you have anything that has "ORDER BY
ENDDATE"? 
The SQL Error that is being returned refers to the improper SQL that is
in the prepared statement "where;DER BY ENDDATE;" 

You could put your original query from the code you are converting into
a <select> tag and it would work fine.

 <select id=".........etc....
 select refnumber, rownumber, begindate, enddate, officenumber from
chema1}.office
SELECT refnumber, rownumber, begindate, enddate, officenumber FROM
office WHERE
 refnumber LIKE ? ORDER BY substr(refnumber,16,12) DESC, refnumber,
begindate DESC
</select>

And it would work fine.

-R

-----Original Message-----
From: davy [mailto:davypulinckx@telenet.be] 
Sent: Monday, August 06, 2007 7:18 AM
To: user-java@ibatis.apache.org
Subject: Re: Question using order by

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