Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle: Prepared Statement

 
Butch Car
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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') ")
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic