Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1]) by hub.org (8.9.3/8.9.3) with ESMTP id DAA51484 for ; Tue, 22 Jun 1999 03:48:03 -0400 (EDT) (envelope-from jose@sferacarta.com) Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it [193.207.10.213]) by bologna.nettuno.it (8.8.6/8.8.8/NETTuno 3.2) with ESMTP id JAA14749; Tue, 22 Jun 1999 09:47:56 +0200 (MDT) Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5] by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian)) id 10wM7R-0003lD-00; Tue, 22 Jun 1999 08:41:25 +0000 Message-ID: <376F2F41.B94E2ED8@sferacarta.com> Date: Tue, 22 Jun 1999 08:37:53 +0200 From: =?iso-8859-1?Q?Jos=E9?= Soares X-Mailer: Mozilla 4.5 [it] (Win95; I) X-Accept-Language: it MIME-Version: 1.0 To: "Hostmaster - Internet au Virtuel Inc." CC: pgsql-sql Subject: Re: [SQL] ODBC SQL question References: <007901bebc43$ef12f3e0$058d60cf@virtuel.qc.ca> Content-Type: multipart/alternative; boundary="------------1D636FAD1613D1E4755998BA" --------------1D636FAD1613D1E4755998BA Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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' --------------1D636FAD1613D1E4755998BA Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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'
  --------------1D636FAD1613D1E4755998BA--