Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cayenne.apache.org
Content-Type: text/plain; charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 9.0 \(3094\))
Subject: Re: likeIgnoreCase queries and EJBQL
From: Hugi Thordarson <hugi@karlmenn.is>
In-Reply-To: <FA936065-D3F5-4EB4-999C-3C28D0FE0154@objectstyle.org>
Date: Tue, 13 Oct 2015 15:16:42 +0000
Content-Transfer-Encoding: quoted-printable
Message-Id: <8945123D-5A85-4AF9-8B4D-D01F480F037D@karlmenn.is>
References: <7F50BDE2-E500-4CA6-B93B-8CA24810066F@karlmenn.is>
 <CAM1yOjbWFQrcXpFDJhZR17_OBzpWqjWZ1hSa-Nje01OjTtitMw@mail.gmail.com>
 <CFC169E9-7DB8-4B3B-A171-5A2BC1C9FE5C@karlmenn.is>
 <90705ED1-A8AE-4694-8845-E279747E7CA8@objectstyle.org>
 <C821C774-0B26-4F85-AC61-D6F4DA0A4B17@karlmenn.is>
 <AD5CF688-BAC6-4A2E-9ECA-78F22AB674E6@karlmenn.is>
 <FA936065-D3F5-4EB4-999C-3C28D0FE0154@objectstyle.org>
To: user@cayenne.apache.org

I=E2=80=99m not sure how useful my work would be since I=E2=80=99m =
writing against a 15 year old Informix version, incompatible with modern =
JDBC-drivers (for example, modern Informix JDBC drivers support {fn =
ucase} just fine). We=E2=80=99re planning to switch this customer to =
Postgres as soon as they have the resources.

But I=E2=80=99ll of course be happy to contribute any code I create if =
anyone happens to need to communicate with Informix 9.30 in the future =
:)

Cheers,
- hugi


> On 13. okt. 2015, at 14:55, Andrus Adamchik <andrus@objectstyle.org> =
wrote:
>=20
> Awesome. Congrats :)
>=20
> Perhaps with your help we can create a fully working Informix adapter =
in Cayenne?
>=20
> Andrus
>=20
>> On Oct 13, 2015, at 10:49 AM, Hugi Thordarson <hugi@karlmenn.is> =
wrote:
>>=20
>> Eeek=E2=80=94I actually did it, and it works like a charm! Found out =
my factory needed to override getConditionTranslator(), not =
getSelectTranslator().
>>=20
>> https://gist.github.com/hugith/f04fd044b59c4ce60aa9
>>=20
>> For the first time, I feel like a total Cayenne Boss=E2=84=A2 :)
>>=20
>> - hugi
>>=20
>>=20
>>=20
>>> On 13. okt. 2015, at 14:45, Hugi Thordarson <hugi@karlmenn.is> =
wrote:
>>>=20
>>>> {fn ucase} is JDBC escape syntax, so presumably the driver should =
convert that to the proper syntax. If a given driver is not capable of =
doing that, that has to be addressed in DbAdapter for that DB. =
DbAdapter.getEjbqlTranslatorFactory() is how you customize EJBQL =
translation. So Mike is right about that.
>>>=20
>>> Thanks=E2=80=A6 I=E2=80=99ve created my adaptor and ensured that =
it=E2=80=99s being used along with my new =
InformixEJBQLSelectTranslator=E2=80=94but I=E2=80=99m not getting any =
invocations of visitUpper and the code generated still contains {fn =
ucase(=E2=80=A6)}. Shouldn=E2=80=99t visitUpper() be where I hook into =
the generation of the upper statement for the DB?
>>>=20
>>> https://gist.github.com/hugith/bb68f1944f7a7d754363
>>>=20
>>>> BTW, which DB are we talking about?
>>>=20
>>> Ah, well=E2=80=A6 It=E2=80=99s informix=E2=80=94 and an old version =
at that. So I would probably always have had to create my own DbAdaptor =
anyway.
>>>=20
>>> Cheers,
>>> - hugi
>>>=20
>>>=20
>>>>=20
>>>> Andrus
>>>>=20
>>>>=20
>>>>> On Oct 13, 2015, at 5:31 AM, Hugi Thordarson <hugi@karlmenn.is> =
wrote:
>>>>>=20
>>>>> Thanks Mike!
>>>>>=20
>>>>> I=E2=80=99m not sure it this is a database plugin problem though. =
Doing regular case insensitive queries works fine, it=E2=80=99s only =
queries created from EJBQL that fail. Also, the only place in the =
Cayenne sources I can find a mention of =E2=80=9Cucase" is in =
EJBQLTranslator=E2=80=99s visitUpper(). Perhaps changing that method to =
do upper( rather than {fn ucase. might solve the problem? I=E2=80=99m =
going to try that out :).
>>>>>=20
>>>>> =
https://github.com/apache/cayenne/blob/master/cayenne-server/src/main/java=
/org/apache/cayenne/access/translator/ejbql/EJBQLConditionTranslator.java#=
L1058
>>>>>=20
>>>>> Cheers,
>>>>> - hugi
>>>>>=20
>>>>>=20
>>>>>=20
>>>>>=20
>>>>>> Sounds like the DbAdaptor for that database needs to be =
special-cased.
>>>>>> Lots of examples of how this is done in cayenne/dba/<database>/*,
>>>>>> especially for oracle
>>>>>>=20
>>>>>> And it's pretty simple to set your app up to use a custom version =
of
>>>>>> the dbAdaptor if you don't want to build your own custom version =
of
>>>>>> cayenne while you wait until it's included in a release.
>>>>>>=20
>>>>>> On Mon, Oct 12, 2015 at 12:46 PM, Hugi Thordarson =
<hugi@karlmenn.is> wrote:
>>>>>>> Hi all.
>>>>>>>=20
>>>>>>> I=E2=80=99m communicating with a database that doesn=E2=80=99t =
have the UCASE function, only UPPER.
>>>>>>>=20
>>>>>>> When I use case insensitive expressions (for example, =
User.NAME.likeIgnoreCase(=E2=80=9CBob=E2=80=9D)) in a regular =
SelectQuery, Cayenne generates SQL using the =E2=80=9CUPPER=E2=80=9D =
function (for expressions generated using likeIgnoreCase). This works =
fine.
>>>>>>>=20
>>>>>>> But if I generate EJBQL from the expression and then use the =
resulting string to perform an EJBQLQuery, Cayenne will attempt to use =
UCASE function in the resulting SQL and things go awry.
>>>>>>>=20
>>>>>>> Can I tell the EJBQL SQL translator to use =E2=80=9Cupper=E2=80=9D=
 rather than =E2=80=9Cucase=E2=80=9D when performing these queries?
>>>>>>>=20
>>>>>>> Cheers,
>>>>>>> - hugi
>>>>>=20
>>>>>=20
>>>>=20
>>>=20
>>=20
>>=20
>=20