Обсуждение: ODBC SQL question

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

The <tt>M$Access boolean value is an integer (0 or -1);</tt><br /><tt>    FALSE=0</tt><br /><tt>    TRUE=-1</tt><br
/><tt>whilethe PostgreSQL boolean is a string;</tt><br /><tt>    TRUE= 'true','t','1','y','yes'</tt><br /><tt>   
FALSE='false','f','0','n','no'</tt><tt></tt><p><tt>Youhave to create an = operator for bool and int4 for compatibility
withM$Access.</tt><br /><tt> </tt><br /><tt>This PL/pgsql script creates all what you need:</tt><tt></tt><p><tt>--this
functionreturns every zero value as FALSE otherwise as TRUE--</tt><br /><tt>create function AccessBool(bool,int4)
returnsbool as '</tt><br /><tt>begin</tt><br /><tt>     if $1 is NULL then</tt><br /><tt>         return NULL;</tt><br
/><tt>    end if;</tt><br /><tt>     if $1 is TRUE then</tt><br /><tt>         if $2 <> 0 then</tt><br
/><tt>            return TRUE;</tt><br /><tt>         end if;</tt><br /><tt>     else</tt><br /><tt>         if $2 = 0
then</tt><br/><tt>             return TRUE;</tt><br /><tt>         end if;</tt><br /><tt>     end if;</tt><br
/><tt>    return FALSE;</tt><br /><tt>end;</tt><br /><tt>' language 'plpgsql';</tt><tt></tt><p><tt>create operator =
(</tt><br/><tt>        leftarg=bool,</tt><br /><tt>        rightarg=int4,</tt><br /><tt>       
procedure=AccessBool,</tt><br/><tt>        commutator='=',</tt><br /><tt>        negator='!=',</tt><br /><tt>       
restrict=eqsel,</tt><br/><tt>        join=eqjoinsel</tt><br /><tt>        );</tt><br /><tt></tt>  <br />  <br /> 
<p>"Hostmaster- Internet au Virtuel Inc." ha scritto: <blockquote type="CITE">Hi <p>I'm running PostgreSQL v6.4.2 on
RedHat Linux 6.0. I use it on a Windows NT <br />server 4.0 with service pack 5 through ODBC calls; the ODBC driver is
the<br />one from Insight Distributions System, v6.40.00.06. <p>Usually everything is fine. My datasource works, as I
canlink tables from <br />MS Access 97 without problems. <p>Here is what I don't understand: <br />Query1: SELECT *
FROMsites WHERE free = FALSE; <br />Query2: SELECT * FROM sites WHERE free IS FALSE; <p>When I run them within psql,
bothqueries output the requested rows. <p>Through the ODBC driver, query1 complains that "ERROR: There is more one <br
/>possibleoperator '=' for types 'bool' and 'int4' You will have to retype <br />this query using an explicit cast (#1)
<p>query2complains for "Invalid use of Is operator in query expression 'free <br />IS FALSE'" <p>I tried fiddling with
theODBC driver parameters, to no avail. What do I do <br />wrong? I ran the queries with MS Access 97 and also with
pgAdmin6.4.3 beta, <br />they both return the same errors. Anybody could help me? <p>Here is the table definition: <br
/>+---------------------+--------------+-------+<br />|       Field         |     Type     | Length| <br
/>+---------------------+--------------+-------+<br />| site_id             | int4         |     4 | <br />|
name               | varchar()    |    50 | <br />| ip                  | varchar()    |    15 | <br />|
parent             | int4         |     4 | <br />| type                | int4         |     4 | <br />|
owner              | int4         |     4 | <br />| admin               | int4         |     4 | <br />|
creation_date      | datetime     |     8 | <br />| non_profit          | bool         |     1 | <br />|
free               | bool         |     1 | <br />| reg_fee             | bool         |     1 | <br />|
bill_period        | int2         |     2 | <br />| bill_date           | datetime     |     8 | <br />|
bill_paid          | bool         |     1 | <br />| paid_until          | datetime     |     8 | <br />|
size               | int4         |     4 | <br />| peek                | int4         |     4 | <br />|
list_personal      | bool         |     1 | <br />| list_organisation   | bool         |     1 | <br />|
list_business      | bool         |     1 | <br />| title_fr            | varchar()    |    75 | <br />|
title_en           | varchar()    |    50 | <br />| description_fr      | varchar()    |   254 | <br />|
description_en     | varchar()    |   254 | <br />| free2               | bool         |     1 | <br
/>+---------------------+--------------+-------+<p>Accept my apologies if I'm not in the right place to ask this. If
thisis <br />the case, please tell me where I should direct my question. <p>Thanks, <p>Nicolas
Cadou</blockquote><blockquotetype="CITE">______________________________________________________________</blockquote>
PostgreSQL6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3 <br
/>^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^<br />Jose' <br />