Commons DBUtils Oracle 11g Prepared Statement

Question

im working with oracle database 11g release 2, and im using apache commons dbutils v1.6, with JDK 8 and tomcat 8.0.30. so im using the QueryRunner and its method and everything works fine if i just concat my variables in text like this

query.query ("select * from table where field = '"+value+"'", rsh);

lately i have been trying to do the query the proper way using prepared statements but to no avail, every time i bind parameters using the query method

query.query ("select ESTREC,LOTE,FECREC from prueba.RECAUDO_ENC where NITREC = ? and ESTREC = ? ORDER BY FECREC DESC", rsh, new Object[]{"1234","PG"});

i get this error for no aparent reason

java.sql.SQLException: ORA-00942: table or view does not exist
  Query: select ESTREC,LOTE,FECREC from prueba.RECAUDO_ENC where NITREC = ? and ESTREC = ? ORDER BY FECREC DESC; Parameters: [1234, PG]

im a 100% sure that the table exists, and user has permissions to the table , also if i do the same query concatenating the params in the query it runs just fine, so im looking for reasons behind this behaviour, is there something wrong in the usage of the method?. also i have read somewhere that there is some problem with BLOB binding using dbutils with oracle, could this be related in someway?

Have you tried passing `new Object[]{1234,"PG"}` as the parameter array (`int` and `String` elements)? It seems that one of your columns is a `NUMBER` and the other is `VARCHAR`. — Mick Mnemonic, Jan 24 '16 at 19:51
they are both strings, and i did tried changing the data types that i was passing. — lacripta, Jan 24 '16 at 19:59

score 2 · Accepted Answer · answered Jan 24 '16 at 20:31

2

Correct query syntax for oracle is:

query.query ("select ESTREC,LOTE,FECREC 
                from prueba.RECAUDO_ENC 
               where NITREC = :P1 and ESTREC = :P2 
               ORDER BY FECREC DESC", 
             rsh, new Object[]{"1234","PG"});

answered Jan 24 '16 at 20:31

Dmitriy

5,525
12
25
38

thx men that was the problem already tried the query and it works just fine, is there some link with this info maybe dbutils docs? i did a search but couldnt find anything – lacripta Jan 25 '16 at 02:32
i dont get it, this syntax does works but not always, sometimes i get the error java.sql.SQLException: ORA-00936: missing expression, why could this be? the query work cancatening the fields, but if i change for the :Pn or the ? it throws the error. so far only 3 query worked this way, anyways thx for the solutin and any feedback is apreciated – lacripta Jan 25 '16 at 03:05
@lacripta Such error usually appears when you have not finished an expression in `select`, `where`, `group by`, or `order by` clause. For example, when you write `select column1, from ...`, or `... where column1 = column2 and order by column3`, or `... where column1 = order by column3` If you assemble your query dynamically, pay attention to this. – Dmitriy Jan 25 '16 at 07:20

score 1 · Answer 2 · answered Oct 04 '18 at 14:01

This error almost turned us crazy. We don't understand why the solution works but here it is. Just double quote and scape all the columns in the SQL sentence:

String sqlStr = "SELECT \"ESTREC\",\"LOTE\",\"FECREC\" " + 
    "FROM prueba.RECAUDO_ENC " +
    "WHERE \"NITREC\" = ? and \"ESTREC\" = ?" + 
    "ORDER BY \"FECREC\" DESC"
query.query(sqlStr, rsh, new Object[]{"1234","PG"});

The funny thing is that we found this error when we added a new constraint to a SELECT sentence. With only one parameter the query would work correctly, but would fail with more than one query param (?).

score 0 · Answer 3 · answered Jan 02 '17 at 04:12

I got the same error with yours.

I think you must be careful the version of jdbc driver. In my case, I solved the problem changing proper jdbc dirver for me.

I misused the jdbc driver. I guess you have a jdbc driver version 12 or higher. Check the version of jdbc driver first in the MANIFEST.MF. It has following lines

    ....
    Implementation-Version: 11.2.0.4.0
    ....

Otherwise, you should have another one for you.

good luck

Commons DBUtils Oracle 11g Prepared Statement

3 Answers3