Обсуждение: 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 > -------------------------------------------------------------- > > ************
Re: [GENERAL] ODBC-client->Linux-server: datatype boolean not recognized?
>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"