@NamedNativeQuery:SQLGrammarException could not execute quer

jaggsmca325

Hi,

I have defined a Native query in my entity class as below

Code:

@NamedNativeQuery(name="Customer.findByaccountNo", 
      query="SELECT TO_CHAR(TIME_STAMP,'DDMONYYYY:HH24MISS') TIME_STAMP," +
            "MERCH_NBR, FDB_SEQ_NBR, OWNER_IIC, ACQUIRER, CHARGE_AMT," +
            "TO_CHAR(CHARGE_DATE,'DDMONYYYY:HH24MISS') CHARGE_DATE, " +
            "CHARGE_CURRENCY, MERCH_NAME, CITY, GEOCODE, INTES," +
            "CHARGE_TYPE, TYPE_OF_CHARGE, REFERENCE_NBR, DATE_TYPE," +
            "APPROVAL_CODE, AUTH_NBR, FUNCTION_CODE, BILLING_CCY, BILLING_AMT, DOLLAR_AMT," +
            "RECAP_ID, RECAP_DET_SEQ, DISPOSITION, AMT_WRITE_OFF, AMT_MERCH_CHRG, AMT_CUST_CHRG, AMT_RECOVERED," +
            "FRAUD_STATUS, TXN_SOURCE, METHOD_OF_ENTRY " +
            "FROM MYDB.CUSTOMER " +
            "WHERE ACCOUNT_NBR = ? AND CHARGE_DATE >= ? AND CHARGE_DATE <= ? ORDER BY TIME_STAMP",
            resultClass=Customer.class),

in my DAO i am using as below

Code:

Query query = this.entityManager.createNativeQuery("Customer.findByaccountNo", Customer.class);
query.setParameter(1, accountNbr);
query.setParameter(2, chargeDateFrom);
query.setParameter(2, chargeDateTo);
return query.getResultList();

Below is the exception trace i got....

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1387)
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1315)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:255)
at com.dcisc.gui.gfd.persistence.dao.FdbFraudTranDataDAO.getFraudDataByAcct(FdbFraudTranDataDAO.java:80)
at com.dcisc.gui.gfd.persistence.entity.FdbFraudTranDataIntegrationTest.testGetFraudDataByAcct2(FdbFraudTranDataIntegrationTest.java:121)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
at java.lang.reflect.Method.invoke(Method.java:599)

DrRakha

Dear jaggsmca325 ,
I notice that the query is written in a complex way ? , you should put it thins why ? Does the exception log ended on the above part ?

i have doubts about this part .
query.setParameter(2, chargeDateTo);

i think it should be
query.setParameter(3, chargeDateTo);

Java

C/C++

PHP

C#

HTML

CSS

ASP

Javascript

JQuery

AJAX

XSD

Python

Matlab

R Scripts

Weka

@NamedNativeQuery:SQLGrammarException could not execute quer

Topic Tags