Thread: Problem in using PostgreSQL ODBC driver with VBA

Good morning

I write in order to obtain clarification (and possibly a solution !) about a problem I can't solve.

First of all I will briefly describe the scenario.

I have several applications written in VBA (Visual basic for Application) using Microsoft Access both as backend and frontend (data are stored in mdb files on a server and applications are stored in mde files on the pcs).

I'm committed in reingeneering these applications in order to migrate data files to a PostgreSQL database, without changing the frontends except that for the connection to the data they manage.

The idea is to connect each frontend to Postgres DB (version 9.5.4) using ODBC (version 9.5.4).

In order to run some tests, I migrated data from an mdb file to PG tables, defined an ODBC connection and linked the Access frontend to the PG database using it.

The problem raises when the application needs to deal with data stored in boolean columns.

If SW_UE is the name of a boolean column in a table, when I use a statements like this

If SW_UE = -1 Then

Do something

End If

the following error raises:

ERROR: Operator does not exist boolean = integer

I already searched the Internet for a solution and I found some, but none solved the problem.

For this reason I decided to write here, hoping in your help and/or some hint.

I thank you in advance for any answer.

Best regards

------------------------------------------------------------
Vincenzo Turturro

Regione Toscana
Direzione generale Governo del territorio
Sistema Informativo Territoriale ed Ambientale

Via di Novoli 26
50127 Firenze

Tel 055 43 83 855
------------------------------------------------------------

On 11/03/2016 08:11 AM, Vincenzo Turturro wrote:
> Good morning
>
> I write in order to obtain clarification (and possibly a solution !)
> about a problem I can't solve.
>
> First of all I will briefly describe the scenario.
>
> I have several applications written in VBA (Visual basic for
> Application) using Microsoft Access both as backend and frontend (data
> are stored in mdb files on a server and applications are stored in mde
> files on the pcs).
>
> I'm committed in reingeneering these applications in order to migrate
> data files to a PostgreSQL database, without changing the frontends
> except that for the connection to the data they manage.
>
> The idea is to connect each frontend to Postgres DB (version 9.5.4)
> using ODBC (version 9.5.4).
>
> In order to run some tests, I migrated data from an mdb file to PG
> tables, defined an ODBC connection and linked the Access frontend to the
> PG database using it.
>
> The problem raises when the application needs to deal with data stored
> in boolean columns.
>
> If SW_UE is the name of a boolean column in a table, when I use a
> statements like this
>
> If SW_UE = -1 Then
>
> Do something
>
> End If
>
> the following error raises:
>
> ERROR: Operator does not exist boolean = integer
>
> I already searched the Internet for a solution and I found some, but
> none solved the problem.
>
> For this reason I decided to write here, hoping in your help and/or some
> hint.

https://odbc.postgresql.org/docs/config.html

Advanced Options 2/2 Dialog Box

True is -1: Represent TRUE as -1 for compatibility with some applications.

While we are on bools:

Advanced Options 1/2 Dialog Box

Data Type Options: affects how some data types are mapped:

Bools as Char: Bools are mapped to SQL_CHAR, otherwise to SQL_BIT.

>
> I thank you in advance for any answer.
>
> Best regards
>
> ------------------------------------------------------------
> Vincenzo Turturro
>
> Regione Toscana
> Direzione generale Governo del territorio
> Sistema Informativo Territoriale ed Ambientale
>
> Via di Novoli 26
> 50127 Firenze
>
> Tel 055 43 83 855
> ------------------------------------------------------------
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Il 03/11/2016 16:16, Adrian Klaver ha scritto:
> On 11/03/2016 08:11 AM, Vincenzo Turturro wrote:
>> Good morning
>>
>> I write in order to obtain clarification (and possibly a solution !)
>> about a problem I can't solve.
>>
>> First of all I will briefly describe the scenario.
>>
>> I have several applications written in VBA (Visual basic for
>> Application) using Microsoft Access both as backend and frontend (data
>> are stored in mdb files on a server and applications are stored in mde
>> files on the pcs).
>>
>> I'm committed in reingeneering these applications in order to migrate
>> data files to a PostgreSQL database, without changing the frontends
>> except that for the connection to the data they manage.
>>
>> The idea is to connect each frontend to Postgres DB (version 9.5.4)
>> using ODBC (version 9.5.4).
>>
>> In order to run some tests, I migrated data from an mdb file to PG
>> tables, defined an ODBC connection and linked the Access frontend to the
>> PG database using it.
>>
>> The problem raises when the application needs to deal with data stored
>> in boolean columns.
>>
>> If SW_UE is the name of a boolean column in a table, when I use a
>> statements like this
>>
>> If SW_UE = -1 Then
>>
>> Do something
>>
>> End If
>>
>> the following error raises:
>>
>> ERROR: Operator does not exist boolean = integer
>>
>> I already searched the Internet for a solution and I found some, but
>> none solved the problem.
>>
>> For this reason I decided to write here, hoping in your help and/or some
>> hint.
>
> https://odbc.postgresql.org/docs/config.html
>
> Advanced Options 2/2 Dialog Box
>
> True is -1: Represent TRUE as -1 for compatibility with some
> applications.
>
> While we are on bools:
>
> Advanced Options 1/2 Dialog Box
>
> Data Type Options: affects how some data types are mapped:
>
> Bools as Char: Bools are mapped to SQL_CHAR, otherwise to SQL_BIT.

Thank you for your answer.
I had already configured the ODBC driver as you suggested, long before
writing the mailing list.
But it didn't work ... and the error continue to raise.

Best regards

>>
>> I thank you in advance for any answer.
>>
>> Best regards
>>
>> ------------------------------------------------------------
>> Vincenzo Turturro
>>
>> Regione Toscana
>> Direzione generale Governo del territorio
>> Sistema Informativo Territoriale ed Ambientale
>>
>> Via di Novoli 26
>> 50127 Firenze
>>
>> Tel 055 43 83 855
>> ------------------------------------------------------------
>>
>
>


--
------------------------------------------------------------
Vincenzo Turturro

Regione Toscana
Direzione generale Governo del territorio
Sistema Informativo Territoriale ed Ambientale

Via di Novoli 26
50127 Firenze

Tel 055 43 83 855
------------------------------------------------------------

On 11/03/2016 08:39 AM, Vincenzo Turturro wrote:
> Il 03/11/2016 16:16, Adrian Klaver ha scritto:
>> On 11/03/2016 08:11 AM, Vincenzo Turturro wrote:
>>> Good morning
>>>
>>> I write in order to obtain clarification (and possibly a solution !)
>>> about a problem I can't solve.
>>>
>>> First of all I will briefly describe the scenario.
>>>
>>> I have several applications written in VBA (Visual basic for
>>> Application) using Microsoft Access both as backend and frontend (data
>>> are stored in mdb files on a server and applications are stored in mde
>>> files on the pcs).
>>>
>>> I'm committed in reingeneering these applications in order to migrate
>>> data files to a PostgreSQL database, without changing the frontends
>>> except that for the connection to the data they manage.
>>>
>>> The idea is to connect each frontend to Postgres DB (version 9.5.4)
>>> using ODBC (version 9.5.4).
>>>
>>> In order to run some tests, I migrated data from an mdb file to PG
>>> tables, defined an ODBC connection and linked the Access frontend to the
>>> PG database using it.
>>>
>>> The problem raises when the application needs to deal with data stored
>>> in boolean columns.
>>>
>>> If SW_UE is the name of a boolean column in a table, when I use a
>>> statements like this
>>>
>>> If SW_UE = -1 Then
>>>
>>> Do something
>>>
>>> End If
>>>
>>> the following error raises:
>>>
>>> ERROR: Operator does not exist boolean = integer
>>>
>>> I already searched the Internet for a solution and I found some, but
>>> none solved the problem.
>>>
>>> For this reason I decided to write here, hoping in your help and/or some
>>> hint.
>>
>> https://odbc.postgresql.org/docs/config.html
>>
>> Advanced Options 2/2 Dialog Box
>>
>> True is -1: Represent TRUE as -1 for compatibility with some
>> applications.
>>
>> While we are on bools:
>>
>> Advanced Options 1/2 Dialog Box
>>
>> Data Type Options: affects how some data types are mapped:
>>
>> Bools as Char: Bools are mapped to SQL_CHAR, otherwise to SQL_BIT.
>
> Thank you for your answer.
> I had already configured the ODBC driver as you suggested, long before
> writing the mailing list.

This was mentioned where in the original post?

> But it didn't work ... and the error continue to raise.

What is raising?:

ERROR: Operator does not exist boolean = integer


Why not do?:

If SW_UE = True

