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 "Angso, Tony" <To...@medco.com> on 2008/03/06 20:42:40 UTC

Need Help on Complex WHERE CLAUSE

Am trying to pass a complex WHERE CLAUSE

sqlMap is 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap>
	
	<parameterMap id="rxlocParam" class="map">
		<parameter property="rx" jdbcType="VARCHAR"
javaType="java.lang.String" />
		<parameter property="loc" jdbcType="VARCHAR"
javaType="java.lang.String" />
	</parameterMap>
	<select id="getEISPolyScript" parameterMap="rxlocParam"
resultClass="java.util.HashMap">
		SELECT F_DOCNUMBER_ID, TIFF_DOC_ID, DOC_COMMITTAL_DTE,
F_LIBRARY_ID
		FROM IM_RX
		WHERE RXNUM_TXT =   #rx#
		   AND LOCATION_CDE = #loc#
	</select>
</sqlMap>

Java code is
		Map map = new HashMap();
		map.put("rx", "0101402722");
		map.put("loc","03");
		
		list = sqlMap.queryForList("getEISPolyScript", map);
// <-- error here
		System.out.println("Selected " + list.size() + "
records.");



Getting this error
[3/6/08 14:25:17:541 EST] 00000026 SystemErr     R
com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred in WEB-INF/config/uv_sqlmap.xml.  
--- The error occurred while applying a parameter map.  
--- Check the getEISPolyScript-InlineParameterMap.  
--- Check the statement (query failed).  
--- Cause: java.sql.SQLException: ORA-00942: table or view does not
exist


Thanks for any help.

Tony

RE: Need Help on Complex WHERE CLAUSE

Posted by Sundar Sankaranarayanan <Su...@phoenix.edu>.
If the obvious fact of table not existing was not missed then I am
guessing it could be that the username with whom u r trying to connect
to from the application, doesn't have access to these tables/schema and
u can probably query these tables with your development login access .
My 2 cents!!!

-S

-----Original Message-----
From: Jared Blitzstein [mailto:mailing-list@blitzstein.net] 
Sent: Thursday, March 06, 2008 12:46 PM
To: user-java@ibatis.apache.org
Subject: Re: Need Help on Complex WHERE CLAUSE

Here is a silly question, but did you investigate "--- Cause:  
java.sql.SQLException: ORA-00942: table or view does exist" not at all?
Turn on log4j and see what statement it's executing.


On Mar 6, 2008, at 2:42 PM, Angso, Tony wrote:

> Am trying to pass a complex WHERE CLAUSE
>
> sqlMap is
>
> <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sqlMap PUBLIC 
> "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
> "http://ibatis.apache.org/dtd/sql-map-2.dtd">
> <sqlMap>
> 	
> 	<parameterMap id="rxlocParam" class="map">
> 		<parameter property="rx" jdbcType="VARCHAR"
> javaType="java.lang.String" />
> 		<parameter property="loc" jdbcType="VARCHAR"
> javaType="java.lang.String" />
> 	</parameterMap>
> 	<select id="getEISPolyScript" parameterMap="rxlocParam"
> resultClass="java.util.HashMap">
> 		SELECT F_DOCNUMBER_ID, TIFF_DOC_ID, DOC_COMMITTAL_DTE,
F_LIBRARY_ID
> 		FROM IM_RX
> 		WHERE RXNUM_TXT =   #rx#
> 		   AND LOCATION_CDE = #loc#
> 	</select>
> </sqlMap>
>
> Java code is
> 		Map map = new HashMap();
> 		map.put("rx", "0101402722");
> 		map.put("loc","03");
> 		
> 		list = sqlMap.queryForList("getEISPolyScript", map); //
<-- error 
> here
> 		System.out.println("Selected " + list.size() + "
> records.");
>
>
>
> Getting this error
> [3/6/08 14:25:17:541 EST] 00000026 SystemErr     R
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in WEB-INF/config/uv_sqlmap.xml.
> --- The error occurred while applying a parameter map.
> --- Check the getEISPolyScript-InlineParameterMap.
> --- Check the statement (query failed).
> --- Cause: java.sql.SQLException: ORA-00942: table or view does not 
> exist
>
>
> Thanks for any help.
>
> Tony

Re: Need Help on Complex WHERE CLAUSE

Posted by Nathan Maves <na...@gmail.com>.
Tony,

This is correct if you are using parameter maps.  If you are using inline
paramter maps you would still use #rx#.

Nathan

On Thu, Mar 6, 2008 at 1:48 PM, Angso, Tony <To...@medco.com> wrote:

> I found the problem...
>
>                WHERE RXNUM_TXT =   #rx#
>                   AND LOCATION_CDE = #loc#
>
> Should be replaced with
>
>                WHERE RXNUM_TXT =  ?
>                   AND LOCATION_CDE = ?
>
>
> And it work...
> Am new to ibatis and currently just playing with it... So thanks for all
> for answering my "newbie" question...
>
> Thanks
>
>
> Tony
>
>
> -----Original Message-----
> From: Angso, Tony
> Sent: Thursday, March 06, 2008 3:09 PM
> To: 'user-java@ibatis.apache.org'
> Subject: RE: Need Help on Complex WHERE CLAUSE
>
>
> Found the error, my sqlMap object is poitning to a different instance of
> Oracle Database, hence it can't find the table... So I fixed that... Now
> it shows a different error..
>
> [3/6/08 15:01:53:682 EST] 00000026 SystemErr     R
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in WEB-INF/config/uv_sqlmap.xml.
> --- The error occurred while applying a parameter map.
> --- Check the rxlocParam.
> --- Check the statement (query failed).
> --- Cause: java.sql.SQLException: ORA-00911: invalid character
>
>  <?xml version="1.0" encoding="UTF-8"?>
>  <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
>  "http://ibatis.apache.org/dtd/sql-map-2.dtd">
>  <sqlMap>
>
>        <parameterMap id="rxlocParam" class="map">
>                <parameter property="rx" jdbcType="VARCHAR"
>  javaType="java.lang.String" />
>                <parameter property="loc" jdbcType="VARCHAR"
>  javaType="java.lang.String" />
>        </parameterMap>
>        <select id="getEISPolyScript" parameterMap="rxlocParam"
>  resultClass="java.util.HashMap">
>                SELECT F_DOCNUMBER_ID, TIFF_DOC_ID, DOC_COMMITTAL_DTE,
> F_LIBRARY_ID
>                FROM IM_RX
>                WHERE RXNUM_TXT =   #rx#
>                   AND LOCATION_CDE = #loc#
>        </select>
>  </sqlMap>
>
> What is the invalid character????
>
> With the above sqlMap,
>
> Map map = new HashMap();
> map.put("rx", "0101402722");
> map.put("loc","03");
>
> list = sqlMap.queryForList("getEISPolyScript", map); // <-- error here
> System.out.println("Selected " + list.size() + " records.");
>
> Is it allowed to use sqlMap.queryForList()?
>
> Thanks for all the patience...
>
> Tony
>
>
>
> -----Original Message-----
> From: Jared Blitzstein [mailto:mailing-list@blitzstein.net]
> Sent: Thursday, March 06, 2008 2:46 PM
> To: user-java@ibatis.apache.org
> Subject: Re: Need Help on Complex WHERE CLAUSE
>
>
> Here is a silly question, but did you investigate "--- Cause:
> java.sql.SQLException: ORA-00942: table or view does exist" not at
> all? Turn on log4j and see what statement it's executing.
>
>
> On Mar 6, 2008, at 2:42 PM, Angso, Tony wrote:
>
> > Am trying to pass a complex WHERE CLAUSE
> >
> > sqlMap is
> >
> > <?xml version="1.0" encoding="UTF-8"?>
> > <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
> > "http://ibatis.apache.org/dtd/sql-map-2.dtd">
> > <sqlMap>
> >
> >       <parameterMap id="rxlocParam" class="map">
> >               <parameter property="rx" jdbcType="VARCHAR"
> > javaType="java.lang.String" />
> >               <parameter property="loc" jdbcType="VARCHAR"
> > javaType="java.lang.String" />
> >       </parameterMap>
> >       <select id="getEISPolyScript" parameterMap="rxlocParam"
> > resultClass="java.util.HashMap">
> >               SELECT F_DOCNUMBER_ID, TIFF_DOC_ID, DOC_COMMITTAL_DTE,
> F_LIBRARY_ID
> >               FROM IM_RX
> >               WHERE RXNUM_TXT =   #rx#
> >                  AND LOCATION_CDE = #loc#
> >       </select>
> > </sqlMap>
> >
> > Java code is
> >               Map map = new HashMap();
> >               map.put("rx", "0101402722");
> >               map.put("loc","03");
> >
> >               list = sqlMap.queryForList("getEISPolyScript", map);
> > // <-- error here
> >               System.out.println("Selected " + list.size() + "
> records.");
> >
> >
> >
> > Getting this error
> > [3/6/08 14:25:17:541 EST] 00000026 SystemErr     R
> > com.ibatis.common.jdbc.exception.NestedSQLException:
> > --- The error occurred in WEB-INF/config/uv_sqlmap.xml.
> > --- The error occurred while applying a parameter map.
> > --- Check the getEISPolyScript-InlineParameterMap.
> > --- Check the statement (query failed).
> > --- Cause: java.sql.SQLException: ORA-00942: table or view does not
> > exist
> >
> >
> > Thanks for any help.
> >
> > Tony
>
>
> -----------------------------------------
> This e-mail message and any attachments contain confidential
> information from Medco. If you are not the intended recipient, you
> are hereby notified that disclosure, printing, copying,
> distribution, or the taking of any action in reliance on the
> contents of this electronic information is strictly prohibited. If
> you have received this e-mail message in error, please immediately
> notify the sender by reply message and then delete the electronic
> message and any attachments.
>

RE: Need Help on Complex WHERE CLAUSE

Posted by "Angso, Tony" <To...@medco.com>.
I found the problem...

 		WHERE RXNUM_TXT =   #rx#
 		   AND LOCATION_CDE = #loc#

Should be replaced with 

 		WHERE RXNUM_TXT =  ?
 		   AND LOCATION_CDE = ?


And it work...
Am new to ibatis and currently just playing with it... So thanks for all
for answering my "newbie" question...

Thanks


Tony


-----Original Message-----
From: Angso, Tony 
Sent: Thursday, March 06, 2008 3:09 PM
To: 'user-java@ibatis.apache.org'
Subject: RE: Need Help on Complex WHERE CLAUSE


Found the error, my sqlMap object is poitning to a different instance of
Oracle Database, hence it can't find the table... So I fixed that... Now
it shows a different error..

[3/6/08 15:01:53:682 EST] 00000026 SystemErr     R
com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred in WEB-INF/config/uv_sqlmap.xml.  
--- The error occurred while applying a parameter map.  
--- Check the rxlocParam.  
--- Check the statement (query failed).  
--- Cause: java.sql.SQLException: ORA-00911: invalid character

 <?xml version="1.0" encoding="UTF-8"?>
 <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" 
 "http://ibatis.apache.org/dtd/sql-map-2.dtd">
 <sqlMap>
 	
 	<parameterMap id="rxlocParam" class="map">
 		<parameter property="rx" jdbcType="VARCHAR" 
 javaType="java.lang.String" />
 		<parameter property="loc" jdbcType="VARCHAR" 
 javaType="java.lang.String" />
 	</parameterMap>
 	<select id="getEISPolyScript" parameterMap="rxlocParam" 
 resultClass="java.util.HashMap">
 		SELECT F_DOCNUMBER_ID, TIFF_DOC_ID, DOC_COMMITTAL_DTE,
F_LIBRARY_ID
 		FROM IM_RX
 		WHERE RXNUM_TXT =   #rx#
 		   AND LOCATION_CDE = #loc#
 	</select>
 </sqlMap>

What is the invalid character????

With the above sqlMap,

Map map = new HashMap();
map.put("rx", "0101402722");
map.put("loc","03");
 		
list = sqlMap.queryForList("getEISPolyScript", map); // <-- error here
System.out.println("Selected " + list.size() + " records.");

Is it allowed to use sqlMap.queryForList()?

Thanks for all the patience...

Tony



-----Original Message-----
From: Jared Blitzstein [mailto:mailing-list@blitzstein.net] 
Sent: Thursday, March 06, 2008 2:46 PM
To: user-java@ibatis.apache.org
Subject: Re: Need Help on Complex WHERE CLAUSE


Here is a silly question, but did you investigate "--- Cause:  
java.sql.SQLException: ORA-00942: table or view does exist" not at  
all? Turn on log4j and see what statement it's executing.


On Mar 6, 2008, at 2:42 PM, Angso, Tony wrote:

> Am trying to pass a complex WHERE CLAUSE
>
> sqlMap is
>
> <?xml version="1.0" encoding="UTF-8"?>
> <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
> "http://ibatis.apache.org/dtd/sql-map-2.dtd">
> <sqlMap>
> 	
> 	<parameterMap id="rxlocParam" class="map">
> 		<parameter property="rx" jdbcType="VARCHAR"
> javaType="java.lang.String" />
> 		<parameter property="loc" jdbcType="VARCHAR" 
> javaType="java.lang.String" />
> 	</parameterMap>
> 	<select id="getEISPolyScript" parameterMap="rxlocParam" 
> resultClass="java.util.HashMap">
> 		SELECT F_DOCNUMBER_ID, TIFF_DOC_ID, DOC_COMMITTAL_DTE,
F_LIBRARY_ID
> 		FROM IM_RX
> 		WHERE RXNUM_TXT =   #rx#
> 		   AND LOCATION_CDE = #loc#
> 	</select>
> </sqlMap>
>
> Java code is
> 		Map map = new HashMap();
> 		map.put("rx", "0101402722");
> 		map.put("loc","03");
> 		
> 		list = sqlMap.queryForList("getEISPolyScript", map);
> // <-- error here
> 		System.out.println("Selected " + list.size() + "
records.");
>
>
>
> Getting this error
> [3/6/08 14:25:17:541 EST] 00000026 SystemErr     R
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in WEB-INF/config/uv_sqlmap.xml.
> --- The error occurred while applying a parameter map.
> --- Check the getEISPolyScript-InlineParameterMap.
> --- Check the statement (query failed).
> --- Cause: java.sql.SQLException: ORA-00942: table or view does not 
> exist
>
>
> Thanks for any help.
>
> Tony


-----------------------------------------
This e-mail message and any attachments contain confidential
information from Medco. If you are not the intended recipient, you
are hereby notified that disclosure, printing, copying,
distribution, or the taking of any action in reliance on the
contents of this electronic information is strictly prohibited. If
you have received this e-mail message in error, please immediately
notify the sender by reply message and then delete the electronic
message and any attachments.

RE: Need Help on Complex WHERE CLAUSE

Posted by "Angso, Tony" <To...@medco.com>.
Found the error, my sqlMap object is poitning to a different instance of
Oracle Database, hence it can't find the table...
So I fixed that... Now it shows a different error..

[3/6/08 15:01:53:682 EST] 00000026 SystemErr     R
com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred in WEB-INF/config/uv_sqlmap.xml.  
--- The error occurred while applying a parameter map.  
--- Check the rxlocParam.  
--- Check the statement (query failed).  
--- Cause: java.sql.SQLException: ORA-00911: invalid character

 <?xml version="1.0" encoding="UTF-8"?>
 <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" 
 "http://ibatis.apache.org/dtd/sql-map-2.dtd">
 <sqlMap>
 	
 	<parameterMap id="rxlocParam" class="map">
 		<parameter property="rx" jdbcType="VARCHAR" 
 javaType="java.lang.String" />
 		<parameter property="loc" jdbcType="VARCHAR" 
 javaType="java.lang.String" />
 	</parameterMap>
 	<select id="getEISPolyScript" parameterMap="rxlocParam" 
 resultClass="java.util.HashMap">
 		SELECT F_DOCNUMBER_ID, TIFF_DOC_ID, DOC_COMMITTAL_DTE,
F_LIBRARY_ID
 		FROM IM_RX
 		WHERE RXNUM_TXT =   #rx#
 		   AND LOCATION_CDE = #loc#
 	</select>
 </sqlMap>

What is the invalid character????

With the above sqlMap,

Map map = new HashMap();
map.put("rx", "0101402722");
map.put("loc","03");
 		
list = sqlMap.queryForList("getEISPolyScript", map); // <-- error here
System.out.println("Selected " + list.size() + " records.");

Is it allowed to use sqlMap.queryForList()?

Thanks for all the patience...

Tony



-----Original Message-----
From: Jared Blitzstein [mailto:mailing-list@blitzstein.net] 
Sent: Thursday, March 06, 2008 2:46 PM
To: user-java@ibatis.apache.org
Subject: Re: Need Help on Complex WHERE CLAUSE


Here is a silly question, but did you investigate "--- Cause:  
java.sql.SQLException: ORA-00942: table or view does exist" not at  
all? Turn on log4j and see what statement it's executing.


On Mar 6, 2008, at 2:42 PM, Angso, Tony wrote:

> Am trying to pass a complex WHERE CLAUSE
>
> sqlMap is
>
> <?xml version="1.0" encoding="UTF-8"?>
> <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" 
> "http://ibatis.apache.org/dtd/sql-map-2.dtd">
> <sqlMap>
> 	
> 	<parameterMap id="rxlocParam" class="map">
> 		<parameter property="rx" jdbcType="VARCHAR" 
> javaType="java.lang.String" />
> 		<parameter property="loc" jdbcType="VARCHAR" 
> javaType="java.lang.String" />
> 	</parameterMap>
> 	<select id="getEISPolyScript" parameterMap="rxlocParam" 
> resultClass="java.util.HashMap">
> 		SELECT F_DOCNUMBER_ID, TIFF_DOC_ID, DOC_COMMITTAL_DTE,
F_LIBRARY_ID
> 		FROM IM_RX
> 		WHERE RXNUM_TXT =   #rx#
> 		   AND LOCATION_CDE = #loc#
> 	</select>
> </sqlMap>
>
> Java code is
> 		Map map = new HashMap();
> 		map.put("rx", "0101402722");
> 		map.put("loc","03");
> 		
> 		list = sqlMap.queryForList("getEISPolyScript", map);
> // <-- error here
> 		System.out.println("Selected " + list.size() + "
records.");
>
>
>
> Getting this error
> [3/6/08 14:25:17:541 EST] 00000026 SystemErr     R
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in WEB-INF/config/uv_sqlmap.xml.
> --- The error occurred while applying a parameter map.
> --- Check the getEISPolyScript-InlineParameterMap.
> --- Check the statement (query failed).
> --- Cause: java.sql.SQLException: ORA-00942: table or view does not
> exist
>
>
> Thanks for any help.
>
> Tony

Re: Need Help on Complex WHERE CLAUSE

Posted by Jared Blitzstein <ma...@blitzstein.net>.
Here is a silly question, but did you investigate "--- Cause:  
java.sql.SQLException: ORA-00942: table or view does exist" not at  
all? Turn on log4j and see what statement it's executing.


On Mar 6, 2008, at 2:42 PM, Angso, Tony wrote:

> Am trying to pass a complex WHERE CLAUSE
>
> sqlMap is
>
> <?xml version="1.0" encoding="UTF-8"?>
> <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
> "http://ibatis.apache.org/dtd/sql-map-2.dtd">
> <sqlMap>
> 	
> 	<parameterMap id="rxlocParam" class="map">
> 		<parameter property="rx" jdbcType="VARCHAR"
> javaType="java.lang.String" />
> 		<parameter property="loc" jdbcType="VARCHAR"
> javaType="java.lang.String" />
> 	</parameterMap>
> 	<select id="getEISPolyScript" parameterMap="rxlocParam"
> resultClass="java.util.HashMap">
> 		SELECT F_DOCNUMBER_ID, TIFF_DOC_ID, DOC_COMMITTAL_DTE,
> F_LIBRARY_ID
> 		FROM IM_RX
> 		WHERE RXNUM_TXT =   #rx#
> 		   AND LOCATION_CDE = #loc#
> 	</select>
> </sqlMap>
>
> Java code is
> 		Map map = new HashMap();
> 		map.put("rx", "0101402722");
> 		map.put("loc","03");
> 		
> 		list = sqlMap.queryForList("getEISPolyScript", map);
> // <-- error here
> 		System.out.println("Selected " + list.size() + "
> records.");
>
>
>
> Getting this error
> [3/6/08 14:25:17:541 EST] 00000026 SystemErr     R
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in WEB-INF/config/uv_sqlmap.xml.
> --- The error occurred while applying a parameter map.
> --- Check the getEISPolyScript-InlineParameterMap.
> --- Check the statement (query failed).
> --- Cause: java.sql.SQLException: ORA-00942: table or view does not
> exist
>
>
> Thanks for any help.
>
> Tony

RE: Need Help on Complex WHERE CLAUSE

Posted by Jason Kratz <ja...@firm58.com>.
The answer it would seem is in the exception. That table isn't viewable
either because it doesn't exist or, more likely, that the user executing the
query in Oracle doesn't have permissions on that table.  Is the table being
queried in a different schema?

Jason

-----Original Message-----
From: Angso, Tony [mailto:Tony_Angso@medco.com] 
Sent: Thursday, March 06, 2008 1:43 PM
To: user-java@ibatis.apache.org
Subject: Need Help on Complex WHERE CLAUSE

Am trying to pass a complex WHERE CLAUSE

sqlMap is 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap>
	
	<parameterMap id="rxlocParam" class="map">
		<parameter property="rx" jdbcType="VARCHAR"
javaType="java.lang.String" />
		<parameter property="loc" jdbcType="VARCHAR"
javaType="java.lang.String" />
	</parameterMap>
	<select id="getEISPolyScript" parameterMap="rxlocParam"
resultClass="java.util.HashMap">
		SELECT F_DOCNUMBER_ID, TIFF_DOC_ID, DOC_COMMITTAL_DTE,
F_LIBRARY_ID
		FROM IM_RX
		WHERE RXNUM_TXT =   #rx#
		   AND LOCATION_CDE = #loc#
	</select>
</sqlMap>

Java code is
		Map map = new HashMap();
		map.put("rx", "0101402722");
		map.put("loc","03");
		
		list = sqlMap.queryForList("getEISPolyScript", map);
// <-- error here
		System.out.println("Selected " + list.size() + "
records.");



Getting this error
[3/6/08 14:25:17:541 EST] 00000026 SystemErr     R
com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred in WEB-INF/config/uv_sqlmap.xml.  
--- The error occurred while applying a parameter map.  
--- Check the getEISPolyScript-InlineParameterMap.  
--- Check the statement (query failed).  
--- Cause: java.sql.SQLException: ORA-00942: table or view does not
exist


Thanks for any help.

Tony