Обсуждение: ms-access and booleans ?
Hi, I transferred a couple of tables from an Access MDB to Postgres. Among the involved column-types are booleans. I could dump the contend of the Access table to PG via an odbc linked table. In psql I can run : SELECT count(*) FROM my_table WHERE some_boolean; It shows some number as expected. 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 How can have booleans that don't break my Access front-end ? cu Andreas
--- Andreas <maps.on@gmx.net> wrote: > Hi, > > I transferred a couple of tables from an Access MDB > to Postgres. > Among the involved column-types are booleans. > > I could dump the contend of the Access table to PG > via an odbc linked table. > > In psql I can run : > SELECT count(*) FROM my_table WHERE > some_boolean; > It shows some number as expected. > > 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. > > How can have booleans that don't break my Access > front-end ? > > > cu > Andreas > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the > unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) __________________________________ Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus
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. 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. Live s*cks I tell ya. =8-}
--- 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
On Monday 19 January 2004 07:36 am, Jeff Eckermann wrote: > --- Andreas <maps.on@gmx.net> wrote: > > Hi, > > > > I transferred a couple of tables from an Access MDB > > to Postgres. > > Among the involved column-types are booleans. > > > > I could dump the contend of the Access table to PG > > via an odbc linked table. > > > > In psql I can run : > > SELECT count(*) FROM my_table WHERE > > some_boolean; > > It shows some number as expected. > > > > 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. > > > How can have booleans that don't break my Access > > front-end ? > > > > > > cu > > Andreas > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the > > unregister command > > (send "unregister YourEmailAddressHere" to > > majordomo@postgresql.org) > > > __________________________________ > Do you Yahoo!? > Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes > http://hotjobs.sweepstakes.yahoo.com/signingbonus > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly The way I have handled this is to avoid check boxes and use a combo box instead. I supply the values as True;1 ,False;0 and bind the field to the second value of each pair. To make things easier for data entry I hide the second column by giving it a width of 0". In my DSN settings I check bool as char and uncheck true as -1. -- Adrian Klaver aklaver@comcast.net