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.
//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
Joined: Jan 12, 2001
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
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') ")