Received: from corppo1.uvsg.com (corppo1.uvsg.com [192.40.65.90]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA51070 for ; Tue, 22 Jun 1999 11:29:06 -0400 (EDT) (envelope-from michael.j.davis@tvguide.com) Received: from prevuepo1.uvsg.com ([192.40.65.44]) by corppo1.uvsg.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2448.0) id N1F3Y8ZZ; Tue, 22 Jun 1999 10:29:21 -0500 Received: by prevuepo1.uvsg.com with Internet Mail Service (5.5.2448.0) id ; Tue, 22 Jun 1999 10:32:20 -0500 Message-ID: <93C04F1F5173D211A27900105AA8FCFC298FC3@lambic.prevuenet.com> From: Michael J Davis To: =?iso-8859-1?Q?=27Jos=E9_Soares=27?= , "Hostmaster - Internet au Virtuel Inc." Cc: pgsql-sql Subject: RE: [SQL] ODBC SQL question Date: Tue, 22 Jun 1999 10:29:17 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2448.0) Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by hub.org id LAA51076 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 AM To: Hostmaster - Internet au Virtuel Inc. Cc: pgsql-sql Subject: 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; 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'