Localization of queries

[didier.besset]

Hello,
I need to execute a query using localization (via the SET lc_time_names = 'xx_XX'). How do I insert this command as part of a query? ";" does not work...
Cheers,
Didier

[UnklDonald418]

As you found, OO Base only allows Queries beginning with the SELECT command.
OO Base will allow the execution of SET commands, but only at Tools>SQL, so the SET and SELECT commands must be executed separately. However, when using LO Base, Tools>SQL has an option to Show output of "select" statements, which should allow execution of both statements together.
I tried executing SET lc_time_names = 'xx_XX' at Tools>SQL but the HSQL database engine returned object not found: LC_TIME_NAMES. Next I searched an HSQL user guide and found nothing related to lc_time_names which begs the question, what database engine are you using for the backend of your database?

[Villeroy]

My search engine indicates that SET lc_time_names may be related to MySQL and/or MariaDB. Without this information we do not even know which software we are talking about

[didier.besset]

I am using OpenOffice 4.1.10 Base HSQL on Windows 10 or Mac OS.
Cheers,
Didier

[UnklDonald418]

Look in the lower left corner of the main Base window if it displays Embedded database, I could find no support for time zones.

If is displays JDBC then the following is from the HSQL users manual beginning with HSQL version 2.3.2

Session Time Zone Displacement
When an SQL session is started (with a JDBC connection) the local time zone of the client JVM (including any seasonal
time adjustments such as daylight saving time) is used as the session time zone displacement. Note that the SQL session
time displacement is not changed when a seasonal time adjustment takes place while the session is open. To change
the SQL session time zone displacement, use the following commands:

SET TIME ZONE
set local time zone statement
<set local time zone statement> ::= SET TIME ZONE <set time zone value>
<set time zone value> ::= <interval value expression> | LOCAL
Set the current default time zone displacement for the current SQL-session. When the session starts, the time zone
displacement is set to the time zone of the client. This command changes the time zone displacement. The effect of
this lasts for the lifetime of the session. If LOCAL is specified, the time zone displacement reverts to the local time
zone of the session.
Example 3.8. Setting Session Time Zone

Code: Select all

SET TIME ZONE LOCAL
SET TIME ZONE INTERVAL '+6:00' HOUR TO MINUTE

The same limitations still apply, the SET command can only be executed at Tools>SQL

[didier.besset]

Thanks for all the replies...
The real problem behind my request is the function MONTHNAME() which returns month names in English only.
So, the only way out is to do one's own localization: setting up a table of month names for each target language and use the function MONTH() together with a JOIN instead.
Kind of reinventing the wheel, but this gets the problem solved at least.
Cheers,
Didier

[Villeroy]

Create a table and map month numbers to localized names.

Code: Select all

CREATE TABLE MONTHS (N VARCHAR(20) NOT NULL, ID TINY INT PRIMARY KEY);
INSERT INTO MONTHS VALUES ('Januar',1);
INSERT INTO MONTHS VALUES ('Februar',2);
INSERT INTO MONTHS VALUES ('März',3);
INSERT INTO MONTHS VALUES ('April',4);
INSERT INTO MONTHS VALUES ('Mai',5);
INSERT INTO MONTHS VALUES ('Juni',6);
INSERT INTO MONTHS VALUES ('Juli',7);
INSERT INTO MONTHS VALUES ('August',8);
INSERT INTO MONTHS VALUES ('September',9);
INSERT INTO MONTHS VALUES ('Oktober',10);
INSERT INTO MONTHS VALUES ('November',11);
INSERT INTO MONTHS VALUES ('Dezember',12);

Now you can look up the localized month name by the Month("DateValue") function which returns an integer

[UnklDonald418]

I'm not sure if it would be of any help in your situation, but if a Date is displayed on a Base Form using a Formatted field control there are options for displaying dates with the month name. The Formatted field control uses the Locale for the default language, or the control has an option to choose a different language for that control.