>
> Best regards
>
>>>
>>> I thank you in advance for any answer.
>>>
>>> Best regards
>>>
>>> ------------------------------------------------------------
>>> Vincenzo Turturro
>>>
>>> Regione Toscana
>>> Direzione generale Governo del territorio
>>> Sistema Informativo Territoriale ed Ambientale
>>>
>>> Via di Novoli 26
>>> 50127 Firenze
>>>
>>> Tel 055 43 83 855
>>> ------------------------------------------------------------
>>>
>>
>>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Been there done that.

I have been using Access with PostgreSQL through ODBC for about 20 years, and boolean is essentially the biggest issue.

I create 2 odbc connections to a postgres database that has tables with boolean fields
connection 1 uses an ODBC file DSN with: PAGE 1: bools as char is checked; page 2: true is -1 is unchecked
connection 2 uses an ODBC file DSN with: PAGE 1: bools as char is UNchecked; page 2: true is -1 is checked

My access database then has 2 connections to the table in question. I sufix the connection 2 table name (in access) as: public_my_table_bolasneg1

The odbc connection using the connection 1 file DSN to this table can be used in queries with a WHERE condition of "0" or "1" to limit records based on the boolean field.
The odbc connection using the connection 2 file DSN to this table can be used to bind the MS Access boolean check box to this field and the user can use the check box natively to update this field.

Yes this is a kludge but it has worked for 20 years.

in your example below I would use the connection 1 table and the statement would be:

If SW_UE = "1" Then

   do something

end if

Thank you for your answer, but your suggestion is not affordable in my situation.

I'm not writing a new application: I'm reingeneering an existing application (eventually the first of a set of ten) and I merely can't browse the thousands of lines of vba code in order to change all the "If" statements.

Nor I can use the automatic "search and replace" function because the if statements can be of the type

If BoolField = BoolVar then
do something
end if

Nor I can change the type of all the boolean variables to Char.

And I simply don't want even to think about the nightmare to dig all the SQL statements, which often are built in VBA "on the fly" according to user's choices ...

Do you think I should evaluate another database, more "Access friendly" ?
Best regards

Fred Parkinson

Application Programmer

Association of Bay Area Governments

415-820-7931

>>> Adrian Klaver <adrian.klaver@aklaver.com> 11/3/2016 8:16 AM >>>

On 11/03/2016 08:11 AM, Vincenzo Turturro wrote:

> Good morning

>

> I write in order to obtain clarification (and possibly a solution !)

> about a problem I can't solve.

>

> First of all I will briefly describe the scenario.

>

> I have several applications written in VBA (Visual basic for

> Application) using Microsoft Access both as backend and frontend (data

> are stored in mdb files on a server and applications are stored in mde

> files on the pcs).

>

> I'm committed in reingeneering these applications in order to migrate

> data files to a PostgreSQL database, without changing the frontends

> except that for the connection to the data they manage.

>

> The idea is to connect each frontend to Postgres DB (version 9.5.4)

> using ODBC (version 9.5.4).

>

> In order to run some tests, I migrated data from an mdb file to PG

> tables, defined an ODBC connection and linked the Access frontend to the

> PG database using it.

>

> The problem raises when the application needs to deal with data stored

> in boolean columns.

>

> If SW_UE is the name of a boolean column in a table, when I use a

> statements like this

>

> If SW_UE = -1 Then

>

> Do something

>

> End If

>

> the following error raises:

>

> ERROR: Operator does not exist boolean = integer

>

> I already searched the Internet for a solution and I found some, but

> none solved the problem.

>

> For this reason I decided to write here, hoping in your help and/or some

> hint.

https://odbc.postgresql.org/docs/config.html

Advanced Options 2/2 Dialog Box

True is -1: Represent TRUE as -1 for compatibility with some applications.

While we are on bools:

Advanced Options 1/2 Dialog Box

Data Type Options: affects how some data types are mapped:

Bools as Char: Bools are mapped to SQL_CHAR, otherwise to SQL_BIT.

>

> I thank you in advance for any answer.

>

> Best regards

>

> ------------------------------------------------------------

> Vincenzo Turturro

>

> Regione Toscana

> Direzione generale Governo del territorio

> Sistema Informativo Territoriale ed Ambientale

>

> Via di Novoli 26

> 50127 Firenze

>

> Tel 055 43 83 855

> ------------------------------------------------------------

>

--

Adrian Klaver

adrian.klaver@aklaver.com

--

Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)

To make changes to your subscription:

