Forum Stats

  • 3,839,847 Users
  • 2,262,545 Discussions
  • 7,901,076 Comments

Discussions

Named native query and orm.xml

roger_gomes
roger_gomes Member Posts: 10
edited May 14, 2008 10:14AM in TopLink/JPA
Hi All,

I'm trying to use select's inside orm.xml on JPA project. But, when I execute the query, return an error of "ORA-00900: invalid SQL statement". If this select is executed like getEntityManager().createNativeQuery(sql) this work fine.

Then, I would like to know how build a select inside orm.xml. If someone has a example of this.

Any help... please !!!

10ks.

Comments

  • Cdelahun-Oracle
    Cdelahun-Oracle Member Posts: 1,191 Employee
    Hello,

    I'm not sure how you would have defined it, but if you are using createNativeQuery, you hopefully are using the named-native-query tag in xml to define it. Something like
      <named-native-query name="findAllQuery" result-class="com.yourclassname">
        <query>SELECT * FROM TABLE</query>
        </named-native-query>
    will return yourclassname objects.

    Can you post the tag, the code you are using to execute the query as well as the exception stack trace? I believe the exception messages includes the actual sql that gets sent to the database.

    native-query
  • roger_gomes
    roger_gomes Member Posts: 10
    Hi. 10ks for your help.

    Let's go...

    My orm.xml
    <?xml version="1.0" encoding="UTF-8"?>
    <entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm"
    	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    	xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_1_0.xsd"
    	version="1.0">
    
    	<named-native-query name="buscaDadosEleitorSemMae">
    		<query>
    			SELECT E.NUM_INSCRICAO, E.NOM_ELEITOR, L.NOM_LOCAL,
    			B.NOM_BAIRRO, L.DES_ENDERECO, Z.NUM_ZONA, S.NUM_SECAO,
    			M.NOM_LOCALIDADE, U.SGL_UF, P.SGL_PARTIDO FROM
    			((((((((((ELEITOR E JOIN SECAO S ON E.COD_OBJETO_SECAO =
    			S.COD_OBJETO) JOIN LOCAL_VOTACAO L ON S.COD_OBJETO_LOCAL =
    			L.COD_OBJETO) JOIN LOCALIDADE M ON L.COD_OBJETO_LOCALIDADE =
    			M.COD_OBJETO) JOIN ZONA Z ON L.COD_OBJETO_ZONA
    			=Z.COD_OBJETO) JOIN BAIRRO B ON L.COD_OBJETO_BAIRRO =
    			B.COD_OBJETO) JOIN UF U ON U.COD_OBJETO = Z.COD_OBJETO_UF)
    			LEFT JOIN FILIADO F ON F.NUM_INSCRICAO = E.NUM_INSCRICAO)
    			LEFT JOIN LISTA_FILIADO LF ON F.COD_OBJETO_LISTA_FILIADO =
    			LF.COD_OBJETO) LEFT JOIN LISTA LS ON LF.COD_OBJETO_LISTA
    			=LS.COD_OBJETO) LEFT JOIN PARTIDO P ON
    			LS.COD_OBJETO_PARTIDO= P.COD_OBJETO) WHERE
    			(E.COD_FON_NOME_ELEITOR = ?1) AND (E.DAT_NASC = ?2) AND
    			(F.IND_SITUACAO = ?3 OR F.IND_SITUACAO IS NULL) GROUP BY
    			E.NUM_INSCRICAO, E.NOM_ELEITOR, L.NOM_LOCAL, B.NOM_BAIRRO,
    			L.DES_ENDERECO, Z.NUM_ZONA, S.NUM_SECAO, M.NOM_LOCALIDADE,
    			U.SGL_UF, P.SGL_PARTIDO
    		</query>
    	</named-native-query>
    
    </entity-mappings>
    And the stack trace is...
    java.sql.SQLException: ORA-00900: invalid SQL statement
    
    	oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    	oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
    	oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
    	oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
    	oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
    	oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)
    	oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
    	oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3316)
    	oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3361)
    	oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:711)
    	oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:486)
    	oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:437)
    	oracle.toplink.essentials.threetier.ServerSession.executeCall(ServerSession.java:465)
    	oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:213)
    	oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:199)
    	oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:270)
    	oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeSelect(DatasourceCallQueryMechanism.java:252)
    	oracle.toplink.essentials.queryframework.DataReadQuery.executeNonCursor(DataReadQuery.java:105)
    	oracle.toplink.essentials.queryframework.DataReadQuery.executeDatabaseQuery(DataReadQuery.java:97)
    	oracle.toplink.essentials.queryframework.DatabaseQuery.execute(DatabaseQuery.java:609)
    	oracle.toplink.essentials.queryframework.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:536)
    	oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2219)
    	oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:937)
    	oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:909)
    	oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:346)
    	oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.getResultList(EJBQueryImpl.java:453)
    	persistence.dao.EleitorDaoImpl.findDados(EleitorDaoImpl.java:53)
    	persistence.dao.EleitorDaoImpl.findDadosEleitor(EleitorDaoImpl.java:25)
    	business.service.impl.EleitorServiceImpl.executaBuscaSemMae(EleitorServiceImpl.java:62)
    	business.service.impl.EleitorServiceImpl.executaBuscaDadosEleitor(EleitorServiceImpl.java:37)
    	presentation.bean.EleitorBean.buscarEleitor(EleitorBean.java:129)
    	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    	java.lang.reflect.Method.invoke(Method.java:597)
    	org.apache.el.parser.AstValue.invoke(AstValue.java:131)
    	org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:276)
    	com.sun.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:68)
    	com.sun.facelets.el.LegacyMethodBinding.invoke(LegacyMethodBinding.java:69)
    	com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:91)
    	javax.faces.component.UICommand.broadcast(UICommand.java:383)
    	org.ajax4jsf.component.AjaxActionComponent.broadcast(AjaxActionComponent.java:55)
    	org.ajax4jsf.component.AjaxViewRoot.processEvents(AjaxViewRoot.java:316)
    	org.ajax4jsf.component.AjaxViewRoot.broadcastEvents(AjaxViewRoot.java:291)
    	org.ajax4jsf.component.AjaxViewRoot.processPhase(AjaxViewRoot.java:248)
    	org.ajax4jsf.component.AjaxViewRoot.processApplication(AjaxViewRoot.java:461)
    	com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:97)
    	com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:251)
    	com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:117)
    	javax.faces.webapp.FacesServlet.service(FacesServlet.java:244)
    	org.ajax4jsf.webapp.BaseXMLFilter.doXmlFilter(BaseXMLFilter.java:147)
    	org.ajax4jsf.webapp.BaseFilter.doFilter(BaseFilter.java:276)
    	org.ajax4jsf.Filter.doFilter(Filter.java:175)
  • Cdelahun-Oracle
    Cdelahun-Oracle Member Posts: 1,191 Employee
    Hi,

    I still can't see the problem with it, assuming it works when you execute it directly. How are you getting and executing the named query? Also, can you turn logging on and see what the SQL query actually being executed is? Try setting the log level to FINE or FINEST, and the statement should appear right before the error in the log.

    The log level can be set using
    <property name="toplink.logging.level" value="FINE"/>

    Best Regards,
    Chris
  • roger_gomes
    roger_gomes Member Posts: 10
    Hi Chris..

    I don't understand what is happen... but take a look in the code. It's inside my dao.
    query = getEntityManager().createNativeQuery("buscaDadosEleitorSemMae");
    In my test code, that works fine.
    EleitorDao dao = new EleitorDaoImpl();
    
    String sqlx = "SELECT E.NUM_INSCRICAO, E.NOM_ELEITOR, L.NOM_LOCAL, B.NOM_BAIRRO, L.DES_ENDERECO, Z.NUM_ZONA, S.NUM_SECAO, M.NOM_LOCALIDADE, U.SGL_UF, P.SGL_PARTIDO FROM ((((((((((ELEITOR E JOIN SECAO S ON E.COD_OBJETO_SECAO = S.COD_OBJETO) JOIN LOCAL_VOTACAO L ON S.COD_OBJETO_LOCAL = L.COD_OBJETO) JOIN LOCALIDADE M ON L.COD_OBJETO_LOCALIDADE = M.COD_OBJETO) JOIN ZONA Z ON L.COD_OBJETO_ZONA =	Z.COD_OBJETO) JOIN BAIRRO B ON L.COD_OBJETO_BAIRRO = B.COD_OBJETO) JOIN UF U ON U.COD_OBJETO = Z.COD_OBJETO_UF)	LEFT JOIN FILIADO F ON F.NUM_INSCRICAO = E.NUM_INSCRICAO) LEFT JOIN LISTA_FILIADO LF ON F.COD_OBJETO_LISTA_FILIADO = LF.COD_OBJETO) LEFT JOIN LISTA LS ON LF.COD_OBJETO_LISTA =	LS.COD_OBJETO) LEFT JOIN PARTIDO P ON LS.COD_OBJETO_PARTIDO	= P.COD_OBJETO) WHERE (E.NUM_INSCRICAO = #inscricao ) AND (F.IND_SITUACAO = 4 OR F.IND_SITUACAO IS NULL) GROUP BY E.NUM_INSCRICAO, E.NOM_ELEITOR, L.NOM_LOCAL, B.NOM_BAIRRO, L.DES_ENDERECO, Z.NUM_ZONA, S.NUM_SECAO, M.NOM_LOCALIDADE,	U.SGL_UF, P.SGL_PARTIDO";
    
    		Query q = dao.getEntityManager().createNativeQuery(sqlx);
    		q.setParameter("inscricao", "138508490264");
    		List<?> dadosEleitor = q.getResultList();
    		System.out.println(dadosEleitor);
    My logs is started, but I can't see any select...
  • Cdelahun-Oracle
    Cdelahun-Oracle Member Posts: 1,191 Employee
    Hello,

    Common problem. createNativeQuery is building a query "buscaDadosEleitorSemMae" which is not a proper sql statement. You need to use createNamedQuery("buscaDadosEleitorSemMae") inorder for it to look up your native query named "buscaDadosEleitorSemMae". If you turn loging on, you will see that it was trying execute a statement "buscaDadosEleitorSemMae" which caused the error.

    Best Regard,
    Chris
  • roger_gomes
    roger_gomes Member Posts: 10
    edited May 14, 2008 10:14AM
    Man, you are the guy !!! It's work now !!!

    10ks.

    I'll pay a beer to you !!!

    Best regards...
This discussion has been closed.