SQL-Ledger 3.2.5 posts double-encoded strings about dbdpg HOT 11 CLOSED

I can test it... get back to you in a day or two.

from dbdpg.

@lkraav Feel free to open an issue and we can discuss some general topics before the details. Here we are at the wrong place to speak about SQL-Ledger.

from dbdpg.

I've tried to correct the UTF-8 handling in SQL-Ledger at https://github.com/Tekki/sql-ledger/tree/utf8_devel. Unfortunately nobody had time during the last 4 months to test my code.

from dbdpg.

The source of the problem is on the side of SQL-Ledger and I've proposed a solution in branch mentioned above (https://github.com/Tekki/sql-ledger/tree/utf8_devel). After half a year, I'm still waiting for someone to test it.

from dbdpg.

Okay, thanks, going to close this issue then. Good luck on the SQL-Ledger side!

from dbdpg.

Any thoughts @turnstep? How can we find out whether it's DBD-Pg or not?

EDIT I've inserted some debug statements like below

$query = qq|UPDATE gl SET
        reference = |.$dbh->quote($form->{reference}).qq|,
        description = |.$dbh->quote($form->{description}).qq|,
        notes = |.$dbh->quote($form->{notes}).qq|,
        transdate = '$form->{transdate}',
        department_id = $department_id,
        curr = '$form->{currency}',
        exchangerate = $form->{exchangerate}
        WHERE id = $form->{id}|;

$form->debug( "/tmp/sql-ledger.log", $query );
$dbh->do($query) || $form->dberror($query);

Checking /tmp/sql-ledger.log it's clear that valid UTF-8 is getting to the app processing function. $dbh->do() messes it up, so still looks like DBD-Pg is buggy.

from dbdpg.

I still don't understand - is DBD::Pg actually supposed to persist encoded strings in the database ("tööd" saves as "tööd") by design because that's how Perl stores it internally?

Nothing I'm doing at Perl level is making "tööd" save as "tööd" in the database :/

EDIT it seems to be about the app after all: https://github.com/Tekki/sql-ledger/blob/v3.2.05/SL/Form.pm#L160 does some custom escaping and probably breaking UTF-8.

from dbdpg.

There are two reasons for this problem: First, SQL-Ledger doesn't work with encodings, but treats everything as binary. Second, dbd-pg at some places converts binary to Unicode when it looks like UTF-8. The workaround for SQL-Ledger is still to install version 3.2.1, as I show in https://github.com/Tekki/ansible-sql-ledger.

from dbdpg.

@Tekki thanks for jumping in here, I was actually going to file an issue about this Unicode stuff at your repo next.

I think I might've reached a solid solution or at least a path to get to true UTF-8 handling through database, templates, etc, see http://www.sql-ledger.com/userforum/forum_entry.php?id=1220&page=0&category=0&order=last_answer for a quick overview of what's needed. I was planning to file the issue and present a list of commits for a proof of concept. Your fork seems to be the only active one left, so perhaps we could discuss further details there if you're interested.

from dbdpg.

I just came across this thread... just wanted to say that SQL-Ledger 3.2.5 and later works only with DBD-Pg version 3.2.1 and earlier, and only if you also set pg_enable_utf8 => 0. It seems that changes to later versions of DBD-Pg to fix UTF8 handling also broke things. If you search around, you’ll see the occasional comment about the breakage from Russians and others.

from dbdpg.

Any status on this? Or anything that needs fixing on the DBD::Pg end?

from dbdpg.