Content-Type: multipart/alternative;
 boundary="------------HdBeLDLTqrb1VNeaYJUjyTme"
Message-ID: <2d478ecb-9f4a-6a3a-acde-4a77d47baabf@gmail.com>
Date: Tue, 7 Feb 2023 17:32:52 -0700
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.4.2
Subject: Re: Quoting issue from ODBC
Content-Language: en-CA
To: pgsql-general@lists.postgresql.org
References: 
 <CAA_1=93z4oEXKQ9GHtDkEQhEe5r39C8YSDMpwO8OHmSBreXw3g@mail.gmail.com>
 <CAA_1=91rYvL1Z3LetskQJKvxZwhiyxuU0KEG8giQLg4B=VbSUA@mail.gmail.com>
 <CAKFQuwZv0ZtOa+NiZP1E+C-B+EPQCieZ-UJtcReXWUETrOsqSA@mail.gmail.com>
From: Rob Sargent <robjsargent@gmail.com>
In-Reply-To: 
 <CAKFQuwZv0ZtOa+NiZP1E+C-B+EPQCieZ-UJtcReXWUETrOsqSA@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/2d478ecb-9f4a-6a3a-acde-4a77d47baabf%40gmail.com>
Precedence: bulk

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

On 2/7/23 17:23, David G. Johnston wrote:
> On Tue, Feb 7, 2023 at 5:20 PM Brad White <b55white@gmail.com> wrote:
>
>     For example, this is the literal code in VBA
>
>     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.
>
>
> Either VBA is doing some query munging or that concatenated string 
> isn't the one that is producing the error.  Which suggests that maybe 
> you have a string somewhere that is not written correctly that need to 
> be fixed so it doesn't produce this error.
>
> David J.
>
Should the SET clause have a table alias at all?  I get an error with

    update foo set foo.id = something;


--------------HdBeLDLTqrb1VNeaYJUjyTme
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/23 17:23, David G. Johnston
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAKFQuwZv0ZtOa+NiZP1E+C-B+EPQCieZ-UJtcReXWUETrOsqSA@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">
        <div dir="ltr">
          <div class="gmail_default"
            style="font-family:arial,helvetica,sans-serif"><span
              style="font-family:Arial,Helvetica,sans-serif">On Tue, Feb
              7, 2023 at 5:20 PM Brad White &lt;<a
                href="mailto:b55white@gmail.com" moz-do-not-send="true"
                class="moz-txt-link-freetext">b55white@gmail.com</a>&gt;
              wrote:</span></div>
        </div>
        <div class="gmail_quote">
          <blockquote class="gmail_quote" style="margin:0px 0px 0px
            0.8ex;border-left:1px solid
            rgb(204,204,204);padding-left:1ex">
            <div dir="ltr">
              <div class="gmail_quote">
                <div dir="ltr">
                  <div>For example, this is the literal code in VBA</div>
                  <div><br>
                  </div>
                  <div>Access: connection.Execute "UPDATE [" &amp;
                    strTable &amp; "] SET [" &amp; strTable &amp;
                    "].[InsertFlag] = Null" _</div>
                      &amp; " WHERE ((([" &amp; strTable &amp;
                  "].[InsertFlag])=" &amp; lngCurrUID &amp; "));", ,
                  adCmdText Or adExecuteNoRecords
                  <div>Note that InsertFlag is bracketed the same way in
                    both instances.</div>
                  <br>
                  <div>PSQL: UPDATE "public"."Orders" SET
                    InsertFlag=NULL  WHERE ("InsertFlag" = 166 )</div>
                  <div>Note that InsertFlag is quoted once but not the
                    other time.</div>
                  <div>Of course this gives the error: column
                    "insertflag" of relation "Orders" does not exist at
                    character 35.</div>
                  <div><br>
                  </div>
                </div>
              </div>
            </div>
          </blockquote>
          <div><br>
          </div>
          <div class="gmail_default"
            style="font-family:arial,helvetica,sans-serif">Either VBA is
            doing some query munging or that concatenated string isn't
            the one that is producing the error.  Which suggests that
            maybe you have a string somewhere that is not written
            correctly that need to be fixed so it doesn't produce this
            error.</div>
          <div class="gmail_default"
            style="font-family:arial,helvetica,sans-serif"><br>
          </div>
          <div class="gmail_default"
            style="font-family:arial,helvetica,sans-serif">David J.</div>
          <div class="gmail_default"
            style="font-family:arial,helvetica,sans-serif"><br>
          </div>
        </div>
      </div>
    </blockquote>
    <font face="Courier New, Courier, monospace">Should the SET clause
      have a table alias at all?  I get an error with <br>
    </font>
    <blockquote><font face="Courier New, Courier, monospace">update foo
        set foo.id = something;<br>
      </font></blockquote>
    <br>
    <br>
    <blockquote><br>
    </blockquote>
  </body>
</html>

--------------HdBeLDLTqrb1VNeaYJUjyTme--