Thread: Unicode, ODBC and MS Access
Hello, I have some problems which I think have to do with Unicode strings in Windows. Currently, my database is set to Unicode encoding (which I understand means UTF-8). I use Windows-based programs (running on .NET) through ODBC to insert some text in UCS-2 encoding. Now when I try to read that data using the same client program, it sometimes truncates long strings (after a few hundred characters or so). Moreover, if I access the same tables using Access XP, in most cases the text is garbled. I see only fragments of the text, with garbage characters in it, or in some cases no text at all. What is the correct way to set up a database (with appropriate encoding) and ODBC drivers so that Windows programs can read and write Unicode text (UCS-2)? Thanks, Marcus B.
Marcus Better wrote:
>
> Hello,
>
> I have some problems which I think have to do with Unicode strings in
> Windows.
>
> Currently, my database is set to Unicode encoding (which I understand means
> UTF-8). I use Windows-based programs (running on .NET) through ODBC to
> insert some text in UCS-2 encoding.
>
> Now when I try to read that data using the same client program, it
> sometimes truncates long strings (after a few hundred characters or so).
>
> Moreover, if I access the same tables using Access XP, in most cases the
> text is garbled. I see only fragments of the text, with garbage characters
> in it, or in some cases no text at all.
>
> What is the correct way to set up a database (with appropriate encoding)
> and ODBC drivers so that Windows programs can read and write Unicode text
> (UCS-2)?
Which driver are you using ?
regards,
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/
> -----Original Message----- > From: Marcus Better [mailto:marcus@dactylis.com] > > > Which driver are you using ? > > PostgreSQL+ Unicode (Beta) version 7.02.0003. Please try the snapshot dll at http://www.geocities.jp/inocchichichi/psqlodbc/ . Maybe a little better. regards, Hiroshi Inoue
> Which driver are you using ? PostgreSQL+ Unicode (Beta) version 7.02.0003.
> -----Original Message-----
> From: Hans-JEgen Schönig [mailto:hs@cybertec.at]
>
> Good morning ...
>
> We have some minor problems with UNICODE as well.
> We are using PostgreSQL 7.3.1 with 7.02.00.05 driver.
>
>
> 2003-02-21 08:39:30 [16593] LOG: connection authorized: user=plank
> database=test
> 2003-02-21 08:39:30 [16593] LOG: query: select version()
> 2003-02-21 08:39:30 [16593] LOG: duration: 0.003769 sec
> 2003-02-21 08:39:30 [16593] LOG: query: set DateStyle to 'ISO'
> 2003-02-21 08:39:30 [16593] LOG: duration: 0.000421 sec
> 2003-02-21 08:39:30 [16593] LOG: query: set geqo to 'OFF'
> 2003-02-21 08:39:30 [16593] LOG: duration: 0.000288 sec
> 2003-02-21 08:39:30 [16593] LOG: query: select oid from pg_type where
> typname='lo'
> 2003-02-21 08:39:30 [16593] LOG: duration: 0.011502 sec
> 2003-02-21 08:39:30 [16593] LOG: query: select pg_client_encoding()
> 2003-02-21 08:39:30 [16593] LOG: duration: 0.001134 sec
> 2003-02-21 08:39:30 [16593] LOG: query: BEGIN
> 2003-02-21 08:39:30 [16593] LOG: duration: 0.000273 sec
> 2003-02-21 08:39:30 [16593] LOG: query: SELECT max(adr_id) FROM
> t_adresse
> 2003-02-21 08:39:30 [16593] LOG: duration: 0.007809 sec
> 2003-02-21 08:39:30 [16593] LOG: query: SELECT last_value FROM
> t_adresse_adr_id_seq
> 2003-02-21 08:39:30 [16593] LOG: duration: 0.001503 sec
> 2003-02-21 08:39:30 [16593] LOG: query: INSERT INTO t_adresse
> VALUES('117',NULL,'ÓELOL.ZÁRÓV','ATÉTRotoform
> ajtókilincs','MUK',NULL,'AT','6020',NULL,NULL,NULL,NULL,NULL,NULL,NULL)
> 2003-02-21 08:39:30 [16593] ERROR: Invalid UNICODE character sequence
> found (0xd345)
> 2003-02-21 08:39:31 [16593] LOG: query: ROLLBACK
> 2003-02-21 08:39:31 [16593] LOG: duration: 0.000203 sec
>
>
> The server side database is set to UNICODE.
>
> [hs@backup hs]$ psql -l
> List of databases
> Name | Owner | Encoding
> -----------+-------+-----------
>
> template0 | hs | SQL_ASCII
> template1 | hs | SQL_ASCII
> test | hs | UNICODE
> (7 rows)
>
>
> Somehow the driver seems to select the client encoding but it does not
> care about it. I am using RedHat 7.3 and it's default UNIX side
> ODBC stuff.
>
> Also: I have an additional problem because "ÓELOL" is not what the
> client is supposed to send me. The "O" should be an "O" with something
> like a " on top of it (some hungarian symbol). A tool is supposed to
> write all data inside an Access database to PostgreSQL. On the Windows
> desktop the hungarian symbols are displayed properly. Where do we lose
> that information or is it a problem with my terminal settings on Linux?
> ([hs@backup postgresql-7.3.1]$ echo $TERM $LANG
> xterm en_US.iso885915)
>
> Does anybody have an idea?
You seem to have to set the client_encoding using the *Connect Settings*
DSN option.
regards,
Hiroshi Inoue
Emmanuel Charpentier wrote:
>
> This is mostly a success note, to let all potential ODBC users
> know that the "snapshot" ODBC drivers as proposed by Hiroshi are
> a great help ... for use with Windows applications. Those drivers
> have cured me of at least two big headaches.
>
> Therefore a question : Hiroshi : do you plan to port them back
> to the Unix source tree ? They would be *really* useful, if only
> for Openoffice and/or R users (I'm both ...). I tried to port the
> enhancements you propose in the unix source tree (as (magnificiently)
> maintained by Oliver Elphick) ... and botched up royally !
I seem to have committed most changes in the snapshot to
the current cvs.
> Another one : the "example" jpeg about miltibyte character support
> on the snapshot page exhibits a checked "(Exp) Updatable cursors"
> checkbox. However, the SQL commands doc for DECLARE tells me that
> PostgreSQL cursors are still read-only. Which is right ?
The Updatable cursor functionality is implemented in the
driver not at the backend side.
> A couple of suggestions, too : updating the driver's docs about
> novelties in 7.3 (schema, among others) would be a Good Thing (TM).
> Reintegrating this doc (even in a terse format) in the "Client
> interfaces" part of the PostgreSQL doc tree would be a Yet Better
> Thing (R). And referring the "snapshot drivers" page somewhere on
> the Gborg site would be a great idea, too ...
Unfortunately I'm not a native English speaker. I hope
someone would update it.
regards,
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/