This discussion is archived
7 Replies Latest reply: Dec 2, 2012 11:59 PM by 976640 RSS

Handling SQLException

976640 Newbie
Currently Being Moderated
Is there any way to fetch the column name name for which SQL exception occurred?

For example a table MyTable is defined as --
UID NOT NULL NUMBER
AGE NUMBER

Now if issue following insert it will raise ORA-01722-
INSERT INTO testchild VALUES(5, 'tes')
as AGE column expects a number, but we are providing a string.
So i want in my JDBC program to get for which column (here age) this error occurred.

Please help on this.
  • 1. Re: Handling SQLException
    Tolls Journeyer
    Currently Being Moderated
    Crossposted at Java Forums.
    http://www.java-forums.org/jdbc/65662-handling-sqlexception.html
  • 2. Re: Handling SQLException
    976640 Newbie
    Currently Being Moderated
    I am not fully aware of the ethics of this forum, but i don't think posting the same problem on multiple forums should create any problem.
    It's just a way to get solution as early as possible.
  • 3. Re: Handling SQLException
    Kayaman Guru
    Currently Being Moderated
    973637 wrote:
    I am not fully aware of the ethics of this forum, but i don't think posting the same problem on multiple forums should create any problem.
    It does if you get an answer on another forum and waste our time by not telling it. That's why it's considerate to tell where else you've posted.
  • 4. Re: Handling SQLException
    gimbal2 Guru
    Currently Being Moderated
    973637 wrote:
    I am not fully aware of the ethics of this forum
    I can tell you that it are the "ethics" of most programming related forums. You're the one asking the question, you should do your very best to complete the question the best that you can. Linking to duplicates of your question in other forums as the very minimum you can do. Keeping an eye on it and updating the thread status of all duplicates you created is of course a natural second.

    Not only gives it people the chance to see what has already been answered to prevent contradiction or to answer that which has already been answered, it allows people with the same question to follow in your steps.
  • 5. Re: Handling SQLException
    976640 Newbie
    Currently Being Moderated
    Thank you all describing the problem of cross posting the same problem.
    Now, i will keep in mind to update each forum if i get the answer.
    Thank you once again.
  • 6. Re: Handling SQLException
    rp0428 Guru
    Currently Being Moderated
    >
    Is there any way to fetch the column name name for which SQL exception occurred?

    For example a table MyTable is defined as --
    UID NOT NULL NUMBER
    AGE NUMBER

    Now if issue following insert it will raise ORA-01722-
    INSERT INTO testchild VALUES(5, 'tes')
    as AGE column expects a number, but we are providing a string.
    So i want in my JDBC program to get for which column (here age) this error occurred.
    >
    Generally speaking - NO, that isn't going to be realistic for most cases.

    And that speaks to the problem you are going to face with your plans in your other thread (SQLException: List of error code for oracle? with providing meaningful error message to your users.

    In that other thread this is what you said you wanted to do
    >
    The purpose is to show user friendly messages based on the error codes.
    >
    But you didn't even do that for us! Why not? All you gave us was 'ORA-01722'. I've been doing this over 30 years and I don't know what that error code means without looking it up. You didn't even provide all of the information that TECHNICAL people need to help with the problem. How do you expect to give a 'user friendly' message that will help?

    Here is the complete text for that message:
    >
    ORA-01722: invalid number

    Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.

    Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation
    >
    Now consider what an EXPERT needs to do to try to find the actual problem assuming you gave them the actual Oracle exception text and not your desired 'user friendly' version.
    1. look up the error code - they will get the info I just posted
    2. scratch their heads -

    Even an expert now has a problem.
    1. What database are we talking about?
    2. What user did this?
    3. What table is involved?
    4. What column is involved?
    5. What value was the user trying to put into that column?

    If the expert can determine the actual query that was executed that caused the exception they can home in on the problem. But there could be 30 columns in that table that might be the issue if the user is inserting or updating multiple columns.

    For a very simple case a business user MIGHT be able to see that they entered 'abc' into a numeric field in an application form but the user won't be able to track down the actual problem in many, if not most, cases.

    Even the expert will have significant trouble if they can't determine what the actual query was. The expert often has to work with the user to find out what app screen they were on, what data they entered, what button they clicked, etc. Even then the actual query that the app submits to the database probably isn't logged anywhere and it may even be difficult to determine from the app code what table is actually being used; especially if an ORM tool like Hibernate is in the middle of things.

    As others have already suggested you need to cross-link your own posts so everyone can follow what you are trying to do and see that this thread is related to the other thread whose link I gave above.






    Do you think that is helpful
  • 7. Re: Handling SQLException
    976640 Newbie
    Currently Being Moderated
    Thank you very much for the detailed explanation.
    What you told, it generally holds true, but unfortunately in my case this is not.

    The business team, those who will use the tool for which i am writing this code, is fully aware of DB structure.
    So i would prefer to call them Datateam instead Business user.

    As part of any tool it tries to log everything as much as possible. So same applies here.
    The design of tool in such way that we can't much modify existing code in sake of cost saving.
    So the basic idea is to use use API or some other way to provide self explanatory log messages.

    Now come back to the original problem. Handling ORA-01722 in java code.
    One idea came in my mind (though it was not so scalable)--
    "whenever we write statement for inserting data to particular column, set a string variable with name of that column before insert statement and mark it null after the insert statement. In the catch where handling ORA-01722 get the name of column by string variable ad show it on log"
    Example-

    String errorColumnName = "";
    String query = "insert into tablechild(UID, age) values(?, ?)";

    //Loop through iterator of VO object
    errorColumnName = "ID"
    statement.setLong(i++, VO.getid());
    errorColumnName = "";

    errorColumnName = "AGE"
    statement.setLong(i++, "age_in_string_which_will_cause_ORA-01722");
    errorColumnName = "";

    Though i am bad in pen down to explain the things, but hope it will give some sight of the problem on which i am working on.

    Thank you once again for looking into this problem and providing me sincere responses.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points