Re: psqlODBC-Driver Test / text fields

> -----Original Message-----
> From: Johann Zuschlag [mailto:zuschlag2(at)online(dot)de]
> Sent: 27 March 2006 13:46
> To: Dave Page
> Cc: pgsql-odbc(at)postgresql(dot)org
> Subject: psqlODBC-Driver Test / text fields
>
> Hi Dave,
>
> here is another one, same problem like Miguel:

Miguel's problem is somewhat unique to BDE from what I recall -
specifically it ignores Unicode text columns which the Unicode driver
will offer by default. The same problem has been reported with Oracle
and other DBMS's on usenet.

> PostgreSQL 8.0X on Debian Sarge
> Unicode Database
> Application with psqlodbc+ 7.02.02.60 on Win XP Sp2
>
> Operation: Reading and writing of a, A, u, U, o, O-Umlaut and
> sz (german
> characters)
> Data type: text
> Remarks: none
> Protocol: 7.4+
> Result: failed, insert works, querying doesn't work

OK, that sounds like the old bug we were seeing. What is the difference
between those and the previous results you reported a few minutes ago?
Is it just the text/varchar difference?

Regards, Dave.

Dave Page schrieb:

>
> OK, that sounds like the old bug we were seeing. What is the difference
> between those and the previous results you reported a few minutes ago?
> Is it just the text/varchar difference?
>
>
>
Yes, I forgot to test the text field. I shall set debug higher to see
what is going on.

Regards,
Johann

Dave Page schrieb:

>OK, that sounds like the old bug we were seeing. What is the difference
>between those and the previous results you reported a few minutes ago?
>Is it just the text/varchar difference?
>
>Regards, Dave.
>
>
>

Hi Dave,

the problem doesn't seem to be related to the text field. Please note
the examples below:

1. Searching for a string starting with a 't' seems to work fine:

2006-03-27 16:49:48 [2931] LOG: statement: declare "SQL_CUR0210FD50"
cursor with hold for SELECT t6.* FROM KUNDE t6 WHERE t6.name >= 't' AND
t6.name <= 'tz' ORDER BY t6.name ASC, t6.kundenid ASC

2. Searching for a string starting with o-Umlaut (german character)
doesn't return any results (in my app.).

2006-03-27 16:50:37 [2931] LOG: statement: declare "SQL_CUR0210FD50"
cursor with hold for SELECT t6.* FROM KUNDE t6 WHERE t6.name >= 'ö'
AND t6.name <= 'öz' ORDER BY t6.name ASC, t6.kundenid ASC

Maybe the WHERE-statement is not parsed by the driver. But the hex
representation of 'ö' is 'C3B6', that is the correct UTF8 code (not
unicode) of o-Umlaut.

3. Furthermore I noticed that I wouldn't get anything back if the
searched string contains any "Umlauts". So I guess there could be a
problem with the result set too.

SELECT without a WHERE-statement works.

Any ideas?

A mylog ist available, as well as the tiny data set (6 lines)..

Hope that helps.

Regards,
Johann

Johann Zuschlag wrote:

> Dave Page schrieb:
>
>> OK, that sounds like the old bug we were seeing. What is the difference
>> between those and the previous results you reported a few minutes ago?
>> Is it just the text/varchar difference?
>>
>> Regards, Dave.
>>
>>
>>
>
> Hi Dave,
>
> the problem doesn't seem to be related to the text field. Please note
> the examples below:
>
> 2. Searching for a string starting with o-Umlaut (german character)
> doesn't return any results (in my app.).
>
> 2006-03-27 16:50:37 [2931] LOG: statement: declare "SQL_CUR0210FD50"
> cursor with hold for SELECT t6.* FROM KUNDE t6 WHERE t6.name >= 'ö'
> AND t6.name <= 'öz' ORDER BY t6.name ASC, t6.kundenid ASC
>
> Maybe the WHERE-statement is not parsed by the driver. But the hex
> representation of 'ö' is 'C3B6', that is the correct UTF8 code (not
> unicode) of o-Umlaut.

Hi Johann,
Could you try the same query using psql with the client_encoding 'UTF8' ?

