From: Brad White <b55white@gmail.com>
To: Adrian Klaver <adrian.klaver@aklaver.com>
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Quoting issue from ODBC
Date: Tue, 7 Feb 2023 22:20:58 -0600
Message-ID: <89465c0e-c683-1563-7e16-12688f77bdc3@gmail.com> (raw)
In-Reply-To: <05d8145a-fd79-1062-ee27-5a87bfd59b0e@aklaver.com>
References: <CAA_1=93z4oEXKQ9GHtDkEQhEe5r39C8YSDMpwO8OHmSBreXw3g@mail.gmail.com>
<05d8145a-fd79-1062-ee27-5a87bfd59b0e@aklaver.com>
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.
>
--
Quote Signature I talk with clients, find out where their pain points
are, and solve those.
On-call IT Management for small companies and non-profits.
SCP, Inc.
bwhite@inebraska.com
402-601-7990
Quote of the Day
There is a huge difference between fathering a child and being a
father.
One produces a child. The other produces an adult.
-- John Eldredge
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: b55white@gmail.com, adrian.klaver@aklaver.com
Subject: Re: Quoting issue from ODBC
In-Reply-To: <89465c0e-c683-1563-7e16-12688f77bdc3@gmail.com>
References: <CAA_1=93z4oEXKQ9GHtDkEQhEe5r39C8YSDMpwO8OHmSBreXw3g@mail.gmail.com> <05d8145a-fd79-1062-ee27-5a87bfd59b0e@aklaver.com> <89465c0e-c683-1563-7e16-12688f77bdc3@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox