none
JDBC) SetObject(1, id, types.VARCHAR) does not work RRS feed

  • Question

  • Hi there,

    [Env]

       SQL Server 2008R2 (SP3) + MS JDBC (4.0?)

    [Query Template]

    exec sp_prepexec @p1 output,N'@P0 nvarchar(4000)'
       ,N'select MAX(Time) from [dbo].[Table] where NO=@P0         '
       ,N'15'

    [Test Scenario]

    1) SendStringParametersAsUnicode = false & SetObject(1, name,Types.NVARCHAR)

    --> OK (called @P0 as nvarchar)

    2) SendStringParametersAsUnicode = false & SetObject(1, name,Types.VARCHAR)

    --> not working (called @P0 as nvarchar)

    3) SendStringParametersAsUnicode = true & SetObject(1, name,Types.VARCHAR)

    --> not working (called @P0 as nvarchar)

    ------------------------------------------------

    What am I missing?

    Thanks in advance for any advice or suggestion.

    Jungsun.


    Best Regards, Jungsun Kim

    Thursday, December 8, 2016 7:10 AM

Answers

  • Hi Jungsun Kim,

    According to the following blog, we could find this following statement. So, your above situation is normal.

    It turns out that the JDBC driver sends character data including varchar() as nvarchar() by default. The reason is to minimize client side conversion from Java’s native string type, which is Unicode.

    https://blogs.msdn.microsoft.com/sqlcat/2010/04/05/character-data-type-conversion-when-using-sql-server-jdbc-drivers/

    As it says, if we want to pass both varchar and nvarchar parameters at the same time, we need to set “sendStringParametersAsUnicode” to false.

    Best Regards,

    Albert Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by JungsunMVP Wednesday, December 14, 2016 2:10 AM
    Wednesday, December 14, 2016 1:31 AM

