Populating Prepared Statement with comma-separated list of integers

Asked Oct 26 '16 at 15:54

Active Oct 26 '16 at 15:54

Viewed 23 times

If I am populating a Prepared Statement

SELECT CODE FROM MY_TABLE WHERE COMPANY_NO IN (?)

How could I populate it correctly?

When I do

String sqlResult = "SELECT CODE FROM MY_TABLE WHERE COMPANY_NO IN (?)"

    try {
        PreparedStatement ps = con.prepareStatement(sqlResult, ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);

         ps.setString(1,"4,5");
   }

then I get

Arithmetic overflow error converting varchar to data type numeric.

which is because it is being populated as below:

SELECT CODE FROM MY_TABLE WHERE COMPANY_NO IN ('4,5')

asked Oct 26 '16 at 15:54

gordon613

2,770
12
52
81

Have you tried ps.setObject1, Arrays.asList(4, 5)) ? – Tobias Otto Oct 26 '16 at 16:02
Thanks - I tried this and it returned `The value is not set for the parameter number 1` – gordon613 Oct 26 '16 at 16:21
I took an idea from the question linked above and replaced SQL with `SELECT CODE FROM MY_TABLE WHERE COMPANY_NO IN (select * from dbo.splitString(?,','))` which worked – gordon613 Oct 26 '16 at 16:50

Populating Prepared Statement with comma-separated list of integers

0 Answers0