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 1pQHGN-0002Zy-CB for pgsql-general@arkaria.postgresql.org; Fri, 10 Feb 2023 00:27:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pQHGL-000484-C3 for pgsql-general@arkaria.postgresql.org; Fri, 10 Feb 2023 00:27:37 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pQHGK-00043s-U1 for pgsql-general@lists.postgresql.org; Fri, 10 Feb 2023 00:27:37 +0000 Received: from mail-yw1-x1135.google.com ([2607:f8b0:4864:20::1135]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pQHGI-0002Mg-3C for pgsql-general@lists.postgresql.org; Fri, 10 Feb 2023 00:27:36 +0000 Received: by mail-yw1-x1135.google.com with SMTP id 00721157ae682-501c3a414acso47926217b3.7 for ; Thu, 09 Feb 2023 16:27:33 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=KF9LhxcJbFsBWd0GQ1Jq/iqvs7K1C1rs1NDN8zQ+7Mw=; b=Rk17Eh8btZYmoK5xp/KQkYrTnWJMPv/40d0KvjgeC+3HgyKE0oTcvveA3zAboRLgse g02Y5u5H7WGU12i45Rb6XJfV9j1hvujcOzJViTJdt/u7hgOD8995hBVaW/5TcAcN0IXZ f4c9kiNorQtml5PdsS7EMvUIZHSGuOxnKKVtftftBukNw6BtztwHb6EDAcwtZI/nB+1l afLISNLEmM9gWlxOXFEo/6+3A42D/2oFNl2QULMrvb16lwu/KwdEzOsXEaQ9JeHGCceQ IriHIy1BC1hbZWwc/VSKepbSdnPn2QX0z39O3fjG9IKgtqpUE1ZvqiE0sG60szW2tFTZ eLPw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=cc: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=KF9LhxcJbFsBWd0GQ1Jq/iqvs7K1C1rs1NDN8zQ+7Mw=; b=7tGzEEtI6y5rXJOJS4SDEOd4oXg+O2y6UJASXGGxFJmRsVHkRrPlcnm6+LiQIwSqKl qLPGkuYt90eMWGjYyzrJHSeaCKe87wNsJGMGwQbKRn8P82KNXQCqE6SfcsKWKiYksENs hU0D6RJuqVj839qyyiCVFYar2PZDYnnQ1pbmUb/jSgmyyDe7Tr0lz4sWK+c5SSLi8+8X Ob3yNPld01bmuPCXHWQoM/Bd09ELTy3H1pD+1EKXgxhIK7eTFahdfMtmbRNcPSYELhbc A6b/wha1m2oB75eycGVu+V/vr23US8aGHH36Jm+cxzBW7b//cwj5zHAH/mS79JEKMeFn V1BQ== X-Gm-Message-State: AO0yUKX5gbR30za8ZPD43zqrvGXAmznczIYrsHd9cH3ul/+OVWQSqEZ/ AyOsUzX7ehTiX3QoEt35QkItuJ3OQ7K9Wfh1LkrgQOY52Kk= X-Google-Smtp-Source: AK7set8dCMqCqCQ45gTIj2zWz3VG3CFNt8ST27RAh8NPqM3BhTE9xTNxAwuIufCXRV8aRgZAFStv34+KbgLTD1qbgB0= X-Received: by 2002:a81:d34b:0:b0:526:2740:5746 with SMTP id d11-20020a81d34b000000b0052627405746mr1808418ywl.497.1675988851671; Thu, 09 Feb 2023 16:27:31 -0800 (PST) MIME-Version: 1.0 References: <05d8145a-fd79-1062-ee27-5a87bfd59b0e@aklaver.com> <89465c0e-c683-1563-7e16-12688f77bdc3@gmail.com> In-Reply-To: From: Brad White Date: Thu, 9 Feb 2023 18:27:20 -0600 Message-ID: Subject: Re: Quoting issue from ODBC To: Adrian Klaver Cc: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="00000000000026c8cc05f44d905b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000026c8cc05f44d905b Content-Type: text/plain; charset="UTF-8" On Thu, Feb 9, 2023 at 5:10 PM Adrian Klaver wrote: > On 2/9/23 14:43, Brad White wrote: > > On Tue, Feb 7, 2023 at 10:20 PM Brad White > > 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 > > https://www.postgresql.org/list/pgsql-odbc/ > > > > > 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. > > > So how the successful UPDATE's called? > I'm still trying to track down all the statements. Because of the translation between the two database systems, I can't just search on a simple string. > > Are the successful UPDATES's on the same tables and columns? > This is the only routine that updates the InsertFlag column. All the order tables have that flag. > > Are these UPDATE's actually necessary? > This system is critical to the company, but has a reputation of being unreliable. I suspect this may be one cause. > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > --00000000000026c8cc05f44d905b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Feb 9, 2023 at 5:10 PM Adrian Kla= ver <adrian.klaver@aklaver.= com> wrote:
On 2/9/23 14:43, Brad White wrote:
> On Tue, Feb 7, 2023 at 10:20 PM Brad White <b55white@gmail.com
> <mailto:b55= white@gmail.com>> wrote:
>
>=C2=A0 =C2=A0 =C2=A0On 2/7/2023 6:19 PM, Adrian Klaver wrote:
>>=C2=A0 =C2=A0 =C2=A0On 2/7/23 16:10, Brad White wrote:
>>>=C2=A0 =C2=A0 =C2=A0Front end: Access 365
>>>=C2=A0 =C2=A0 =C2=A0Back end: Postgres 9.4
>>>=C2=A0 =C2=A0 =C2=A0(I know, we are in the process of upgrading= )
>>>
>>>=C2=A0 =C2=A0 =C2=A0I'm getting some cases where the SQL se= nt from MS-Access is failing.
>>>=C2=A0 =C2=A0 =C2=A0Looking at the postgres log shows that the = field names and table
>>>=C2=A0 =C2=A0 =C2=A0names are not being quoted properly.
>>>=C2=A0 =C2=A0 =C2=A0It has been my experience that Access usual= ly does a better job
>>>=C2=A0 =C2=A0 =C2=A0at converting the queries than I would have= expected, but not in
>>>=C2=A0 =C2=A0 =C2=A0this instance.
>>>
>>>=C2=A0 =C2=A0 =C2=A0For example
>>>
>>>=C2=A0 =C2=A0 =C2=A0Access:=C2=A0connection.Execute "UPDAT= E [" & strTable & "] SET [" &
>>>=C2=A0 =C2=A0 =C2=A0strTable & "].[InsertFlag] =3D Nul= l" _
>>>=C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0 & " WHERE ((([= " & strTable & "].[InsertFlag])=3D" & lngCurrUID=
>>>=C2=A0 =C2=A0 =C2=A0& "));", , adCmdText Or adExe= cuteNoRecords
>>>=C2=A0 =C2=A0 =C2=A0Note that InsertFlag is bracketed the same = way in both instances.
>>>
>>>=C2=A0 =C2=A0 =C2=A0PSQL:=C2=A0UPDATE "public"."= Orders" SET InsertFlag=3DNULL =C2=A0WHERE
>>>=C2=A0 =C2=A0 =C2=A0("InsertFlag" =3D 166 )
>>>=C2=A0 =C2=A0 =C2=A0Note that InsertFlag is quoted once but not= the other time.
>>>=C2=A0 =C2=A0 =C2=A0Of course this gives the error: column &quo= t;insertflag" of relation
>>>=C2=A0 =C2=A0 =C2=A0"Orders" does not exist at charac= ter 35.
>>>
>>>=C2=A0 =C2=A0 =C2=A0Looks like I have=C2=A0about 16 unique inst= ances of statements not
>>>=C2=A0 =C2=A0 =C2=A0being quoted correctly resulting=C2=A0in ov= er 500 errors in the log
>>>=C2=A0 =C2=A0 =C2=A0for today.
>>
>>=C2=A0 =C2=A0 =C2=A0Where these preexisting queries or where they c= reated today?
>
>=C2=A0 =C2=A0 =C2=A0These queries are decades old but I don't view = this log file very
>=C2=A0 =C2=A0 =C2=A0often, so I don't know how long.
>
>=C2=A0 =C2=A0 =C2=A0I'll review when I get back on site Thursday an= d see if I can find
>=C2=A0 =C2=A0 =C2=A0any users that are not getting the error or when it= started.
>
>>
>>>
>>>=C2=A0 =C2=A0 =C2=A0Any suggestions on where to look?
>>>
>>>=C2=A0 =C2=A0 =C2=A0Thanks,
>>>=C2=A0 =C2=A0 =C2=A0Brad.
>
> 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.=C2=A0 Is there a better place to report = those?
>
> Driver:=C2=A0PostgreSQL Unicode
> Filename: PSQLODBC35W.DLL
> Version: 13.02.00
> ReleaseDate: 9/22/2021

https://www.postgresql.org/list/pgsql-odbc/

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


So how the successful UPDATE's called?
I'm sti= ll trying to track down all the statements. Because of the translation betw= een the two database systems, I can't just search on a simple string.= =C2=A0

Are the successful UPDATES's on the same tables and columns?
This is the only routine that updates the InsertFlag column. All= the order tables have that flag.=C2=A0

Are these UPDATE's actually necessary?
This system= is critical to the company, but has a reputation of being unreliable. I su= spect this may be one cause.


--
Adrian Klaver
adrian.klave= r@aklaver.com

--00000000000026c8cc05f44d905b--