18

  1. According to the java docs of PreparedStatement.setNull: "Note: You must specify the parameter's SQL type". What is the reason that the method requires the SQL type of the column?

  2. I noticed that passing java.sql.Types.VARCHAR also works for non-varchar columns. Are there scenarios in which VARCHAR won't be suitable (certain column types or certain DB providers)?

Thanks.

Improve this question
1
  • The JDBC Specification Appendix B answers the second part of the question. It shows a large number of mandatory coerciions, and there is a statement that the driver may perform more. Commented Sep 11, 2021 at 5:21

3 Answers 3

Reset to default
11

According to the java docs of PreparedStatement.setNull: "Note: You must specify the parameter's SQL type". What is the reason that the method requires the SQL type of the column?

For maximum compatibility; as per the specification, there are some databases which don't allow untyped NULL to be sent to the underlying data source.

I noticed that passing java.sql.Types.VARCHAR also works for non-varchar columns. Are there scenarios in which VARCHAR won't be suitable (certain column types or certain DB providers)?

I don't think that sort of behaviour really is part of the specification or if it is, then I'm sure there is some sort of implicit coercion going on there. In any case, relying on such sort of behaviour which might break when the underlying datastore changes is not recommended. Why not just specify the correct type?

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

5 Comments

1. I still don't understand why I have to declare the type. As far as I know UPDATE command with SET MY_COLUMN = NULL will work on every column type so why isn't it enough?
2. I have a method that gets an SQL string and a Collection<Object> and invokes PreparedStatement.setObject / setNull for each object. If I want that the correct type will be passed to setNull I need to pass the type of each object and create an ugly if. If the database driver can handle setObject, in my opinion, it should handle setNull and check internally as well.
@user: Regarding 1; as I've already mentioned, JDBC is an attempt to standardize interaction with a database, but every database implementation out there has it's own way of doing things. For e.g I've heard that Oracle complains if a proper type isn't supplied when setting NULL. Hence JDBC requires that you specify the type, whether the underlying JDBC implementation passes it on to the database is another thing. It can't be helped if a particular database wire protocol decides that types be passed when setting NULL values so there's no point in reasoning with that.
Regarding 2; that's a design decision which you've made. I'm not aware of any db helper/wrapper code/framework which tries to hide database types from the user. In hibernate, you specify annotations/XML configuration to set column types. How about adding a feature to your code which deals with user supplied types? If all this is really a bother, just pass in VARCHAR, cross your fingers and hope for the best. :-)
The JDBC Specification Appendix B answers the second part of the question. It shows a large number of mandatory coerciions, and there is a statement that the driver may perform more.
8

JDBC drivers appear to be moving away from setNull. See Add support for setObject(<arg>, null).

My list of databases supporting the more logical behaviour is:

  1. Oracle
  2. MySQL
  3. Sybase
  4. MS SQL Server
  5. HSQL

My list of databases NOT supporting this logical behaviour is:

  1. Derby Queries with guarded null Parameter fail
  2. PostgreSQL Cannot pass null in Parameter in Query for ISNULL Suggested solution
Improve this answer

Comments

1

When it comes to Oracle it would be very unwise to use varchar2 towards other datatypes. This might fool the optimizer and you could get an bad execution plan. For instance filtering on a date column using a timestamp datatype in your bind, Oracle could end up reading all your rows converting all dates to timestamp, then filtering out the wanted rows. If you have a index on your date column, it could even get worse (if oracle chose to use it) - doing single reads on your oracle blocks.

--Lasse

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.