Postgres Pro
Downloads
Home   >   mailing lists

Re: Problem in using PostgreSQL ODBC driver with VBA - Mailing list pgsql-odbc

From Fred Parkinson
Subject Re: Problem in using PostgreSQL ODBC driver with VBA
Date
Msg-id 58203522020000A70003717F@gwia1.abag.ca.gov
Whole thread Raw
In response to Re: Problem in using PostgreSQL ODBC driver with VBA  (Vincenzo Turturro <vincenzo.turturro@regione.toscana.it>)
Responses Re: Problem in using PostgreSQL ODBC driver with VBA  ("Tsunakawa, Takayuki" <tsunakawa.takay@jp.fujitsu.com>)
Re: Problem in using PostgreSQL ODBC driver with VBA  ("Inoue, Hiroshi" <h-inoue@dream.email.ne.jp>)
Re: Problem in using PostgreSQL ODBC driver with VBA  ("Arnaud L." <arnaud.listes@codata.eu>)
List pgsql-odbc
Tree view
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
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


--
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
------------------------------------------------------------

pgsql-odbc by date:

Previous
From: "Tsunakawa, Takayuki"
Date:
Subject: Re: [RFC] Changing the default of UseDeclareFetch and Protocol (statement -> transaction rollback)
Next
From: "Tsunakawa, Takayuki"
Date:
Subject: Re: Problem in using PostgreSQL ODBC driver with VBA