http://www.postgresql.org/mailpref/pgsql-odbc

-- 
------------------------------------------------------------
Vincenzo Turturro

Regione Toscana
Direzione generale Governo del territorio
Sistema Informativo Territoriale ed Ambientale

Via di Novoli 26
50127 Firenze

Tel 055 43 83 855
------------------------------------------------------------

Il 03/11/2016 16:56, Adrian Klaver ha scritto:
> On 11/03/2016 08:39 AM, Vincenzo Turturro wrote:
>> Il 03/11/2016 16:16, Adrian Klaver ha scritto:
>>> On 11/03/2016 08:11 AM, Vincenzo Turturro wrote:
>>>> Good morning
>>>>
>>>> I write in order to obtain clarification (and possibly a solution !)
>>>> about a problem I can't solve.
>>>>
>>>> First of all I will briefly describe the scenario.
>>>>
>>>> I have several applications written in VBA (Visual basic for
>>>> Application) using Microsoft Access both as backend and frontend (data
>>>> are stored in mdb files on a server and applications are stored in mde
>>>> files on the pcs).
>>>>
>>>> I'm committed in reingeneering these applications in order to migrate
>>>> data files to a PostgreSQL database, without changing the frontends
>>>> except that for the connection to the data they manage.
>>>>
>>>> The idea is to connect each frontend to Postgres DB (version 9.5.4)
>>>> using ODBC (version 9.5.4).
>>>>
>>>> In order to run some tests, I migrated data from an mdb file to PG
>>>> tables, defined an ODBC connection and linked the Access frontend
>>>> to the
>>>> PG database using it.
>>>>
>>>> The problem raises when the application needs to deal with data stored
>>>> in boolean columns.
>>>>
>>>> If SW_UE is the name of a boolean column in a table, when I use a
>>>> statements like this
>>>>
>>>> If SW_UE = -1 Then
>>>>
>>>> Do something
>>>>
>>>> End If
>>>>
>>>> the following error raises:
>>>>
>>>> ERROR: Operator does not exist boolean = integer
>>>>
>>>> I already searched the Internet for a solution and I found some, but
>>>> none solved the problem.
>>>>
>>>> For this reason I decided to write here, hoping in your help and/or
>>>> some
>>>> hint.
>>>
>>> https://odbc.postgresql.org/docs/config.html
>>>
>>> Advanced Options 2/2 Dialog Box
>>>
>>> True is -1: Represent TRUE as -1 for compatibility with some
>>> applications.
>>>
>>> While we are on bools:
>>>
>>> Advanced Options 1/2 Dialog Box
>>>
>>> Data Type Options: affects how some data types are mapped:
>>>
>>> Bools as Char: Bools are mapped to SQL_CHAR, otherwise to SQL_BIT.
>>
>> Thank you for your answer.
>> I had already configured the ODBC driver as you suggested, long before
>> writing the mailing list.
>
> This was mentioned where in the original post?

I'm sorry: it was "included" in the "I already searched the Internet for
a solution and I found some" sentence ...

>> But it didn't work ... and the error continue to raise.
>
> What is raising?:
>
> ERROR: Operator does not exist boolean = integer

Yes

> Why not do?:
>
> If SW_UE = True

Because it raises the same error ...
In Access VBA, True is a constant that is equivalent to -1.

>> Best regards
>>
>>>>
>>>> I thank you in advance for any answer.
>>>>
>>>> Best regards
>>>>
>>>> ------------------------------------------------------------
>>>> Vincenzo Turturro
>>>>
>>>> Regione Toscana
>>>> Direzione generale Governo del territorio
>>>> Sistema Informativo Territoriale ed Ambientale
>>>>
>>>> Via di Novoli 26
>>>> 50127 Firenze
>>>>
>>>> Tel 055 43 83 855
>>>> ------------------------------------------------------------
>>>>
>>>
>>>
>>
>>
>
>


--
------------------------------------------------------------
Vincenzo Turturro

Regione Toscana
Direzione generale Governo del territorio
Sistema Informativo Territoriale ed Ambientale

Via di Novoli 26
50127 Firenze

Tel 055 43 83 855
------------------------------------------------------------

On 11/04/2016 04:56 AM, Vincenzo Turturro wrote:
> Il 03/11/2016 16:56, Adrian Klaver ha scritto:
>> On 11/03/2016 08:39 AM, Vincenzo Turturro wrote:
>>> Il 03/11/2016 16:16, Adrian Klaver ha scritto:
>>>> On 11/03/2016 08:11 AM, Vincenzo Turturro wrote:
>>>>> Good morning
>>>>>

