SQL-Ledger Homepage

SQL-Ledger User Forum

Forum index page

Log in | Register

Back to index page
Thread view  Board view
hartundweich(R)

Homepage

Austria,
05.04.2016, 18:17
 

Still problems with german special characters (General)

Hello,

I thought I've solved this issue - but today it turned out, that it is not solved.
I'm running SQL Ledger 3.2.0 with apache 2.4.18 on a Gentoo System.

If I add "AddDefaultCharset On" to my vhost configuration and swith IE to force Unicode Display I can add a new recordset that is correct stored in my database and correct shown in Frontend.

When I edit this recordset and click on "save" the special characters are destroyed - also in the database...

Any hint how to solve?

I have already tried different methods of "AddDefaultCharset" in my vhost configuration - I've placed it inside and outside <VirtualHost>

It seems that somethin does not work as expected... In Apache 2.2 everything was working like a charme - why is 2.4 so heavy to get to work with SQL Ledger?

When we cannot solve this issue I think we have to switch back to Apache 2.2 - but I do not really want to do this..

Available system locales:
C
de_AT.utf8
en_US.utf8
POSIX

My system environment:
LANG=en_US.utf8

PostgreSQL charset for database:
Encoding: UTF8
Collation: en_US.utf8
Character Type: en_US.utf8

I think these variables look pretty fine - so I'm really really thinking my apache 2.4 does not work properly - maybe it is necessary to install charset_lite modul?

Thanks for any tips and hints..

localguru(R)

E-mail

Germany,
06.04.2016, 11:08

@ hartundweich
 

Still problems with german special characters

Hi,

just some ideas:

Did you set language (Sprache) to German (UTF-8) in your user preferences?

What does your user config in users/ look like:

%myconfig = (
countrycode => 'de_utf',
...
dboptions => 'set DateStyle to \'GERMAN\'',
...
}

Ciao
Marcus

hartundweich(R)

Homepage

Austria,
06.04.2016, 17:13

@ localguru
 

Still problems with german special characters

Hi Marcus,

just looked once again in my users/user@dataset.conf file:

dboptions => 'set DateStyle to \'GERMAN\'',
countrycode => 'de_utf',

