A character string to numeric conversion failed while using syntax like 'OPE' =?

Question

I try to run this query using Spring JDBC Template

  public static String FIND_CNC_OPE_GRAPPE_BY_FCT_ID = 
        "SELECT "
                    +"EXPO.COD_NAT_XPN "
                    +", EXPO.IDF_TEC_XPN "
                    +", EXPO.IDF_FCT_XPN_RIS "
                    +", EXPO.DAT_PRM_IPE_LC_CEC  "
        +"FROM  T_CCA_EXPO EXPO "
        +"WHERE "
        + " 'OPE' = ? ";

for this i use this java code :

  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.util.ArrayList;
  import java.util.List;

  import org.apache.commons.lang3.StringUtils;
  import org.springframework.beans.factory.annotation.Autowired;
  import org.springframework.jdbc.core.JdbcTemplate;
  import org.springframework.stereotype.Repository;

  import lombok.extern.slf4j.Slf4j;

  @Repository
  @Slf4j
  public class ContractGrappeRepository implements ContractGrappeInterfaceRepository {

       static String query = GrappeQueryConstant.FIND_CNC_OPE_GRAPPE_BY_FCT_ID;

       @Autowired
       private JdbcTemplate baseJDBCTemplate;

       private Connection dbConnection;

       private PreparedStatement statement = null;

       private ResultSet dealResultSet;

       @Override
       public ResultSet findGrappeByDealFctID() {

       try {
          dbConnection = baseJDBCTemplate.getDataSource().getConnection();
          statement = dbConnection.prepareStatement(query);
          statement.setString(1,"AKA");
          dealResultSet = statement.executeQuery();
       } catch (SQLException e) {
          throw new RestructNumberException("Exception while fetching contracts",e);
       }

       return null; // TO MODIFY
   }

 }

When the compilator arrive to this line dbConnection.prepareStatement(query); i got this error message ;

   java.sql.SQLException: [Teradata Database] [TeraJDBC 16.00.00.24] [Error 3535] [SQLState 22003] A character string failed conversion to a numeric value.
at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDatabaseSQLException(ErrorFactory.java:309)
at com.teradata.jdbc.jdbc_4.statemachine.ReceiveInitSubState.action(ReceiveInitSubState.java:103)
at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.subStateMachine(StatementReceiveState.java:311)
at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.action(StatementReceiveState.java:200)
at com.teradata.jdbc.jdbc_4.statemachine.StatementController.runBody(StatementController.java:137)
at com.teradata.jdbc.jdbc_4.statemachine.StatementController.run(StatementController.java:128)
at com.teradata.jdbc.jdbc_4.TDStatement.executeStatement(TDStatement.java:389)
at com.teradata.jdbc.jdbc_4.TDStatement.prepareRequest(TDStatement.java:576)
at com.teradata.jdbc.jdbc_4.TDPreparedStatement.<init>(TDPreparedStatement.java:128)
at com.teradata.jdbc.jdk6.JDK6_SQL_PreparedStatement.<init>(JDK6_SQL_PreparedStatement.java:30)
at com.teradata.jdbc.jdk6.JDK6_SQL_Connection.constructPreparedStatement(JDK6_SQL_Connection.java:82)
at com.teradata.jdbc.jdbc_4.TDSession.prepareStatement(TDSession.java:1330)
at com.teradata.jdbc.jdbc_4.TDSession.prepareStatement(TDSession.java:1374)
at com.teradata.jdbc.jdbc_4.TDSession.prepareStatement(TDSession.java:1360)
at com.teradata.jdbc.jdbc.ManagerConnectionBase.prepareStatement(ManagerConnectionBase.java:294)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)

When i run my query on Terdata sql assistant, i don't have any error. but when i change in my query the where clause

    +"WHERE "
    + " 'OPE' = ? ";

to

    +"WHERE "
    + " 'OPE' = EXP.COD_NAT_XPN ";

I don't have any errors,

Why java don't accept this syntax 'OPE' = ?

I am using Spring boot 1.5.9.RELEASE

Teradata version 16.00.00.24

Answer 1

Change statement.setString(1,"AKA") to setInt

Edit: at

statement = dbConnection.prepareStatement(query);

you are getting error because of following rule link where TeraData is trying to compile your SQL query.

For your issue I found solution here. What you should do is change query as below:

" 'OPE' = cast(? as number) ";

Answer 2

Putting JDBC objects in ContractGrappeRepository's fields is dangerous; they must be closed, and every instance must not be shared.

   try (Connection dbConnection = baseJDBCTemplate.getDataSource().getConnection();
          Prepared statement = dbConnection.prepareStatement(query)) {
      statement.setString(1,"AKA");
      try (ResultSet dealResultSet = statement.executeQuery()) {
          while (dealResultSet.next()) {
              ...
          }
          return ...
      }
   } catch (SQLException e) {
      throw new RestructNumberException("Exception while fetching contracts",e);
   }

There is a conceptual error: ? is placeholder for a value, the result being

'OPE' = 'AKA'               ***WRONG***

So you need for a column comparison concatenate/String.format the SQL string:

     statement = dbConnection.prepareStatement(query.replace("?", "AKA"));