RE: [SQL] ODBC SQL question - Mailing list pgsql-sql

I choose to define all booleans in Access97 as int2 in PostgreSQL.  This
eliminated the problem with any code or SQL changes.
-----Original Message-----From:    José Soares [SMTP:jose@sferacarta.com]Sent:    Tuesday, June 22, 1999 12:38 AMTo:
Hostmaster- Internet au Virtuel Inc.Cc:    pgsql-sqlSubject:    Re: [SQL] ODBC SQL question
 
The M$Access boolean value is an integer (0 or -1);     FALSE=0     TRUE=-1 while the PostgreSQL boolean is a string;
 TRUE= 'true','t','1','y','yes'     FALSE='false','f','0','n','no' 
 
You have to create an = operator for bool and int4 for compatibility
with M$Access.   This PL/pgsql script creates all what you need: 
--this function returns every zero value as FALSE otherwise as
TRUE-- create function AccessBool(bool,int4) returns bool as ' begin      if $1 is NULL then          return NULL;
endif;      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=AccessBool,         commutator='=',
      negator='!=',         restrict=eqsel,         join=eqjoinsel         );       
 
"Hostmaster - Internet au Virtuel Inc." ha scritto: 
    Hi 
    I'm running PostgreSQL v6.4.2 on Red Hat Linux 6.0. I use it
on a Windows NT     server 4.0 with service pack 5 through ODBC calls; the ODBC
driver is the     one from Insight Distributions System, v6.40.00.06. 
    Usually everything is fine. My datasource works, as I can
link tables from     MS Access 97 without problems. 
    Here is what I don't understand:     Query1: SELECT * FROM sites WHERE free = FALSE;     Query2: SELECT * FROM
sitesWHERE free IS FALSE; 
 
    When I run them within psql, both queries output the
requested rows. 
    Through the ODBC driver, query1 complains that "ERROR: There
is more one     possible operator '=' for types 'bool' and 'int4' You will
have to retype     this query using an explicit cast (#1) 
    query2 complains for "Invalid use of Is operator in query
expression 'free     IS FALSE'" 
    I tried fiddling with the ODBC driver parameters, to no
avail. What do I do     wrong? I ran the queries with MS Access 97 and also with
pgAdmin 6.4.3 beta,     they both return the same errors. Anybody could help me? 
    Here is the table definition:     +---------------------+--------------+-------+     |       Field         |
Type    | Length|     +---------------------+--------------+-------+     | site_id             | int4         |     4 |
   | name                | varchar()    |    50 |     | ip                  | varchar()    |    15 |     | parent
      | int4         |     4 |     | type                | int4         |     4 |     | owner               | int4
  |     4 |     | admin               | int4         |     4 |     | creation_date       | datetime     |     8 |     |
non_profit         | bool         |     1 |     | free                | bool         |     1 |     | reg_fee
| bool         |     1 |     | bill_period         | int2         |     2 |     | bill_date           | datetime     |
  8 |     | bill_paid           | bool         |     1 |     | paid_until          | datetime     |     8 |     | size
             | int4         |     4 |     | peek                | int4         |     4 |     | list_personal       |
bool        |     1 |     | list_organisation   | bool         |     1 |     | list_business       | bool         |
1|     | title_fr            | varchar()    |    75 |     | title_en            | varchar()    |    50 |     |
description_fr     | varchar()    |   254 |     | description_en      | varchar()    |   254 |     | free2
| bool         |     1 |     +---------------------+--------------+-------+ 
 
    Accept my apologies if I'm not in the right place to ask
this. If this is     the case, please tell me where I should direct my question. 
    Thanks, 
    Nicolas Cadou

______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^Jose'