pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedODBC 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