Re: [GENERAL] Checkboxes on MSAccess and PostgreSQL - Mailing list pgsql-general

That's the same thing I did today to get rid of the boolean problem. Now I've
got another one ;)
I want to use my data contained in the table filled with boolean stuff and I'd
like to convert all the booleans to int2. I know there's a way to do that by
doing something like:

INSERT INTO mynewtable SELECT field1, field2, (booleanfield='t'), field3,...
FROM myoldtable;

but "(booleanfield='t')" gives another boolean value. I think I should typecast
it into an int2 but I don't know how to do that since there isn't a function
liek a bool_to_int2.
Any ideas ?

Michael Davis wrote:

> I defined my Access boolean fields as int2 in Postgres.  This, in
> combination with removing the "Bools as Char" flag in the ODBC driver
> appears to work.  However, I have not been able to much testing.
>
>         -----Original Message-----
>         From:   Valerio Santinelli [SMTP:tanis@mediacom.it]
>         Sent:   Wednesday, February 24, 1999 4:25 AM
>         To:     sferac@bo.nettuno.it
>         Cc:     pgsql-interfaces@postgreSQL.org;
> pgsql-general@postgreSQL.org
>         Subject:        Re: [GENERAL] Checkboxes on MSAccess and PostgreSQL
>
>         Jose' Soares wrote:
>
>         > Valerio Santinelli wrote:
>         > >
>         > > First of all.. thanks to everybody for helping me out witht the
>         > > "sequence" stuff. :)
>         > >
>         > > I've got another questions for you dudes.  When using a CheckBox
> in a
>         > > Form written in MSAccess that's related to a table in a
>         > > PostgreSQL database, if I simply turn its status from ON to OFF
> it works
>         > > fine, while if i'm doing the opposite it won't work.
>         > >
>         > > I noticed that when exporting the table from MSAccess to
> PostgreSQL the
>         > > "yes/no" fields all became char(1) and not boolean.. maybe this
> could be
>         > > the problem.
>         > >
>         > > I also noticed from the logs that when updating the status from
> ON to
>         > > OFF the UPDATE goes out with something like field_name='0'
>         > > I'm not sure it should use the "'" since it's more like a
> numerical, no
>         > > ?
>         > >
>         > > Thanks again,
>         > >
>         > > Valerio Santinelli
>         > > tanis@mediacom.it
>         >
>         > You have to uncheck "Bools as Char" on Advanced options Driver on
> ODBC
>         > Data Source Administrator
>         > and then you have to create an operator for bool=int4 like this:
>         >
>         > /* M$Access tratta il valore booleano come un intero 0=false o
> -1=true
>         >    mentre invece PostgreSQL lo tratta come una stringa:
>         >    'true','t','1','y','yes','false','f','0','n','no'
>         >    Questo script crea l'operatore = e implicitamente <> per bool e
> int4
>         >    per compatibilita' con M$Access.
>         > */
>         > drop operator = (bool,int4);
>         > drop function MsAccessBool(bool,int4);
>         >
>         > create function MsAccessBool(bool,int4)
>         >   returns bool
>         >   as ''
>         >   language 'internal';
>         >
>         > create operator = (
>         >         leftarg=bool,
>         >         rightarg=int4,
>         >         procedure=MsAccessBool,
>         >         commutator='=',
>         >         negator='<>',
>         >         restrict=eqsel,
>         >         join=eqjoinsel
>         >         );
>
>         Some time ago I got this message and I tried to make booleans work
> with my
>         database, but there's been no way to do that. First of all, during
> the
>         "DROP MsAccessBool" my PostgreSQL database engine reports that the
> function
>         can't be dropped because it's an internal (built-in).
>         Second, it seems to replace it by creating the function (i don't
> know how it
>         could if the function already exists..) but then again when I'm
> updating a
>         record in a table with booleans it simply doesn't let me do that.
> I'm
>         desperately seeking help now.. I don't really know how to solve this
> problem.
>
>         Thanks in advance
>
>         Valerio Santinelli
>         tanis@mediacom.it
>

--

C'ya!

Valerio Santinelli a.k.a. TANiS
[tanis@mediacom.it]+:+[http://www.mediacom.it/~tanis]