pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael J Davis <michael.j.davis@tvguide.com>
To: '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 10:29:17 -0500
Message-ID: <93C04F1F5173D211A27900105AA8FCFC298FC3@lambic.prevuenet.com> (raw)

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' 
	 



view thread (3+ messages)

Message-ID: <93C04F1F5173D211A27900105AA8FCFC298FC3@lambic.prevuenet.com>
Permalink:  ../93C04F1F5173D211A27900105AA8FCFC298FC3@lambic.prevuenet.com/
Also on:    postgresql.org/message-id/93C04F1F5173D211A27900105AA8FCFC298FC3@lambic.prevuenet.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: michael.j.davis@tvguide.com, jose@sferacarta.com, hostmaster@virtuel.qc.ca
  Subject: Re: [SQL] ODBC SQL question
  In-Reply-To: <93C04F1F5173D211A27900105AA8FCFC298FC3@lambic.prevuenet.com>
  References: <93C04F1F5173D211A27900105AA8FCFC298FC3@lambic.prevuenet.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