>>
>> This was mentioned where in the original post?
>
> I'm sorry: it was "included" in the "I already searched the Internet for
> a solution and I found some" sentence ...
>
>>> But it didn't work ... and the error continue to raise.
>>
>> What is raising?:
>>
>> ERROR: Operator does not exist boolean = integer

So the above is coming from Postgres:

test[5432]=# select false = 1;
ERROR:  operator does not exist: boolean = integer

whereas:

test[5432]=# select false = '1';
  ?column?
----------
  f


>
> Yes
>
>> Why not do?:
>>
>> If SW_UE = True
>
> Because it raises the same error ...
> In Access VBA, True is a constant that is equivalent to -1.


I know I have an install that uses Access and Postgres and it works. I
remember dealing with this issue when I started out. Right now I cannot
remember exactly what I did to get it to work. I will be at that
location later this morning and I will check the ODBC settings and
report back.


In the meantime can you grab the actual return value of SW_UE?

>
>>> Best regards
>>>
>>>>>
>>>>> I thank you in advance for any answer.
>>>>>
>>>>> Best regards
>>>>>
>>>>> ------------------------------------------------------------
>>>>> Vincenzo Turturro
>>>>>
>>>>> Regione Toscana
>>>>> Direzione generale Governo del territorio
>>>>> Sistema Informativo Territoriale ed Ambientale
>>>>>
>>>>> Via di Novoli 26
>>>>> 50127 Firenze
>>>>>
>>>>> Tel 055 43 83 855
>>>>> ------------------------------------------------------------
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 11/04/2016 04:52 AM, Vincenzo Turturro wrote:
> Il 03/11/2016 16:53, Fred Parkinson ha scritto:
>> Been there done that.
>>

>
> I'm not writing a new application: I'm reingeneering an existing
> application (eventually the first of a set of ten) and I merely can't
> browse the thousands of lines of vba code in order to change all the
> "If" statements.
>
> Nor I can use the automatic "search and replace" function because the if
> statements can be of the type
>
> If BoolField = BoolVar then
> do something
> end if
>
> Nor I can change the type of all the boolean variables to Char.
>
> And I simply don't want even to think about the nightmare to dig all the
> SQL statements, which often are built in VBA "on the fly" according to
> user's choices ...
>
> Do you think I should evaluate another database, more "Access friendly" ?


So in the Access instance I have running successfully against Postgres
the ODBC Bools as Char is checked and True is -1 is not checked. One
thing I did run across, which I forgotten was the 32/64 bit problem.
The app is using the 32 bit ODBC driver whereas the default ODBC manager
in Control Panel is the 64 bit one. To get at the correct one I had to run:

https://odbc.postgresql.org/faq.html#6.8

%SystemRoot%\syswow64\odbcad32.exe

Could that be an issue in your case?

In your original post you say SW_UE is boolean column in the database.
Just to be sure, it is defined as boolean in the Postgres table?

The more I thought about it the more I was sure that:

If SW_UE = -1 Then

is not the problem. That comparison is being done in VBA(Access) and I
would think would raise a VBA error not the Postgres one you show. That
leads me to believe the issue is in:

Do something

End If

in particular the Do something part. That what is being returned to
Postgres is the problem. So what is being returned?


--
Adrian Klaver
adrian.klaver@aklaver.com

From: pgsql-odbc-owner@postgresql.org
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Fred Parkinson
> One thing I now remember from when this issue first appeared is that there
> was another possible solution.
> When trying to use FALSE or TRUE in an Access query that uses a postgres
> boolean, I would get the error:
> ODBC call failed: ERROR: operator does not exist: boolean = integer;...
>
> Apparently you can write an operator and add it to the postgres server,
> an operator that will perform the comparison between a boolean and an
> integer.
> The intern I was working with at the time tried once to write that operator,
> but when his attempt failed at the first try, we went with the kludge because
> we were in a hurry and didn't want to spend the time debugging his code.
>
> I am not sure but I think the operator was written in PHP. In any case,
> that might be a fruitful line of inquery given that you can't rewrite a
> major application to use the kludge I describe below.

A dirty hack is to run the following UPDATE only once on the target database.  You can do this from any program such as
psql.

