Re: Quoting issue from ODBC

On Tue, Feb 7, 2023 at 10:20 PM Brad White <b55white(at)gmail(dot)com> wrote:

> 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.
>
> Back in the office today and I note that all of the fields that are
getting the issue are the target field in an UPDATE statement.
All the other tables and field names are quoted correctly.

I suspect an ODBC driver bug. Is there a better place to report those?

Driver: PostgreSQL Unicode
Filename: PSQLODBC35W.DLL
Version: 13.02.00
ReleaseDate: 9/22/2021

On the other hand, the app updates things all the time. Only about 12 of
the update statements are ending up in the log. Still looking for the
common denominator in how those statements are called.

ERROR: column "*commitrep*" of relation "Order Items" does not exist at
character 35
STATEMENT: UPDATE "public"."Order Items" SET *CommitRep*='jdoe' WHERE
(("OrderFID" = 557837 ) AND ("*CommitRep*" IS NULL ) )

Here is the original query. You can see that [CommitRep] is written the
same way both times in the query, but in the final quoted correctly once
and incorrectly once.
RunADO "CreditRepWithCommit()", "UPDATE [Order Items] SET *[CommitRep]*
= '" & UID & "'" _
& " WHERE [OrderFID] = " & OrderFID & " AND * [CommitRep]* IS NULL;"

A few other samples from the log. Always just the target field of the
UPDATE that is not quoted.

ERROR: column "*availableserialcount*" of relation "Serial Pools" does not
exist at character 36
STATEMENT: UPDATE "public"."Serial Pools" SET *AvailableSerialCount*=143
WHERE ("ID" = 134 )

ERROR: column "*serialnum*" of relation "Order Items" does not exist at
character 35
STATEMENT: UPDATE "public"."Order Items" SET *SerialNum*='205757'
,LastSerCaptureTypeID=2 WHERE ("ID" = 1891128 )

ERROR: column "*issuedate*" of relation "Order Items" does not exist at
character 35
STATEMENT: UPDATE "public"."Order Items" SET *IssueDate*='2023-02-09
14:28:09'::timestamp ,*IssueRep*=' jdoe ' WHERE ("ID" = 1891128 )