Hello Greg!
Thanks for the reply.
On Sat, Apr 19, 2014 at 09:12:10PM -0000, Greg Sabino Mullane wrote:
>
> > statements, but the results of SELECT statements are now ISO-8859 8 bit
> > characters.
>
> Hi. It's hard to say without more data. Setting client_encoding to UTF-8
> is a great first step. What makes you think the strings are returned
> as ISO-8859? Can you show us a test script and its output, or better yet,
> a self-contained test script we can run?
I have created a minimal example. I have created a new database TEST
with a single table with a single row that contains German special
characters using UTF-8. I used Emacs and the shell package to make a
transcript as to how I did this; see attached file "create".
I have made a dump of this database and have attached it as test.db.
$ file test.db
yields
UTF-8 Unicode text.
Using the binary editor bvi also indicates that these are two byte encodings.
I then created a minimal perl script that reads from this table and
writes to STDOUT. I have attached this script as well; test.pl.
Redirecting the output fro test.pl to a file and then using the file
binary again, one gets
$ ISO-8859 text, with no line terminators
The bvi indicates single byte German national characters.
Again, I'd be very grateful for any pointers on this. I suspect that my
configuration here could be responsible as obviously nobody else has
squealed. I changed to UTF-8 only about a year ago, and perhaps I
missed something. Yet for the life of me I know not what. As I said in
my original post, things got broken with DBD version 3.0.
What could have changed with 3.0 that could possibly have caused this?
Cheers,
Mike Dowling
PS
During the week I have no access to my email; only from Fridays to
Sundays, so my responses may be slow.
--
Dr. Michael L. Dowling
Gaußstr. 27
38106 Braunschweig
Germany
!512 /home/mike$mkdir data
!513 /home/mike$initdb -E utf8 -D data
The files belonging to this database system will be owned by user "mike".
This user must also own the server process.
The database cluster will be initialized with locales
COLLATE: en_NZ.UTF-8
CTYPE: en_NZ.UTF-8
MESSAGES: en_NZ.UTF-8
MONETARY: en_IE.UTF-8
NUMERIC: en_NZ.UTF-8
TIME: en_DK.UTF-8
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
creating configuration files ... ok
creating template1 database in data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
postgres -D data
or
pg_ctl -D data -l logfile start
!516 /home/mike$su - root
Password:
390 /root#su - postgres
[postgres@moocow ~]$ createdb -E utf8 test
[postgres@moocow ~]$ psql test
psql (9.3.4)
Type "help" for help.
test=# create user test;
CREATE ROLE
test=# grant all privileges on database test to test;
GRANT
test=# \q
could not save history to file "/var/lib/postgres/.psql_history": No such file
or directory
[postgres@moocow ~]$ id
uid=88(postgres) gid=88(postgres) groups=88(postgres)
[postgres@moocow ~]$ logout
!517 /home/mike$psql test
SET
SET
psql (9.3.4)
Type "help" for help.
test=> CREATE TABLE test (test character varying(30));
CREATE TABLE
test=> insert into test values ('Umlauts: ÄäüÜÖöß; degree: °');
INSERT 0 1
test=> select * from test;
WARNING: terminal is not fully functional
- (press RETURN)
^
^ test
-----------------------------
Umlauts: ÄäüÜÖöß; degree: °
(1 row)
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
(END)
test=> #!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $dbhandle = DBI->connect("dbi:Pg:dbname = test", undef, undef, {PrintError => 0})
or die "Cannot connect to database \"moocow\": $DBI::errstr ($DBI::err)\n";
my $test = $dbhandle->prepare("SET CLIENT_ENCODING TO 'UTF8'; SELECT test from test");
$test->execute();
my $vals = $test->fetchall_arrayref();
print "$vals->[0][0]";
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET search_path = public, pg_catalog;
DROP TABLE public.test;
DROP EXTENSION plpgsql;
DROP SCHEMA public;
--
-- Name: public; Type: SCHEMA; Schema: -; Owner: postgres
--
CREATE SCHEMA public;
ALTER SCHEMA public OWNER TO postgres;
--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--
COMMENT ON SCHEMA public IS 'standard public schema';
--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
--
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: test; Type: TABLE; Schema: public; Owner: mike; Tablespace:
--
CREATE TABLE test (
test character varying(30)
);
ALTER TABLE public.test OWNER TO mike;
--
-- Data for Name: test; Type: TABLE DATA; Schema: public; Owner: mike
--
COPY test (test) FROM stdin;
Umlauts: ÄäüÜÖöß; degree: °
\.
--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;
--
-- PostgreSQL database dump complete
--
