0

I'm trying to execute a LIKE query in Java using prepared statements but I'm getting the following error 

 ORA-00904: "%12P1A%": invalid identifier

    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;

    try {
        connection = DataSourceFactory.getConnection();

        statement = connection.prepareStatement("select * from users where userID like ?");
        statement.setString(1, "%12P1A%");

        resultSet = statement.executeQuery();

        //....

    }catch (SQLException e) {
        throw new DAOException(e.getMessage());
    } finally {
        DaoUtil.closeAll(connection, statement, resultSet);
    }

May I know why is this incorrect?

For further Information, I'm actually getting '%12P1A%' by some other function so the code is something like 

statement = connection.prepareStatement("select * from users where userID like ?");
statement.setString(1, getValue());

the query parses to something like 

select * from users where userID like '%12P1A%'

but it is throwing MISSING IN or OUT Paramter. Idk why it is not picking the value. Any suggestions?

Iris_geek
  • 393
  • 5
  • 19
  • It's correct; at least the two lines you showed us are correct. Please include the full code. – Tim Biegeleisen Jan 30 '20 at 12:47
  • 3
    Column userID data type? (Is it really a character type?!?) – jarlh Jan 30 '20 at 12:48
  • @TimBiegeleisen that's exactly what I'm trying to run, rest is just executing query in try catch. @ jarlh yup it is a varchar – Iris_geek Jan 30 '20 at 12:51
  • 1
    `statement` must be a `PreparedStatement` and the query must be `statement.executeQuery()` (without repeating the SQL string). Also `statement` should not be a field, but rather a local variable to prevent concurrent usage. – Joop Eggen Jan 30 '20 at 12:51
  • Hint: I am not the Java expert, but I am able to reproduce the issue when a string literal is wrapped with double quotes: `select * from dual where 1 like "%12P1A%"` -- ORA-00904: "%12P1A%": invalid identifier -- It must be passed as `'%12P1A%'` -- If it helps – Popeye Jan 30 '20 at 12:53
  • @Tejash Prepared statements don't work this way. _No_ double quotes would be bound in this case. – Tim Biegeleisen Jan 30 '20 at 12:54
  • No double quotes in SQL are for for column names officially, and single quotes for strings. – Joop Eggen Jan 30 '20 at 12:54
  • CORRECT code, try `select * from users where userID like '%12P1A%'` – Joop Eggen Jan 30 '20 at 12:57
  • "I'm getting the following error" - which error? – Thomas Jan 30 '20 at 12:57
  • Can you just try with this: `statement = connection.prepareStatement("select * from users where userID like '%' || ? || '%'"); statement.setString(1, "12P1A");` -- See this if it helps: https://stackoverflow.com/questions/8247970/using-like-wildcard-in-prepared-statement – Popeye Jan 30 '20 at 13:05
  • "it is throwing MISSING IN or OUT Paramter. " - please note that statements like this don't help that much. If you're asking about exceptions or errors you should always post the entire message along with the stacktrace if possible (post it as a code block to keep the formatting). – Thomas Jan 30 '20 at 14:22

1 Answers1

0

You could extract the value between '% and %' that you are getting from the input and then create your query as - 

statement = connection.prepareStatement("select * from users where userID like '%" + <value-from-some-other-method> + "%'");

If there is an option, ask the method response to be the value directly instead of having it formatted for query parameter.

S B
  • 384
  • 2
  • 8