Monday, April 03, 2006

The Access ODBC PostgreSQL boolean mess

I like using PostgreSQL with an MS Access frontend for various little database applications, but always had trouble with boolean fields. Finally, after all sorts of tests, I think I nailed down what is needed to make it work, and avoid all these errors like "operator does not exist: boolean = integer", "Data Type mismatch in criteria expression", "invalid input syntax for type boolean: "-" (#7)", and another one which I can't remember. What we want is simple:
  • Booleans should be usable in Access queries with simple statements like SELECT x FROM y WHERE z; (and no silly stuff like where z=true, z='t', z=-1 or the insane where cbool(z)=true)
  • Booleans should appear as check boxes in Access forms and tables
I still don't quite understand why this is not hanlded transparently by the ODBC driver, but anyway here is what seems to be needed with PostgreSQL 7.4:
  • In PostgreSQL, make sure your boolean fields have a default value. Access cannot handle NULLs in booleans.
  • Configure the ODBC driver for "Bools as Char": no, "True is -1": yes.
  • Create these functions and operators in your database and/or in template1 for new databases:
    CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
    AS '$libdir/plpgsql', 'plpgsql_call_handler'
    LANGUAGE c;
    
    CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
    
    CREATE FUNCTION inttobool(integer, boolean) RETURNS boolean
    AS '
    begin
      if $1=0 and not $2 then
      return true;
      elsif $1<>0 and $2 then
      return true;
      else
              return false;
      end if;
    end;
    '
    LANGUAGE plpgsql;
    
    CREATE FUNCTION inttobool(boolean, integer) RETURNS boolean
    AS '
    begin
      return inttobool($2, $1);
    end;
    '
    LANGUAGE plpgsql;
    
    CREATE FUNCTION notinttobool(boolean, integer) RETURNS boolean
    AS '
    begin
      return not inttobool($2,$1);
    end;
    '
    LANGUAGE plpgsql;
    
    CREATE FUNCTION notinttobool(integer, boolean) RETURNS boolean
    AS '
    begin
    return not inttobool($1,$2);
    end;
    '
    LANGUAGE plpgsql;
    
    CREATE OPERATOR = (
    PROCEDURE = inttobool,
    LEFTARG = boolean,
    RIGHTARG = integer,
    COMMUTATOR = =,
    NEGATOR = <>
    );
    
    CREATE OPERATOR <> (
    PROCEDURE = notinttobool,
    LEFTARG = integer,
    RIGHTARG = boolean,
    COMMUTATOR = <>,
    NEGATOR = =
    );
    
    CREATE OPERATOR = (
    PROCEDURE = inttobool,
    LEFTARG = integer,
    RIGHTARG = boolean,
    COMMUTATOR = =,
    NEGATOR = <>
    );
    
    CREATE OPERATOR <> (
    PROCEDURE = notinttobool,
    LEFTARG = boolean,
    RIGHTARG = integer,
    COMMUTATOR = <>,
    NEGATOR = =
    );
    After linking the tables in Access, if you want check boxes in table view, you need to go into table design mode. It will show an error because it's a linked table; just ignore it. Select your boolean field(s) and set their Lookup -> Display Control to Check Box.
Update: See also this page.

Labels: , , , , ,

2 Comments:

Anonymous Alexis said...

Salut Bahut,

J'ai trouvé ta page en cherchant à propos des booleans dans Postgresql.

Tu dis qu'il faut faire un setup d'ODBC mais tu ne dis pas comment ni où il faut le faire. Dans quel fichier?! Le .ini utilise des = pas des :, non? Ça supporte les deux?

31 October, 2008 22:39  
Anonymous Milivoj said...

If you are editing the .ini file directly, it is

"BoolsAsChar"="0"
and
"TrueIsMinus1"="1"

But you can configure it through the driver's GUI.

20 November, 2008 18:12  

Post a Comment

Links to this post:

Create a Link

<< Home