Re: ms-access and booleans ?
От | Jeff Eckermann |
---|---|
Тема | Re: ms-access and booleans ? |
Дата | |
Msg-id | 20040120014624.95395.qmail@web20803.mail.yahoo.com обсуждение исходный текст |
Ответ на | Re: ms-access and booleans ? (Andreas <maps.on@gmx.net>) |
Список | pgsql-odbc |
--- Andreas <maps.on@gmx.net> wrote: > Jeff Eckermann wrote: > > >--- Andreas <maps.on@gmx.net> wrote: > > > > > >>I Access I get an Error: > >>ODBC-Call failed. > >>Error: Operator doesn't exist: boolean = integer > >>(#7) > >> > >>In the System-DSN I have > >>Bool as Char : NO > >>True is -1 : YES > >> > >> > > > >You should have success by playing with these > >settings. I use different settings, but that is a > >historical matter. I believe that the ODBC driver > >handling of booleans for MS Access has been > optimized > >somewhat in recent releases. Probably just setting > >"bool as char" to true will do it. But I admit > that I > >haven't played with these lately, not wanting to > mess > >up a working setup. > > > > > > No way :( > The problem lies in the way a SELECT is handled by > Access. > > I have a test table with a bool-column called "bol". > > Access generates : > SELECT test1.* > FROM test1 > WHERE (((test1.bol)=True)); > > Trying this Access throws an error which says there > is no comparison > function between boolean and integer. That's PostgreSQL complaining, not Access: jeff=# select '1' = true; ?column? ---------- t (1 row) jeff=# select 1 = true; ERROR: Unable to identify an operator '=' for types 'integer' and 'boolean' You will have to retype this query using an explicit cast Access is converting 'true' and 'false' to 0 and -1 behind the scenes. > > In contrast pgAdmin as well as psql run the same > query without complaints. > Both show the bool-column as t or f. > Access would display 0 / -1. > > Interestingly I can enter TRUE or FALSE into the > table view of this > table within Access. > TRUE gets translated to -1 and FALSE to 0. > Even 1 gets translated to -1. > > But no way I could use the bool-column in an Access > query as criterium, > if not "bool as char" is set. > Then I can query bol = "t" > On the other hand "bool as char" breaks the Access > check boxes. > Initially they show the bool-values correctly but if > I try to set a > checkbox to TRUE I get an error that the value was > too big for the > column. I guess "-1" doesn't fit into PG's BOOLEAN > which is CHAR(1) as > far as I know. I had this problem too. I solved it by unchecking the "bool as char" option, and creating the missing operator in PostgreSQL: 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 ); Put this in your template1 database, as well as any other you are working in, and you should be in good shape. I was sure that that I had heard of some enhancements to the driver that made all of this unnecessary. Perhaps not that many people are using checkboxes in Access? > > > Live s*cks I tell ya. =8-} > > > > > > > > __________________________________ Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus
В списке pgsql-odbc по дате отправления: