FAQ
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

Search Discussions

  • Mike Kienenberger at Oct 12, 2015 at 5:27 pm
    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 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
  • Hugi Thordarson at Oct 13, 2015 at 9:32 am
    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 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
  • Andrus Adamchik at Oct 13, 2015 at 1:29 pm
    {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 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 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
  • Hugi Thordarson at Oct 13, 2015 at 2:45 pm
    {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 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 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
  • Hugi Thordarson at Oct 13, 2015 at 2:50 pm
    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 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 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 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
  • Andrus Adamchik at Oct 13, 2015 at 2:56 pm
    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 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 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 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 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
  • Hugi Thordarson at Oct 13, 2015 at 3:17 pm
    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 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 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 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 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 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
  • Mike Kienenberger at Oct 13, 2015 at 3:36 pm
    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 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 :)
  • Hugi Thordarson at Oct 13, 2015 at 7:09 pm
    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 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 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 :)

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categoriescayenne
postedOct 12, '15 at 4:46p
activeOct 13, '15 at 7:09p
posts10
users3
websitecayenne.apache.org

People

Translate

site design / logo © 2021 Grokbase