CODEWITHSUNDEEP
javasql-serverjdbccollation
Tony
Tony

Reputation: 351

Sqlserver: what are the differences between execute sql with jdbc driver and execute with sql client

I have a table named "T_ROLE", it has just one column named "NAME" which type is nvarchar(255), the sqlserver Collation is "SQL_Latin1_General_CP1_CI_AS"(en_US), now i want to insert japanese character, so i know that i need do sql like this:

INSERT INTO T_ROLE(NAME) VALUES(N'japaneseString')

this can be successful.

if i do sql:

INSERT INTO T_ROLE(NAME) VALUES('japaneseString')

which without N prefix, it will saved as '?', i can under these behavior.

But when i use sqlserver jdbc driver to do insert operation like this:

String sql = "INSERT INTO T_ROLE (NAME) VALUES(?)";
stmt.setString(1, "");
stmt.execute(sql);

notice: i don't use stmt.setNString() method, but it can be saved successful, why?

Upvotes: 2

Views: 432

Answers (1)

Andreas
Andreas

Reputation: 159125

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

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.

So how do you force the JDBC driver not to behave this way? There is a connection string property, named sendStringParametersAsUnicode. By default it’s true.

One would ask what if I want to pass both varchar and nvarchar parameters at the same time? Well, even with the connection property set false, you can explicitly specify nvarchar type like this:

pStmt.setObject(2,Id,Types.NVARCHAR); //Java app code

Simple Google search for sql server jdbc nvarchar found this answer.

Upvotes: 2

Related Questions