Thread: No fun with MS Access and Latin1
Greetings,
I've some troubles with MS Access 97 and PG 8 Beta1, both on Windows XP Pro
(German, locale is Windows Codepage 1252 wich shoud be equivalent to
Latin1?).
The database encoding is Latin1. The ODBC driver (the one from the MSI
installer package, version 7.05.02) sets the client encoding to UTF-8.
The database is populated with a dump from a sybase database encoded in
Latin1. Notepad shows all characters correctly.
Selecting data in Access works and displays even German umlauts correctly
-- if and only if the primary key field is not of type varchar.
In this case every row and column is displayed as "#Deleted". Adding
an oid column and using fake oid index helps here as a workaround.
But updating a row which contains an umlaut (in any field) or inserting
a row with umlauts fails. From the error message and log file it seems as if Access
sends Latin1 instead of UTF8 data. (The error is something like "Cannot
convert char 0x0.. to UTF8", and the hex number is the ASCII/Latin1
equivalent of the first umlaut in the row.)
Inserting a fresh row works if I paste the UTF8 represention of the
umlauts as Latin1 into the form: instead of "hälp" I insert "hälp".
Refreshing shows "hälp" as it should be.
If i insert just "hälp" I get "hp" back. If the umlaut happens to be the
last character of the field, it yields a syntax error as the conversion
somehow eats the closing single quote from the update statement, producing
a single random character instead of <umlaut><single quote>.
Updating a row which already contains umlauts does not work at all, because
Access restricts the update with a where clause which states the umlaut
field, yielding the "Cannot convert..." error.
Specifying "set client_encoding to 'Latin1'" in the ODBC DSN settings
makes umlauts appear as question mark in Access.
Using Unicode as the database encoding doesn't help either, and I'd
rather avoid that, if possible.
What do I do wrong? Is this a bug in Access or in the ODBC driver?
Browsing the archives/faq/how to's did not reveal much help, except
that using a non-Unicode ODBC driver works well for French. How do I find
such a driver, or how do I tell it not to use Unicode at all?
Any help would be appreciated.
TIA,
Christof Glaser
--
gl.aser . software engineering . internet service
Dölitzer Straße 37 . 04277 Leipzig . http://gl.aser.de
fon 0341.303 20 51 . fax 0341.303 20 52 . sms 0177.779 28 43
PHP . Lisp . C++ . HTML . SQL . LaTeX . Linux . Mac . WinCC
Hello Christof,
What ODBC driver are you using?
There are 3 drivers: Postgresql, Postgresql Legacy, Postgresql Unicode.
I'm using the first one "Postgresql", (snapshot 208 or 209), with Access 2000, 2002 or 2003 without any problem. I
haven'ttried Access 97.
My databases are encoded in LATIN1 as well, for French. Update with accents are fine, at least with PGSQL 7.3.x and
7.4.x.
I hope this helps.
Bye
Philippe
-----Message d'origine-----
De : pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] De la part de Christof Glaser
Envoyé : mardi, 7. septembre 2004 19:48
À : pgsql-odbc@postgresql.org
Objet : [ODBC] No fun with MS Access and Latin1
Greetings,
I've some troubles with MS Access 97 and PG 8 Beta1, both on Windows XP Pro (German, locale is Windows Codepage 1252
wichshoud be equivalent to Latin1?).
The database encoding is Latin1. The ODBC driver (the one from the MSI installer package, version 7.05.02) sets the
clientencoding to UTF-8.
The database is populated with a dump from a sybase database encoded in Latin1. Notepad shows all characters correctly.
Selecting data in Access works and displays even German umlauts correctly
-- if and only if the primary key field is not of type varchar.
In this case every row and column is displayed as "#Deleted". Adding an oid column and using fake oid index helps here
asa workaround.
But updating a row which contains an umlaut (in any field) or inserting a row with umlauts fails. From the error
messageand log file it seems as if Access sends Latin1 instead of UTF8 data. (The error is something like "Cannot
convertchar 0x0.. to UTF8", and the hex number is the ASCII/Latin1 equivalent of the first umlaut in the row.)
Inserting a fresh row works if I paste the UTF8 represention of the umlauts as Latin1 into the form: instead of "hälp"
Iinsert "hälp".
Refreshing shows "hälp" as it should be.
If i insert just "hälp" I get "hp" back. If the umlaut happens to be the last character of the field, it yields a
syntaxerror as the conversion somehow eats the closing single quote from the update statement, producing a single
randomcharacter instead of <umlaut><single quote>.
Updating a row which already contains umlauts does not work at all, because Access restricts the update with a where
clausewhich states the umlaut field, yielding the "Cannot convert..." error.
Specifying "set client_encoding to 'Latin1'" in the ODBC DSN settings makes umlauts appear as question mark in Access.
Using Unicode as the database encoding doesn't help either, and I'd rather avoid that, if possible.
What do I do wrong? Is this a bug in Access or in the ODBC driver?
Browsing the archives/faq/how to's did not reveal much help, except that using a non-Unicode ODBC driver works well for
French.How do I find such a driver, or how do I tell it not to use Unicode at all?
Any help would be appreciated.
TIA,
Christof Glaser
--
gl.aser . software engineering . internet service
Dölitzer Straße 37 . 04277 Leipzig . http://gl.aser.de
fon 0341.303 20 51 . fax 0341.303 20 52 . sms 0177.779 28 43 PHP . Lisp . C++ . HTML . SQL . LaTeX . Linux . Mac .
WinCC
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Hello Philippe, Am 08.09.2004 um 08:21 schrieb Philippe Lang: > Hello Christof, > > What ODBC driver are you using? > > There are 3 drivers: Postgresql, Postgresql Legacy, Postgresql Unicode. I'm not so sure, but I think it is called 'Postgresql' as I remember seeing this option somewhere in the ODBC dialogs. There were no other options to choose from. The servers' log file shows me "select pg_client_encoding()" and directly after "set client_encoding to 'UTF8'", hence I assume it is a Unicode version. I'm currently not at the Windows machine, but I could send logfile snippets later, if necessary. > I'm using the first one "Postgresql", (snapshot 208 or 209), with > Access 2000, 2002 or 2003 without any problem. I haven't tried Access > 97. > > My databases are encoded in LATIN1 as well, for French. Update with > accents are fine, at least with PGSQL 7.3.x and 7.4.x. Sound good. Does your server also run on Windows (cygwin)? Perhaps I give 7.4. a try on cygwin, that's easier than switching to a newer Access version. Thanks and best regards, Christof Best regards, Christof Glaser -- -- gl.aser . software engineering . internet service -- Doelitzer Strasse 37 . D-04277 Leipzig . fon +49-341-303 20 51 -- http://gl.aser.de/ . sms +49-177-779 28 43 . fax +49-341-303 20 52
Replying myself: Latin1 characters work when using the ODBC library
version 7.3.200 with the "Postgresql" driver. The Server log does not
show "set client_encoding to 'utf8'" anymore, everything works fine now.
The Legacy and Unicode drivers give an ODBC error, they don't even connect
to the backend (no connection logged).
7.3.209, 7.5.001, 7.5.002 (from the PG 8 Beta1 MSI) seem to fail
converting Latin1 to/from Unicode correctly when sending SQL commands to
the backend. Perhaps this is also the reason why primary keys on
varchar fields did not work? I'll try that again now with the older
ODBC lib.
Thanks Philippe for pointing me in the right direction.
Best regards,
Christof
On Wed, Sep 08, 2004 at 01:03:11PM +0200, Christof Glaser wrote:
> Hello Philippe,
>
> Am 08.09.2004 um 08:21 schrieb Philippe Lang:
>
> >Hello Christof,
> >
> >What ODBC driver are you using?
> >
> >There are 3 drivers: Postgresql, Postgresql Legacy, Postgresql Unicode.
>
> I'm not so sure, but I think it is called 'Postgresql' as I remember
> seeing this option somewhere in the ODBC dialogs.
> There were no other options to choose from.
>
> The servers' log file shows me "select pg_client_encoding()" and
> directly after "set client_encoding to 'UTF8'", hence I assume it is a
> Unicode version. I'm currently not at the Windows machine, but I could
> send logfile snippets later, if necessary.
>
> >I'm using the first one "Postgresql", (snapshot 208 or 209), with
> >Access 2000, 2002 or 2003 without any problem. I haven't tried Access
> >97.
> >
> >My databases are encoded in LATIN1 as well, for French. Update with
> >accents are fine, at least with PGSQL 7.3.x and 7.4.x.
>
> Sound good. Does your server also run on Windows (cygwin)?
>
> Perhaps I give 7.4. a try on cygwin, that's easier than switching to a
> newer Access version.
--
gl.aser . software engineering . internet service
Dölitzer Straße 37 . 04277 Leipzig . http://gl.aser.de
fon 0341.303 20 51 . fax 0341.303 20 52 . sms 0177.779 28 43
PHP . Lisp . C++ . HTML . SQL . LaTeX . Linux . Mac . WinCC