Thread: Only fails when logging is off

Only fails when logging is off

From

"Fred Parkinson"

Date:

20 February 2003, 15:27:26

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

Re: Only fails when logging is off

From

Jeff Eckermann

Date:

20 February 2003, 18:45:11

--- Fred Parkinson <FredP@abag.ca.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/