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'

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'

JT Kirkpatrick ha scritto:

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??

I'm using also access97 with postodbc 6.40.0005 and PostgreSQL v6.5beta1 and it
works fine and I have primary
keys like:

Table = figure_pkey
+----------------------------------+----------------------------------+-------+

| Field | Type | Length|

+----------------------------------+----------------------------------+-------+

| azienda | char() | 16 |

| tipo | char() | 2 |

| gruppo | int4 | 4 |

| inizio_attivita | date | 4 |

+----------------------------------+----------------------------------+-------+

Which version of psqlodbc are you using?
I know that Access uses dynaset or snapshots to connect to database.
- If you attach a table without define a primary key Access connects to the
table as snapshot and you can't write table.
- If you define a primary key then Access connects to the table as dynaset and
you are able to write table.
- You must define primary key during table linkage time.
- If you have the ODBC manager option RECOGNIZE UNIQUE INDEXES checked then you
don't need define it
because Access recognize the primary key automatically.
- If you have the ODBC manager option READ ONLY checked then all linked tables
are read only.
--
______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'