Content-Type: multipart/alternative;
 boundary="------------3fTM68ovb0XfURdUAsT5Zg01"
Message-ID: <89465c0e-c683-1563-7e16-12688f77bdc3@gmail.com>
Date: Tue, 7 Feb 2023 22:20:58 -0600
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:102.0) Gecko/20100101
 Thunderbird/102.0.2
Subject: Re: Quoting issue from ODBC
Content-Language: en-US
To: Adrian Klaver <adrian.klaver@aklaver.com>,
 "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: 
 <CAA_1=93z4oEXKQ9GHtDkEQhEe5r39C8YSDMpwO8OHmSBreXw3g@mail.gmail.com>
 <05d8145a-fd79-1062-ee27-5a87bfd59b0e@aklaver.com>
From: Brad White <b55white@gmail.com>
In-Reply-To: <05d8145a-fd79-1062-ee27-5a87bfd59b0e@aklaver.com>
Archived-At: 
 <https://www.postgresql.org/message-id/89465c0e-c683-1563-7e16-12688f77bdc3%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------3fTM68ovb0XfURdUAsT5Zg01
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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
--------------3fTM68ovb0XfURdUAsT5Zg01
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 2/7/2023 6:19 PM, Adrian Klaver
      wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:05d8145a-fd79-1062-ee27-5a87bfd59b0e@aklaver.com">On
      2/7/23 16:10, Brad White wrote:
      <br>
      <blockquote type="cite">Front end: Access 365
        <br>
        Back end: Postgres 9.4
        <br>
        (I know, we are in the process of upgrading)
        <br>
        <br>
        I'm getting some cases where the SQL sent from MS-Access is
        failing.
        <br>
        Looking at the postgres log shows that the field names and table
        names are not being quoted properly.
        <br>
        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.
        <br>
        <br>
        For example
        <br>
        <br>
        Access: connection.Execute "UPDATE [" &amp; strTable &amp; "]
        SET [" &amp; strTable &amp; "].[InsertFlag] = Null" _
        <br>
             &amp; " WHERE ((([" &amp; strTable &amp; "].[InsertFlag])="
        &amp; lngCurrUID &amp; "));", , adCmdText Or adExecuteNoRecords
        <br>
        Note that InsertFlag is bracketed the same way in both
        instances.
        <br>
        <br>
        PSQL: UPDATE "public"."Orders" SET InsertFlag=NULL  WHERE
        ("InsertFlag" = 166 )
        <br>
        Note that InsertFlag is quoted once but not the other time.
        <br>
        Of course this gives the error: column "insertflag" of relation
        "Orders" does not exist at character 35.
        <br>
        <br>
        Looks like I have about 16 unique instances of statements not
        being quoted correctly resulting in over 500 errors in the log
        for today.
        <br>
      </blockquote>
      <br>
      Where these preexisting queries or where they created today?
      <br>
    </blockquote>
    <p>These queries are decades old but I don't view this log file very
      often, so I don't know how long.</p>
    <p>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.<br>
    </p>
    <blockquote type="cite"
      cite="mid:05d8145a-fd79-1062-ee27-5a87bfd59b0e@aklaver.com">
      <br>
      <blockquote type="cite">
        <br>
        Any suggestions on where to look?
        <br>
        <br>
        Thanks,
        <br>
        Brad.
        <br>
      </blockquote>
      <br>
    </blockquote>
    <div class="moz-signature">-- <br>
      <title>Quote Signature</title>
      <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
      <meta content="MSHTML 6.00.6000.16735" name="GENERATOR">
      <font size="2" face="Calibri" color="#606060">
        I talk with clients, find out where their pain points are, and
        solve those.<br>
        On-call IT Management for small companies and non-profits.<br>
        SCP, Inc.<br>
        <a class="moz-txt-link-abbreviated" href="mailto:bwhite@inebraska.com">bwhite@inebraska.com</a><br>
        402-601-7990<br>
        <br>
        <br>
      </font><font face="size=3">
        Quote of the Day<br>
        <div align="left">   There is a huge difference between
          fathering a child and being a father. </div>
        <div align="left">   One produces a child. The other produces an
          adult. </div>
        <div align="left">    -- John Eldredge</div>
      </font>
    </div>
  </body>
</html>

--------------3fTM68ovb0XfURdUAsT5Zg01--