Re: Quoting issue from ODBC

On 2/9/23 14:43, Brad White wrote:
> On Tue, Feb 7, 2023 at 10:20 PM Brad White <b55white(at)gmail(dot)com
> <mailto:b55white(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com