Named query exception JPA

[Former Member]

Hello

I wrote a simple named query in my entity class which is throwing me an exception.

@NamedQueries({

@NamedQuery(name="custrecords",query="SELECT c FROM CustDetails c WHERE lower(c.customerName) = :customerName"),

@NamedQuery(name="allcustrecords",query="SELECT c FROM CustDetails c")

})

Exception in defaultTrace.trc file:

The generated SQL statement is not valid for the underlying database platform (platform no. 99). For more details see attached exception. SQL statement is SELECT "C"."CUSTOMER_NO" "C_CUSTOMER_NO_customerNo","C"."CUSTOMER_NAME" "C_CUSTOMER_NAME_customerName","C"."STREET_1" "C_STREET_1_street1","C"."BUILDING_NAME" "C_BUILDING_NAME_buildingName","C"."CONTACT_NAME" "C_CONTACT_NAME_contactName","C"."POST_CODE" "C_POST_CODE_postCode","C"."CREDIT_LIMIT" "C_CREDIT_LIMIT_creditLimit","C"."COMPANY_REGN_NO" "DISPLAY_1","C"."COUNTRY" "C_COUNTRY_country","C"."VAT_REGN_NO" "C_VAT_REGN_NO_vatRegnNo" FROM "TMP_CUST_DETAILS" "C" WHERE LOWER("C"."CUSTOMER_NAME") = ?#

..............................................

.............................................

com.sap.engine.lib.injection.InjectionException: Injection on field em of instance com.centrica.scv.service.CustomerBean@1d53bb failed. Could not get a value to be injected from the factory.

I am using MAX DB.

Can some one tell me how to resolve this.

Thanks

Vidyadhar

[adrian_goerler]

Hi,

there is one mor option I forgot to mention: you can advise SAP JPA to bypass the Open SQL execution layer for a particular query by setting the hint @QueryHint(name = "com.sap.jpa.query.execution-layer", value = "native"). If you apply this hint, your JPQL query would be mapped to the dialect of the underyling database and the lower function would be available.

For details see this page:

http://help.sap.com/saphelp_nwce72/helpdata/en/4a/0cf02870c540caab611d56220ec0cb/frameset.htm

-Adrian

[Former Member]

Thanks a lot Adrian and Rolf. That was quite insightful. My query was working after adding QueryHint.

Regards

Vidyadhar

[rolf_paulsen]

Hi Vidyadhar,

independent of this restriction of SAP JPA, it is a good idea always to follow Adrians hint because a function like lower in the WHERE-clause "breaks" an index on column CUSTOMER_NAME - even if you build such an index, the database will not use it with the "lower"-function.

Regards

Rolf

[adrian_goerler]

Hi Vidyadhar,

it seems that you are running SAP JPA on Open SQL for Java. Open SQL for Java does not support the lower function:

http://help.sap.com/saphelp_nwce72/helpdata/en/9b/f46cabaa874bc9a82234e8cf1d0696/frameset.htm

Hence, lower cannot be mapped on Open SQL for Java. I reccomend that you add an additional attribute to keep the customerName in a normalized form and do the normalization in Java.

For other restrictions using SAP JPA on the System Data Source and on Open SQL please have a look at this page:

http://help.sap.com/saphelp_nwce72/helpdata/en/1c/0131b37d604f759fdb3dc9779b0b95/frameset.htm

-Adrian