You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by xianwinwin <xi...@gmail.com> on 2007/04/16 23:25:39 UTC
what is the correct SQL for IN ?
Hi there,
I wonder how to implement this query in ibatis as it gives me an error
message.
query: ..... AND LG.state IN ('ny', 'bj') (in plain sql)
in ibatis:
<isNotNull prepend="AND" property="state">
LG.state IN #state:VARCHAR#
</isNotNull>
error: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
''(\'ny\', \'nj\')'
the state value is: "('ny', 'nj')"
thanks
--
View this message in context: http://www.nabble.com/what-is-the-correct-SQL-for-IN---tf3587326.html#a10024994
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
RE: what is the correct SQL for IN ?
Posted by xianwinwin <xi...@gmail.com>.
thanks.
I did this and it's working (with out the IN):
<isNotNull prepend="AND" property="states">
<iterate property="states"
open="(" close=")" conjunction="OR">
LG.state=#states[]#
</iterate>
</isNotNull>
Poitras Christian wrote:
>
> The iterate tag is explained in the documentation
> http://ibatis.apache.org/docs/java/pdf/iBATIS-SqlMaps-2_en.pdf
> On page 49.
> The complete doucmentation of dynamic sql starts on page 46.
>
> The notation ?property? Is used mainly in cases where the property must
> not be converted to a jdbc type.
> For example.
> <select resultMap="someMap" parameterClass="map">
> SELECT *
> FROM ?tableName?
> WHERE id = #id#
> </select>
>
> In your case, the best solution would look more like this.
> <select resultMap="someMap" parameterClass="myClass">
> SELECT *
> FROM LG
> WHERE LG.state
> <iterate property="stateList" prepend="IN" open="(" close=")"
> conjunction=",">
> #stateList[]#
> </iterate>
> </select>
>
> Christian
>
>
> -----Original Message-----
> From: xianwinwin [mailto:xianwinwin@gmail.com]
> Sent: Monday, 16 April 2007 18:21
> To: user-java@ibatis.apache.org
> Subject: RE: what is the correct SQL for IN ?
>
>
> thank you!
>
> IN ?state? WORKS!
>
> where in the docs does it explain this issue?
> thanks again
>
>
>
> Poitras Christian wrote:
>>
>> Make a List of states (or an array) and use the iterate tag (see the
>> documentation).
>>
>> Another possibility is to use
>> <isNotNull prepend="AND" property="state">
>> LG.state IN ?state?
>> </isNotNull>
>> But I don't recommend this in your case. You should really use the
>> iterate tag.
>>
>> Christian
>>
>> -----Original Message-----
>> From: xianwinwin [mailto:xianwinwin@gmail.com]
>> Sent: Monday, 16 April 2007 17:26
>> To: user-java@ibatis.apache.org
>> Subject: what is the correct SQL for IN ?
>>
>>
>> Hi there,
>>
>> I wonder how to implement this query in ibatis as it gives me an error
>
>> message.
>>
>> query: ..... AND LG.state IN ('ny', 'bj') (in plain sql)
>>
>> in ibatis:
>>
>> <isNotNull prepend="AND" property="state">
>> LG.state IN #state:VARCHAR#
>> </isNotNull>
>>
>>
>> error: You have an error in your SQL syntax; check the manual that
>> corresponds to your MySQL server version for the right syntax to use
>> near
>> ''(\'ny\', \'nj\')'
>>
>> the state value is: "('ny', 'nj')"
>>
>> thanks
>> --
>> View this message in context:
>> http://www.nabble.com/what-is-the-correct-SQL-for-IN---tf3587326.html#
>> a1
>> 0024994
>> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>>
>>
>>
>
> --
> View this message in context:
> http://www.nabble.com/what-is-the-correct-SQL-for-IN---tf3587326.html#a1
> 0025815
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
>
--
View this message in context: http://www.nabble.com/what-is-the-correct-SQL-for-IN---tf3587326.html#a10037979
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
RE: what is the correct SQL for IN ?
Posted by Poitras Christian <Ch...@ircm.qc.ca>.
The iterate tag is explained in the documentation
http://ibatis.apache.org/docs/java/pdf/iBATIS-SqlMaps-2_en.pdf
On page 49.
The complete doucmentation of dynamic sql starts on page 46.
The notation ?property? Is used mainly in cases where the property must
not be converted to a jdbc type.
For example.
<select resultMap="someMap" parameterClass="map">
SELECT *
FROM ?tableName?
WHERE id = #id#
</select>
In your case, the best solution would look more like this.
<select resultMap="someMap" parameterClass="myClass">
SELECT *
FROM LG
WHERE LG.state
<iterate property="stateList" prepend="IN" open="(" close=")"
conjunction=",">
#stateList[]#
</iterate>
</select>
Christian
-----Original Message-----
From: xianwinwin [mailto:xianwinwin@gmail.com]
Sent: Monday, 16 April 2007 18:21
To: user-java@ibatis.apache.org
Subject: RE: what is the correct SQL for IN ?
thank you!
IN ?state? WORKS!
where in the docs does it explain this issue?
thanks again
Poitras Christian wrote:
>
> Make a List of states (or an array) and use the iterate tag (see the
> documentation).
>
> Another possibility is to use
> <isNotNull prepend="AND" property="state">
> LG.state IN ?state?
> </isNotNull>
> But I don't recommend this in your case. You should really use the
> iterate tag.
>
> Christian
>
> -----Original Message-----
> From: xianwinwin [mailto:xianwinwin@gmail.com]
> Sent: Monday, 16 April 2007 17:26
> To: user-java@ibatis.apache.org
> Subject: what is the correct SQL for IN ?
>
>
> Hi there,
>
> I wonder how to implement this query in ibatis as it gives me an error
> message.
>
> query: ..... AND LG.state IN ('ny', 'bj') (in plain sql)
>
> in ibatis:
>
> <isNotNull prepend="AND" property="state">
> LG.state IN #state:VARCHAR#
> </isNotNull>
>
>
> error: You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near
> ''(\'ny\', \'nj\')'
>
> the state value is: "('ny', 'nj')"
>
> thanks
> --
> View this message in context:
> http://www.nabble.com/what-is-the-correct-SQL-for-IN---tf3587326.html#
> a1
> 0024994
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
>
--
View this message in context:
http://www.nabble.com/what-is-the-correct-SQL-for-IN---tf3587326.html#a1
0025815
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
RE: what is the correct SQL for IN ?
Posted by xianwinwin <xi...@gmail.com>.
thank you!
IN ?state? WORKS!
where in the docs does it explain this issue?
thanks again
Poitras Christian wrote:
>
> Make a List of states (or an array) and use the iterate tag (see the
> documentation).
>
> Another possibility is to use
> <isNotNull prepend="AND" property="state">
> LG.state IN ?state?
> </isNotNull>
> But I don't recommend this in your case. You should really use the
> iterate tag.
>
> Christian
>
> -----Original Message-----
> From: xianwinwin [mailto:xianwinwin@gmail.com]
> Sent: Monday, 16 April 2007 17:26
> To: user-java@ibatis.apache.org
> Subject: what is the correct SQL for IN ?
>
>
> Hi there,
>
> I wonder how to implement this query in ibatis as it gives me an error
> message.
>
> query: ..... AND LG.state IN ('ny', 'bj') (in plain sql)
>
> in ibatis:
>
> <isNotNull prepend="AND" property="state">
> LG.state IN #state:VARCHAR#
> </isNotNull>
>
>
> error: You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near
> ''(\'ny\', \'nj\')'
>
> the state value is: "('ny', 'nj')"
>
> thanks
> --
> View this message in context:
> http://www.nabble.com/what-is-the-correct-SQL-for-IN---tf3587326.html#a1
> 0024994
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
>
--
View this message in context: http://www.nabble.com/what-is-the-correct-SQL-for-IN---tf3587326.html#a10025815
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
RE: what is the correct SQL for IN ?
Posted by Poitras Christian <Ch...@ircm.qc.ca>.
Make a List of states (or an array) and use the iterate tag (see the
documentation).
Another possibility is to use
<isNotNull prepend="AND" property="state">
LG.state IN ?state?
</isNotNull>
But I don't recommend this in your case. You should really use the
iterate tag.
Christian
-----Original Message-----
From: xianwinwin [mailto:xianwinwin@gmail.com]
Sent: Monday, 16 April 2007 17:26
To: user-java@ibatis.apache.org
Subject: what is the correct SQL for IN ?
Hi there,
I wonder how to implement this query in ibatis as it gives me an error
message.
query: ..... AND LG.state IN ('ny', 'bj') (in plain sql)
in ibatis:
<isNotNull prepend="AND" property="state">
LG.state IN #state:VARCHAR#
</isNotNull>
error: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near
''(\'ny\', \'nj\')'
the state value is: "('ny', 'nj')"
thanks
--
View this message in context:
http://www.nabble.com/what-is-the-correct-SQL-for-IN---tf3587326.html#a1
0024994
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.