Feedback-ID: i76984098:Fastmail
Message-ID: <05d8145a-fd79-1062-ee27-5a87bfd59b0e@aklaver.com>
Date: Tue, 7 Feb 2023 16:19:08 -0800
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.6.1
Subject: Re: Quoting issue from ODBC
Content-Language: en-US
To: Brad White <b55white@gmail.com>,
 "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: 
 <CAA_1=93z4oEXKQ9GHtDkEQhEe5r39C8YSDMpwO8OHmSBreXw3g@mail.gmail.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: 
 <CAA_1=93z4oEXKQ9GHtDkEQhEe5r39C8YSDMpwO8OHmSBreXw3g@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/05d8145a-fd79-1062-ee27-5a87bfd59b0e%40aklaver.com>
Precedence: bulk

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.klaver@aklaver.com