Обсуждение: ERROR: invalid input syntax for type boolean: "-"(#7)

I get this error with an ms-access front-end boolean type field
where access produces "-1" and Postgresql's bool field does not
accept this.

Im using Postgres 7.4.5, Access 2002, mdac 2.8, psqlodbc-07_03_0200.

Is there a proper work around?


Peter

i'm not too familiar with access, but in my c# code i do something
like this to convert the 1s to bools. i'm not sure if this is the best
way to handle this.  it's kind of ugly:

((string)tableRow["boolCol"]=="1" ? true : false)

also, I don't know if this helps but you can change the -1s to 1s if
you want.  if you're using a DSN to connect to your database, then go
to Data Sources in Admin Tools and if you open up your DSN, you can
configure it by clicking on "datasource".  the second page will have
an option "True as -1".  if you uncheck it then you'll get 1 for true.

amir

On Wed, 29 Sep 2004 03:52:51 +0200, Peter van Heck <usenet@heckwork.nl> wrote:
> I get this error with an ms-access front-end boolean type field
> where access produces "-1" and Postgresql's bool field does not
> accept this.
>
> Im using Postgres 7.4.5, Access 2002, mdac 2.8, psqlodbc-07_03_0200.
>
> Is there a proper work around?
>
> Peter
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

> -----Original Message-----
> From: pgsql-odbc-owner@postgresql.org
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Peter van Heck
> Sent: 29 September 2004 02:53
> To: pgsql-odbc@postgresql.org
> Subject: [ODBC] ERROR: invalid input syntax for type boolean: "-"(#7)
>
> I get this error with an ms-access front-end boolean type
> field where access produces "-1" and Postgresql's bool field
> does not accept this.
>
> Im using Postgres 7.4.5, Access 2002, mdac 2.8, psqlodbc-07_03_0200.
>
> Is there a proper work around?

There is a 'True is -1' option on the second options dialog.

Regards, Dave

On Fri, 1 Oct 2004 20:55:54 +0100, dpage@vale-housing.co.uk ("Dave
Page") wrote:

>> -----Original Message-----
>> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Peter van Heck
>> Sent: 29 September 2004 02:53
>> Subject: [ODBC] ERROR: invalid input syntax for type boolean: "-"(#7)
>>
>> I get this error with an ms-access front-end boolean type
>> field where access produces "-1" and Postgresql's bool field
>> does not accept this.
>>
>> Im using Postgres 7.4.5, Access 2002, mdac 2.8, psqlodbc-07_03_0200.
>>
>> Is there a proper work around?
>
>There is a 'True is -1' option on the second options dialog.

This 'True is -1' option + "Bool as Char" unchecked in combination
with code mentioned below solved my problems what this concens.
Thanks, also to Amir:

DROP OPERATOR = (bool, int4);
DROP FUNCTION MsAccessBool (bool, int4);
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
);


Peter