It's not a secret anymore!*
The moose likes JDBC and the fly likes Oracle:  Prepared Statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Oracle:  Prepared Statement" Watch "Oracle:  Prepared Statement" New topic
Author

Oracle: Prepared Statement

Butch Car
Greenhorn

Joined: Jan 12, 2001
Posts: 13
I am converting some string based SQL to prepared
statements. In one of the queries I need to
query against a list of team ids (integers). In
SQL I could just say where team in (1,2,3,4) and
everything would work fine. However in a prepared
statement when I build the 1,2,3,4 string and set
it in the statement, Oracle returns an 'ORA-01722: invalid number'
error.

e.g.

//Build statement
...
//Team ID List
sbSQL.append(" And T1.ITEAMID in (?) ");
...
_pstmt = con.prepareStatement(sbSQL.toString());
String sTeams = new String("1,2");
_pstmt.setString (1,sTeams);
...
_rs = _pstmt.executeQuery();
...

ORA-01722: invalid number

If I change sTeams to new String("1") everything
works fine. Does this have to do with the
comma separator? Any suggestions are welcome.
Thanks in advance
Butch Car
Greenhorn

Joined: Jan 12, 2001
Posts: 13
Based on other posts in this forum, I take it this
can't be done. Unless someone knows otherwise no
need to reply.
Thanks again
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

I beleive the setString(1,"1,2") acually inserts single quotes in to your query around the string that was set so when you throw in "1, 2" I believe it creates the query:
" And T1.ITEAMID in ('1,2') " which is not a valid query because there is no such number '1,2'
Jamie
p.s. it should work with one number because DB's usually allow a for single quotes around a number even for numeric columns because they will cast the valid strings to numbers(ie " And T1.ITEAMID in ('2') ")
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Oracle: Prepared Statement
 
Similar Threads
java.sql.SQLException: ORA-01722: invalid number
Environment problem: ORA-01722: invalid number
java.sql.SQLException: ORA-01722: invalid number
java.sql.SQLException: ORA-01722: invalid number
java.sql.SQLException: ORA-01722: invalid number