Can't get unicode with ResultSet.getString()

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\u9f23b\u4011c" returns "a?b?c"
What do I need to set/do to get back the unicode values in the
database?
thanks - dave
david@.at-at-at@.windward.dot.dot.net
Windward Reports -- http://www.WindwardReports.com
Page 2 Stage -- http://www.Page2Stage.com
Enemy Nations -- http://www.EnemyNations.com
me -- http://dave.thielen.com
Barbie Science Fair -- http://www.BarbieScienceFair.info
(yes I have lots of links)
Here is some code that illustrates the problem - all return a?b?c
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;
import java.io.InputStream;
import java.io.Reader;
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 = "mmouse";
public static void main(String[] args) throws Exception {
String textInDb = "a?b?c"; // was the actual text (not
\u)
System.out.println("text = " + textInDb);
displayString(textInDb);
textInDb = "a\u98a8b\u0436c";
System.out.println("text = " + textInDb);
displayString(textInDb); // only correct display
System.out.println("Standard open");
Class.forName(className).newInstance();
Connection conn = DriverManager.getConnection(url,
username, password);
Statement stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_INSENSI TIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("select * from Person
where PersonId = 25325");
displayResult(rs);
byte [] data = new byte[10];
InputStream is = rs.getAsciiStream("name");
int len = is.read(data);
//obj = rs.getBytes("name");
Reader rdr = rs.getCharacterStream("name");
char [] cbuf = new char[10];
len = rdr.read(cbuf);
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_INSENSI TIVE,
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_INSENSI TIVE,
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_INSENSI TIVE,
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_INSENSI TIVE,
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)) + " ");
}
}
david@.at-at-at@.windward.dot.dot.net
Windward Reports -- http://www.WindwardReports.com
Page 2 Stage -- http://www.Page2Stage.com
Enemy Nations -- http://www.EnemyNations.com
me -- http://dave.thielen.com
Barbie Science Fair -- http://www.BarbieScienceFair.info
(yes I have lots of links)
|||Yes this is a bug (I am assuming you are using the SQL Server 2005 JDBC
driver Beta2, perhaps not). We cut the getAsciiStream function we had a few
serious bugs in it that were not easy to resolve in a clear cut manner. The
JDBC spec is not super clear on how it is supposed to work.
For example, should getAsciiStream take the incoming TDS character data and
convert this to US-ASCII? What if the incoming data is in Japanese
collation and this is a lossy conversion, etc... there are lots of
situations where this could be lossy. Also, does it just mean send back the
raw bytes? Then why is it called Ascii Stream, etc... It makes my head
hurt bad when I am writing support for SQL Server's 1000+ TDS language
collations and I don't want to be lossy and corrupt customer data.
I think you can use getBytes to work around this for now, something like
this I believe ->
ByteArrayInputStream bas = new ByteArrayInputStream(rs.getBytes(2));
This might give you bytes in UNICODE that would necessitate you converting
to single byte stream, but this depends upon your back end collation. If
the collation is a simple 2:1 UNICODE -> Single Byte mapping then it is easy
to strip out every other byte.
You can do things like this as well:
InputStreamReader isr = new InputStreamReader(new
ByteArrayInputStream(rs.getBytes(2)), "US-ASCII");
Twiggle around with the "US-ASCII", maybe you want to convert using some
other encoding like UTF-8, etc...
Let me know which driver you are using and perhaps I can come up with a
better solution.
Also, I would like to hear your reasoning behind using getAsciiStream, why
do you find you need to use this API, let me know about this, this would be
good feedback for our team working on the SQL JDB 2005 driver.
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" <david@.windward.net> wrote in message
news:c1srh1te9er4nh0h089oagua0frhgupuhm@.4ax.com...
> Here is some code that illustrates the problem - all return a?b?c
> 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;
> import java.io.InputStream;
> import java.io.Reader;
> 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 = "mmouse";
> public static void main(String[] args) throws Exception {
> String textInDb = "a?b?c"; // was the actual text (not
> \u)
> System.out.println("text = " + textInDb);
> displayString(textInDb);
> textInDb = "a\u98a8b\u0436c";
> System.out.println("text = " + textInDb);
> displayString(textInDb); // only correct display
> System.out.println("Standard open");
> Class.forName(className).newInstance();
> Connection conn = DriverManager.getConnection(url,
> username, password);
> Statement stmt =
> conn.createStatement(ResultSet.TYPE_SCROLL_INSENSI TIVE,
> ResultSet.CONCUR_UPDATABLE);
> ResultSet rs = stmt.executeQuery("select * from Person
> where PersonId = 25325");
> displayResult(rs);
> byte [] data = new byte[10];
> InputStream is = rs.getAsciiStream("name");
> int len = is.read(data);
> // obj = rs.getBytes("name");
> Reader rdr = rs.getCharacterStream("name");
> char [] cbuf = new char[10];
> len = rdr.read(cbuf);
>
> 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_INSENSI TIVE,
> 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_INSENSI TIVE,
> 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_INSENSI TIVE,
> 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_INSENSI TIVE,
> 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)) + " ");
> }
> }
>
> david@.at-at-at@.windward.dot.dot.net
> Windward Reports -- http://www.WindwardReports.com
> Page 2 Stage -- http://www.Page2Stage.com
> Enemy Nations -- http://www.EnemyNations.com
> me -- http://dave.thielen.com
> Barbie Science Fair -- http://www.BarbieScienceFair.info
> (yes I have lots of links)

