Re: TRIM bug

On 10/06/2016 07:39 PM, James Bellinger wrote:

Ccing list.
> Hello,
>
> MS Access is 2003
>
> psqlodbc I downloaded the newest yesterday from postgresql.org.
>
> PostgreSQL is 9.4.1.
>
> I am fetching the final query string from the CSV log of my Postgres
> server.
>
> The query I pasted in is the query I am giving MS Access, in a combo box
> dropdown. If I remove TRIM(), it has the right number of parenthesis.

So it is being processed by Access before being sent to Postgres. My
experience with Access is that when it does that it creates queries that
are only understood by itself.

In the Query tab(working from memory) there should be an option to do a
Pass-Through query. This sends whatever you type to Postgres as written
and not reparsed by Access. In that paste:



SELECT
      DISTINCT "public"."Orders"."Name"
FROM (
          "public"."Orders"
      LEFT OUTER JOIN "public"."POs" ON (
          "public"."Orders"."OnlineCode" = "public"."POs"."OnlineCode" ) )
WHERE
      "public"."Orders"."Name" IS NOT NULL
AND
      char_length (trim ("public"."Orders"."Name" ) )   > 1
ORDER BY
      "public"."Orders"."Name"


This will take Access out of the loop and help determine whether
psqlodbc is editorializing over what it is being passed in.


>
> Thanks!
>
> James
>
>
> On 10/6/2016 7:55 PM, Adrian Klaver wrote:
>> On 10/06/2016 04:42 PM, James Bellinger wrote:
>>> Hello,
>>>
>>> I just ran into this and narrowed it down to TRIM. Without the TRIM(),
>>> it works fine.
>>>
>>> In MS Access,
>>> SELECT DISTINCT [Name] FROM [Customer Information] WHERE [Name] IS NOT
>>> NULL AND LEN(TRIM([Name])) > 1 ORDER BY [Name] ASC;
>>> becomes
>>> SELECT DISTINCT "public"."Orders"."Name"  FROM ("public"."Orders" LEFT
>>> OUTER JOIN "public"."POs" ON ("public"."Orders"."OnlineCode" =
>>> "public"."POs"."OnlineCode" ) ) WHERE (NOT(("public"."Orders"."Name" IS
>>> NULL ) ) AND (char_length(ltrim(rtrim("public"."Orders"."Name" )))))> 1
>>> ) ) ORDER BY "public"."Orders"."Name"
>>> As you can see, there is an extra right parenthesis. (Customer
>>> Information is an Access query onto Orders, in case the name change is
>>> confusing from my test example.)
>>
>> Access version?
>>
>> psqlobdc version?
>>
>> Postgres version?
>>
>> Is this being generated by the Access query builder?
>>
>> What if you do a Pass-Though query?
>>
>> Where are fetching the final query text from?
>>
>>
>>
>> SELECT
>>     DISTINCT "public"."Orders"."Name"
>> FROM (
>>         "public"."Orders"
>>     LEFT OUTER JOIN "public"."POs" ON (
>>         "public"."Orders"."OnlineCode" = "public"."POs"."OnlineCode" ) )
>> WHERE (
>>     NOT (
>>         (
>>             "public"."Orders"."Name" IS NULL ) )
>>     AND (
>>         char_length (
>>             ltrim (
>>                 rtrim (
>>                     "public"."Orders"."Name" ) ) ) ) )
>>     > 1 ) )
>> ORDER BY
>>     "public"."Orders"."Name"
>>
>>
>> I count two extra right parentheses.
>>
>>>
>>> Thanks!
>>>
>>> James
>>>
>>>
>>
>>
>


--
Adrian Klaver
adrian.klaver@aklaver.com