MIME-Version: 1.0
References: 
 <CAA_1=93z4oEXKQ9GHtDkEQhEe5r39C8YSDMpwO8OHmSBreXw3g@mail.gmail.com>
In-Reply-To: 
 <CAA_1=93z4oEXKQ9GHtDkEQhEe5r39C8YSDMpwO8OHmSBreXw3g@mail.gmail.com>
From: Brad White <b55white@gmail.com>
Date: Mon, 13 Feb 2023 11:11:05 -0600
Message-ID: 
 <CAA_1=93AMh3OOoU-TCZ3xOyVYywUaSb67LFNDRMbEa9OikKZGQ@mail.gmail.com>
Subject: Fwd: Quoting issue from ODBC
To: pgsql-odbc@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000005ac1ce05f497ef2d"
Archived-At: 
 <https://www.postgresql.org/message-id/CAA_1%3D93AMh3OOoU-TCZ3xOyVYywUaSb67LFNDRMbEa9OikKZGQ%40mail.gmail.com>
Precedence: bulk

--0000000000005ac1ce05f497ef2d
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)
ODBC Driver: PostgreSQL Unicode v13.02, PSQLODBC35W, 9/22/2021

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.
Only on UPDATE statements.
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 log: 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.

This is not a new issue. The logs have these errors stretching back months.

Any suggestions on where to look?

Thanks,
Brad.

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

<div dir=3D"ltr"><div class=3D"gmail_quote"><br><div dir=3D"ltr"><div>Front=
 end: Access 365<br>Back end: Postgres 9.4<br>(I know, we are in the proces=
s of upgrading)<br></div><div>ODBC Driver: PostgreSQL Unicode v13.02, PSQLO=
DBC35W, 9/22/2021</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 properl=
y.</div><div>Only on UPDATE statements.</div><div>It has been my experience=
 that Access usually does a better job at converting the queries than I wou=
ld have expected, but not in this instance.</div><div><br></div><div>For ex=
ample</div><div><br></div><div>Access:=C2=A0connection.Execute &quot;UPDATE=
 [&quot; &amp; strTable &amp; &quot;] SET [&quot; &amp; strTable &amp; &quo=
t;]<b>.[InsertFlag]</b> =3D Null&quot; _</div>=C2=A0 =C2=A0 &amp; &quot; WH=
ERE ((([&quot; &amp; strTable &amp; &quot;].<b>[InsertFlag]</b>)=3D&quot; &=
amp; lngCurrUID &amp; &quot;));&quot;, , adCmdText Or adExecuteNoRecords<di=
v>Note that InsertFlag is bracketed the same way in both instances.</div><b=
r><div>PSQL log:=C2=A0UPDATE &quot;public&quot;.&quot;Orders&quot; SET <b>I=
nsertFlag</b>=3DNULL =C2=A0WHERE (<b>&quot;InsertFlag&quot;</b> =3D 166 )</=
div><div>Note that InsertFlag is quoted once but not the other time.</div><=
div>Of course this gives the error: column &quot;insertflag&quot; of relati=
on &quot;Orders&quot; does not exist at character 35.</div><div><br></div>L=
ooks like I have=C2=A0about 16 unique instances of statements not being quo=
ted correctly resulting=C2=A0in over 500 errors in the log for today.</div>=
<div dir=3D"ltr"><br></div><div dir=3D"ltr">This is not a new issue. The lo=
gs have these errors stretching back months.<br><div><br><div class=3D"gmai=
l_quote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8e=
x;border-left:1px solid rgb(204,204,204);padding-left:1ex"></blockquote></d=
iv><div>Any suggestions on where to look?</div><div><br></div><div>Thanks,<=
br>Brad.</div></div></div>
</div></div>

--0000000000005ac1ce05f497ef2d--