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: Tue, 7 Feb 2023 18:19:56 -0600
Message-ID: 
 <CAA_1=91rYvL1Z3LetskQJKvxZwhiyxuU0KEG8giQLg4B=VbSUA@mail.gmail.com>
Subject: Fwd: Quoting issue from ODBC
To: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000fcadf805f425397d"
Archived-At: 
 <https://www.postgresql.org/message-id/CAA_1%3D91rYvL1Z3LetskQJKvxZwhiyxuU0KEG8giQLg4B%3DVbSUA%40mail.gmail.com>
Precedence: bulk

--000000000000fcadf805f425397d
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, 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.

No ORM involved.

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.

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

<div dir=3D"ltr"><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail=
_attr">Front end: Access 365<br></div><div dir=3D"ltr"><div>Back end: Postg=
res 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 f=
ailing.</div><div>Looking at the postgres log shows that the field names an=
d table names are not being quoted properly.</div><div>It has been my exper=
ience that Access usually does a better job at converting the queries than =
I would have expected, but not in this instance.</div><div><br></div><div>F=
or example, this is the literal code in VBA</div><div><br></div><div>Access=
:=C2=A0connection.Execute &quot;UPDATE [&quot; &amp; strTable &amp; &quot;]=
 SET [&quot; &amp; strTable &amp; &quot;].[InsertFlag] =3D Null&quot; _</di=
v>=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=A0UPDATE &quot;public&quot;.&quot;O=
rders&quot; SET InsertFlag=3DNULL =C2=A0WHERE (&quot;InsertFlag&quot; =3D 1=
66 )</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 =
relation &quot;Orders&quot; does not exist at character 35.</div><div><br><=
/div><div>No ORM involved.</div><div><br></div>Looks like I have=C2=A0about=
 16 unique instances of statements not being quoted correctly resulting=C2=
=A0in over 500 errors in the log for today.<div><br><div class=3D"gmail_quo=
te"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bor=
der-left:1px solid rgb(204,204,204);padding-left:1ex"></blockquote></div><d=
iv>Any suggestions on where to look?</div><div><br></div><div>Thanks,<br>Br=
ad.</div></div></div>
</div></div>

--000000000000fcadf805f425397d--