All replies

  • Hi Jungsun Kim,

    Could you first clarify what does the “not working” mean? Does it mean the select query doesn’t return result or the code happen error?

    As I searched, when SendStringParametersAsUnicode is true, the parameter value would be sent as Unicode by default. You could have a try to use setNString of SQLServerPreparedStatement, because the new JDBC 4.0 national character methods, the method setNString always send their parameter values to the server in Unicode regardless of the setting of this property.

    https://msdn.microsoft.com/en-us/library/ms378988(v=sql.110).aspx

    Best Regards,

    Albert Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 9, 2016 2:54 AM
  • Hi Albert,

    Thank you for reply.

    We have varchar & nvarchar columns on one table so we need to call the query (string parameter) in non-Unicode and Unicde, for example,

    select * from where nonunicode_col = ? and unicode_col = ?

    and then call the query using SetObject(,, types.VARCHAR) and SetObject(,, types.NVARCHAR).

    finally, after trace the query using SQL Profiler

    1) SetObject(,, types.NVARCHAR) is sent to the server in Unicode format,

    2) SetObject(,, types.VARCHAR)  is also sent to the server in Unicode format not non-unicode(actually varchar type) --> I want to sent it in varchar type

    That't what I wonder.

    Sorry for my poor English^^

    Regards,

    Jungsun


    Best Regards, Jungsun Kim




    • Edited by JungsunMVP Friday, December 9, 2016 3:20 PM
    Friday, December 9, 2016 9:31 AM
  • Hi Jungsun,

    Thank you for your response.

    I have made a test with following code and it could send the “varchar” value to SQL Server. I use the JDBC 4.0 from this link. My SQL Server version is “Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)”. You could have a look at it and test it on your machine to check whether it’s ok.

    The table SQL

    create table t (id int identity(1,1), col1 varchar(20),col2 nvarchar(20))
    go
    insert into t values ('abcd',N'你好')
    insert into t values ('efgh',N'안녕하세요')
    go
    select * from t
    go
    

    The JAVA code

    import java.sql.*;
    public class Main {
    
    	public static void main(String[] args) {
    		try  
    	       { 
    	            // Load the SQLServerDriver class, build the 
    	            // connection string, and get a connection 
    	            String connectionUrl = "jdbc:sqlserver://ServerIP;" + 
    	                                    "database=test;" + 
    	                                    "user=sa;" + 
    	                                    "password=sa"+";sendStringParametersAsUnicode=false"; 
    	            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
    	            Connection con = DriverManager.getConnection(connectionUrl); 
    	            System.out.println("Connected.");
    
    	            // Create and execute an SQL statement that returns some data.  
    	            String SQL = "SELECT * FROM t where col1 = ? and col2= ?";  
    	            PreparedStatement stmt = con.prepareStatement(SQL); 
    	            
    
    	            stmt.setObject(1, "efgh",Types.VARCHAR);
    	            stmt.setObject(2, "안녕하세요",Types.NVARCHAR);
    	            
    	            //This following method could also send non-Unicode value
    	            //stmt.setString(1, "abcd");
    	            //stmt.setNString(2, "你好");
    	            ResultSet rs = stmt.executeQuery();
    
    	            // Iterate through the data in the result set and display it.  
    	            while (rs.next())  
    	            {  
    	               System.out.println(rs.getString(1)+" "+ rs.getString(2) + " " + rs.getString(3));  
    	            }
    
    	       }  
    	       catch(Exception e)  
    	       { 
    	            System.out.println(e.getMessage()); 
    	            System.exit(0);  
    	       } 
    	}
    
    }
    

    The result comes from SQL Profiler

    declare @p1 int
    set @p1=1
    exec sp_prepexec @p1 output,N'@P0 varchar(8000),@P1 nvarchar(4000)',N'SELECT * FROM t where col1 = @P0 and col2= @P1                ','efgh',N'안녕하세요'
    select @p1
    

    Best Regards,

    Albert Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 13, 2016 11:54 AM
  • Hi Albert,

    Thank so much for your kindess and testing.

    But it is a little different test scenario with my case and mentioned on my first post.

    What I wonder is,

    1) sendStringParametersAsUnicode=true   //not false, so default is NVARCHAR

    and then

    2) stmt.setObject(1, "efgh",Types.VARCHAR);

    is sent to the server in Unicode format. I guess it has to sent as Non-unicode format.

    Does it mean TYPE.VARCHAR doest not take precedence over sendStringParametersAsUnicode connection property?

    Best Regards,

    Jungsun


    Best Regards, Jungsun Kim

    Tuesday, December 13, 2016 3:27 PM
  • Hi Jungsun Kim,

    According to the following blog, we could find this following statement. So, your above situation is normal.

    It turns out that the JDBC driver sends character data including varchar() as nvarchar() by default. The reason is to minimize client side conversion from Java’s native string type, which is Unicode.

    https://blogs.msdn.microsoft.com/sqlcat/2010/04/05/character-data-type-conversion-when-using-sql-server-jdbc-drivers/

    As it says, if we want to pass both varchar and nvarchar parameters at the same time, we need to set “sendStringParametersAsUnicode” to false.

    Best Regards,

    Albert Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by JungsunMVP Wednesday, December 14, 2016 2:10 AM
    Wednesday, December 14, 2016 1:31 AM
  • Hi Albert,

    OK I see, the situation is normal (I read the blog).

    Personally, I don't understand the behavior but anyway I got it :)

    Thanks Albert. Have a good day~

    Best Regards,

    Jungsun


    Best Regards, Jungsun Kim

    Wednesday, December 14, 2016 1:46 AM
  • Hi Jungsun Kim,

    I’m so happy to discuss this issue together with you.

    In my opinion, the “sendStringParametersAsUnicode” setting has the higher precedence than “stmt.setObject”. If we need to send varchar value, it provides a method to achieve it by setting the property to false. This is by design.

    Besides, if some of my replies are helpful, please mark them as answers to close this thread.

    Thank you for your understanding and support. Good luck to you.

    Best Regards,

    Albert Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 14, 2016 2:09 AM