regards,
Hiroshi Inoue

Hiroshi Inoue schrieb:

>
>>
>> 2. Searching for a string starting with o-Umlaut (german character)
>> doesn't return any results (in my app.).
>>
>> 2006-03-27 16:50:37 [2931] LOG: statement: declare "SQL_CUR0210FD50"
>> cursor with hold for SELECT t6.* FROM KUNDE t6 WHERE t6.name >= 'ö'
>> AND t6.name <= 'öz' ORDER BY t6.name ASC, t6.kundenid ASC
>>
>> Maybe the WHERE-statement is not parsed by the driver. But the hex
>> representation of 'ö' is 'C3B6', that is the correct UTF8 code (not
>> unicode) of o-Umlaut.
>
>
> Hi Johann,
> Could you try the same query using psql with the client_encoding 'UTF8' ?
>
> regards,
> Hiroshi Inoue
>

Hi Hiroshi,

Do you mean psql on the Linux-server?

my locales:
de_DE.ISO-8859-1 (default)
de_DE.UTF-8
de_DE(dot)UTF-8(at)euro
de_DE(dot)ISO-8859-15(at)euro

I call psql:
- set client_encoding='UTF8';
- select name from kunde;
result e.g: 'öä-test' (=o-Umlaut, a-Umlaut, -test)

- select name from kunde where name >= 'ö' and name <= 'öz' order by
name asc;

result: ERROR: Unicode characters greater than or equal to 0x10000 are
not supported

export LANG=de_DE.UTF-8 doesn't change the behavior.

(select name from kunde where name >= 'ö' and name <= 'öz' order by
name asc; works of course)

regards,
Johann

Johann Zuschlag schrieb:

>
>
> (select name from kunde where name >= 'ö' and name <= 'öz' order by
> name asc; works of course)
>
>
Maybe that is more precise:

select name from kunde where name >= 'ö' and name <= 'öz' order by
name asc;

Does not give an error, but 0 result lines.

regards,
Johann

Johann Zuschlag wrote:

> Johann Zuschlag schrieb:
>
>>
>>
>> (select name from kunde where name >= 'ö' and name <= 'öz' order by
>> name asc; works of course)
>>
>>
> Maybe that is more precise:
>
> select name from kunde where name >= 'ö' and name <= 'öz' order by
> name asc;
>
> Does not give an error, but 0 result lines.

Thanks.
Could you issue the following 2 queries

select name from kunde where name >= 'ö' order by name asc;

select name from kunde where name <= 'öz' order by name asc;

and see the results ?

regards,
Hiroshi Inoue

Hiroshi Inoue schrieb:
>
> Thanks.
> Could you issue the following 2 queries
>
> select name from kunde where name >= 'ö' order by name asc;
>
name
--------
öä-test
öäüÃÃ
ÃÃ-test
(2 Zeilen)

> select name from kunde where name <= 'öz' order by name asc;
>
name
--------
Hühne
Müller
Täst
test
test-2
(5 Zeilen)

The complete "data" set:

select name from kunde order by name asc;

name
---------------
Hühne
Müller
Täst
test
test-2
öä-test
öäüÃÃ
ÃÃ-test
(7 Zeilen)

Seems, that the query is not correct my application is sending.

Regards,
Johann

Johann Zuschlag wrote:

> Hiroshi Inoue schrieb:
>
>>
>> Thanks.
>> Could you issue the following 2 queries
>>
>> select name from kunde where name >= 'ö' order by name asc;
>>
> name
> --------
> öä-test
> öäüÃÃ
> ÃÃ-test
> (2 Zeilen)
>
>> select name from kunde where name <= 'öz' order by name asc;
>>
> name
> --------
> Hühne
> Müller
> Täst
> test
> test-2
> (5 Zeilen)

Hmm utf8 code of a-umlaut seems bigger than 'z' .

Well how is the result of the following(original ?) query
under default encoding ?

select name from kunde where name >= 'ö' and name <= 'öz' order by
name asc;

regards,
Hiroshi inoue

When I look at a character map, for instance with Windows Start, Run, All Programs, Accessories, System
Tools, Character Map and a extended character set like Arial.
I see that all the accented characters (umlauts, graves, etc.) are higher that unaccented letters. I also
can see the UTF8 equivalents.

Hiroshi Inoue wrote:
> Johann Zuschlag wrote:
>
>> Hiroshi Inoue schrieb:
>>
>>>
>>> Thanks.
>>> Could you issue the following 2 queries
>>>
>>> select name from kunde where name >= 'ö' order by name asc;
>>>
>> name
>> --------
>> öä-test
>> öäüÃÃ
>> ÃÃ-test
>> (2 Zeilen)
>>
>>> select name from kunde where name <= 'öz' order by name asc;
>>>
>> name
>> --------
>> Hühne
>> Müller
>> Täst
>> test
>> test-2
>> (5 Zeilen)
>
>
>
> Hmm utf8 code of a-umlaut seems bigger than 'z' .
>
> Well how is the result of the following(original ?) query
> under default encoding ?
>
> select name from kunde where name >= 'ö' and name <= 'öz' order by name
> asc;
>
> regards,
> Hiroshi inoue
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

Hiroshi Inoue schrieb:
>
> Hmm utf8 code of a-umlaut seems bigger than 'z' .
>
> Well how is the result of the following(original ?) query
> under default encoding ?
>
> select name from kunde where name >= 'ö' and name <= 'öz' order by
> name asc;
>
ERROR: Unicode characters greater than or equal to 0x10000 are not
supported

:-)

Regards,
Johann

Johann Zuschlag wrote:

> Hiroshi Inoue schrieb:
>
>>
>> Hmm utf8 code of a-umlaut seems bigger than 'z' .
>>
>> Well how is the result of the following(original ?) query
>> under default encoding ?
>>
>> select name from kunde where name >= 'ö' and name <= 'öz' order by
>> name asc;
>>
> ERROR: Unicode characters greater than or equal to 0x10000 are not
> supported

With the default encoding(ISO-8859-1 ?) ?

regards,
Hiroshi Inoue

Campbell, Greg wrote:

> When I look at a character map, for instance with Windows Start, Run,
> All Programs, Accessories, System Tools, Character Map and a extended
> character set like Arial.
> I see that all the accented characters (umlauts, graves, etc.) are
> higher that unaccented letters. I also can see the UTF8 equivalents.
>

Do you mean the behabior reported by Johann is reasonable ?
I'm not familiar with LATIN encoding.

regards,
Hiroshi Inoue

I think, yes.
When I look at characters and numeric representations of them, SORTS and comparison operators seem to be
behaving correctly.

Hiroshi Inoue wrote:

> Campbell, Greg wrote:
>
>> When I look at a character map, for instance with Windows Start, Run,
>> All Programs, Accessories, System Tools, Character Map and a extended
>> character set like Arial.
>> I see that all the accented characters (umlauts, graves, etc.) are
>> higher that unaccented letters. I also can see the UTF8 equivalents.
>>
>
> Do you mean the behabior reported by Johann is reasonable ?
> I'm not familiar with LATIN encoding.
>
> regards,
> Hiroshi Inoue
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

"Campbell, Greg" <greg(dot)campbell(at)us(dot)michelin(dot)com> writes:

> When I look at a character map, for instance with Windows Start, Run,
> All Programs, Accessories, System Tools, Character Map and a extended
> character set like Arial.
> I see that all the accented characters (umlauts, graves, etc.) are
> higher that unaccented letters. I also can see the UTF8 equivalents.
>

Not 100% sure I understood this issue, but it seems you are confusing
encoding and collation.

See strcmp() and strcoll().

Hiroshi Inoue schrieb:
> Johann Zuschlag wrote:
>
>> Hiroshi Inoue schrieb:
>>
>>>
>>> Hmm utf8 code of a-umlaut seems bigger than 'z' .
>>>
>>> Well how is the result of the following(original ?) query
>>> under default encoding ?
>>>
>>> select name from kunde where name >= 'ö' and name <= 'öz' order by
>>> name asc;
>>>
>> ERROR: Unicode characters greater than or equal to 0x10000 are not
>> supported
>
>
> With the default encoding(ISO-8859-1 ?) ?
>
Oh, I misunderstood you. Do you mean the database default encoding?
IIRC ISO-8859-1 would be equivalent to LATIN1?
I generated a new database with the same data in LATIN1:

