Re: Passing a list to a Java prepared statementThis is a discussion on Re: Passing a list to a Java prepared statement within the JDBC JAVA forums in Framework and Interface Programming category; On Wed, 06 Oct 2004 07:30:31 -0700, GMan wrote: > I'm using JDBC with Oracle 8i and I have the following prepared statement > code: > > ... > > String sql = "Select ID from Users where level in (?)"; > > PreparedStatement statement = connection.prepareStatement(sql); > > String levels = "1, 2, 3"; > > statement.setString(1, levels); > > rs = (ResultSet) statement.executeQuery(); > > ... > > - The executeQuery call fails with: "ORA-01722: invalid number". - Level > is an integer field in the users table > > So, I'm guessing I've got to do something ... | ||||||||
| | ||||||||
| ||||||||
| Register | FAQ | Calendar | Search | Today's Posts | Mark Forums Read |
|
#1
10-31-2004, 02:48 PM
| |||
| |||
 Re: Passing a list to a Java prepared statement On Wed, 06 Oct 2004 07:30:31 -0700, GMan wrote: > I'm using JDBC with Oracle 8i and I have the following prepared statement > code: > > ... > > String sql = "Select ID from Users where level in (?)"; > > PreparedStatement statement = connection.prepareStatement(sql); > > String levels = "1, 2, 3"; > > statement.setString(1, levels); > > rs = (ResultSet) statement.executeQuery(); > > ... > > - The executeQuery call fails with: "ORA-01722: invalid number". - Level > is an integer field in the users table > > So, I'm guessing I've got to do something different here to handle these > kinds of "list" parameters. Any thoughts? Probably the easiest solution in Java is to create a statement String in a StringBuffer and append the same number of placeholders (?) as list elements, convert to a String, create a PreparedStatement and fill in the placeholders. Eg: StringBuffer sb = new StringBuffer( "Select ID from Users where level in (?"); int n = list.size(); for(int i = 1; i < n; i++) { sb.append(",?"); } PreparedStatement ps = con.prepareStatement(sb.toString()); for(int i = 0; i < n; i++) { ps.setObject(i, list.get(i)); } ResultSet rs = ps.executeQuery(); Hope this helps. Chuck  |
|
#2
10-31-2004, 02:57 PM
| |||
| |||
| Re: Passing a list to a Java prepared statement On Sun, 31 Oct 2004 13:48:32 -0600, Chuck Simpson wrote: > On Wed, 06 Oct 2004 07:30:31 -0700, GMan wrote: > >> I'm using JDBC with Oracle 8i and I have the following prepared >> statement code: >> >> ... >> >> String sql = "Select ID from Users where level in (?)"; >> >> PreparedStatement statement = connection.prepareStatement(sql); >> >> String levels = "1, 2, 3"; >> >> statement.setString(1, levels); >> >> rs = (ResultSet) statement.executeQuery(); >> >> ... >> >> - The executeQuery call fails with: "ORA-01722: invalid number". - Level >> is an integer field in the users table >> >> So, I'm guessing I've got to do something different here to handle these >> kinds of "list" parameters. Any thoughts? > > Probably the easiest solution in Java is to create a statement String in a > StringBuffer and append the same number of placeholders (?) as list > elements, convert to a String, create a PreparedStatement and fill in the > placeholders. Eg: > > StringBuffer sb = new StringBuffer( > "Select ID from Users where level in (?"); > int n = list.size(); > for(int i = 1; i < n; i++) { > sb.append(",?"); > } > PreparedStatement ps = con.prepareStatement(sb.toString()); for(int i = 0; > i < n; i++) { > ps.setObject(i, list.get(i)); > } > ResultSet rs = ps.executeQuery(); > > Hope this helps. > > Chuck PS: I should have put the line: sb.append(")"); just before creating the PreparedStatement to close the in operand. Chuck |
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
All times are GMT -5. The time now is 08:25 PM.
