Re: Quoting issue from 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

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.

Any suggestions on where to look?

Thanks,
Brad.

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?

If preexisting have there been any changes to the software stack since
yesterday?

If new how where the queries generated?

>
> Any suggestions on where to look?
>
> Thanks,
> Brad.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

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.

On Tue, Feb 7, 2023 at 5:20 PM Brad White <b55white(at)gmail(dot)com> wrote:

> 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.
>
>
Either VBA is doing some query munging or that concatenated string isn't
the one that is producing the error. Which suggests that maybe you have a
string somewhere that is not written correctly that need to be fixed so it
doesn't produce this error.

David J.

On 2/7/23 17:23, David G. Johnston wrote:
> On Tue, Feb 7, 2023 at 5:20 PM Brad White <b55white(at)gmail(dot)com> wrote:
>
> 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.
>
>
> Either VBA is doing some query munging or that concatenated string
> isn't the one that is producing the error.  Which suggests that maybe
> you have a string somewhere that is not written correctly that need to
> be fixed so it doesn't produce this error.
>
> David J.
>
Should the SET clause have a table alias at all?  I get an error with

update foo set foo.id = something;

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.
>
--
Quote Signature I talk with clients, find out where their pain points
are, and solve those.
On-call IT Management for small companies and non-profits.
SCP, Inc.
bwhite(at)inebraska(dot)com
402-601-7990

Quote of the Day
   There is a huge difference between fathering a child and being a
father.
   One produces a child. The other produces an adult.
    -- John Eldredge

On Tue, Feb 7, 2023 at 10:20 PM Brad White <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

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.

ERROR: column "*commitrep*" of relation "Order Items" does not exist at
character 35
STATEMENT: UPDATE "public"."Order Items" SET *CommitRep*='jdoe' WHERE
(("OrderFID" = 557837 ) AND ("*CommitRep*" IS NULL ) )

Here is the original query. You can see that [CommitRep] is written the
same way both times in the query, but in the final quoted correctly once
and incorrectly once.
RunADO "CreditRepWithCommit()", "UPDATE [Order Items] SET *[CommitRep]*
= '" & UID & "'" _
& " WHERE [OrderFID] = " & OrderFID & " AND * [CommitRep]* IS NULL;"

A few other samples from the log. Always just the target field of the
UPDATE that is not quoted.

ERROR: column "*availableserialcount*" of relation "Serial Pools" does not
exist at character 36
STATEMENT: UPDATE "public"."Serial Pools" SET *AvailableSerialCount*=143
WHERE ("ID" = 134 )

ERROR: column "*serialnum*" of relation "Order Items" does not exist at
character 35
STATEMENT: UPDATE "public"."Order Items" SET *SerialNum*='205757'
,LastSerCaptureTypeID=2 WHERE ("ID" = 1891128 )

ERROR: column "*issuedate*" of relation "Order Items" does not exist at
character 35
STATEMENT: UPDATE "public"."Order Items" SET *IssueDate*='2023-02-09
14:28:09'::timestamp ,*IssueRep*=' jdoe ' WHERE ("ID" = 1891128 )

>> 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.

Going back to early 2020, I don't have any logs that don't have these
errors, so it is not a recent change.

I don't yet see anything that is user specific.

Brad.

>

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?

Are the successful UPDATES's on the same tables and columns?

From your subsequent post:

"Going back to early 2020, I don't have any logs that don't have these
errors, so it is not a recent change."

Are these UPDATE's actually necessary?

In other words has nobody noticed a problem with the data over that time
frame?

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

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
>
>

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.

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.

> Are these UPDATE's actually necessary?
> In other words has nobody noticed a problem with the data over that time
frame?

I don't know what to make of it.
I had the same question you did.

I now have proof that these hundreds of errors are not an issue.
The postgres logs in pg_log clearly show an error, but the errors never
show up in the Access log.
That means that Access didn't get an error back.
Further, I have evidence that the values are actually getting updated.
The financial balances are all correct.
Issues that were closed are all closed, etc.
Even though the Access log says the statement was only run once and
Postgres says ERROR, I see no other evidence of it.
I can't fathom how that can happen.

¯\_(ツ)_/¯

On the other hand, there are some real errors in the log. I'll put that in
another post.

Brad

On 2/14/23 14:58, Brad White wrote:
> > Are these UPDATE's actually necessary?
> > In other words has nobody noticed a problem with the data over that
> time frame?
>
> I don't know what to make of it.
> I had the same question you did.
>
> I now have proof that these hundreds of errors are not an issue.
> The postgres logs in pg_log clearly show an error, but the errors never
> show up in the Access log.

Then either:

1) They are not coming from Access.

2) Or indirectly from Access, which kicks off some other code that is
not logged.

In either case they seem to be surplus to requirements. In other words
cruft that is not actually relevant to the application.

> That means that Access didn't get an error back.
> Further, I have evidence that the values are actually getting updated.
> The financial balances are all correct.
> Issues that were closed are all closed, etc.
> Even though the Access log says the statement was only run once and
> Postgres says ERROR, I see no other evidence of it.
> I can't fathom how that can happen.
>
> ¯\_(ツ)_/¯
>
> On the other hand, there are some real errors in the log. I'll put that
> in another post.
>
> Brad

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com