Re: Quoting issue from ODBC - Mailing list pgsql-general

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?

Are these UPDATE's actually necessary?

--
Adrian Klaver
adrian.klaver@aklaver.com