Message-ID: <01BE9622.7BAAD400.jt-kirkpatrick@mpsllc.com>
From: JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>
To: =?us-ascii?Q?=27Jose_Soares=27?= <jose@sferacarta.com>, hackers
	<pgsql-hackers@postgresql.org>, "'pgsql-interfaces@hub.org'"
	<pgsql-interfaces@hub.org>
Subject: RE: [INTERFACES] error message
Date: Tue, 4 May 1999 11:37:22 -0400
Organization: MPS, LLC
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

i do have a few fields (combo boxes based on a query) that sorted on a 
calculated field -- but they seem to work fine in other forms.  --don't 
think that was it.  but, based on your postulations i re-did a few of the 
queries to NOT sort on the calculated fields.  still bombed though. . . 
 so, i deleted the link to the particular table, and then re-linked to it, 
and to my surprise, i can now pull up the form without it bombing.  i can 
even zip through all the records (36k of them) if i wish.  but now it still 
has a problem -- the recordset is not updateable.  if i open the table 
directly (not through any query, and again, from within access97, pgsql 
6.4.2), the table itself is not updateable.  the table DOES have a primary 
key -- a DUAL FIELD primary key.  I found over the last few weeks that 
Access97 will treat any ODBC table without a primary key defined as not 
updateable, but will it also do the same for DUAL FIELD primary key ODBC 
tables??  it appears so, but can anyone confirm it??

jt

-----Original Message-----
From:	Jose Soares [SMTP:jose@sferacarta.com]
Sent:	Tuesday, May 04, 1999 8:42 AM
To:	JT Kirkpatrick; hackers
Subject:	Re: [INTERFACES] error message

JT Kirkpatrick ha scritto:

> hi, i'm using access97 linked to postgres(6.4.2) tables through the new
> v.6.4 odbc.  i can open a form, it shows me data for an initial record, 
and
> then bombs.  here is the message in the log file -- i can't figure out 
why
> it is bombing.  does anyone have a clue??  do those "-" or "/" in various
> "vinvnum" fields cause problems??  it shows valid data first, waits for a
> second, and then bombs!
>

I had a similar error when I tried to order retrieved data by a field not 
in
the table or a calculated field.
Seems that Access request an order by a field with an unknown type.
I can emulate a similar message as:

select 'AAAAAA' union select 'ZZZZZZ' order by 1 asc;
ERROR:  Unable to identify a binary operator '>' for types unknown and 
unknown

select 'aaaaaa' union select 'zzzzzz' order by 1;
ERROR:  Unable to identify a binary operator '<' for types unknown and 
unknown

May be we need a default for UNKNOWN types (what do you think Thomas, if we
make unknown type =  text type?)

Any way. Try these functions:

create function unknown_lt(unknown,unknown) returns bool as
'declare
        i1 text;
        i2 text;
begin
        i1:= $1;
        i2:= $2;
        return (i1 < i2);
end; ' language 'plpgsql';
CREATE

create operator < (
        leftarg=unknown,
        rightarg=unknown,
        procedure=unknown_lt,
        commutator='<',
        negator='>=',
        restrict=eqsel,
        join=eqjoinsel
        );
CREATE

create function unknown_gt(unknown,unknown) returns bool as
'declare
        i1 text;
        i2 text;
begin
        i1:= $1;
        i2:= $2;
        return (i1 > i2);
end; ' language 'plpgsql';
CREATE
create operator > (
        leftarg=unknown,
        rightarg=unknown,
        procedure=unknown_gt,
        commutator='>',
        negator='<=',
        restrict=eqsel,
        join=eqjoinsel
        );
CREATE

select 'AAAAAA' union select 'ZZZZZZ' order by 1 asc;
?column?
--------
AAAAAA
ZZZZZZ
(2 rows)

select 'aaaaaa' union select 'zzzzzz' order by 1 desc;
?column?
--------
zzzzzz
aaaaaa
(2 rows)

EOF

______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'