IMHO everything should work as expected - but it does not :-(

Also when I try to create a new dataset - same problems with german characters.

BTW: I cannot import Austrian-chart.sql because the SQL File is not UTF-8 encoded - but in my opinion these german special characters should also work with an eglish chart... Or am I wrong and something on my system does not work as expected?

In the meanwhile I know, that I can create new customers, goods, employees, etc. with the webfrontend but when I have to edit these records I have to go to PHPPGADMIN to correct the special german characters if there are some in the edited recordset - this can only be a temporary solution and has to be fixed for me as soon as possible - but where and what to search for?

Regards
Andreas Fragner

hartundweich(R)

Homepage

Austria,
06.04.2016, 17:56

@ hartundweich
 

Still problems with german special characters

So some things tried, nothing helped - but I have some more informations and hope these informations will help to solve my problem:

When I add "AddDefaultCharset On" to my VHOST configuration my browser says website encoding is "western european" - no special german character is showed - neither texts from the menu nor records from database

BUT: when I create a new customer, employee,... the german characters are stored correct in my database. But not shown in webfrontend.
When I switch my browser to website encoding "Unicode" all these charaters are shown. When I want to edit, I have to switch back the website encoding in my browser to "Western Europe" and can edit and save the record correctly.

When removing "AddDefaultCharset On" from my VHOST configuration, my browser detects unicode encoding and all values are showed correctly. When I want to create or edit records with special german characters, I have to switch website encoding to western europe so that my special german characters find their correct way to the database...

So whats going wrong with my Apache 2.4? Or is it worth to manually install SQL Ledger Files and not through setup.pl?

localguru(R)

E-mail

Germany,
06.04.2016, 18:19

@ hartundweich
 

Still problems with german special characters

I just checked my settings in Chrome and Firefox. Firefox uses Westlich encoding, Chrome UTF-8. With both browsers I don't have problems with umlauts when creating or changing an invoice.

Wrong: on login Firefox uses Westlich encoding and then switches to Unicode after login. When creating and saving an invoice with umlauts Unicode is used. If I switch by hand to Westlich I get an pg error when saving:

--------
DBD::Pg::db do failed: ERROR: invalid byte sequence for encoding "UTF8": 0xf6 0xf6 0xf6 0xf6 at SL/IS.pm line 1160.
Error!

UPDATE invoice SET
description = 'Sonstiges: Test Rechnung 123 ',
qty = 1,
sellprice = 1,
fxsellprice = 1,
discount = 0,
allocated = 0,
unit = 'FP',
deliverydate = NULL,
project_id = NULL,
serialnumber = '',
ordernumber = '',
ponumber = '',
itemnotes = ' ',
lineitemdetail = '1',
cost = 0,
vendor = '',
vendor_id = 0
WHERE id = 18860
ERROR: invalid byte sequence for encoding "UTF8": 0xf6 0xf6 0xf6 0xf6Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Pg::db handle dbname=xxxx;host=xxxx;port=xxx at SL/Form.pm line 377.
--------

Though never been aware that browser encoding has this effect, I don't have any problems with my Firefox or Chrome to handle umlauts.

Ciao!

hartundweich(R)

Homepage

Austria,
06.04.2016, 18:58

@ localguru
 

Still problems with german special characters

Hi,

I'm using Firefox - maybe I should try Chrome?

It does not matter with which encoding I try to save a customer or employee - I cannot reproduce your postgresql error message - I only get this error message when I try to create a new dataset with austrian charts - but I think this is because the SQL is ISO-8859 coded instead of UTF-8

By the way: just switched back from Apache 2.4 to Apache 2.2 - shit, here we have the same troubles.

So at the moment I do not know how to solve my issues.... Any ideas will be welcome.

Maybe I will set up a different VHOST and try again from the beginning. This is my last idea...

Maybe something is wrong with template1?

Greetings
Andreas Fragner

localguru(R)

E-mail

Germany,
06.04.2016, 18:54

@ hartundweich
 

Still problems with german special characters

Hi,

another idea: before upgrading to 3.2.0 and moving to another box I did an dump of the old 2.8.32 using --encoding='UTF-8':

pg_dump --encoding='UTF-8' yoursqlledgerdb > dump.20160321

The old database was on LATIN1, dump contains "SET client_encoding = 'UTF-8';"

Ciao!

hartundweich(R)

Homepage

Austria,
06.04.2016, 19:01

@ localguru
 

Still problems with german special characters

Hi,

another idea: before upgrading to 3.2.0 and moving to another box I did an
dump of the old 2.8.32 using --encoding='UTF-8':

pg_dump --encoding='UTF-8' yoursqlledgerdb > dump.20160321

The old database was on LATIN1, dump contains "SET client_encoding =
'UTF-8';"

Ciao!

As I said in my last post - any ideas are welcome - I think I will give this a try. I'm using SQL Ledger since 8 years - maybe something went wrong with one of the latest update - I updated from 3.0.5 to 3.2.0 and hoped to solve my PDF problem - it did indeed solve my PDF problem but now I'm stuck in another big problem which wants to be solved :-)

Best whishes!

hartundweich(R)

Homepage

Austria,
06.04.2016, 19:22

@ hartundweich
 

Still problems with german special characters

just seen: some of my de_utf locale files are encoded US-ASCII - does this matter?

hartundweich(R)

Homepage

Austria,
06.04.2016, 19:41

@ localguru
 

Still problems with german special characters

Hi,

another idea: before upgrading to 3.2.0 and moving to another box I did an
dump of the old 2.8.32 using --encoding='UTF-8':

pg_dump --encoding='UTF-8' yoursqlledgerdb > dump.20160321

The old database was on LATIN1, dump contains "SET client_encoding =
'UTF-8';"

Ciao!

I've just tried - it does not matter if I use "--encoding..." at pg_dump - the resulting SQL file always contains "SET client_encoding = 'UTF-8';"

So what to do next? Just empty my production database and import the SQL file? How can I make this DB dump to work on a new dataset? I don't want to play around with my production data and maybe make all financial data unuseable...

hartundweich(R)

Homepage

Austria,
06.04.2016, 19:54

@ hartundweich
 

Still problems with german special characters

Managed to get started with a new dataset - same problem :-(

it does not matter if I use "AddDefaultCharset On" to my VHOST - same things happen:
I have to set encoding in Browser to western before I post to database. And then reading out (e.g. list customers) shows correct results with browser encoding set to unicode...

Mysterious...

Maybe I will try tomorrow with a new VHOST - unfortunately I do not have a spare server with postgres so I have to use the same database server...

At the moment I do not know where to search - it's just try and error :-(

Leho Kraav(R)

12.06.2017, 22:29

@ hartundweich
 

Still problems with german special characters

Managed to get started with a new dataset - same problem :-(

it does not matter if I use "AddDefaultCharset On" to my VHOST - same
things happen:
I have to set encoding in Browser to western before I post to database. And
then reading out (e.g. list customers) shows correct results with browser
encoding set to unicode...

Mysterious...

Maybe I will try tomorrow with a new VHOST - unfortunately I do not have a
spare server with postgres so I have to use the same database server...

At the moment I do not know where to search - it's just try and error :-(

I am seeing the same thing with et_EE.UTF-8 locale.

Is it safe to assume that this never got resolved?

Testing has showed that DBD-Pg-3.6.2 has an effect when I manipulate it with the deprecated `pg_enable_utf8` parameter values - but even though frontend display improves, any umlauts stored in database get mis-encoded.

I have also tried `binmode( STDOUT, ':utf8');` and `use open ':std', ':encoding(utf-8)';`

This makes database values display correctly, but breaks template string display + database input gets double-encoded.

A massive huge mess. Would not touch Perl with a 6 foot pole for web app development, but here we are and have to get this fixed.

Leho Kraav(R)

18.07.2017, 20:19

@ Leho Kraav
 

Still problems with german special characters


I am seeing the same thing with et_EE.UTF-8 locale.

Is it safe to assume that this never got resolved?

Testing has showed that DBD-Pg-3.6.2 has an effect when I manipulate it
with the deprecated `pg_enable_utf8` parameter values - but even though
frontend display improves, any umlauts stored in database get mis-encoded.

It is still really difficult to get correct UTF-8 working with modern DBD-Pg.

Based on research and tons of testing, I'm fairly certain the path forward includes the following:

Apache:
```
SetEnv LANG et_EE.utf8
SetEnv PGCLIENTENCODING UTF8
SetEnv PERL_UNICODE ASL
```

SQL-Ledger:
Needs to insert `use utf8;` in all its programs. `locale/` templates definitely need it for display, otherwise `PERL_UNICODE` setting will break.

DBD-Pg:
Needs to learn to save UTF-8 data into the database, I'm fairly certain it's plain broken. No configuration scenario that I could think of was able to save the word "td" as "td" in Postgres (as verified by `psql`), instead it gets saved as "t¶¶d" or even worse - encoded multiple times like "t&#131;¶&#131;¶d".

If anyone has further ideas who's more unicode-broken here, SQL-Ledger or DBD-Pg, I'd appreciate your thoughts.

EDIT

Could SL::Form::escape() and unescape() be involved here? These look like incredibly risky replacement operations for unicode.

Leho Kraav(R)

18.07.2017, 22:16

@ Leho Kraav
 

Still problems with german special characters


Could SL::Form::escape() and unescape() be involved here? These look like
incredibly risky replacement operations for unicode.

Wow, it IS these custom escaping functions :(

With a full modern Unicode stack setup, we need to manually encode/decode after escaping. This is how form input gets saved into the database in normal UTF-8 form.

```
package Form;
use Encode;
use utf8;

sub escape {
...

$str = encode('UTF-8', $str);
$str =~ s/([^a-zA-Z0-9_.-])/sprintf("%%%02x", ord($1))/ge;

...

}


sub unescape {
...

$str =~ s/%([0-9a-fA-Z]{2})/pack("c",hex($1))/eg;
$str = decode('UTF-8', $str);

...
}
```

Dieter, have you been aware of this Unicode solution strategy?

Would be super interesting to get hartundweich's testing this.

Back to index page
Thread view  Board view
986 Postings in 325 Threads, 327 registered users, 103 users online (0 registered, 103 guests)
SQL-Ledger User Forum | Admin contact
RSS-Feed
powered by my little forum