pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
ODBC SQL question
3+ messages / 3 participants
[nested] [flat]

* ODBC SQL question
@ 1999-06-22 00:12  Hostmaster - Internet au Virtuel Inc. <hostmaster@virtuel.qc.ca>
  0 siblings, 1 reply; 3+ messages in thread

From: Hostmaster - Internet au Virtuel Inc. @ 1999-06-22 00:12 UTC (permalink / raw)
  To: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] ODBC SQL question
@ 1999-06-22 06:37  José Soares <jose@sferacarta.com>
  parent: Hostmaster - Internet au Virtuel Inc. <hostmaster@virtuel.qc.ca>
  0 siblings, 0 replies; 3+ messages in thread

From: José Soares @ 1999-06-22 06:37 UTC (permalink / raw)
  To: Hostmaster - Internet au Virtuel Inc. <hostmaster@virtuel.qc.ca>; +Cc: pgsql-sql

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'

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* RE: [SQL] ODBC SQL question
@ 1999-06-22 15:29  Michael J Davis <michael.j.davis@tvguide.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Michael J Davis @ 1999-06-22 15:29 UTC (permalink / raw)
  To: 'José Soares' <jose@sferacarta.com>; Hostmaster - Internet au Virtuel Inc. <hostmaster@virtuel.qc.ca>; +Cc: 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 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' 
	 



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~1999-06-22 15:29 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-22 00:12 ODBC SQL question Hostmaster - Internet au Virtuel Inc. <hostmaster@virtuel.qc.ca>
1999-06-22 06:37 ` José Soares <jose@sferacarta.com>
1999-06-22 15:29 RE: [SQL] ODBC SQL question Michael J Davis <michael.j.davis@tvguide.com>

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