UPDATE pg_cast
SET castcontext = 'I'
WHERE castsource = 'int'::regtype AND
    casttarget = 'boolean'::regtype;

This will make the type cast from integer to Boolean implicit, enabling the comparison like "WHERE 1 = true".

But implicit casts might cause unexpected trouble.  If you find something wrong, revert the castcontext to 'e'.

Regards
Takayuki Tsunakawa

Hi Vincenzo, Fred,

Could you please try the drivers 9.5.0406 at
    http://www.ne.jp/asahi/inocchichichi/entrance/psqlodbc/
?

regards,
Hiroshi Inoue

One thing I now remember from when this issue first appeared is that there was another possible solution.

When trying to use FALSE or TRUE in an Access query that uses a postgres boolean, I would get the error:

ODBC call failed: ERROR: operator does not exist: boolean = integer;...

Apparently you can write an operator and add it to the postgres server, an operator that will perform the comparison between a boolean and an integer.

The intern I was working with at the time tried once to write that operator, but when his attempt failed at the first try, we went with the kludge because we were in a hurry and didn't want to spend the time debugging his code.

I am not sure but I think the operator was written in PHP. In any case, that might be a fruitful line of inquery given that you can't rewrite a major application to use the kludge I describe below.

Hope this helps!

Fred Parkinson

Application Programmer

Association of Bay Area Governments

415-820-7931

>>> Vincenzo Turturro <vincenzo.turturro@regione.toscana.it> 11/4/2016 4:52 AM >>>

>

Il 03/11/2016 16:53, Fred Parkinson ha scritto:

Been there done that.

I have been using Access with PostgreSQL through ODBC for about 20 years, and boolean is essentially the biggest issue.

I create 2 odbc connections to a postgres database that has tables with boolean fields
connection 1 uses an ODBC file DSN with: PAGE 1: bools as char is checked; page 2: true is -1 is unchecked
connection 2 uses an ODBC file DSN with: PAGE 1: bools as char is UNchecked; page 2: true is -1 is checked

My access database then has 2 connections to the table in question. I sufix the connection 2 table name (in access) as: public_my_table_bolasneg1

The odbc connection using the connection 1 file DSN to this table can be used in queries with a WHERE condition of "0" or "1" to limit records based on the boolean field.
The odbc connection using the connection 2 file DSN to this table can be used to bind the MS Access boolean check box to this field and the user can use the check box natively to update this field.

Yes this is a kludge but it has worked for 20 years.

in your example below I would use the connection 1 table and the statement would be:

If SW_UE = "1" Then

do something

end if

Thank you for your answer, but your suggestion is not affordable in my situation.

I'm not writing a new application: I'm reingeneering an existing application (eventually the first of a set of ten) and I merely can't browse the thousands of lines of vba code in order to change all the "If" statements.

Nor I can use the automatic "search and replace" function because the if statements can be of the type

If BoolField = BoolVar then

do something

end if

Nor I can change the type of all the boolean variables to Char.

And I simply don't want even to think about the nightmare to dig all the SQL statements, which often are built in VBA "on the fly" according to user's choices ...

Do you think I should evaluate another database, more "Access friendly" ?

Best regards

Il 08/11/2016 01:58, Tsunakawa, Takayuki ha scritto:
> From: pgsql-odbc-owner@postgresql.org
>> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Fred Parkinson
>> One thing I now remember from when this issue first appeared is that there
>> was another possible solution.
>> When trying to use FALSE or TRUE in an Access query that uses a postgres
>> boolean, I would get the error:
>> ODBC call failed: ERROR: operator does not exist: boolean = integer;...
>>
>> Apparently you can write an operator and add it to the postgres server,
>> an operator that will perform the comparison between a boolean and an
>> integer.
>> The intern I was working with at the time tried once to write that operator,
>> but when his attempt failed at the first try, we went with the kludge because
>> we were in a hurry and didn't want to spend the time debugging his code.
>>
>> I am not sure but I think the operator was written in PHP. In any case,
>> that might be a fruitful line of inquery given that you can't rewrite a
>> major application to use the kludge I describe below.
> A dirty hack is to run the following UPDATE only once on the target database.  You can do this from any program such
aspsql. 
>
> UPDATE pg_cast
> SET castcontext = 'I'
>
> WHERE castsource = 'int'::regtype AND
>      casttarget = 'boolean'::regtype;
>
> This will make the type cast from integer to Boolean implicit, enabling the comparison like "WHERE 1 = true".
>
> But implicit casts might cause unexpected trouble.  If you find something wrong, revert the castcontext to 'e'.
>
> Regards
> Takayuki Tsunakawa

Arigato !

Thank you very much for your answer.
I tried your suggestion and it worked !

But, in order to have the issue solved, I had to slightly change the SQL
statement you provided:

SET castcontext = 'i' (lowercase)
instead of
SET castcontext = 'I' (uppercase)

Thank you again !

Kon'nichiwa (in Italy is late morning ...)

------------------------------------------------------------
Vincenzo Turturro

Regione Toscana
Direzione generale Governo del territorio
Sistema Informativo Territoriale ed Ambientale

Via di Novoli 26
50127 Firenze

Tel 055 43 83 855
------------------------------------------------------------

Le 7/11/2016 à 17:02, Fred Parkinson a écrit :
> One thing I now remember from when this issue first appeared is that
> there was another possible solution.
> When trying to use FALSE or TRUE in an Access query that uses a postgres
> boolean, I would get the error:
> ODBC call failed: ERROR: operator does not exist: boolean = integer;...
>
> Apparently you can write an operator and add it to the postgres server,
> an operator that will perform the comparison between a boolean and an
> integer.

Hi, sorry for joining the discussion so late.
I've been using the solution from this post for a long time and never
had any problem :
http://www.postgresonline.com/journal/index.php?url=archives/24-Using-MS-Access-with-PostgreSQL.html
See the section "dealing with boolean".
I like the fact that function is written in pure SQL.

Cheers

--
Arnaud

Il 10/11/2016 09:42, Arnaud L. ha scritto:
> Le 7/11/2016 à 17:02, Fred Parkinson a écrit :
>> One thing I now remember from when this issue first appeared is that
>> there was another possible solution.
>> When trying to use FALSE or TRUE in an Access query that uses a postgres
>> boolean, I would get the error:
>> ODBC call failed: ERROR: operator does not exist: boolean = integer;...
>>
>> Apparently you can write an operator and add it to the postgres server,
>> an operator that will perform the comparison between a boolean and an
>> integer.
>
> Hi, sorry for joining the discussion so late.
> I've been using the solution from this post for a long time and never
> had any problem :
> http://www.postgresonline.com/journal/index.php?url=archives/24-Using-MS-Access-with-PostgreSQL.html
>
> See the section "dealing with boolean".
> I like the fact that function is written in pure SQL.
>
> Cheers
>
> --
> Arnaud

Thank you for your answer.

I have tried the solution you suggest, but without success.

I used the SQL statements provided in the post, but the problem wasn't
solved.

Maybe I missed something that is not mentioned in the post because it's
obvious for PG users ?

Keep in mind I'm a newby in PG !

Best regards

------------------------------------------------------------
Vincenzo Turturro

Regione Toscana
Direzione generale Governo del territorio
Sistema Informativo Territoriale ed Ambientale

Via di Novoli 26
50127 Firenze

Tel 055 43 83 855
------------------------------------------------------------

Le 10/11/2016 à 10:46, Vincenzo Turturro a écrit :
> Il 10/11/2016 09:42, Arnaud L. ha scritto:
>> I've been using the solution from this post for a long time and never
>> had any problem :
>> http://www.postgresonline.com/journal/index.php?url=archives/24-Using-MS-Access-with-PostgreSQL.html
>>
>> See the section "dealing with boolean".
>> I like the fact that function is written in pure SQL.
>>
>
> I have tried the solution you suggest, but without success.
> I used the SQL statements provided in the post, but the problem wasn't
> solved.
> Maybe I missed something that is not mentioned in the post because it's
> obvious for PG users ?
> Keep in mind I'm a newby in PG !

I'm no PG guru either, so the only "obvious" thing I can think of is
that the operators and functions you are creating are not in your search
path when you connect through ODBC.

So I guess the best for you would be to create them in the public
schema, and to check in your ODBC connection that the public schema is
in your search path. (a simple "show search_path;" query)

Then through ODBC, what do you get when you try to directly call
inttobool() or notinttobool ? Do these functions exist and work as
expected ?
i.e. select inttobool(-1,true);
And what about a direct call to the operators ?
i.e. what do you have when you call
select 1::int4 <> false::boolean ;
and what about the schema-qualified version ?
select 1::int4 operator(public.<>) false::boolean;


Regards

--
Arnaud