Feedback-ID: i76984098:Fastmail
Message-ID: <abd8e837-3c67-6c74-8ec9-39b9302eb95b@aklaver.com>
Date: Thu, 9 Feb 2023 15:10:04 -0800
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.6.1
Subject: Re: Quoting issue from ODBC
To: Brad White <b55white@gmail.com>,
 "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: 
 <CAA_1=93z4oEXKQ9GHtDkEQhEe5r39C8YSDMpwO8OHmSBreXw3g@mail.gmail.com>
 <05d8145a-fd79-1062-ee27-5a87bfd59b0e@aklaver.com>
 <89465c0e-c683-1563-7e16-12688f77bdc3@gmail.com>
 <CAA_1=93_rUYmMgY6vU2svik6G107soojb4J0xzoYa1TibpP_3Q@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: 
 <CAA_1=93_rUYmMgY6vU2svik6G107soojb4J0xzoYa1TibpP_3Q@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/abd8e837-3c67-6c74-8ec9-39b9302eb95b%40aklaver.com>
Precedence: bulk

On 2/9/23 14:43, Brad White wrote:
> On Tue, Feb 7, 2023 at 10:20 PM Brad White <b55white@gmail.com 
> <mailto:b55white@gmail.com>> wrote:
> 
>     On 2/7/2023 6:19 PM, Adrian Klaver wrote:
>>     On 2/7/23 16:10, Brad White wrote:
>>>     Front end: Access 365
>>>     Back end: Postgres 9.4
>>>     (I know, we are in the process of upgrading)
>>>
>>>     I'm getting some cases where the SQL sent from MS-Access is failing.
>>>     Looking at the postgres log shows that the field names and table
>>>     names are not being quoted properly.
>>>     It has been my experience that Access usually does a better job
>>>     at converting the queries than I would have expected, but not in
>>>     this instance.
>>>
>>>     For example
>>>
>>>     Access: connection.Execute "UPDATE [" & strTable & "] SET [" &
>>>     strTable & "].[InsertFlag] = Null" _
>>>          & " WHERE ((([" & strTable & "].[InsertFlag])=" & lngCurrUID
>>>     & "));", , adCmdText Or adExecuteNoRecords
>>>     Note that InsertFlag is bracketed the same way in both instances.
>>>
>>>     PSQL: UPDATE "public"."Orders" SET InsertFlag=NULL  WHERE
>>>     ("InsertFlag" = 166 )
>>>     Note that InsertFlag is quoted once but not the other time.
>>>     Of course this gives the error: column "insertflag" of relation
>>>     "Orders" does not exist at character 35.
>>>
>>>     Looks like I have about 16 unique instances of statements not
>>>     being quoted correctly resulting in over 500 errors in the log
>>>     for today.
>>
>>     Where these preexisting queries or where they created today?
> 
>     These queries are decades old but I don't view this log file very
>     often, so I don't know how long.
> 
>     I'll review when I get back on site Thursday and see if I can find
>     any users that are not getting the error or when it started.
> 
>>
>>>
>>>     Any suggestions on where to look?
>>>
>>>     Thanks,
>>>     Brad.
> 
> Back in the office today and I note that all of the fields that are 
> getting the issue are the target field in an UPDATE statement.
> All the other tables and field names are quoted correctly.
> 
> I suspect an ODBC driver bug.  Is there a better place to report those?
> 
> Driver: PostgreSQL Unicode
> Filename: PSQLODBC35W.DLL
> Version: 13.02.00
> ReleaseDate: 9/22/2021

https://www.postgresql.org/list/pgsql-odbc/

> 
> On the other hand, the app updates things all the time. Only about 12 of 
> the update statements are ending up in the log. Still looking for the 
> common denominator in how those statements are called.


So how the successful UPDATE's called?

Are the successful UPDATES's on the same tables and columns?

 From your subsequent post:

"Going back to early 2020, I don't have any logs that don't have these 
errors, so it is not a recent change."

Are these UPDATE's actually necessary?

In other words has nobody noticed a problem with the data over that time 
frame?


-- 
Adrian Klaver
adrian.klaver@aklaver.com