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 Jeff Butler <je...@gmail.com> on 2008/10/06 22:30:26 UTC
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 <al...@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
RE: linked server with list parameter
Posted by Niels Beekman <n....@wis.nl>.
I'm not familiar with the openquery syntax, you should at least get it
working by using $'s instead of #'s, but that I don't know what that
does with your query plans. Also, be sure that you are checking input,
since this solution is vulnerable to SQL injection (no problem if the
list contains only numbers).
Niels
-----Original Message-----
From: Tomoiaga, Alin [mailto:alin.tomoiaga@ttu.edu]
Sent: Monday, October 06, 2008 11:55 PM
To: user-java@ibatis.apache.org
Subject: RE: linked server with list parameter
Do you mean the openquery syntax?
select * from openquery(LINKED_SERVER, 'remote query') ?
I was trying to mimic the openquery syntax in ibatis.
Is there another way to do this with ibatis?
Thank you,
Alin
-----Original Message-----
From: Niels Beekman [mailto:n.beekman@wis.nl]
Sent: Monday, October 06, 2008 4:47 PM
To: user-java@ibatis.apache.org
Subject: RE: linked server with list parameter
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 <al...@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
RE: linked server with list parameter
Posted by "Tomoiaga, Alin" <al...@ttu.edu>.
Do you mean the openquery syntax?
select * from openquery(LINKED_SERVER, 'remote query') ?
I was trying to mimic the openquery syntax in ibatis.
Is there another way to do this with ibatis?
Thank you,
Alin
-----Original Message-----
From: Niels Beekman [mailto:n.beekman@wis.nl]
Sent: Monday, October 06, 2008 4:47 PM
To: user-java@ibatis.apache.org
Subject: RE: linked server with list parameter
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 <al...@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
RE: linked server with list parameter
Posted by Niels Beekman <n....@wis.nl>.
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 <al...@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
RE: linked server with list parameter
Posted by "Tomoiaga, Alin" <al...@ttu.edu>.
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 <al...@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