Java does not run prepared statement with parameter and not throwing any errors

Question

I'm trying to execute a query as simple as the below in a 'VIEW' of my MSSQL database. Not successful after trying for 2 days. So I'm turning to SO.

PreparedStatement preparedStatement = connection.prepareStatement(
"SELECT * FROM Vw_Transactions WHERE Cust_No=? ");

preparedStatement.setString(1, "1234");                
ResultSet resultSet = preparedStatement.executeQuery();

The problem is I don't get any response back after the query.Not even an error. Not even after waiting for a long time e.g. 3mins. Once or twice I got some result after waiting for upto 5 minutes. But that's useless. I'm curious to know why before I do something stupid like semi-sanitize and string concantenate arguments to the query, as the last resort.

Interesting Observations:

• The same query works from java when I hard code the customer id into the string and skip prepared statement. It brings the result very fast so now I'm sure it's not data load that is causing slowness.
• The same query works if I run it in the Visual Studio console when I use the sample customer id directly
• The same query format works if I choose a different VIEW from the database

The only last log I can see is :

> SQLServerPreparedStatement:3: calling sp_prepexec: PreparedHandle:0, SQL:SELECT * FROM Vw_Transactions WHERE Cust_No=@P0

Note: I've already referred this quesiton and mine is not a duplicate scenario. I already made sure I havent dont those mistakes.

Answer 1

Depending on your Datatype for Cust_No, sqljdbc driver might send the String in unexpected format.

This would then force a data conversion to happen in SQL Server, rendering all indexes useless on that table / view.

As your Cust_No is of type char in SQL Server, this would be a charset conversion in this case.

As http://msdn.microsoft.com/en-us/library/ms378988(v=sql.110).aspx points out, String parameters are send as Unicode ( = nvarchar) by default, causing your query to perform char to nvarchar conversions for all Cust_No.

Try adding ;sendStringParametersAsUnicode=false into your connection string to disable this feature.