JDBC - Can't get unicode with ResultSet.getString()

David Thielen

unread,

Sep 2, 2005, 3:40:04 PM9/2/05

to

Hi;

I am using MS Sql Server and it has an nvarchar field holding a name.

For ascii chars I get back the text in the database.

But if it is anything other than ascii, I get back a ? for each non 7-bit
character. So a field with "a風bбc" returns "a?b?c"

What do I need to set/do to get back the unicode values in the database?

--
thanks - dave

Note: I couldn't find a forum for the Sql Server jdbc driver so I figured
this was the closest thing.

Steven Cheng[MSFT]

unread,

Sep 5, 2005, 3:42:16 AM9/5/05

to

Hi Dave,

Welcome to MSDN newsgroup.
Regarding on the problem you mentioned, here are some of my understanding
and suggestions:

Since you mentioned that the SQLServer field is a nvarchar column, so I
think the sqldatabase should have stored the data correctly as long as
there hasn't occur converting issue when you put them into the sql db.

I think you can verfify this by using the SQLserver's query analyzer or
other client component such as .NET, VB to query the certain column to see
whether the data stored in SQLdb is ok. If the data is ok, we should have
a further look in the data retrieving and displaying portion. For example,
it's possbile that the data is retrieved incorrectly when you through JDBC
api or being converted incorrectly when write out to the client page(when
developing web application). BTW, are you using THE JDBC in JSP web
application? If so, there is likely problems in charset as I mentioned
above.

Thanks,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

David Thielen

unread,

Sep 5, 2005, 9:21:02 AM9/5/05

to

The data is correct in Sql Server - I viewed it there.

And it is the jdbc getString() that returns it wrong, I viewed that in my
debugger.

So for somereason the Microsoft Sql Server JDBC driver is not returning
unicode. Is there a special setting needed to do this? Or should I use a call
other than getString()?

--
thanks - dave

"Steven Cheng[MSFT]" wrote:

> | character. So a field with "aé¢¨bÐ±c" returns "a?b?c"

Matt Neerincx [MSFT]

unread,

Sep 6, 2005, 3:28:52 AM9/6/05

to

You can try microsoft.public.sqlserver.jdbcdriver this area is good.

But I can help you here in the meanwhile.

What is the collation set on the database as well as the columns on the
table? This can have an impact, I could set this up and reproduce the
problem, etc...

--
Matt Neerincx [MSFT]

This posting is provided "AS IS", with no warranties, and confers no rights.

Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"David Thielen" <thi...@nospam.nospam> wrote in message
news:9A43688E-224B-4617...@microsoft.com...

>> | character. So a field with "a風bбc" returns "a?b?c"

David Thielen

unread,

Sep 6, 2005, 8:12:01 AM9/6/05

to

Hello;

The column is a nvarchar and I use ResultSet.getString() to retrieve it.

For most of my access I use hibernate and it does successfully store and
return the unicode values for the string (that is how the values originally
get written to the database).

But when I get a connection dorectly and call getString(), then I get just
ascii and ? for the chars > 127.

Is there something special I have to do when I get the connection or a call
other than getString I should use? I tried getBytes but that threw an
exception.

--
thanks - dave

"Matt Neerincx [MSFT]" wrote:

> >> | character. So a field with "aé¢¨bÐ±c" returns "a?b?c"

Steven Cheng[MSFT]

unread,

Sep 6, 2005, 8:34:24 AM9/6/05

to

Hi Dave,

As far as I know, there seems haven't any particular setting for us to use
the getString() method in JDBC driver. Also, for wide char string(unicode),
I've also used them through the SQLServer JDBC provider before(with
chinese) and didn't encounter the problem you mentioned. I'm wondering
whether the problem is related to particular characters. Is the problem
related to all the wide chars in your db or just some particular ones?

Thanks,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

--------------------
| Thread-Topic: JDBC - Can't get unicode with ResultSet.getString()

| thread-index: AcWyHKmab8ACUdRiTaSrVi7Q2ulyzg==

| X-WBNR-Posting-Host: 199.45.247.98
| From: "=?Utf-8?B?RGF2aWQgVGhpZWxlbg==?=" <thi...@nospam.nospam>

| References: <DEBAF362-6134-43F1...@microsoft.com>
<u3AUb1es...@TK2MSFTNGXA01.phx.gbl>
| Subject: RE: JDBC - Can't get unicode with ResultSet.getString()
| Date: Mon, 5 Sep 2005 06:21:02 -0700
| Lines: 97
| Message-ID: <9A43688E-224B-4617...@microsoft.com>

| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2663
| X-Tomcat-NG: microsoft.public.sqlserver.odbc

David Thielen

unread,

Sep 6, 2005, 8:48:30 AM9/6/05

to

I tried Chinese (traditional), Russian, and Japanese.

Here is my connection code:

public JdbcDataSource(String className, String url, String username, String
password) throws DataSourceException {

if (log.isDebugEnabled())
log.debug("JdbcDataSource(" + className + ", " + url + ", ...)");

ctorClass = className;
ctorUrl = url;
ctorUser = username;

Properties info = new Properties();
info.put("user", username);
info.put("password", password);
info.put("charSet", "UTF-8");

try {
Class.forName(className).newInstance();
conn = DriverManager.getConnection(url, info); //bugbug username,
password);
closeConnOnExit = true;
} catch (Exception e) {
log.error("JdbcDataSource could not open " + url, e);
throw new DataSourceException("JdbcDataSource could not open " + url, e);
}
}

