Lost nvarchar parameters.

[halfspawn]

Driver version or jar name

All versions (4.1, build from git source)

SQL Server version

All versions (2008 to 2016)

Client operating system

Windows 10, Centos 7.3

Java/JVM version

java version "1.8.0_141"

Table schema

Please tell us the table schema

Problem description

With sendStringParametersAsUnicode=true (default),
Use of SetNull(position, Types.VARCHAR) on a NVARCHAR output parameter of a stored procedure, bind a varchar variable --> lost of nvarchar character.

Expected behavior and actual behavior

If sendStringParametersAsUnicode=true, act as SetNull(position, Types.NVARCHAR)

Repro code

    String polishchar = "\u0143";
    m_cnx.createStatement().execute(
            "create procedure proc1(@p1 nvarchar(255), @p2 nvarchar(255) output) as select @p2=@p1 return 0");
    cs=m_cnx.prepareCall("{?=call proc1(?,?)}");           
    cs.registerOutParameter (1, Types.INTEGER);
    cs.setString(2,polishchar);
    cs.setNull(3, Types.VARCHAR);
    cs.registerOutParameter (3, Types.VARCHAR);        
    cs.execute();
    String p2=cs.getString(3);
    
    System.out.println("p1="+polishchar+" p2="+p2);

    cs=m_cnx.prepareCall("{?=call proc1(?,?)}");           
    cs.registerOutParameter (1, Types.INTEGER);
    cs.setString(2,polishchar);
    cs.setString(3, ""); 
    cs.registerOutParameter (3, Types.VARCHAR);        
    cs.execute();
    p2=cs.getString(3);
    
    System.out.println("p1="+polishchar+" p2="+p2);

[xiangyushawn]

Hello @halfspawn thank you very much for providing the repro code. It helps a lot.

I believe if the value is unicode, we need to use setNull(3, Types.NVARCHAR) instead of setNull(3, Types.VARCHAR), that will resolve the issue.

Thank you for the report.

[xiangyushawn]

Sorry @halfspawn, I think I misunderstood the issue. I didn't realize you meant Types.NVARCHAR and Types.VARCHAR should behave the same when sendStringParametersAsUnicode=true. I will continue investigation and keep you updated. Thank you!

[xiangyushawn]

Hello @halfspawn , thanks for your patience, I am going to create a PR to fix this issue. The repro code you provided is very helpful! Do you mind if we add the repro code into our tests?

[xiangyushawn]

hello @halfspawn PR #445 should be able to fix the issue. It would be great if we could add your repro code into the PR as one of the tests. Thank you :)

[halfspawn]

Hi Shawn, Yes, you can add the repro code to your tests. Thank you very much ! Regards, De : Shawn Sun [mailto:[email protected]] Envoyé : mardi 15 août 2017 00:36 À : Microsoft/mssql-jdbc Cc : jerome brauge; Mention Objet : Re: [Microsoft/mssql-jdbc] Lost nvarchar parameters. (#437) Hello @halfspawn<https://github.com/halfspawn> , thanks for your patience, I am going to create a PR to fix this issue. The repro code you provided is very helpful! Do you mind if we add the repro code into our tests? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub<#437 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/ACJHlV59BQoMm6ZLTl80BsKzSWMoe3izks5sYMu5gaJpZM4O0lme>.

[xiangyushawn]

Hello @halfspawn, thank you very much for your permission. Your repro code has been added to the test now 😄