Can't get unicode strings using JDBC driver

Hi;

If I call the following to get a Statement, ResultSet.getString() works fine with unicode strings:
Statement stmt = conn.createStatement();

But If I use the following:
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);

Then all chars > 127 are returned as an ? from ResultSet.getString().

I think this is a bug.

? - thanks - dave

ps - I believe it's the latest Sql Server 200 drivers - they are all dated 4/21/04|||David --

It looks like this was resolved in the microsoft.public.sqlserver.jdbcdriver forum. Let us know if you still have issues.

-shelby

Can't get unicode strings using JDBC driver

Hi;

If I call the following to get a Statement, ResultSet.getString() works fine with unicode strings:
Statement stmt = conn.createStatement();

But If I use the following:
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);

Then all chars > 127 are returned as an ? from ResultSet.getString().

I think this is a bug.

? - thanks - dave

ps - I believe it's the latest Sql Server 200 drivers - they are all dated 4/21/04|||David --

It looks like this was resolved in the microsoft.public.sqlserver.jdbcdriver forum. Let us know if you still have issues.

-shelby

Can't get unicode strings

Hi;
If I call the following to get a Statement, ResultSet.getString()
works fine with unicode strings:
Statement stmt = conn.createStatement();
But If I use the following:
Statement stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_INSENSI TIVE,
ResultSet.CONCUR_UPDATABLE);
Then all chars > 127 are returned as an ?
I think this is a (BIG BIG BIG) bug.
? - thanks - dave
david@.at-at-at@.windward.dot.dot.net
Windward Reports -- http://www.WindwardReports.com
Page 2 Stage -- http://www.Page2Stage.com
Enemy Nations -- http://www.EnemyNations.com
me -- http://dave.thielen.com
Barbie Science Fair -- http://www.BarbieScienceFair.info
(yes I have lots of links)
What driver are you using? 2000, 2005 beta 1 or 2005 beta 2?
Thanks,
Angel Saenz-Badillos [MS] DataWorks
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.
I am now blogging: http://weblogs.asp.net/angelsb/
"David Thielen" <david@.windward.net> wrote in message
news:3rqsk1tti6dmn9dr5ifevdsls612iqu7hs@.4ax.com...
> Hi;
> If I call the following to get a Statement, ResultSet.getString()
> works fine with unicode strings:
> Statement stmt = conn.createStatement();
> But If I use the following:
> Statement stmt =
> conn.createStatement(ResultSet.TYPE_SCROLL_INSENSI TIVE,
> ResultSet.CONCUR_UPDATABLE);
> Then all chars > 127 are returned as an ?
> I think this is a (BIG BIG BIG) bug.
> ? - thanks - dave
> david@.at-at-at@.windward.dot.dot.net
> Windward Reports -- http://www.WindwardReports.com
> Page 2 Stage -- http://www.Page2Stage.com
> Enemy Nations -- http://www.EnemyNations.com
> me -- http://dave.thielen.com
> Barbie Science Fair -- http://www.BarbieScienceFair.info
> (yes I have lots of links)
|||I think the latest SqlServer 2000 ones. They are dated 4-21-04
thanks - dave
On Fri, 14 Oct 2005 15:13:15 -0700, "Angel Saenz-Badillos[MS]"
<angelsa@.online.microsoft.com> wrote:

