Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pRcj0-0003Yw-8h for pgsql-odbc@arkaria.postgresql.org; Mon, 13 Feb 2023 17:34:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pRciz-0003ED-1r for pgsql-odbc@arkaria.postgresql.org; Mon, 13 Feb 2023 17:34:45 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pRciy-0003E1-Qt for pgsql-odbc@lists.postgresql.org; Mon, 13 Feb 2023 17:34:44 +0000 Received: from mail-yb1-xb2e.google.com ([2607:f8b0:4864:20::b2e]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pRciw-0004DV-L6 for pgsql-odbc@lists.postgresql.org; Mon, 13 Feb 2023 17:34:43 +0000 Received: by mail-yb1-xb2e.google.com with SMTP id e75so3820287ybh.13 for ; Mon, 13 Feb 2023 09:34:42 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=UEUKl6uZIsFIU8EKJZskwCG8oY5Zd8TAjN+es0XayWg=; b=VgYJv53mbUmhfqrRdukxSODaI+g1vd9jphod5TA1nWz7P/YWy9qdPVAhbdOTy2EXlD wlqKgZDqN/w4ChdB6xUw+D31FgXMxj0LKU6uADngRG2WjmZpBkN2LwLn1R7q57qTKYkI mjZA9dhtblTohWQH3RSYQOj+s4muRxdeFlzMCR7CqufgRJWzR5jkj2/Mf06WuUESoHaV qM0eS9fkfk6wBE43PnlC1ieQxbOXiGRAnfNaNX/66PBQL9zQ81IDJNeSn7JUBSCbijkX KB0mpPSykddHAFWbz+6bh4Emq46SscXcJMKDFyza8Ga5NJs3QxELcI6Fj07TOwOwIWYx EtJQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=UEUKl6uZIsFIU8EKJZskwCG8oY5Zd8TAjN+es0XayWg=; b=8LDzsLJMJYbQBmal6JIY0Wbo2volypqZtozZ5HOhgw9beK8FkZPEQIc/kvxMpVjzZ1 Nb1MQnJWgQNjIgcx/zhv+3m3/zx16KHOp5RfhpLtgIL5ZyVy2E5Dc/JVhX/uC9Dtq57E vajoBHQGLfVXDdem75n+ebfWCVDJnrC3LeVrYPxdXQZAoZnqa5kdBctlB7U4FnWXkjeR gyscaIEbH8pydSkQi5/xujAdMyuj+f5A3qPDhkUiZCsgl+jCJJAq7dfNN67Uww+oDOrb ddPk4M+x7mhjOPkHDQo5z9FgtZ89J21A4jgn5xoQPMnQtHpO620VASOAHHr/ecUJXR4p ZJ1A== X-Gm-Message-State: AO0yUKWtTPdxy8a47aTJfA/3dOsU+FtmYKv83tXQZ8S5ilVGVQ6/iPta TmvRd7bq4fFNG/otwJgM/OxXefpeHZ7Swkr7qK64haH1 X-Google-Smtp-Source: AK7set+pi3cbE9MojxRcUY7nK5e30Vl5/a9waPsjFez5dvgJDfZrMyEjcTVVUmQWol6PrUKakmaM+rwRbpdXhlHnhU8= X-Received: by 2002:a05:6902:3d2:b0:900:29d5:4817 with SMTP id g18-20020a05690203d200b0090029d54817mr1114430ybs.194.1676309681672; Mon, 13 Feb 2023 09:34:41 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Brad White Date: Mon, 13 Feb 2023 11:34:30 -0600 Message-ID: Subject: Re: Quoting issue from ODBC To: pgsql-odbc@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000001c16a405f49843a3" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000001c16a405f49843a3 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. --0000000000001c16a405f49843a3 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
=C2=A0Front end: Access 36= 5
Back end: Postgres 9.4
(I know, we are in the process of upgradin= g)
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 ar= e not being quoted properly.
Only on UPDATE statements.
It has been m= y experience that Access usually does a better job at converting the querie= s than I would have expected, but not in this instance.

For example<= br>
Access:=C2=A0connection.Execute "UPDATE [" & strTable = & "] SET [" & strTable & "].[InsertFlag] = =3D Null" _
=C2=A0 =C2=A0 & " WHERE ((([" & strTa= ble & "].[InsertFlag])=3D" & lngCurrUID & &quo= t;));", , adCmdText Or adExecuteNoRecords
Note that InsertFlag is b= racketed the same way in both instances.

PSQL log:=C2=A0UPDATE "= ;public"."Orders" SET InsertFlag=3DNULL =C2=A0WHERE (= "InsertFlag" =3D 166 )
Note that InsertFlag is quoted o= nce but not the other time.
Of course this gives the error: column "= ;insertflag" of relation "Orders" does not exist at characte= r 35.

Looks like I have=C2=A0about 16 unique instances of statements= not being quoted correctly resulting=C2=A0in over 500 errors in the log fo= r today.

This is not a new issue. The logs have these errors stretch= ing back months.

Any suggestions on where t= o look?

Thanks,
Brad.
--0000000000001c16a405f49843a3--