Обсуждение: ODBC-client->Linux-server: datatype boolean not recognized?

Hello all,

I'm trying to connect from a ODBC-client to PostgreSQL on a Linux-server.
All is going well, except that the ODBC-client seems not to recognize the
PostgreSQL boolean-datatype.

Is this really caused by PostgreSQL? If yes: is there a workaround, like
altering the datatypename in the proper places (pg_type?)? If no: then it
is another problem and can somebody give a hint?

Thanks,
Jelle.


I tested and encountered it in the following testsituations:

Situation 1:
Client: Mac via OpenLink ODBC-Client
Server: Linux RH 6.0 via OpenLink Requestbroker
Result: it looks like PostgreSQL-boolean is converted to SQL_C_CHAR

Situation 2:
Client: NT with psqlODBC
Server: same Linux
Result: in MS-Query:
-  viewing the table-definitions, it comes with the message that it
doesn't recognize datatype 'bool'
-  selecting values it looks like PostgreSQL-boolean is converted to a
numeric

PostgreSQL: 6.5.1


--------------------------------------------------------------
                   NEROC Publishing Solutions

                      Jelle Ruttenberg

De Run 1131, 5503 LB Veldhoven    Phone  : +31-(0)40-2586641
P.O.Box 133, 5500 AC Veldhoven    Fax    : +31-(0)40-2541893
The Netherlands                   E-mail : ruttenberg@neroc.nl
--------------------------------------------------------------

I had the same problem using m$access as client because m$access treats the
boolean value like an integer
0=false and -1=true while PostgreSQL treats the same value like a string (
'true','t','1','y','yes','false','f','0','n','no').
I created the following function and operator and now it works.

create function MsAccessBool(bool,int4)  returns bool
  as '' language 'internal';

create operator = (
        leftarg=bool,
        rightarg=int4,
        procedure=MsAccessBool,
        commutator='=',
        negator='<>',
        restrict=eqsel,
        join=eqjoinsel
        );

José

PS: remember to uncheck the field: "Bools as char" in the ODBC manager.


Jelle Ruttenberg ha scritto:

> Hello all,
>
> I'm trying to connect from a ODBC-client to PostgreSQL on a Linux-server.
> All is going well, except that the ODBC-client seems not to recognize the
> PostgreSQL boolean-datatype.
>
> Is this really caused by PostgreSQL? If yes: is there a workaround, like
> altering the datatypename in the proper places (pg_type?)? If no: then it
> is another problem and can somebody give a hint?
>
> Thanks,
> Jelle.
>
> I tested and encountered it in the following testsituations:
>
> Situation 1:
> Client: Mac via OpenLink ODBC-Client
> Server: Linux RH 6.0 via OpenLink Requestbroker
> Result: it looks like PostgreSQL-boolean is converted to SQL_C_CHAR
>
> Situation 2:
> Client: NT with psqlODBC
> Server: same Linux
> Result: in MS-Query:
> -  viewing the table-definitions, it comes with the message that it
> doesn't recognize datatype 'bool'
> -  selecting values it looks like PostgreSQL-boolean is converted to a
> numeric
>
> PostgreSQL: 6.5.1
>
> --------------------------------------------------------------
>                    NEROC Publishing Solutions
>
>                       Jelle Ruttenberg
>
> De Run 1131, 5503 LB Veldhoven    Phone  : +31-(0)40-2586641
> P.O.Box 133, 5500 AC Veldhoven    Fax    : +31-(0)40-2541893
> The Netherlands                   E-mail : ruttenberg@neroc.nl
> --------------------------------------------------------------
>
> ************

>create function MsAccessBool(bool,int4)  returns bool
>  as '' language 'internal';

There is surely something missing here, between the empty single
quotes? When I execute that, I get "There is no internal function
msaccessbool"

create function MsBool(bool,int4) returns bool as '
declare
        bool_int int4;
begin
        if $1 is NULL 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=MsBool,
        commutator='=',
        negator='!=',
        restrict=eqsel,
        join=eqjoinsel
        );
 
 

Moray McConnachie ha scritto:

>create function MsAccessBool(bool,int4)  returns bool
>  as '' language 'internal';

There is surely something missing here, between the empty single
quotes? When I execute that, I get "There is no internal function
msaccessbool"