SAP loves to make a point of eating its own dog food and so lowly SAP clerks like yours-truly are offered the experience to participate in multiple user communities be it SCN or one of the many SAP JAM groups.
In one of the SAP internal groups I recently answered a question, that I believe will be interesting to SCN readers, too.
So this is it:
"... a user want to have the client display in English format.
Decimal values are displayed in german format "123,5", but they want to have it the english format "123.5" - 'decimal point is point'.
[...]
I set the properties for the system in HANA Studio (or Eclipse) with Properties --> Additional Properties --> Locale "English".
I also tried to set the User Parameter 'LOCALE' for the user to "EN" or "EN-EN", also without effect.
Has anybody an idea, which client setting has to be chosen to get decimal values displayed in english format? ... "
To answer this question there are a couple of things to be aware of.
Pieces of the puzzle
Piece 1: SAP HANA Studio has a preference setting to switch on or off result data formatting.
Piece 2: SAP HANA Studio is a Eclipse based product.
Piece 3: Eclipse is written in JAVA.
Piece 4: JAVA provides a rich set of API functions to format data, exposed via Formatter objects.
Piece 5: The JAVA Formatter objects are using so-called Locales, which are objects that bundle localization specific settings.
Putting the pieces together
Whether or not the SAP HANA studio formats the data in the SQL result set grid depends on the setting SAP HANA -> Runtime -> Result -> Format values.
Now, having activated the formatting, we can see that e.g. numeric data with decimal places gets formatted.
When I am logged on with a German Windows language setting, I will see that the number 1234.56 will be printed out as '1.234,56' - as the thousands delimiter for Germany is the dot '.' and the decimal separator is the comma ','.
With English language settings this would have been the other way round.
If you ask yourself "Why would I ever switch this off?", then it's probably good to know that formatting lots of data is not a trivial task and can take up a considerable time when printing your results. That might slow down your development cycle of re-running queries considerably...
"Stop talking, show us how it looks like!"
... I hear you say, so here you go:
Formatting Enabled, German locale settings
Behold and compare to the output with formatting switched off:
Formatting disabled, output as raw as possible in SAP HANA Studio
SAP HANA Studio provides a place where one can specify the session language for a user connection and it is even called 'Locale':
Irritatingly this setting only affects the SAP HANA behavior within a SAP HANA DB session. That is, text joins and language filters use that setting to return language dependent data.
An important part in understanding why this setting doesn't help here is to understand that the whole business of printing data out to the screen is done by the client application and not by the database server. This includes formatting the output.
Technically speaking, numbers don't have a format to the database.
In the database numbers have a scale and a number of significant fractional digits.
If and how those are printed out is a different matter - just like your good old TI-30 would calculate with 11 significant digits internally, while displaying 8 of them at max.
Having said that, I would agree with the notion that when we have a setting called LOCALE this should either change the 'whole experience' or there should be more specific setting options. Something like 'output locale' or so... (like here API: Internationalization).
Anyhow, the point is: the LOCALE setting with the connection doesn't fix our formatting requirement.
Fiddling with the invisible pieces
So, we know that the data gets formatted via a JAVA Formatter, but apparently the LOCALE setting doesn't set this thing up for us.
Checking the JAVA SDK documentation (LOCALE and FORMATTER again) the avid reader figures out:
If the application doesn't specify the LOCALE for the FORMATTER it uses the LOCALE that is currently active in the JAVA VM.
As it turns out, the default JVM behavior is to try to figure out the locale setting of the operating system user that is running the JVM.
But there are options to overwrite this, as we find here.
Working the puzzle from the edges
So, we can specify command line arguments when starting a JVM to set locale parameters:
-Duser.language=EN , -Duser.country=UK and -Duser.variant=EN
This is all great, but how to start the SAP HANA Eclipse with such parameters?
Putting in the final pieces
This last piece is actually easy.
Eclipse uses a parameter file called eclipse.ini located in the folder where you find the the Eclipse executable.
SAP HANA Studio simply uses a renamed version of those files, so the parameter file we're looking for is called hdbstudio.ini just as the executable is called hdbstudio.exe.
-startup
plugins/org.eclipse.equinox.launcher_1.3.0.v20140415-2008.jar
--launcher.library
plugins/org.eclipse.equinox.launcher.win32.win32.x86_64_1.1.200.v20150204-1316
-vm
plugins/com.sap.ide.sapjvm.jre.win32.x86_64_81.0.0/jre/bin
--launcher.XXMaxPermSize
512m
--launcher.defaultAction
openFile
--launcher.appendVmargs
-vmargs
-Xmx1024m
-Dfile.encoding=UTF-8
-Xms256m
-XX:MaxPermSize=512m
-XX:+HeapDumpOnOutOfMemoryError
Looking into this file we find that it contains a part that starts with -vmargs.
This section allows to specify parameters for the JVM used by Eclipse respectively SAP HANA Studio.
Putting in the desired locale parameters like this
[...]
-vmargs
-Xmx1024m
-Dfile.encoding=UTF-8
-Duser.language=en
-Duser.country=UK
-Duser.variant=EN
-Xms256m
-XX:MaxPermSize=512m
-XX:+HeapDumpOnOutOfMemoryError
will make provide the setting we are looking for.
Be aware that in order to be able to safe this file on MS Windows you will need to have elevated privileges.
The final picture
To set the new settings active it is required to restart SAP HANA Studio.
We can see that the formatting option now uses the specified locale.
Formatting Enabled, English locale settings
Unfortunately the setting is active for all connections used by this SAP HANA Studio.
So we don't have a proper user specific setting for the data formatting - a workaround at best.
This puts SAP HANA Studio clearly into not being a data consumer/end-user tool.
It's an administration and development tool, just like it had been positioned since day one.
Having the picture completed, we can take a last look at our puzzle and pack it away again.
There you go - now you know.
Cheers,
Lars
p.s.
An alternative approach would have been to set the JVM arguments via environment variables.
This approach would allow to easily create several different startup scripts that first set the parameter and then start SAP HANA Studio.
If you got the gist of this post, you shouldn't have issue plugging this together.
- SAP Managed Tags:
3 Comments
