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