>What driver are you using? 2000, 2005 beta 1 or 2005 beta 2?
>Thanks,
david@.at-at-at@.windward.dot.dot.net
Windward Reports -- http://www.WindwardReports.com
Page 2 Stage -- http://www.Page2Stage.com
Enemy Nations -- http://www.EnemyNations.com
me -- http://dave.thielen.com
Barbie Science Fair -- http://www.BarbieScienceFair.info
(yes I have lots of links)
|||Dave:
That behavior doesn't reproduce for me -- can you post some code?
-shelby
"David Thielen" <david@.windward.net> wrote in message
news:teh0l19vvmg9gc2fvkes9a24rh9jpr9otv@.4ax.com...
>I think the latest SqlServer 2000 ones. They are dated 4-21-04
> thanks - dave
> On Fri, 14 Oct 2005 15:13:15 -0700, "Angel Saenz-Badillos[MS]"
> <angelsa@.online.microsoft.com> wrote:
>
> david@.at-at-at@.windward.dot.dot.net
> Windward Reports -- http://www.WindwardReports.com
> Page 2 Stage -- http://www.Page2Stage.com
> Enemy Nations -- http://www.EnemyNations.com
> me -- http://dave.thielen.com
> Barbie Science Fair -- http://www.BarbieScienceFair.info
> (yes I have lots of links)
|||On Mon, 17 Oct 2005 18:03:36 -0700, "Shelby Goerlitz [MSFT]"
<shelbyg@.online.microsoft.com> wrote:

>Dave:
>That behavior doesn't reproduce for me -- can you post some code?
Please take a look at http://www.windward.net/jdbc_unicode.zip Two key
notes:
1) use conn.createStatement(ResultSet.TYPE_SCROLL_INSENSI TIVE,
ResultSet.CONCUR_UPDATABLE) - if you just do conn.createStatement() it
works.
2) If you create a database with, for example Chinese as the default
charset, then Chinese can be retrieved - but not Russian. So the
"default" charset seems to be ok - but not other charsets. If your
default charset is English then most anything > 127 is a problem.
thanks - dave

>-shelby
>"David Thielen" <david@.windward.net> wrote in message
>news:teh0l19vvmg9gc2fvkes9a24rh9jpr9otv@.4ax.com.. .
>
david@.at-at-at@.windward.dot.dot.net
Windward Reports -- http://www.WindwardReports.com
Page 2 Stage -- http://www.Page2Stage.com
Enemy Nations -- http://www.EnemyNations.com
me -- http://dave.thielen.com
Barbie Science Fair -- http://www.BarbieScienceFair.info
(yes I have lots of links)
|||Hi;
Any luck finding the bug using the example I created?
thanks - dave
On Tue, 18 Oct 2005 07:58:16 -0600, David Thielen <david@.windward.net>
wrote:

>On Mon, 17 Oct 2005 18:03:36 -0700, "Shelby Goerlitz [MSFT]"
><shelbyg@.online.microsoft.com> wrote:
>
>Please take a look at http://www.windward.net/jdbc_unicode.zip Two key
>notes:
>1) use conn.createStatement(ResultSet.TYPE_SCROLL_INSENSI TIVE,
>ResultSet.CONCUR_UPDATABLE) - if you just do conn.createStatement() it
>works.
>2) If you create a database with, for example Chinese as the default
>charset, then Chinese can be retrieved - but not Russian. So the
>"default" charset seems to be ok - but not other charsets. If your
>default charset is English then most anything > 127 is a problem.
>thanks - dave
>
>
>david@.at-at-at@.windward.dot.dot.net
>Windward Reports -- http://www.WindwardReports.com
>Page 2 Stage -- http://www.Page2Stage.com
>Enemy Nations -- http://www.EnemyNations.com
>me -- http://dave.thielen.com
>Barbie Science Fair -- http://www.BarbieScienceFair.info
>(yes I have lots of links)
david@.at-at-at@.windward.dot.dot.net
Windward Reports -- http://www.WindwardReports.com
Page 2 Stage -- http://www.Page2Stage.com
Enemy Nations -- http://www.EnemyNations.com
me -- http://dave.thielen.com
Barbie Science Fair -- http://www.BarbieScienceFair.info
(yes I have lots of links)
|||Dave:
Sorry for the delay -- the issue has been escalated on this side and the dev
team is looking at it.
-shelby
"David Thielen" <david@.windward.net> wrote in message
news:cq5jl1d9gk6csfr197rc2lbaqfsk4lqgbs@.4ax.com...
> Hi;
> Any luck finding the bug using the example I created?
> thanks - dave
>
> On Tue, 18 Oct 2005 07:58:16 -0600, David Thielen <david@.windward.net>
> wrote:
>
> david@.at-at-at@.windward.dot.dot.net
> Windward Reports -- http://www.WindwardReports.com
> Page 2 Stage -- http://www.Page2Stage.com
> Enemy Nations -- http://www.EnemyNations.com
> me -- http://dave.thielen.com
> Barbie Science Fair -- http://www.BarbieScienceFair.info
> (yes I have lots of links)