DB2 jdbc SQL Error: SQLCODE=-302, SQLSTATE=22001 on Select

Question

There is a select query that I am executing with DB2 JDBC. I am using Prepared Statement to pass in the value for the parameter. The column length of that parameter in the database is 12 so everything works fine until the length of the value is 12 and then it fails. Throws an exception with the error message as in the title. I did some searching and found an explanation in the following link http://www-01.ibm.com/support/docview.wss?uid=swg21319477 and the resolution mentioned in there is as below

Resolving the problem Add additional client side validation code to prevents queries, with values that are larger than the allowed maximum length to be ran.

I don't want to do this. Why wouldn't the query just return back with no results. Any idea how do I go about this?

EDIT

String sql = "select student_id, student_name from student where student_id = ?";

try (Connection connection = DBUtils.GetConnection)
{
    try (PreparedStatement statement = connection.prepareStatement(sql))
    {
        statement.setString(1, student_id);

        ResultSet result = statement.executeQuery();

        while (result.next())
        {
            //...
        }
    }
}

Are you passing in a string or a numeric value? and are you sure you're always passing in a value? — xQbert
– xQbert, Commented Nov 17, 2016 at 15:13
Yes. I am very sure that I am passing in a value and the data type is a string (varchar in db). — Abhi
– Abhi, Commented Nov 17, 2016 at 15:14
so varchar(12) in database and when you pass in a 12 character string it fails? Are you sure you don't have a space (or non-display character) in the value being passed in making it 12+? 12 = 12 so it should work.. but if you have an extra character being passed in that you can't see.. (space, enter other non-display character) it may be passing in 13 and you can only SEE 12... I'd output the length of value being passed in and ensure it's 12 to debug the issue. — xQbert
– xQbert, Commented Nov 17, 2016 at 15:40

Hans · Accepted Answer · 2017-09-28 08:57:44Z

1

Even though I do not recommend doing it: We had a similar problem and found that - at least in our case -, if you really want that empty result, you can use a native SQL query instead of the prepared statement. Apparently, it is the argument binding for the prepared statement which runs into the argument validation. The native query (which you would have manually constructed using your arguments) seemed to sidestep this validation, and just returned an empty result.

(For completeness' sake: If really manually constructing your SQL query from given arguments, be sure to know what you are doing, validate your arguments, and specifically beware of SQL injection.)

answered Sep 28, 2017 at 8:57

Hans

2,6643 gold badges35 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gaius Gracchus Over a year ago

This is a really good answer. It identifies the exact part of the query that creates the problem. Logically the select query should not error out if the item is not found regardless of the length of the comparison string in the select. One option would be to try/catch and inspect the error result codes and ignore them or return an empty value.

Brad Bentley · Accepted Answer · 2020-12-06 09:50:35Z

The correct answer here would be what @Gaius Gracchus offers up as an alternative suggestion in his comment to @Hans's answer. You try/catch the SQLException, gather its SQL State (always better than an SQL Code), and handle/throw a custom exception to indicate invalid input to the client. An empty result set (even though that is what the OP desires) is not accurate. The only other real alternative is to increase the size of the column or procedural input/input-output (not likely).

try {
    // sql bind or execute
}
catch (SQLException e) {
    String sqlState = e.getSQLState();
    if (sqlState != null && sqlState.equals("22001")) {
        throw new CustomException("Invalid input, etc");
    }
    throw e;
}

Collectives™ on Stack Overflow

DB2 jdbc SQL Error: SQLCODE=-302, SQLSTATE=22001 on Select

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related