Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2007
    Posts
    10

    Unanswered: Java to Oracle Update Error

    I am getting the following error trying to update a string in an Oracle 9.2 database from java:

    [Oracle][ODBC]Option type out of range <0>

    This occurs when calling the resultSet.updateRow() command.

    Using java jdk 1.4 and the sun.jdbc.odbc.JdbcOdbcDriver

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    How do we know what's wrong if you don't show us what you are doing? Show us the SQL code you are using, otherwise you'll be forced to wait for someone who has a working crystal ball to find out what your problem is (your question is equivalent to the sentence: "My xyz car with a Diesel engine is not working, how do I fix it?)

    Btw: Why do you use the JDBC/ODBC driver?
    It's buggy and slow. You should use Oracle's (thin) JDBC driver.

  3. #3
    Join Date
    Aug 2007
    Posts
    10
    I am updating a string (varchar2) and if there is a way to print the exact sql that the ResultSet uses I am unaware of it. If someone supplies the way I'll post the string. However the select statement is:

    Code:
    SELECT field_a, field_b, org_id, site_cd, language, form_nbr, 
    substr(REPLACE(part_nbr,'=R',RPAD(' ',25)),1,25) part_nbr, part_rev_lvl 
    FROM tbl1, tbl2 
    WHERE form_nbr = 'MBD' 
    AND language = 'FTP' 
    AND DATE_TS > TO_CHAR(SYSDATE-21,'YYYYMMDD') 
    AND ord_nbr = substr(key_data, 1, 6) 
    AND sub_ord_nbr = substr(key_data, 7, 3)
    The select works fine and I am trying to update the language field which resides in tbl1. It may have to do with two tables being used.

    I.E.
    Code:
    if (rs.next()) {
    rs.updateString ( "language", "new" );
    rs.updateRow(); // execution fails here
    }
    Thanks for the Oracle Driver advice but I cannot use it.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If 'date_ts' is a DATE column, this line is horrible
    Code:
    AND DATE_TS > TO_CHAR(SYSDATE-21,'YYYYMMDD')
    Why do you compare dates with strings? It should be
    Code:
    AND date_ts > SYSDATE - 21
    As of your Java problem - sorry, I don't speak Java (so, if above doesn't solve your problem ... wait for another answer).

  5. #5
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    rs.updateRow(); // execution fails here
    Well, you are building a ResultSet from two tables. Without even knowing the error message (that you didn't supply, and which most probably contains valuable information about the cause of the error) I cannot image how that ResultSet can be updateable (because of the join).

    Btw: implemenation of updateable ResultSet is done in the driver. So even if you manage to get a ResultSet that could be updated, I doubt that the JDBC/ODBC bridge supports it.

  6. #6
    Join Date
    Aug 2007
    Posts
    10
    I did supply the error message. Here I'll do it again:

    [Oracle][ODBC]Option type out of range <0>

    There. Thanks. Joined updates works with oracle and visual basic result sets by the way. Which is unfortunately what I'm converting to Java.

    And Date_ts is not a date. These tables, which I do not have control over, do not have primary keys or I'd try to do the update without a join. If there is another way to guarantee an update will only effect one row then that would be very helpful.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    More about DATE_TS: its name, as well as TO_CHAR(SYSDATE-21,'YYYYMMDD') strongly suggest that it is about date. If it is stored into the character column, well, it is a poor design.

  8. #8
    Join Date
    Aug 2007
    Posts
    10
    Ha. That is because DATE_TS does represent a date. However it IS a varchar2. Of very bizarre format at that. I agree about the poor design, but we do not all get to choose the systems that we work with.

    I suppose it is best to think of it as challenging. However I doubt it has anything to do with my problem as I am not interested in updating that field.

  9. #9
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by p1px
    I did supply the error message. Here I'll do it again:
    Sorry, didn't see it in the first post
    I would very much suspect that this is a problem of the JDBC/ODBC bridge.

    Joined updates works with oracle and visual basic result sets by the way. Which is unfortunately what I'm converting to Java.
    Then you should definitely try the real JDBC driver and don't waste your time using the ODBC bridge. If VB (I assume that is OLEDB?) supports it, then Oracle's JDBC driver should support it as well.

    Btw: why can't you use Oracle's driver?

  10. #10
    Join Date
    Aug 2007
    Posts
    10
    The easiest way to describe the reason for the JDBC bridge is to say that my supervisor wants it and that there is probably no logical reason.

  11. #11
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Ouch. I know that. But I think there is one reason to not use the JDBC/ODBC bridge: it obviously does not work properly

    And besides: is he aware that the thin driver does not need any client installation which might make the rollout of your Java app a lot easier (no Oracle Client to install...)

    Good luck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •