Lists: | pgsql-odbc |
---|
From: | "Fred Parkinson" <FredP(at)abag(dot)ca(dot)gov> |
---|---|
To: | <pgsql-odbc(at)postgresql(dot)org> |
Subject: | Only fails when logging is off |
Date: | 2003-02-20 17:17:56 |
Message-ID: | se549d65.080@groupwise.abag.ca.gov |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-odbc |
Here is a problem that disappears when logging is on:
Attempting to issue the following SQL query from Access '97 against a postgres database:
SELECT tcompanies.company_id, CStr([tcompanies].[login_id]) & ": " & [tcompanies].[name] AS Expr1, tcompanies.name
FROM tcompanies
WHERE tcompanies.active
ORDER BY tcompanies.name;
column 'active' is boolean
2 messages ppear in succession
1. ODBC Call failed
2. ERROR: Unable to identify an operator "=" for types "boolean" and "integer"
You will have to retype this query using an explicit cast (#7)
Any ideas?
Fred Parkinson
Association of Bay Area Governments
From: | Jeff Eckermann <jeff_eckermann(at)yahoo(dot)com> |
---|---|
To: | Fred Parkinson <FredP(at)abag(dot)ca(dot)gov>, pgsql-odbc(at)postgresql(dot)org |
Subject: | Re: Only fails when logging is off |
Date: | 2003-02-20 20:34:59 |
Message-ID: | 20030220203500.33841.qmail@web20805.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-odbc |
--- Fred Parkinson <FredP(at)abag(dot)ca(dot)gov> wrote:
> Here is a problem that disappears when logging is
> on:
What version of PostgreSQL is this?
> 2. ERROR: Unable to identify an operator "=" for
> types "boolean" and "integer"
> You will have to retype this query using an
> explicit cast (#7)
>
Sounds like an old problem. Try running the following
code in in PostgreSQL:
CREATE FUNCTION MsAccessBool (bool, int4) RETURNS BOOL
AS '
BEGIN
IF $1 ISNULL THEN
RETURN NULL;
END IF;
IF $1 IS TRUE THEN
IF $2 <> 0 THEN
RETURN TRUE;
END IF;
ELSE
IF $2 = 0 THEN
RETURN TRUE;
END IF;
END IF;
RETURN FALSE;
END;
' LANGUAGE 'plpgsql';
CREATE OPERATOR = (
LEFTARG = BOOL,
RIGHTARG = INT4,
PROCEDURE = MsAccessBool,
COMMUTATOR = '=',
NEGATOR = '<>',
RESTRICT = EQSEL,
JOIN = EQJOINSEL
);
If this doesn't help you, please post your ODBC driver
settings. I assume these are boolean fields in
PostgreSQL: they should show as "yes/no" in Access.
If they do not, try relinking your tables after
running the above code.
__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/