--
thanks - dave

"Steven Cheng[MSFT]" wrote:

> | > | character. So a field with "aÃ©Â¢Â¨bÃ Â±c" returns "a?b?c"

David Thielen

unread,

Sep 6, 2005, 9:23:02 AM9/6/05

to

Hi;

I just realized something. I am not storing this as utf-8, it is stored as
unicode. (The storing is performed via hibernate.) When I view the data in
the Sql Server Enterprise Manager it displays the chinese character, not a 2
byte sequence as you would see for utf-8 (or I assume you would see).

I have tried with the CharSet not set - still not good.

????

--
thanks - dave

"Steven Cheng[MSFT]" wrote:

> | > | character. So a field with "aÃ©Â¢Â¨bÃ Â±c" returns "a?b?c"

David Thielen

unread,

Sep 6, 2005, 9:32:12 AM9/6/05

to

Attached is a program I wrote to test - all attempts return x3f for the
chinese & russian character.

package net.windward.store.util.test;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.Properties;

public class TestJdbcUnicode {

private static String className =
"com.microsoft.jdbc.sqlserver.SQLServerDriver";
private static String url =
"jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=StoreTest";
private static String username = "sa";
private static String password = "*****";

public static void main(String[] args) throws Exception {

String textInDb = "a風bжc";
System.out.println("text = " + textInDb);
displayString(textInDb);

textInDb = "a\ue758b\u0436c";
System.out.println("text = " + textInDb);
displayString(textInDb);

System.out.println("Standard open");
Class.forName(className).newInstance();
Connection conn = DriverManager.getConnection(url, username, password);

Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("select * from Person where PersonId =
25325");
displayResult(rs);

System.out.println("Properties open");
Class.forName(className).newInstance();

Properties info = new Properties();
info.put("user", username);
info.put("password", password);

conn = DriverManager.getConnection(url, info);

stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from Person where PersonId = 25325");
displayResult(rs);

System.out.println("Properties open UTF-8");
Class.forName(className).newInstance();

info = new Properties();
info.put("user", username);
info.put("password", password);
info.put("charSet", "UTF-8");

conn = DriverManager.getConnection(url, info);

stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from Person where PersonId = 25325");
displayResult(rs);

System.out.println("Properties open UTF-16");
Class.forName(className).newInstance();

info = new Properties();
info.put("user", username);
info.put("password", password);

info.put("charSet", "UTF-16");
conn = DriverManager.getConnection(url, info);

stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from Person where PersonId = 25325");
displayResult(rs);

System.out.println("Properties open unicode");
Class.forName(className).newInstance();

info = new Properties();
info.put("user", username);
info.put("password", password);

info.put("charSet", "unicode");
conn = DriverManager.getConnection(url, info);

stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from Person where PersonId = 25325");
displayResult(rs);
}

private static void displayResult(ResultSet rs) throws Exception {

if (! rs.next()) {
System.err.println("No results");
return;
}

String rtn = rs.getString("name");
displayString(rtn);
}

private static void displayString(String rtn) {

System.out.println("rtn = " + rtn);
System.out.print("rtn[] = ");
for (int ind=0; ind<rtn.length(); ind++)
System.out.print("x" + Integer.toHexString((int)rtn.charAt(ind)) + " ");
}
}

Steven Cheng[MSFT]

unread,

Sep 8, 2005, 3:23:46 AM9/8/05

to

Hi Dave,

What's the version of JDBC SQLserver driver are you using , would you point
me the link? Also, as you mentioned that when you configure the JDBC driver
to be used through hibernate, hibernate can correctly retrieve the wide
char strings? If so, I think the problem should be something with any
configuration or property setting on the JDBC driver or the related
classes. I'll help you have a consult on some other SQL guys, however, IMO,
it'd be better for you to try posting in some JAVA community or forums
since there may have some experienced JAVA guy who already got it. I'll
update you when I got any new findings.

Thanks,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

--------------------
| Thread-Topic: JDBC - Can't get unicode with ResultSet.getString()

| thread-index: AcWy52MepuDxciepSBm6bd4FwLfdMg==

| X-WBNR-Posting-Host: 199.45.247.98
| From: "=?Utf-8?B?RGF2aWQgVGhpZWxlbg==?=" <thi...@nospam.nospam>
| References: <DEBAF362-6134-43F1...@microsoft.com>
<u3AUb1es...@TK2MSFTNGXA01.phx.gbl>

<9A43688E-224B-4617...@microsoft.com>
<E63pW9ts...@TK2MSFTNGXA01.phx.gbl>

| Subject: RE: JDBC - Can't get unicode with ResultSet.getString()

| Date: Tue, 6 Sep 2005 06:32:12 -0700
| Lines: 112
| Message-ID: <2DCC5DDB-B647-41E4...@microsoft.com>

| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2671
| X-Tomcat-NG: microsoft.public.sqlserver.odbc