File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Oracle:  Prepared Statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Oracle:  Prepared Statement" Watch "Oracle:  Prepared Statement" New topic

Oracle: Prepared Statement

Butch Car

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'


//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

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'
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:
subject: Oracle: Prepared Statement
jQuery in Action, 3rd edition