Why are you using quotes for the inner SELECT? You now have a query
without any parameters, hence the error.
Niels
-----Original Message-----
From: Tomoiaga, Alin [mailto:alin.tomoiaga@ttu.edu]
Sent: Monday, October 06, 2008 11:42 PM
To: user-java@ibatis.apache.org
Subject: RE: linked server with list parameter
Thank you for the reply.
After enabling debugging, this is the statement that is being generated
(my parameter list is comprised of two elements):
select * from openquery(LINKED_SERVER,
'select ID from REMOTE_TABLE
where NAME in( ?, ?)
') RT
left outer join LOCAL_TABLE LT on (RT.ID = LT.ID)
This fails with the error message:
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the linkedServerWithListParameter -InlineParameterMap.
--- Check the parameter mapping for the '[0]' property.
--- Cause: java.sql.SQLException: Invalid parameter index 1.
Alin
-----Original Message-----
From: Jeff Butler [mailto:jeffgbutler@gmail.com]
Sent: Monday, October 06, 2008 3:30 PM
To: user-java@ibatis.apache.org
Subject: Re: linked server with list parameter
Have you enabled logging to see what statement is being generated? It
would probably help.
Jeff Butler
On Mon, Oct 6, 2008 at 2:50 PM, Tomoiaga, Alin <alin.tomoiaga@ttu.edu>
wrote:
> Hi,
>
> My previous statement was missing a quotation mark. I fixed it
> below, but my initial problem remains.
>
>
>
> <statement id="linkedServerWithListParameter" parameterClass="list"
>
> resultMap="correctResultMap">
>
> select * from openquery(LINKED_SERVER,
>
> 'select ID from REMOTE_TABLE
>
> <dynamic prepend=" where ">
>
> <iterate open=" NAME in(" close=")" conjunction=",">
>
> #[]#
>
> </iterate>
>
> </dynamic>
>
> ') RT
>
> left outer join LOCAL_TABLE LT on (RT.ID = LT.ID)
>
> <statement>
>
>
>
> Thank you,
>
> Alin
>
>
>
>
>
> ________________________________
>
> From: Tomoiaga, Alin [mailto:alin.tomoiaga@ttu.edu]
> Sent: Friday, October 03, 2008 4:31 PM
> To: user-java@ibatis.apache.org
> Subject: linked server with list parameter
>
>
>
> Hi,
>
> I am trying to join two tables across a linked server
while
> iterating through a list parameter .
>
>
>
> I haven't been able to find the right syntax for this to
work.
>
> The below statement is not working:
>
>
>
> <statement id="linkedServerWithListParameter" parameterClass="list"
> resultMap="correctResultMap">
>
> select * from openquery(LINKED_SERVER,
>
> 'select ID from REMOTE_TABLE
>
> <dynamic prepend=" where ">
>
> <iterate open=" NAME in(" close=") conjunction=",">
>
> #[]#
>
> </iterate>
>
> </dynamic>
>
> ) RT
>
> left outer join LOCAL_TABLE LT on (RT.ID = LT.ID)
>
> <statement>
>
>
>
>
>
> I tried different combinations of quotes and apostrophes,
but it
> didn't fix it.
>
> Due to my system configuration, "openquery" has to be used
for
> the remote query (cannot use a four-part name).
>
>
>
> Does anyone know how to accomplish this? Any help would be
> appreciated.
>
>
>
> Thank you,
>
> Alin
>
> Texas Tech University
|