Announcement Announcement Module
Collapse
No announcement yet.
PreparedStatementCallback; bad SQL grammar Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • PreparedStatementCallback; bad SQL grammar

    I am preparing a complex sql statement and have few nested queries. I get the following trace when I try to execute this Query using Spring JDBC Framework. However, when I Copy the query on to TOAD and execute against Oracle ,it gives the results without any error.

    17:06:14,33org.springframework.jdbc.BadSqlGrammarE xception: PreparedStatementCallback; bad SQL grammar [SELECT p.SITE_CODE, p.USERNAME, p.CREATION_DATE, r.IMAGE FROM USER_TABLE p, IMAGE_TABLE r WHERE p.KEY = r.KEY AND ROWNUM < 1002 AND p.VERSION = 'Y' AND p.STATUS IN ( 'OPEN' ,'COMPLETE' ) AND p.KEY IN (select KEY from BIG_IMAGE s WHERE IMAGE_KEY IN (SELECT tempId FROM temp_table q WHERE q.ID=111)) ]; nested exception is java.sql.SQLException: Invalid column name
    Caused by: java.sql.SQLException: Invalid column name
    at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:125)
    at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:162)
    at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:227)
    at oracle.jdbc.driver.OracleStatement.get_column_inde x(OracleStatement.java:3057)
    at oracle.jdbc.driver.OracleResultSetImpl.findColumn( OracleResultSetImpl.java:1861)
    at oracle.jdbc.driver.OracleResultSet.getString(Oracl eResultSet.java:1559)
    at weblogic.jdbc.wrapper.ResultSet_oracle_jdbc_driver _OracleResultSetImpl.getString(Unknown Source)
    at com.pfizer.pgrd.chemistry.enotebook.storage.jdbc.N otebookPageSelector.mapRow(NotebookPageSelector.ja va:40)
    at com.pfizer.pgrd.chemistry.enotebook.storage.jdbc.S earchSelector.mapRow(SearchSelector.java:29)
    at org.springframework.jdbc.object.MappingSqlQuery.ma pRow(MappingSqlQuery.java:64)
    at org.springframework.jdbc.object.MappingSqlQueryWit hParameters$RowMapperImpl.mapRow(MappingSqlQueryWi thParameters.java:115)
    at org.springframework.jdbc.core.RowMapperResultSetEx tractor.extractData(RowMapperResultSetExtractor.ja va:96)
    at org.springframework.jdbc.core.JdbcTemplate$1.doInP reparedStatement(JdbcTemplate.java:565)
    at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:499)
    at org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:553)
    at org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:578)
    at org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:610)
    at org.springframework.jdbc.object.SqlQuery.execute(S qlQuery.java:105)
    at org.springframework.jdbc.object.SqlQuery.execute(S qlQuery.java:115)
    at org.springframework.jdbc.object.SqlQuery.execute(S qlQuery.java:130)
    at com.pfizer.pgrd.chemistry.enotebook.storage.dao.No tebookSearchDAO.getSearchedNotebookPages(NotebookS earchDAO.java:98)
    at com.pfizer.pgrd.chemistry.enotebook.storage.ejb.St orageEJB.searchReactionInfo(StorageEJB.java:421)
    at com.pfizer.pgrd.chemistry.enotebook.storage.ejb.St orageRemote_ptetod_EOImpl.searchReactionInfo(Stora geRemote_ptetod_EOImpl.java:826)
    at com.pfizer.pgrd.chemistry.enotebook.storage.ejb.St orageRemote_ptetod_EOImpl_WLSkel.invoke(Unknown Source)
    at weblogic.rmi.internal.BasicServerRef.invoke(BasicS erverRef.java:477)
    at weblogic.rmi.cluster.ReplicaAwareServerRef.invoke( ReplicaAwareServerRef.java:108)
    at weblogic.rmi.internal.BasicServerRef$1.run(BasicSe rverRef.java:420)
    at weblogic.security.acl.internal.AuthenticatedSubjec t.doAs(AuthenticatedSubject.java:363)
    at weblogic.security.service.SecurityManager.runAs(Se curityManager.java:144)
    at weblogic.rmi.internal.BasicServerRef.handleRequest (BasicServerRef.java:415)
    at weblogic.rmi.internal.BasicExecuteRequest.execute( BasicExecuteRequest.java:30)
    at weblogic.kernel.ExecuteThread.execute(ExecuteThrea d.java:219)
    at weblogic.kernel.ExecuteThread.run(ExecuteThread.ja va:178)

  • #2
    Is Toad connecting with the same user account as Spring?

    Comment


    • #3
      Can we see the actual java DB code and the table DDL?

      Comment


      • #4
        Bad SQL Grammer

        Thanks for the responses, Following is the sample code which i am using to execute a search query. Provided with 2 classes and xml. XML has code to lookup the DataSource Object from the pool defined in local weblogic.
        The pool is configured to use the same user account as that used by TOAD.



        public class SelectOperation extends MappingSqlQuery {

        public SelectOperation (DataSource dataSource, String sqlQuery){
        super(dataSource,sqlQuery);
        }

        //Subclass is required to implement this method
        public Object mapRow(ResultSet resultSet, int rowNumber) throws SQLException{
        //Code for mapping to an object.
        }

        }

        //DAO class which kicks off the select operation
        public class SearchDAO implements InitializingBean{
        DataSource dSource = null;

        public void setDataSource(DataSource ds){
        dSource = ds;
        }

        public void search(){
        String sqlQuery= "SELECT p.SITE_CODE, p.USERNAME, p.CREATION_DATE, r.IMAGE FROM USER_TABLE p, IMAGE_TABLE r WHERE p.KEY = r.KEY AND ROWNUM < 1002 AND p.VERSION = 'Y' AND p.STATUS IN ( 'OPEN' ,'COMPLETE' ) AND p.KEY IN (select KEY from BIG_IMAGE s WHERE IMAGE_KEY IN (SELECT tempId FROM temp_table q WHERE q.ID=111))";

        SelectOperation searcher = new SelectOperation(getDataSource(), sqlQuery);
        searcher.execute();

        }

        }

        Search-Dao.xml
        ---------------
        <?xml version="1.0" encoding="UTF-8"?>
        <!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN/EN" "http://www.springframework.org/dtd/spring-beans.dtd">
        <beans>
        <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryB ean">
        <property name="jndiName" value="test_pool"/>
        </bean>
        <bean class="SearchDAO" id="searchDAO">
        <property name="dataSource">
        <ref local="dataSource"/>
        </property>
        </bean>
        </beans>

        Comment


        • #5
          The query is executing correctly. However something is wrong in your RowMapper.

          Code:
          at com.pfizer.pgrd.chemistry.enotebook.storage.jdbc.N otebookPageSelector.mapRow(NotebookPageSelector.ja va:40)
          at com.pfizer.pgrd.chemistry.enotebook.storage.jdbc.S earchSelector.mapRow(SearchSelector.java:29)
          at org.springframework.jdbc.object.MappingSqlQuery.ma pRow(MappingSqlQuery.java:64)
          at org.springframework.jdbc.object.MappingSqlQueryWit hParameters$RowMapperImpl.mapRow(MappingSqlQueryWi thParameters.java:115)
          at org.springframework.jdbc.core.RowMapperResultSetEx tractor.extractData(RowMapperResultSetExtractor.ja va:96)
          at org.springframework.jdbc.core.JdbcTemplate$1.doInP reparedStatement(JdbcTemplate.java:565)
          As you see it is trying to map the results using your rowmapper. Probably you made a typo in the columnnames you try to retrieve/map.

          Comment


          • #6
            I agree with Marten, can we *really* see the code, specifically the mapRow method.

            Comment


            • #7
              Solved!!

              Thank you Marten and karldmoore, I overlooked the trace and was only trying to find issue with the SQL query or the Connection mechanism. I thought the exception title "Bad SQL Grammer" was kind of misleading.

              The issue was indeed in the code, The code was trying to read a column which was not specified in the select clause.

              Thanks,
              Sridhar

              Comment

              Working...
              X