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:[EMAIL PROTECTED] Sent: Monday, October 06, 2008 11:55 PM To: [email protected] 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:[EMAIL PROTECTED] Sent: Monday, October 06, 2008 4:47 PM To: [email protected] 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:[EMAIL PROTECTED] Sent: Monday, October 06, 2008 11:42 PM To: [email protected] 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:[EMAIL PROTECTED] Sent: Monday, October 06, 2008 3:30 PM To: [email protected] 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 <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED] > Sent: Friday, October 03, 2008 4:31 PM > To: [email protected] > 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
