#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    68
    Rep Power
    16

    jdbc / mysql / errorhandling help


    hi im not sure which forum this should go in so ive posted it in the mysql and java forums...

    basically im having trouble catching errors. Ive got gui which is connected to a mysql database via jdbc.

    i have a button on my gui which states "get user". This button retieves user information from the database it also calls a method getQuestions() which calls a join query....(ive left out all the resultset object stuff for clarity)...........

    try{
    "SELECT users_ans.user_id, users_ans.q_id, question.q_text,question.q_type,users_ans.a_text,user.username "
    + "FROM users_ans, user, question "
    + "WHERE username = '" + userList.getSelectedValue() + "'"
    + "AND users_ans.user_id = user.user_id "
    + "AND question.q_id = users_ans.q_id");

    // Here i iterate through the result set gathering the relevent information i need.
    }catch(SQLException selectException) {
    displaySQLErrors(selectException);
    }


    My problem is when a particular user may not have content in the column in the database. (ie that particular column is empty or null) when i press "get user"my gui crashes / locks up.
    Im trying to catch the exceptions but im kinda stuck.... i hope this is explained well enough .....
    any help would be appreciated to shed some light on this for me ...
    thanks
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    68
    Rep Power
    16
    ive done some fiddlin and i think the problem isnt the query ...
    the query works ok its what i do with the result set after...
    ive tried calling a rs.getString when i know that its empty and i got this.....

    SQLException: Illegal operation on empty result set
    SQLState: S1000
    VendorError: 0

    so basically i think i need to know how do i negotiate null values with jdbc ?

    thanks
  4. #3
  5. Prom night: 1973
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Dec 2001
    Posts
    1,152
    Rep Power
    28
    You need to handle a result with 0 rows. You can do this in an if or a while statement using the next() method of a result set. Since you didn't post any code, I can only provide a rough example:
    Code:
    ArrayList questions = new ArrayList();
    Question question = null;
    ResultSet rs = statement.executeQuery(sql);
    while (rs.next()) {
      question = new Question(rs.getObject("UserID"), rs.getObject("QuestionID"), rs.getString("Q_Text"));
      questions.add(question);
    }
    In this example, if no rows are returned from MySQL, the while loop is never entered and it won't generate the error you're getting.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    68
    Rep Power
    16
    sorry heres my method in full........i basically get question and answer strings from the query add to arraylists then get the strings....

    i think the error is occuring because of the do while loop .....
    maybe i dont need it but i thought you had to check to make sure the result set doesnt reach the end...... ill try just a while loop like u suggested though......
    thanks


    private void getQuestions() {
    try{
    clearQAText();
    questionList = new ArrayList();
    answerList = new ArrayList();
    Statement statement = connection.createStatement();
    ResultSet rs = statement.executeQuery(
    "SELECT users_ans.user_id, users_ans.q_id, question.q_text, question.q_type,users_ans.a_text,user.username "
    + "FROM users_ans, user, question "
    + "WHERE username = '" + userList.getSelectedValue() + "'"
    + "AND users_ans.user_id = user.user_id "
    + "AND question.q_id = users_ans.q_id");
    do{
    if(rs.next()) {
    // here try to intercept null values but i dont think its doing any thing
    if(rs.getString("q_text") == null) {
    System.out.println("No values");

    } else {
    questionList.add(rs.getString("q_text"));
    answerList.add(rs.getString("a_text"));


    System.out.println(rs.getString("q_text"));
    System.out.println(rs.getString("a_text"));

    }
    }
    }while(!rs.isAfterLast());

    /********* Extract question and answer text from appropriate arraylists ************/

    try{

    a1Text.setText((String)answerList.get(0));
    a2Text.setText((String)answerList.get(1));
    a3Text.setText((String)answerList.get(2));
    a4Text.setText((String)answerList.get(3));
    a5Text.setText((String)answerList.get(4));
    }catch(Exception e){}
    try {
    q1Text.setText((String)questionList.get(0));
    q2Text.setText((String)questionList.get(1));
    q3Text.setText((String)questionList.get(2));
    q4Text.setText((String)questionList.get(3));
    q5Text.setText((String)questionList.get(4));
    }catch(Exception e) {}

    }catch(SQLException selectException) {
    displaySQLErrors(selectException);
    }

    }

IMN logo majestic logo threadwatch logo seochat tools logo