You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Hugi Thordarson <hu...@karlmenn.is> on 2015/10/12 18:46:17 UTC
likeIgnoreCase queries and EJBQL
Hi all.
I’m communicating with a database that doesn’t have the UCASE function, only UPPER.
When I use case insensitive expressions (for example, User.NAME.likeIgnoreCase(“Bob”)) in a regular SelectQuery, Cayenne generates SQL using the “UPPER” function (for expressions generated using likeIgnoreCase). This works fine.
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.
Can I tell the EJBQL SQL translator to use “upper” rather than “ucase” when performing these queries?
Cheers,
- hugi
Re: likeIgnoreCase queries and EJBQL
Posted by Hugi Thordarson <hu...@karlmenn.is>.
Admirable effort and duly noted. I know I’d have loved it if someone did this before me and shared the result, so I’ll keep the Informix-specific stuff separate from everything else and we’ll see if this ends up going far enough to make the DB in question “cayenne compatible”. It’s a reeeaaally obscure case though :).
- hugi
> On 13. okt. 2015, at 15:35, Mike Kienenberger <mk...@gmail.com> wrote:
>
> We are willing to support older obsolete versions of databases as well
> as new ones. :)
>
> Our Oracle driver code is an example of how we support multiple driver versions.
>
>
> On Tue, Oct 13, 2015 at 11:16 AM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>> I’m not sure how useful my work would be since I’m 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’re planning to switch this customer to Postgres as soon as they have the resources.
>>
>> But I’ll of course be happy to contribute any code I create if anyone happens to need to communicate with Informix 9.30 in the future :)
Re: likeIgnoreCase queries and EJBQL
Posted by Mike Kienenberger <mk...@gmail.com>.
We are willing to support older obsolete versions of databases as well
as new ones. :)
Our Oracle driver code is an example of how we support multiple driver versions.
On Tue, Oct 13, 2015 at 11:16 AM, Hugi Thordarson <hu...@karlmenn.is> wrote:
> I’m not sure how useful my work would be since I’m 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’re planning to switch this customer to Postgres as soon as they have the resources.
>
> But I’ll of course be happy to contribute any code I create if anyone happens to need to communicate with Informix 9.30 in the future :)
Re: likeIgnoreCase queries and EJBQL
Posted by Hugi Thordarson <hu...@karlmenn.is>.
I’m not sure how useful my work would be since I’m 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’re planning to switch this customer to Postgres as soon as they have the resources.
But I’ll 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 <an...@objectstyle.org> wrote:
>
> Awesome. Congrats :)
>
> Perhaps with your help we can create a fully working Informix adapter in Cayenne?
>
> Andrus
>
>> On Oct 13, 2015, at 10:49 AM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>
>> Eeek—I actually did it, and it works like a charm! Found out my factory needed to override getConditionTranslator(), not getSelectTranslator().
>>
>> https://gist.github.com/hugith/f04fd044b59c4ce60aa9
>>
>> For the first time, I feel like a total Cayenne Boss™ :)
>>
>> - hugi
>>
>>
>>
>>> On 13. okt. 2015, at 14:45, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>>
>>>> {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.
>>>
>>> Thanks… I’ve created my adaptor and ensured that it’s being used along with my new InformixEJBQLSelectTranslator—but I’m not getting any invocations of visitUpper and the code generated still contains {fn ucase(…)}. Shouldn’t visitUpper() be where I hook into the generation of the upper statement for the DB?
>>>
>>> https://gist.github.com/hugith/bb68f1944f7a7d754363
>>>
>>>> BTW, which DB are we talking about?
>>>
>>> Ah, well… It’s informix— and an old version at that. So I would probably always have had to create my own DbAdaptor anyway.
>>>
>>> Cheers,
>>> - hugi
>>>
>>>
>>>>
>>>> Andrus
>>>>
>>>>
>>>>> On Oct 13, 2015, at 5:31 AM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>>>>
>>>>> Thanks Mike!
>>>>>
>>>>> I’m not sure it this is a database plugin problem though. Doing regular case insensitive queries works fine, it’s only queries created from EJBQL that fail. Also, the only place in the Cayenne sources I can find a mention of “ucase" is in EJBQLTranslator’s visitUpper(). Perhaps changing that method to do upper( rather than {fn ucase. might solve the problem? I’m going to try that out :).
>>>>>
>>>>> https://github.com/apache/cayenne/blob/master/cayenne-server/src/main/java/org/apache/cayenne/access/translator/ejbql/EJBQLConditionTranslator.java#L1058
>>>>>
>>>>> Cheers,
>>>>> - hugi
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>> 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
>>>>>>
>>>>>> 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.
>>>>>>
>>>>>> On Mon, Oct 12, 2015 at 12:46 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>>>>>> Hi all.
>>>>>>>
>>>>>>> I’m communicating with a database that doesn’t have the UCASE function, only UPPER.
>>>>>>>
>>>>>>> When I use case insensitive expressions (for example, User.NAME.likeIgnoreCase(“Bob”)) in a regular SelectQuery, Cayenne generates SQL using the “UPPER” function (for expressions generated using likeIgnoreCase). This works fine.
>>>>>>>
>>>>>>> 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.
>>>>>>>
>>>>>>> Can I tell the EJBQL SQL translator to use “upper” rather than “ucase” when performing these queries?
>>>>>>>
>>>>>>> Cheers,
>>>>>>> - hugi
>>>>>
>>>>>
>>>>
>>>
>>
>>
>
Re: likeIgnoreCase queries and EJBQL
Posted by Andrus Adamchik <an...@objectstyle.org>.
Awesome. Congrats :)
Perhaps with your help we can create a fully working Informix adapter in Cayenne?
Andrus
> On Oct 13, 2015, at 10:49 AM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>
> Eeek—I actually did it, and it works like a charm! Found out my factory needed to override getConditionTranslator(), not getSelectTranslator().
>
> https://gist.github.com/hugith/f04fd044b59c4ce60aa9
>
> For the first time, I feel like a total Cayenne Boss™ :)
>
> - hugi
>
>
>
>> On 13. okt. 2015, at 14:45, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>
>>> {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.
>>
>> Thanks… I’ve created my adaptor and ensured that it’s being used along with my new InformixEJBQLSelectTranslator—but I’m not getting any invocations of visitUpper and the code generated still contains {fn ucase(…)}. Shouldn’t visitUpper() be where I hook into the generation of the upper statement for the DB?
>>
>> https://gist.github.com/hugith/bb68f1944f7a7d754363
>>
>>> BTW, which DB are we talking about?
>>
>> Ah, well… It’s informix— and an old version at that. So I would probably always have had to create my own DbAdaptor anyway.
>>
>> Cheers,
>> - hugi
>>
>>
>>>
>>> Andrus
>>>
>>>
>>>> On Oct 13, 2015, at 5:31 AM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>>>
>>>> Thanks Mike!
>>>>
>>>> I’m not sure it this is a database plugin problem though. Doing regular case insensitive queries works fine, it’s only queries created from EJBQL that fail. Also, the only place in the Cayenne sources I can find a mention of “ucase" is in EJBQLTranslator’s visitUpper(). Perhaps changing that method to do upper( rather than {fn ucase. might solve the problem? I’m going to try that out :).
>>>>
>>>> https://github.com/apache/cayenne/blob/master/cayenne-server/src/main/java/org/apache/cayenne/access/translator/ejbql/EJBQLConditionTranslator.java#L1058
>>>>
>>>> Cheers,
>>>> - hugi
>>>>
>>>>
>>>>
>>>>
>>>>> 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
>>>>>
>>>>> 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.
>>>>>
>>>>> On Mon, Oct 12, 2015 at 12:46 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>>>>> Hi all.
>>>>>>
>>>>>> I’m communicating with a database that doesn’t have the UCASE function, only UPPER.
>>>>>>
>>>>>> When I use case insensitive expressions (for example, User.NAME.likeIgnoreCase(“Bob”)) in a regular SelectQuery, Cayenne generates SQL using the “UPPER” function (for expressions generated using likeIgnoreCase). This works fine.
>>>>>>
>>>>>> 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.
>>>>>>
>>>>>> Can I tell the EJBQL SQL translator to use “upper” rather than “ucase” when performing these queries?
>>>>>>
>>>>>> Cheers,
>>>>>> - hugi
>>>>
>>>>
>>>
>>
>
>
Re: likeIgnoreCase queries and EJBQL
Posted by Hugi Thordarson <hu...@karlmenn.is>.
Eeek—I actually did it, and it works like a charm! Found out my factory needed to override getConditionTranslator(), not getSelectTranslator().
https://gist.github.com/hugith/f04fd044b59c4ce60aa9
For the first time, I feel like a total Cayenne Boss™ :)
- hugi
> On 13. okt. 2015, at 14:45, Hugi Thordarson <hu...@karlmenn.is> wrote:
>
>> {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.
>
> Thanks… I’ve created my adaptor and ensured that it’s being used along with my new InformixEJBQLSelectTranslator—but I’m not getting any invocations of visitUpper and the code generated still contains {fn ucase(…)}. Shouldn’t visitUpper() be where I hook into the generation of the upper statement for the DB?
>
> https://gist.github.com/hugith/bb68f1944f7a7d754363
>
>> BTW, which DB are we talking about?
>
> Ah, well… It’s informix— and an old version at that. So I would probably always have had to create my own DbAdaptor anyway.
>
> Cheers,
> - hugi
>
>
>>
>> Andrus
>>
>>
>>> On Oct 13, 2015, at 5:31 AM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>>
>>> Thanks Mike!
>>>
>>> I’m not sure it this is a database plugin problem though. Doing regular case insensitive queries works fine, it’s only queries created from EJBQL that fail. Also, the only place in the Cayenne sources I can find a mention of “ucase" is in EJBQLTranslator’s visitUpper(). Perhaps changing that method to do upper( rather than {fn ucase. might solve the problem? I’m going to try that out :).
>>>
>>> https://github.com/apache/cayenne/blob/master/cayenne-server/src/main/java/org/apache/cayenne/access/translator/ejbql/EJBQLConditionTranslator.java#L1058
>>>
>>> Cheers,
>>> - hugi
>>>
>>>
>>>
>>>
>>>> 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
>>>>
>>>> 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.
>>>>
>>>> On Mon, Oct 12, 2015 at 12:46 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>>>> Hi all.
>>>>>
>>>>> I’m communicating with a database that doesn’t have the UCASE function, only UPPER.
>>>>>
>>>>> When I use case insensitive expressions (for example, User.NAME.likeIgnoreCase(“Bob”)) in a regular SelectQuery, Cayenne generates SQL using the “UPPER” function (for expressions generated using likeIgnoreCase). This works fine.
>>>>>
>>>>> 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.
>>>>>
>>>>> Can I tell the EJBQL SQL translator to use “upper” rather than “ucase” when performing these queries?
>>>>>
>>>>> Cheers,
>>>>> - hugi
>>>
>>>
>>
>
Re: likeIgnoreCase queries and EJBQL
Posted by Hugi Thordarson <hu...@karlmenn.is>.
> {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.
Thanks… I’ve created my adaptor and ensured that it’s being used along with my new InformixEJBQLSelectTranslator—but I’m not getting any invocations of visitUpper and the code generated still contains {fn ucase(…)}. Shouldn’t visitUpper() be where I hook into the generation of the upper statement for the DB?
https://gist.github.com/hugith/bb68f1944f7a7d754363
> BTW, which DB are we talking about?
Ah, well… It’s informix— and an old version at that. So I would probably always have had to create my own DbAdaptor anyway.
Cheers,
- hugi
>
> Andrus
>
>
>> On Oct 13, 2015, at 5:31 AM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>
>> Thanks Mike!
>>
>> I’m not sure it this is a database plugin problem though. Doing regular case insensitive queries works fine, it’s only queries created from EJBQL that fail. Also, the only place in the Cayenne sources I can find a mention of “ucase" is in EJBQLTranslator’s visitUpper(). Perhaps changing that method to do upper( rather than {fn ucase. might solve the problem? I’m going to try that out :).
>>
>> https://github.com/apache/cayenne/blob/master/cayenne-server/src/main/java/org/apache/cayenne/access/translator/ejbql/EJBQLConditionTranslator.java#L1058
>>
>> Cheers,
>> - hugi
>>
>>
>>
>>
>>> 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
>>>
>>> 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.
>>>
>>> On Mon, Oct 12, 2015 at 12:46 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>>> Hi all.
>>>>
>>>> I’m communicating with a database that doesn’t have the UCASE function, only UPPER.
>>>>
>>>> When I use case insensitive expressions (for example, User.NAME.likeIgnoreCase(“Bob”)) in a regular SelectQuery, Cayenne generates SQL using the “UPPER” function (for expressions generated using likeIgnoreCase). This works fine.
>>>>
>>>> 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.
>>>>
>>>> Can I tell the EJBQL SQL translator to use “upper” rather than “ucase” when performing these queries?
>>>>
>>>> Cheers,
>>>> - hugi
>>
>>
>
Re: likeIgnoreCase queries and EJBQL
Posted by Andrus Adamchik <an...@objectstyle.org>.
{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.
BTW, which DB are we talking about?
Andrus
> On Oct 13, 2015, at 5:31 AM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>
> Thanks Mike!
>
> I’m not sure it this is a database plugin problem though. Doing regular case insensitive queries works fine, it’s only queries created from EJBQL that fail. Also, the only place in the Cayenne sources I can find a mention of “ucase" is in EJBQLTranslator’s visitUpper(). Perhaps changing that method to do upper( rather than {fn ucase. might solve the problem? I’m going to try that out :).
>
> https://github.com/apache/cayenne/blob/master/cayenne-server/src/main/java/org/apache/cayenne/access/translator/ejbql/EJBQLConditionTranslator.java#L1058
>
> Cheers,
> - hugi
>
>
>
>
>> 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
>>
>> 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.
>>
>> On Mon, Oct 12, 2015 at 12:46 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>> Hi all.
>>>
>>> I’m communicating with a database that doesn’t have the UCASE function, only UPPER.
>>>
>>> When I use case insensitive expressions (for example, User.NAME.likeIgnoreCase(“Bob”)) in a regular SelectQuery, Cayenne generates SQL using the “UPPER” function (for expressions generated using likeIgnoreCase). This works fine.
>>>
>>> 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.
>>>
>>> Can I tell the EJBQL SQL translator to use “upper” rather than “ucase” when performing these queries?
>>>
>>> Cheers,
>>> - hugi
>
>
Re: likeIgnoreCase queries and EJBQL
Posted by Hugi Thordarson <hu...@karlmenn.is>.
Thanks Mike!
I’m not sure it this is a database plugin problem though. Doing regular case insensitive queries works fine, it’s only queries created from EJBQL that fail. Also, the only place in the Cayenne sources I can find a mention of “ucase" is in EJBQLTranslator’s visitUpper(). Perhaps changing that method to do upper( rather than {fn ucase. might solve the problem? I’m going to try that out :).
https://github.com/apache/cayenne/blob/master/cayenne-server/src/main/java/org/apache/cayenne/access/translator/ejbql/EJBQLConditionTranslator.java#L1058
Cheers,
- hugi
> 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
>
> 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.
>
> On Mon, Oct 12, 2015 at 12:46 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>> Hi all.
>>
>> I’m communicating with a database that doesn’t have the UCASE function, only UPPER.
>>
>> When I use case insensitive expressions (for example, User.NAME.likeIgnoreCase(“Bob”)) in a regular SelectQuery, Cayenne generates SQL using the “UPPER” function (for expressions generated using likeIgnoreCase). This works fine.
>>
>> 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.
>>
>> Can I tell the EJBQL SQL translator to use “upper” rather than “ucase” when performing these queries?
>>
>> Cheers,
>> - hugi
Re: likeIgnoreCase queries and EJBQL
Posted by Mike Kienenberger <mk...@gmail.com>.
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
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.
On Mon, Oct 12, 2015 at 12:46 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
> Hi all.
>
> I’m communicating with a database that doesn’t have the UCASE function, only UPPER.
>
> When I use case insensitive expressions (for example, User.NAME.likeIgnoreCase(“Bob”)) in a regular SelectQuery, Cayenne generates SQL using the “UPPER” function (for expressions generated using likeIgnoreCase). This works fine.
>
> 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.
>
> Can I tell the EJBQL SQL translator to use “upper” rather than “ucase” when performing these queries?
>
> Cheers,
> - hugi