Re: [GENERAL] Access bool integer solution

2005-12-19 Thread Richard Huxton

Sim Zacks wrote:
I've been having problems with bools in my Access frontend and 
PostGreSQL backend. The problem is that Access uses -1 for true and 0 
for false and when it does a select it uses those numbers instead of the 
true or false values. PostGreSQL does not have an implicit conversion 
from int to bool, so it was failing was with an error Operator does not 
exist: boolean=integer
I got around this in my queries by using the cbool function, which 
passed in the actual true or false values. This solution did not work 
for internal filters, because it internally calls the select statement 
and does not give an interface to the code.


Are you sure there isn't a setting in the ODBC driver to handle this?

--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


Re: [GENERAL] Access bool integer solution

2005-12-19 Thread Sim Zacks

There are 3 bool possibilities in the ODBC driver
1) bools as char
2) true = -1
3) default (whatever that is, neither option is chosen)

When it is the default (my current setting) or (True =-1 and not bools 
as char), then Access recognizes the data type as Yes/No. However, if 
you do a comparison in any query you get the operator does not exist 
error, unless you use the cbool function to convert the field that 
access recognizes as a bool but considers an integer, from an integer to 
a real bool.


When bools as char is chosen and not true=-1 then access sees it as a 
single character and you cannot click on a checkbox because Access tries 
to put in a -1 which is 2 characters. However, queries work without any 
extra functions.


When bools as char and True=-1 are chosen then Access see it as a 2 
character field, so it should accept the -1, but it gives an error that 
-1 does not fit in the field.



Richard Huxton wrote:

Sim Zacks wrote:
I've been having problems with bools in my Access frontend and 
PostGreSQL backend. The problem is that Access uses -1 for true and 0 
for false and when it does a select it uses those numbers instead of 
the true or false values. PostGreSQL does not have an implicit 
conversion from int to bool, so it was failing was with an error 
Operator does not exist: boolean=integer
I got around this in my queries by using the cbool function, which 
passed in the actual true or false values. This solution did not work 
for internal filters, because it internally calls the select statement 
and does not give an interface to the code.


Are you sure there isn't a setting in the ODBC driver to handle this?



---(end of broadcast)---
TIP 6: explain analyze is your friend


[GENERAL] Access bool integer solution

2005-12-18 Thread Sim Zacks
I've been having problems with bools in my Access frontend and 
PostGreSQL backend. The problem is that Access uses -1 for true and 0 
for false and when it does a select it uses those numbers instead of the 
true or false values. PostGreSQL does not have an implicit conversion 
from int to bool, so it was failing was with an error Operator does not 
exist: boolean=integer
I got around this in my queries by using the cbool function, which 
passed in the actual true or false values. This solution did not work 
for internal filters, because it internally calls the select statement 
and does not give an interface to the code.


Using PostGreSQL flexibility and extensibility I have solved this 
problem by defining an = and  operator between int and boolean.


I hope this code can help someone else, as I couldn't find any real 
solution in the archives, but I saw the same question a number of times.


create or replace function inttobool(num int,val bool) returns bool as
$$
begin
if num=0 and not val then
return true;
elsif num0 and val then
return true;
else return false;
end if;
end;
$$ language 'plpgsql';
create or replace function inttobool(val bool, num int) returns bool as
$$
begin
return inttobool(num,val);
end;
$$ language 'plpgsql';
create or replace function notinttobool(val bool, num int) returns bool as
$$
begin
return not inttobool(num,val);
end;
$$ language 'plpgsql';
create or replace function notinttobool(num int, val bool) returns bool as
$$
begin
return not inttobool(num,val);
end;
$$ language 'plpgsql';

CREATE OPERATOR = (
leftarg = integer,
rightarg = boolean,
procedure = inttobool,
commutator = =,
negator = !=
);
CREATE OPERATOR = (
leftarg = boolean,
rightarg = integer,
procedure = inttobool,
commutator = =,
negator = !=
);
CREATE OPERATOR  (
leftarg = integer,
rightarg = boolean,
procedure = notinttobool,
commutator = ,
negator = =
);
CREATE OPERATOR  (
leftarg = boolean,
rightarg = integer,
procedure = notinttobool,
commutator = ,
negator = =
);


Sim Zacks

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq