public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: Brad White <[email protected]>
To: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: Quoting issue from ODBC
Date: Thu, 9 Feb 2023 15:10:04 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAA_1=93_rUYmMgY6vU2svik6G107soojb4J0xzoYa1TibpP_3Q@mail.gmail.com>
References: <CAA_1=93z4oEXKQ9GHtDkEQhEe5r39C8YSDMpwO8OHmSBreXw3g@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAA_1=93_rUYmMgY6vU2svik6G107soojb4J0xzoYa1TibpP_3Q@mail.gmail.com>
On 2/9/23 14:43, Brad White wrote:
> On Tue, Feb 7, 2023 at 10:20 PM Brad White <[email protected]
> <mailto:[email protected]>> 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
[email protected]
view thread (14+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Quoting issue from ODBC
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox