2

I have a problem with this query when I pass it to an oracle dbms

SELECT * FROM RD_RBF WHERE REQUEST_ID = 'S2N-F01-000000000001'

because of the dashes in the string the jvm return me this exception

java.sql.SQLException: Fail to convert to internal representation

How can I pass this query to oracle correctly? Thanks a lot

P.S. I'm not shure of the code because I'm using Talend software that generates automatically the code of components but I can post part of the code above

 String dbquery_tOracleInput_1 = "SELECT * FROM RD_RBF WHERE REQUEST_ID = 'S2N-F01-000000000001'";

java.sql.ResultSet rs_tOracleInput_1 = null;
    try{
        rs_tOracleInput_1 = stmt_tOracleInput_1.executeQuery(dbquery_tOracleInput_1);
        java.sql.ResultSetMetaData rsmd_tOracleInput_1 = rs_tOracleInput_1.getMetaData();
        int colQtyInRs_tOracleInput_1 = rsmd_tOracleInput_1.getColumnCount();
Leonardo
  • 21
  • 2
  • 2
    can you show us your code please? – Youcef LAIDANI Jun 01 '18 at 13:18
  • Voting to close as unclear, because while your question is interesting, we can't answer it without seeing your Java code. – Tim Biegeleisen Jun 01 '18 at 13:22
  • I modified the post inserting part of the code. I'm using Talend software thet automatically generates the code of the components I use. So probably I can't post all the code but I've read somewhere that the problem is made by the dashes in the string because in oracle dashes are special characters. In fact if I revome dashes the jvm doesn't return an exception. – Leonardo Jun 01 '18 at 13:30
  • 1
    @Leonardo The Oracle RDBMS doesn't care about the dashes inside a string literal, so the issue is somewhere else. – GriffeyDog Jun 01 '18 at 13:38
  • 1
    Chances are your table has an type datatype or something not the "-" causing this. See :> https://stackoverflow.com/questions/4268093/how-can-i-prevent-this-exception-java-sql-sqlexception-fail-to-convert-to-inte – Kris Rice Jun 01 '18 at 13:39
  • @GriffeyDog thank you for the answer. But why if I remove the dashes the propgram runs correctly? I don't understand – Leonardo Jun 01 '18 at 13:42
  • @Leonardo All I'm saying is the issue isn't with the query you've shown and Oracle, assuming `RD_RBF.REQUEST_ID` is an Oracle datatype like `varchar2`. Try running your query directly in Oracle (using SQLPlus, TOAD, etc.). – GriffeyDog Jun 01 '18 at 13:56
  • @GriffeyDog I tried to execute the query directly in Oracle using SQL Developer and everything works well. Yes, the datatype of 'REQUEST_ID' is VARCHAR2. But I'm not understanding why removing the dashes in the java query everything works I tried also to use double slashes \\ before the dashes and everything works. – Leonardo Jun 01 '18 at 14:06
  • @Leonardo So now you have proof that's it's not an issue with the Oracle database. It's somewhere on the Java side. Where, I don't know. – GriffeyDog Jun 01 '18 at 14:09
  • My take is you are retrieving metadata, and that's where the issue is. Not in the query itself. As far as the code you have shown you still haven't retrieved any row, right? Try `rs_tOracleInput_1.next()` to read the first row. – The Impaler Jun 01 '18 at 14:09
  • @GriffeyDog I tried to substitute the 0000001 whith AAAAAA characters and now the jvm returns no exceptions. Everything works. This is a mistery for me. – Leonardo Jun 01 '18 at 14:16
  • Can you post the full stack trace, please? Or at least the first few lines. – ewramner Jun 01 '18 at 14:16
  • @TheImpaler The ridiculous fact is that I can read the code but I can modify nothing. I just can add components with some custom code but I've not the effectively control af all the mechanism. – Leonardo Jun 01 '18 at 14:19
  • Interesting. WHat happens if you wrap a "string" function around the literal like `SUBSTR('S2N-F01-000000000001', 1)`, or try `SELECT * FROM RD_RBF WHERE REQUEST_ID = 'S2N' || CHR(45) || 'F01' || CHR(45) || '000000000001'`? – TenG Jun 01 '18 at 16:32
  • can you validate that dash is actually dash/hyphen/minus ie ASCII 45? There are other characters that looks like dash, especially if this id was copied from ms-word. If it is not dash then you have to either change it to dash or fix encoding setting. – gagan singh Jun 01 '18 at 23:48
  • @Leonardo , there will be no data after removing 'dashes' from the query so it might ran well. Have you check the datatypes in oracle and the one which are you assigning in the talend component. – Vish Jun 04 '18 at 07:34

1 Answers1

0

Use toraclerow component.

Query as follows in component:

"SELECT * FROM RD_RBF WHERE REQUEST_ID = ?" Go to advance setting, select use prepared statement and add 1 paarmeter index. also select the propagate Query's record set.

tOracleRow_1 -----> tParseRecordSet----->

Sravanth Kothuri
  • 211
  • 1
  • 3
  • 11