Fwd: Quoting issue from ODBC

From: Brad White <b55white(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Fwd: Quoting issue from ODBC
Date: 2023-02-08 00:19:56
Message-ID: CAA_1=91rYvL1Z3LetskQJKvxZwhiyxuU0KEG8giQLg4B=VbSUA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-odbc

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.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2023-02-08 00:23:49 Re: Quoting issue from ODBC
Previous Message Justin 2023-02-08 00:19:55 Re: Need help debugging slow logical replication

Browse pgsql-odbc by date

  From Date Subject
Next Message David G. Johnston 2023-02-08 00:23:49 Re: Quoting issue from ODBC
Previous Message Adrian Klaver 2023-02-08 00:19:08 Re: Quoting issue from ODBC