MIME-Version: 1.0
From: Brad White <b55white@gmail.com>
Date: Tue, 7 Feb 2023 18:10:44 -0600
Message-ID: 
 <CAA_1=93z4oEXKQ9GHtDkEQhEe5r39C8YSDMpwO8OHmSBreXw3g@mail.gmail.com>
Subject: Quoting issue from ODBC
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000019685405f42519a8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAA_1%3D93z4oEXKQ9GHtDkEQhEe5r39C8YSDMpwO8OHmSBreXw3g%40mail.gmail.com>
Precedence: bulk

--00000000000019685405f42519a8
Content-Type: text/plain; charset="UTF-8"

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.

Any suggestions on where to look?

Thanks,
Brad.

--00000000000019685405f42519a8
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Front end: Access 365<br>Back end: Postgres 9.4<br>(I=
 know, we are in the process of upgrading)<br></div><div><br></div><div>I&#=
39;m getting some cases where the SQL sent from MS-Access is failing.</div>=
<div>Looking at the postgres log shows that the field names and table names=
 are not being quoted properly.</div><div>It has been my experience that Ac=
cess usually does a better job at converting the queries than I would have =
expected, but not in this instance.</div><div><br></div><div>For example</d=
iv><div><br></div><div>Access:=C2=A0connection.Execute &quot;UPDATE [&quot;=
 &amp; strTable &amp; &quot;] SET [&quot; &amp; strTable &amp; &quot;].[Ins=
ertFlag] =3D Null&quot; _</div>=C2=A0 =C2=A0 &amp; &quot; WHERE ((([&quot; =
&amp; strTable &amp; &quot;].[InsertFlag])=3D&quot; &amp; lngCurrUID &amp; =
&quot;));&quot;, , adCmdText Or adExecuteNoRecords<div>Note that InsertFlag=
 is bracketed the same way in both instances.</div><br><div>PSQL:=C2=A0UPDA=
TE &quot;public&quot;.&quot;Orders&quot; SET InsertFlag=3DNULL =C2=A0WHERE =
(&quot;InsertFlag&quot; =3D 166 )</div><div>Note that InsertFlag is quoted =
once but not the other time.</div><div>Of course this gives the error: colu=
mn &quot;insertflag&quot; of relation &quot;Orders&quot; does not exist at =
character 35.</div><div><br></div>Looks like I have=C2=A0about 16 unique in=
stances of statements not being quoted correctly resulting=C2=A0in over 500=
 errors in the log for today.<div><br><div class=3D"gmail_quote"><blockquot=
e class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px s=
olid rgb(204,204,204);padding-left:1ex"></blockquote></div><div>Any suggest=
ions on where to look?</div><div><br></div><div>Thanks,<br>Brad.</div></div=
></div>

--00000000000019685405f42519a8--