I have written a query string for SQL Server I am executing as a NativeSQLQuery. I used indexed parameter binding and things are working perfectly until I bind a search parameter that is unicode. Then the match just fails. In my JDBC connection, I have the parameter set: sendStringParametersAsUnicode=false. If I remove this the query works fine. I set this because it was sending all parameters as unicode and causing full table scans since some columns were not unicode at all. The server then reported possible deadlocks. Once I set this the system started working fine.
As I bind my parameters to my query, is there any way I can give the query a hint to treat as unicode? I see where if I were using string literals I could do something like "mycolumn = N’somevalue’. Is there any way I can give it such a hint when binding my parameter?
Can you try to use some kind of cast? IMO this is an issue of the JDBC driver so maybe you should ask the SQL Server JDBC driver folks.
JDBC prepared statements have a method called setNString()
Sets the designated parameter to the given String object.
The driver converts this to a SQL NCHAR or NVARCHAR or LONGNVARCHAR value (depending on the argument’s size relative to the driver’s limits on NVARCHAR values) when it sends it to the database.
I guess what I am asking is if Hibernate Query object has a similiar method where I can tell it to pass this to the JDBC driver as NVARCHAR?
You can bind the parameter with a specific type through
org.hibernate.query.NativeQuery#setParameter(int, java.lang.Object, org.hibernate.type.Type). The passed type is the responsible for binding the parameter to the JDBC statement. You can use
StringNVarcharType.INSTANCE for this purpose.
That was exactly what I was looking for. Couldn’t find the StringNVarcharType. I added it to my code and it solved the problem. Thanks!