JDBC: Latin1 to UTF8

Question

JDBC: Latin1 to UTF8

Sergo A 11

There is a table in SQL Server with collation SQL_Latin1_General_CP1_CS_AS.
The table has a column varchar(35) with the same collation SQL_Latin1_General_CP1_CS_AS.

The column contains a string with the character 8f (hexadecimal).

See https://www.fileformat.info/info/unicode/char/008f/index.htm
According to this page, this character converted into UTF8 should become c28f.

When I read the value from this column in Java and convert it to UTF-8, the 8f is replaced with efbfbd. So the 8f get's lost... a kind of.
See https://www.fileformat.info/info/unicode/char/0fffd/index.htm

public static String convertStrToHex(String str) {  
    byte[] getBytesFromString = str.getBytes(StandardCharsets.UTF_8);  
      
    BigInteger bigInteger = new BigInteger(1, getBytesFromString);  
    String convertedResult = String.format("%X", bigInteger);  
  
    return convertedResult;  
}

When I query the table

System.out.println(convertStrToHex(resultSet.getString(1));

I get EFBFBD.

When I declare a string variable "\u008f" and convert it in UTF-8:

String code="\u008f";  
System.out.println(convertStrToHex(code));

I get correctly C28F.

Testet with:
SQL Server 2017 and 2019
JDBC: mssql and jTDS

with the same result.

I would appreciate any help!
As I understand, the JDBC driver is to blame. But why??

Viorel 122.6K Reputation points

2022-06-22T18:47:19.863+00:00

Is it possible to use resultSet.getBytes instead of getString, and to investigate the bytes?

What SQL query are you using?

Sergo A 11

Thank you for your reply!

I tried

byte[] dbStr = resultSet.getBytes(1);  
BigInteger bigInteger = new BigInteger(1, dbStr);  
String convertedResult = String.format("%X", bigInteger);  
System.out.println("\n" + convertedResult);

And I got 8F. Did you mean this?

My SQL query is simple:

select BadCol from MyTbl

Viorel 122.6K Reputation points

2022-06-23T05:57:03.167+00:00
Check if this gives the expected character (8F) in corresponding position:

byte[] dbStr = resultSet.getBytes(1); String s = new String(dbStr, StandardCharsets.ISO_8859_1);
Sergo A 11 Reputation points

2022-06-23T18:46:32.447+00:00

No, it is 3F, a question mark.

Viorel 122.6K

Is it 3F even in this test?

byte[] dbStr = {(byte)0x8F};  
String s = new String(dbStr, StandardCharsets.ISO_8859_1);  
System.out.println( String.format("%X", (int)s.charAt(0)));

Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2022-06-23T21:21:50.28+00:00

Keep in mind that ? is the general fallback character when nothing else fits.
Sergo A 11 Reputation points

2022-06-24T01:26:48.62+00:00

No, in this case it is 8F. But if I change it to StandardCharsets.US_ASCII, then it is 3F.
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2022-06-24T08:38:36.05+00:00

I thought in this case, you had 8f in the data?

What I was referring to is what happens when you convert from one code page to another. For instance, take the Swedish word räksmörgås. If you convert this to a Japanese code page, you may get raksmorgas. The Swedish characters åäö are not available in a Japanese code page and replaced with lookalikes. These are known as fallback characters. But if there is no suitable lookalike, you get the generic fallback character which is the question mark.
Sergo A 11 Reputation points

2022-06-24T10:02:09.04+00:00

äöüß are converted correctly, because they are valid 1252 western european characters.

3 answers

Your answer

Viorel 122.6K Reputation points

2022-06-22T18:47:19.863+00:00

Is it possible to use resultSet.getBytes instead of getString, and to investigate the bytes?

What SQL query are you using?
Sergo A 11 Reputation points

2022-06-22T20:58:23.063+00:00

Thank you for your reply!

I tried

byte[] dbStr = resultSet.getBytes(1); BigInteger bigInteger = new BigInteger(1, dbStr); String convertedResult = String.format("%X", bigInteger); System.out.println("\n" + convertedResult);

And I got 8F. Did you mean this?

My SQL query is simple:

select BadCol from MyTbl
Viorel 122.6K Reputation points

2022-06-23T05:57:03.167+00:00

Check if this gives the expected character (8F) in corresponding position:

byte[] dbStr = resultSet.getBytes(1); String s = new String(dbStr, StandardCharsets.ISO_8859_1);
Sergo A 11 Reputation points

2022-06-23T18:46:32.447+00:00

No, it is 3F, a question mark.
Viorel 122.6K Reputation points

2022-06-23T18:53:53.403+00:00

Is it 3F even in this test?

byte[] dbStr = {(byte)0x8F}; String s = new String(dbStr, StandardCharsets.ISO_8859_1); System.out.println( String.format("%X", (int)s.charAt(0)));
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2022-06-23T21:21:50.28+00:00

Keep in mind that ? is the general fallback character when nothing else fits.
Sergo A 11 Reputation points

2022-06-24T01:26:48.62+00:00

No, in this case it is 8F. But if I change it to StandardCharsets.US_ASCII, then it is 3F.
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2022-06-24T08:38:36.05+00:00

I thought in this case, you had 8f in the data?

What I was referring to is what happens when you convert from one code page to another. For instance, take the Swedish word räksmörgås. If you convert this to a Japanese code page, you may get raksmorgas. The Swedish characters åäö are not available in a Japanese code page and replaced with lookalikes. These are known as fallback characters. But if there is no suitable lookalike, you get the generic fallback character which is the question mark.
Sergo A 11 Reputation points

2022-06-24T10:02:09.04+00:00

äöüß are converted correctly, because they are valid 1252 western european characters.

Answer 1

Erland Sommarskog 121.9K MVP Volunteer Moderator

The character SINGLE SHIFT THREE you refer to is U+00BF in Unicode, but with varchar and SQL_Latin1_General_CI_AS you are outside the realm of Unicode. That is character 143 in code page 1252 and what that is defined as, I don't know. But maybe it is a character that becomes EFBFBD in UTF-8. Then again, that does not even seem to be a legal UTF-8 sequence.

Sergo A 11 Reputation points

2022-06-23T01:17:09.227+00:00
Why U+00BF? It is U+008F.
if i cast to nvarchar, then it is converted to UTF-8 correctly as C28F:

select cast(BadCol as nvarchar) as BadCol from MyTbl
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2022-06-23T21:17:26.923+00:00

Sorry, U+00BF was a typo. I did mean to say U+008F.

Answer 2

YufeiShao-msft 7,146

Hi @Sergo A ,

SQL Server 2019 introduces an additional option for UTF-8 encoding, but need create or change an object's collation to a collation that has a UTF8 suffix
https://learn.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver16#utf8

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

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Sergo A 11 Reputation points

2022-06-23T10:13:21.69+00:00

I know.
My question is why without any changes (cast, convert, collate etc) i get efbfbd and not c28f.
There are several workarounds. But i want to understand why a string variable converted to UTF8 becomes c28f as expected and the value from database, converted the same way to UTF8, becomes efbfbd.
As I understand there are no connection properties Encoding or Charset or something like this in MS JDBC driver.
Sergo A 11 Reputation points

2022-06-23T14:43:41.677+00:00

Apparently the reason is just because Java String is unicode and varchar column is not. Isn't it?

Answer 3

Erland Sommarskog 121.9K MVP Volunteer Moderator

I should have looked at both links you had in the initial post. The character you get is UxFDFF, REPLACEMENT CHARACTER. This is the glyph you typically get when there is an encoding error, for instance an incorrect UTF-8 sequence.

Why you get this here, I can't say, but as I noted, this is not a defined code point in code page 1252, so that could be the reason.

Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2022-06-24T08:40:29.037+00:00

By the way, what happens if the character is 8e, which in code page 1252 is Ž, that is uppercase Z with caron?
Sergo A 11 Reputation points

2022-06-24T10:32:39.4+00:00

Ž in UTF8 gets C5BD
in ISO-8859-1: 3F
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2022-06-24T21:11:15.59+00:00

So Ž is handled correctly. I believe that confirms my theory that 8f results in the REPLACEMENT CHARACTER because it is not a defined code point in code page 1252.

That Ž yields a question mark when converting to iso-8859-1 is not strange, as the range 128-159 is not defined in iso-8859-1.
Sergo A 11 Reputation points

2022-06-25T10:16:28.867+00:00

yes, i think you are right

Share via

JDBC: Latin1 to UTF8

3 answers

Your answer