pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: José Soares <jose@sferacarta.com>
To: Hostmaster - Internet au Virtuel Inc. <hostmaster@virtuel.qc.ca>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: [SQL] ODBC SQL question
Date: Tue, 22 Jun 1999 08:37:53 +0200
Message-ID: <376F2F41.B94E2ED8@sferacarta.com> (raw)
References: <007901bebc43$ef12f3e0$058d60cf@virtuel.qc.ca>

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;
     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=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 sites WHERE 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'

view thread (3+ messages)  latest in thread

Message-ID: <376F2F41.B94E2ED8@sferacarta.com>
Permalink:  ../376F2F41.B94E2ED8@sferacarta.com/
Also on:    postgresql.org/message-id/376F2F41.B94E2ED8@sferacarta.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: jose@sferacarta.com, hostmaster@virtuel.qc.ca
  Subject: Re: [SQL] ODBC SQL question
  In-Reply-To: <376F2F41.B94E2ED8@sferacarta.com>
  References: <007901bebc43$ef12f3e0$058d60cf@virtuel.qc.ca> <376F2F41.B94E2ED8@sferacarta.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox