You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by db...@kaffeeschluerfer.com on 2011/12/08 13:21:18 UTC
German Sharp S and UCASE
Hello,
I'm trying to use UCASE to "normalize" different case variants of text field values. There's just a little problem. It works fine for all normal characters including German umlauts (Ä, Ö, Ü, ä, ö, ü) ...
==vv= COPIED FROM IJ CONSOLE =vv==
ij> VALUES UCASE('ÜbErSeTzUnG');
1
-----------
ÜBERSETZUNG
1 Zeile ausgewählt
==================================
... but it seems to cause trouble with the "German Sharp S": ß
(See http://en.wikipedia.org/wiki/Sharp_S)
==vv= COPIED FROM IJ CONSOLE =vv==
ij> VALUES UCASE('Straßenbahn');
1
-----------
STRASSENBA&
1 Zeile ausgewählt
==================================
It correctly (!) replaces "ß" by "SS" but then it truncates the resulting string, thereby appending an ampersand "&".
How can I make it return the complete, correct result "STRASSENBAHN"?
(Which, by the way, means "streetcar". ;-))
My DERBY_OPTS look like this:
DERBY_OPTS=-Dderby.ui.locale=de_DE -Dderby.ui.codeset=Cp850
Many thanks in advance for your help!
--
NEU: FreePhone - 0ct/min Handyspartarif mit Geld-zurück-Garantie!
Jetzt informieren: http://www.gmx.net/de/go/freephone
Re: German Sharp S and UCASE
Posted by Rick Hillegas <ri...@oracle.com>.
On 12/8/11 7:16 AM, Bryan Pendleton wrote:
>> ResultSet rs = s.executeQuery("values upper('Straße')");
>>
>> So it seems the value is returned correctly, but the meta-data is wrong
>> (STRASSE is 7 characters long, not 6). ij uses the meta-data to
>> determine how much space each column should have.
>
> 6 *characters* long, but 7 *bytes* long?
>
> Do we have a way to represent such a difference?
The javadoc for ResultSetMetaData.getColumnDisplaySize() says that the
method returns a length in characters, not bytes. For character data,
ResultSetMetaData.getPrecision() is also supposed to return a length in
characters. Since there is no JDBC standard way of declaring the
application's character encoding, I don't think we can expect more
support out of the JDBC driver. Maybe ij could figure out the maximum
number of bytes per character in the codeset specified by
derby.ui.codeset. Then ij could multiply that by
ResultSetMetaData.getColumnDisplaySize() in order to calculate the
actual column display size.
>
> Certainly sounds like a bug, and with a nice small test case!
As a workaround, the value can be cast to a slightly larger length:
values cast( ucase('Straßenbahn') as varchar( 12 ) );
Hope this helps,
-Rick
>
> bryan
>
>
>
Re: German Sharp S and UCASE
Posted by Bernt Marius Johnsen <be...@oracle.com>.
Generally, in Unicode, it can't be assumed that
length(string) == length(uppercase(string))
or that
length(string) == length(lowercase(string))
So it's not a German-specific feature, and there is obviously a bug in
Derby based on that wrong assumption.
Furthermore in the same vein, it can't be assumed that
uppercase(lowercase(uppercasestring)) == uppercasestring
nor that
lowercase(uppercase(lowercasestring)) == lowercasestring
(e.g. lowercase(uppercase('straße')) gives 'strasse')
Bernt
Re: German Sharp S and UCASE
Posted by Knut Anders Hatlen <kn...@oracle.com>.
Bryan Pendleton <bp...@gmail.com> writes:
>> ResultSet rs = s.executeQuery("values upper('Straße')");
>>
>> So it seems the value is returned correctly, but the meta-data is wrong
>> (STRASSE is 7 characters long, not 6). ij uses the meta-data to
>> determine how much space each column should have.
>
> 6 *characters* long, but 7 *bytes* long?
Nope, "STRASSE" is both 7 characters and 7 bytes. :)
S T R A S S E
1 2 3 4 5 6 7
"Straße" is 6 characters long (and 7 bytes in UTF-8).
> Do we have a way to represent such a difference?
I don't think so. We return character strings, not byte arrays, and the
byte length will vary between different encondings.
> Certainly sounds like a bug, and with a nice small test case!
Now filed in JIRA:
https://issues.apache.org/jira/browse/DERBY-5525
--
Knut Anders
Re: German Sharp S and UCASE
Posted by Bryan Pendleton <bp...@gmail.com>.
> ResultSet rs = s.executeQuery("values upper('Straße')");
>
> So it seems the value is returned correctly, but the meta-data is wrong
> (STRASSE is 7 characters long, not 6). ij uses the meta-data to
> determine how much space each column should have.
6 *characters* long, but 7 *bytes* long?
Do we have a way to represent such a difference?
Certainly sounds like a bug, and with a nice small test case!
bryan
Re: German Sharp S and UCASE
Posted by Knut Anders Hatlen <kn...@oracle.com>.
Myrna van Lunteren <m....@gmail.com> writes:
> On Thu, Dec 8, 2011 at 4:21 AM, <db...@kaffeeschluerfer.com> wrote:
>> Hello,
>>
>> I'm trying to use UCASE to "normalize" different case variants of
>> text field values. There's just a little problem. It works fine for
>> all normal characters including German umlauts (Ä, Ö, Ü, ä, ö, ü)
>> ...
>>
>>
>> ==vv= COPIED FROM IJ CONSOLE =vv==
>> ij> VALUES UCASE('ÜbErSeTzUnG');
>> 1
>> -----------
>> ÜBERSETZUNG
>>
>> 1 Zeile ausgewählt
>> ==================================
>>
>>
>> ... but it seems to cause trouble with the "German Sharp S": ß
>> (See http://en.wikipedia.org/wiki/Sharp_S)
>>
>>
>> ==vv= COPIED FROM IJ CONSOLE =vv==
>> ij> VALUES UCASE('Straßenbahn');
>> 1
>> -----------
>> STRASSENBA&
>>
>> 1 Zeile ausgewählt
>> ==================================
>>
>>
>> It correctly (!) replaces "ß" by "SS" but then it truncates the resulting string, thereby appending an ampersand "&".
>>
>> How can I make it return the complete, correct result "STRASSENBAHN"?
>> (Which, by the way, means "streetcar". ;-))
>>
>>
>> My DERBY_OPTS look like this:
>> DERBY_OPTS=-Dderby.ui.locale=de_DE -Dderby.ui.codeset=Cp850
>>
>>
>> Many thanks in advance for your help!
>>
>> --
>> NEU: FreePhone - 0ct/min Handyspartarif mit Geld-zurück-Garantie!
>> Jetzt informieren: http://www.gmx.net/de/go/freephone
>
> Hi,
>
> The value is likely returned correctly, but you're hitting the maximum
> display width for columns in ij.
> This is controlled by the property ij.maximumDisplayWidth, and you can
> also set it during your ij session using maximumDisplayWidth.
> I think the default is 10.
The default is 128, actually, which should be enough for Straßenbahn. :)
It looks like the result set meta-data gets confused by this
transformation, as can be seen by this code snippet:
Connection c = DriverManager.getConnection(
"jdbc:derby:memory:db;create=true;territory=de_DE;" +
"collation=TERRITORY_BASED");
Statement s = c.createStatement();
ResultSet rs = s.executeQuery("values upper('Straße')");
System.out.println(rs.getMetaData().getPrecision(1));
rs.next();
System.out.println(rs.getString(1));
This prints
6
STRASSE
So it seems the value is returned correctly, but the meta-data is wrong
(STRASSE is 7 characters long, not 6). ij uses the meta-data to
determine how much space each column should have.
--
Knut Anders
Re: German Sharp S and UCASE
Posted by Myrna van Lunteren <m....@gmail.com>.
On Thu, Dec 8, 2011 at 4:21 AM, <db...@kaffeeschluerfer.com> wrote:
> Hello,
>
> I'm trying to use UCASE to "normalize" different case variants of text field values. There's just a little problem. It works fine for all normal characters including German umlauts (Ä, Ö, Ü, ä, ö, ü) ...
>
>
> ==vv= COPIED FROM IJ CONSOLE =vv==
> ij> VALUES UCASE('ÜbErSeTzUnG');
> 1
> -----------
> ÜBERSETZUNG
>
> 1 Zeile ausgewählt
> ==================================
>
>
> ... but it seems to cause trouble with the "German Sharp S": ß
> (See http://en.wikipedia.org/wiki/Sharp_S)
>
>
> ==vv= COPIED FROM IJ CONSOLE =vv==
> ij> VALUES UCASE('Straßenbahn');
> 1
> -----------
> STRASSENBA&
>
> 1 Zeile ausgewählt
> ==================================
>
>
> It correctly (!) replaces "ß" by "SS" but then it truncates the resulting string, thereby appending an ampersand "&".
>
> How can I make it return the complete, correct result "STRASSENBAHN"?
> (Which, by the way, means "streetcar". ;-))
>
>
> My DERBY_OPTS look like this:
> DERBY_OPTS=-Dderby.ui.locale=de_DE -Dderby.ui.codeset=Cp850
>
>
> Many thanks in advance for your help!
>
> --
> NEU: FreePhone - 0ct/min Handyspartarif mit Geld-zurück-Garantie!
> Jetzt informieren: http://www.gmx.net/de/go/freephone
Hi,
The value is likely returned correctly, but you're hitting the maximum
display width for columns in ij.
This is controlled by the property ij.maximumDisplayWidth, and you can
also set it during your ij session using maximumDisplayWidth.
I think the default is 10.
This is documented in the Derby Tools and Utilities Guide, for
instance for the latest alpha manuals:
http://db.apache.org/derby/docs/dev/tools/tools-single.html
Myrna