test-latin1=# select name from kunde order by name asc;
name
--------------
Hühne
Müller
Täst
test
test-2
öä-test
öäüÖÄÜß-test
(7 Zeilen)

test-latin1=# select name from kunde where name >= 'ö' and name <= 'öz'
order by name asc;
name
------
(0 Zeilen)

And please note the following:

test-latin1=# select name from kunde where name >= 'ö' and name <= 'ö'
order by name asc;
name
------
(0 Zeilen)

Hmm...

Regards,
Johann

> test-latin1=# select name from kunde order by name asc;
> name
> --------------
> Hühne
> Müller
> Täst
> test
> test-2
> öä-test
> öäüÖÄÜß-test
> (7 Zeilen)
>
> test-latin1=# select name from kunde where name >= 'ö' and name <= 'öz'
> order by name asc;
> name
> ------
> (0 Zeilen)
>
> And please note the following:
>
> test-latin1=# select name from kunde where name >= 'ö' and name <= 'ö'
> order by name asc;
> name
> ------
> (0 Zeilen)
>
> Hmm...

The last query is equivalent with:
select name from kunde where name = 'ö'

So I'm not surprised with the result ;-)

What is the locale for backend? Isn't this the real problem?

When you have the problem in psql client feel free to ask
in another pgsql-* mailing list. It isn't odbc related bug.
Maybe we solve your problem but maybe you get the right advice
faster in another list.

Regards,

Luf

Ludek Finstrle schrieb:
>> The last query is equivalent with:
>> select name from kunde where name = 'ö'
>>
>> So I'm not surprised with the result ;-)
>>
>> What is the locale for backend? Isn't this the real problem?
>>
Hi Luf,

sure, that is the same, but the result is 0 lines for both. :-)

test-latin1=# select name from kunde where name = 'ö';
name
------
(0 Zeilen)

the psql driver is definitely better than ever. In my (humble) opinion
we don't need two drivers anymore. Thanks a lot for your work. So far
all my tests with UNICODE, LATIN1 and SQL-ASCII databases seem to work
with the new 7.02.260 driver on Win XP, (i.e. insert, update, select)

My set-up is:

PostgreSQL 8.0X on Debian Sarge
Unicode or LATIN1 or SQL-ASCII Database

locale=de_DE

de_DE.ISO-8859-1 (default)
de_DE.UTF-8
de_DE(dot)UTF-8(at)euro
de_DE(dot)ISO-8859-15(at)euro

changing to de_DE didn't change anything, client_encoding didn't help,
since the problem doesn't seem to be related to the driver.

Hiroshi tracked down the problem by doing the same query with the
psql.-client. So you may be right that this an old problem.

regards,
Johann

> >>The last query is equivalent with:
> >>select name from kunde where name = 'ö'
> >>
> >>So I'm not surprised with the result ;-)
> >>
> >>What is the locale for backend? Isn't this the real problem?
>
> sure, that is the same, but the result is 0 lines for both. :-)

It's ok result. I see no 'ö' in data you have posted. I see only
'ösomething'.

> we don't need two drivers anymore. Thanks a lot for your work. So far

It's not my work. I only fixed 08.01 branch since 08.01.0102.
The enahnced branch is Hiroshi (Inoue and Saito) work.

> PostgreSQL 8.0X on Debian Sarge
> Unicode or LATIN1 or SQL-ASCII Database

You tried this all?

> locale=de_DE

I think it could be the problem. You have unicode data but you try
sort it in de_DE.ISO-8859-1.
Could you try change the locale for _backend_ process to de_DE.UTF-8 or
de_DE(dot)UTF-8(at)euro? (Maybe also in postmaster.conf configuration file
of postgresql). I'm not sure if it isn't even initdb time related.

