CODEWITHSUNDEEP
javasql-serverjdbcprepared-statement
kchang
kchang

Reputation: 91

Workaround for JDBC Limitation on Placeholders Fails for Update Statements

I am implementing a pattern to get around a JDBC limitation. For prepared statements, JDBC limits the number of placeholders to 2100. To get around this, I am using an xml string containing 2100+ values and parsing it on SQL Server's side using a function tf_splitxml. I am doing this for ~4 Java methods that use prepared statements.

This tf_splitxml just constructs a single column "token" containing all the values. So an xml string of: 

'<node><value>1</value><value>2</value></node>'

would get converted to a column containing two rows, with the values 1 and 2.

This pattern seems to work fine for select statements, but it is failing for update statements. Here's the general pattern:

declare @xml xml; set @xml = ?; --Replaced with xml string in PreparedStatement

update tableX
...
where ids in (select token from tf_splitxml(@xml));

It tells me that conversion failed when converting the nvarchar value to data type int. [for the above xml string]. What's strange is that, if I extract the query set up by the prepared statement, I can run it perfectly in SQL Server!

My thoughts:

  1. Of the 4 methods, 3 work using this xml pattern; these 3 are all select statements and use executeQuery(). The 4th failed method is an update statement and uses executeUpdate(). Maybe the problem has to do with the way the prepared statement pre-compiles the sql?

Things I've tried:

  1. I created a table that could persist the tokens generated from tf_splitxml. For the failed method, tf_splitxml is never getting called before JDBC throws the error.

  2. On the Java side, I am using ps.setString(index, convertToXML(idsArray)). This works for the first 3 methods, even though my @xml isn't a string (it's declared as an xml variable). I tried switching this to the SQLXML object, to no avail. I still get the same 3/4 methods working properly.

  3. I can run all my prepared queries directly within my SQL Server editor perfectly fine.

My sincerest gratitude in advance! :)

Upvotes: 1

Views: 795

Answers (1)

jonathan.cone
jonathan.cone

Reputation: 6706

That code looks pretty nuts, check out my ChunkWorkTemplate. Here's a quick example:

public void delete(final List<Integer> employeeIds) {
  new ChunkWorkTemplate<Integer>(50, employeeIds) {
    protected void executeOnChunk(List<Integer> chunk) {
      Session session = getSession();
      Query query = session.createSQLQuery("delete from Employee where employeeId in (:employeeIds)");
      query.setParameterList("employeeIds", chunk);
      query.executeUpdate();
    }
  }.execute();
}

Note that the first parameter to the template is the chunk size, you can set that to whatever you like. Basically, it will chunk up the input collection and execute the body in chunks. So in the example above, if you had 207 employee IDs, with a chunk size of 50 you would have 5 chunks (50+50+50+50+7). I wrote this to solve the exact problem you're facing. Hope this helps.

Upvotes: 1

Related Questions