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