> de_DE.ISO-8859-1 (default)
> de_DE.UTF-8
> de_DE(dot)UTF-8(at)euro
> de_DE(dot)ISO-8859-15(at)euro
>
> changing to de_DE didn't change anything, client_encoding didn't help,
> since the problem doesn't seem to be related to the driver.

I don't speak about psql (client or driver) locale. I speak about
backend locale (and settings in postmaster.conf).
The sort operation is performed in backend process.

I suggest to try ask for this (with psql client problem) in pgsql-general.

Regards,

Luf

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Luv,<br>
Hi Hiroshi,<br>
Hi Dave,<br>
<br>
I'm sorry, but I made a mistake during my last tests.<br>
<blockquote cite="mid20060329153519(dot)GL18148(at)soptik(dot)pzkagis(dot)cz"
type="cite">
<pre wrap="">It's not my work. I only fixed 08.01 branch since 08.01.0102.
The enahnced branch is Hiroshi (Inoue and Saito) work.

</pre>
</blockquote>
Yeah, I know. But I hope your work for 8.01.0102 can be included in the
future driver<br>
<blockquote cite="mid20060329153519(dot)GL18148(at)soptik(dot)pzkagis(dot)cz"
type="cite">
<pre wrap=""></pre>
<blockquote type="cite">
<pre wrap="">PostgreSQL 8.0X on Debian Sarge
Unicode or LATIN1 or SQL-ASCII Database
</pre>
</blockquote>
<pre wrap=""><!---->
You tried this all?

</pre>
</blockquote>
Yes, and while doing so many initdb's I made a mistake with my locales
for the last tests.<br>
Thanks for pointing me in the right direction.<br>
<br>
So my first test results where indeed correct. I'm sorry for keeping
you all so busy. But I wanted to supply you with complete test results.
So finally: concerning the ANSI/UTF8 problem the driver seems to work
(at least for me :-) ). WinME test will follow later.<br>
<br>
Only some minor issues are left:<br>
<br>
- sometimes my application gives an (memory) error when leaving. (Will
try to trace that)<br>
- continuously I get 'unknown configuration parameter
"max_identifier_length"<br>
- Typ "lo" is not existing (I don't think my app is doing that)<br>
<br>
I never got the last two with the former drivers. But I'll check the
mylog.<br>
<br>
For further tests I switched back my Debian test system to PostgreSQL
7.4.X since my production server is still running that version.<br>
<br>
Regards,<br>
Johann<br>
<br>
</body>
</html>

Johann Zuschlag wrote:

>Hi Luv,
>Hi Hiroshi,
>Hi Dave,
>
>I'm sorry, but I made a mistake during my last tests.
>
>>It's not my work. I only fixed 08.01 branch since 08.01.0102.
>>The enahnced branch is Hiroshi (Inoue and Saito) work.
>>
>>
>>
>Yeah, I know. But I hope your work for 8.01.0102 can be included in the future
>driver
>
>>>PostgreSQL 8.0X on Debian Sarge
>>>Unicode or LATIN1 or SQL-ASCII Database
>>>
>>>
>>
>>You tried this all?
>>
>>
>>
>Yes, and while doing so many initdb's I made a mistake with my locales for the
>last tests.
>Thanks for pointing me in the right direction.
>
>So my first test results where indeed correct. I'm sorry for keeping you all so
>busy. But I wanted to supply you with complete test results. So finally:
>concerning the ANSI/UTF8 problem the driver seems to work (at least for me :-)
>). WinME test will follow later.
>
>

Thanks for your testing.
What we've had so far is *locale* support which I've never used and
we've had
no *collation* support unfortunaltely.

>Only some minor issues are left:
>- sometimes my application gives an (memory) error when leaving. (Will try to
>trace that)
>
>

I think this is fixed in the dll at
http://www.geocities.jp/inocchichichi/psqlodbc/index.html .

>- continuously I get 'unknown configuration parameter "max_identifier_length"
>
>

7.4 doesn't seems to have the max_identifier_length parameter.
This may be fixed in the abov dll also..

>- Typ "lo" is not existing (I don't think my app is doing that)
>
>

This isn't an error at all, just checking the existence of lo type when
connecting to the server.

regards,
Hiroshi Inoue