[FEATURE REQUEST] SQLServerPreparedStatement#setNull(idx, Types.OTHER) should work with all data types #1269
Comments
harawata
added
the
Enhancement
|
seems that this is an error generated by the database engine because the request you are generating is invalid/not supported. Why would you want to pass a database specific SQL type into a column when you know the Java type? |
|
Thank you for the comment, @mprins , Yes, it is the engine returning the error. Imagine if you want to write a method that takes an arbitrary SQL statement and a parameter object. void insert(String sql, Object param) {
PreparedStatement stmt = getConnection().prepareStatement(sql);
if (param == null) {
stmt.setNull(1, Types.OTHER);
} else if (param.getClass() == String.class) {
stmt.setString(1, param);
} else if (...) {
...
}
stmt.execute();
}As you can see, when the parameter is Hope this makes my intention clearer. |
|
Hi @harawata , |
|
Hi @harawata , I first wanted to mention that, Please also note that, SQL Server requires data type to be specified when inserting The reason why you are seeing inconsistent behavior, is because when the driver is unable to detect the java Hope this clarifies things. |
|
Thank you for the reply, @ulvii , Okay. Then how about I change this request to 'Improve setNull(index, Types.BINARY) to work with all data types consistently' ? I understand that changing the engine's behavior is a big deal, but could you transfer this request to the engine development team for further consideration? |
|
Hi @harawata , This does not: Please feel free to create a ticket for the server team to consider the change. |
|
Thank you for the quick and informative reply, @ulvii ! |
Is your feature request related to a problem? If so, please give a short summary of the problem and how the feature would resolve it
java.sql.PreparedStatement#setNull()requires JDBC type as the second argument.In a JDBC wrapper like MyBatis, it is common to specify
java.sql.Types.OTHERorjava.sql.Types.NULLas the second argument to handle various data types universally.mssql-jdbc accepts
OTHERorNULLfor most data types, but not all of them, unfortunately.To demonstrate the issue, I wrote the following test
Here is the result.
8 out of 26 tests fail.
There seems to be two kinds of error messages:
Operand type clash: varbinary is incompatible with xxx
Implicit conversion from data type varbinary to xxx is not allowed
Describe the preferred solution
Given that the value being set is
null, it seems reasonable to expectsetNull(idx, Types.OTHER)to work with all data types instead of throwing exception about data type incompatibility.Describe alternatives you've considered
It's OK if
Types.NULLis supported as the universal second argument instead ofTypes.OTHER.Additional context
n/a
Reference Documentations/Specifications
n/a
Reference Implementation
n/a
The text was updated successfully, but these errors were encountered: