Re: Quoting issue from ODBC

On Thu, Feb 9, 2023 at 5:10 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 2/9/23 14:43, Brad White wrote:
> > On Tue, Feb 7, 2023 at 10:20 PM Brad White <b55white(at)gmail(dot)com
> > <mailto: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
>
> 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(dot)klaver(at)aklaver(dot)com
>
>