Re: Quoting issue from ODBC

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Brad White <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: Quoting issue from ODBC
Date: Thu, 9 Feb 2023 18:27:20 -0600
Message-ID: <CAA_1=92sAnVFGzR4do1Dious+=rroQw6yyFkoGJE_eVkmESP8Q@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAA_1=93z4oEXKQ9GHtDkEQhEe5r39C8YSDMpwO8OHmSBreXw3g@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CAA_1=93_rUYmMgY6vU2svik6G107soojb4J0xzoYa1TibpP_3Q@mail.gmail.com>
	<[email protected]>

On Thu, Feb 9, 2023 at 5:10 PM Adrian Klaver <[email protected]>
wrote:

> On 2/9/23 14:43, Brad White wrote:
> > On Tue, Feb 7, 2023 at 10:20 PM Brad White <[email protected]
> > <mailto:[email protected]>> 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
> [email protected]
>
>

view thread (14+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Quoting issue from ODBC
  In-Reply-To: <CAA_1=92sAnVFGzR4do1Dious+=rroQw6yyFkoGJE_eVkmESP8Q@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox