1

I have a query which works fine in local database. I just moved into a remote database and the error occurred as follow:

DAO:

public String changeLecture(String CoursesID, String strDate, String strTime, String endTime, String venue, NewCourseInfoBean courseInfo) {

      //preparing some objects for connection 
      Connection currentCon = null;
      ResultSet rs = null;
      PreparedStatement pstmt = null;

      String result = "";

       try 
           {
              //connect to DB 
              currentCon = ConnectionManager.getConnection();
              pstmt = currentCon.prepareStatement("select * from course_info where course_code=? and c_date=? and start_time=? and end_time=? and venue=?", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
              pstmt.setString(1, CoursesID);
              pstmt.setString(2, strDate);
              pstmt.setString(3, strTime);
              pstmt.setString(4, endTime);
              pstmt.setString(5, venue);
              pstmt.executeQuery();
              rs = pstmt.getResultSet();

              //check whether the class is existed
              if (rs.next()) 
              {

                  rs.updateObject("c_date", courseInfo.getChangeC_date());
                  rs.updateObject("start_time", courseInfo.getChangeStart_time());
                  rs.updateObject("end_time", courseInfo.getChangeEnd_time());
                  rs.updateObject("venue", courseInfo.getChangeVenue());
                  rs.updateRow();
                  result = "Lecture slot updated successfully!";
              }else{
                  result = "You do not have lecture with the details provided in 'current lecture slot' fields. Please check your schedule.";

              }
            } 
        //catch
        //some exception handling

       return result;

   }

error message:

 [MySQL][ODBC 5.3(a) Driver]
[mysqld-5.5.43-0ubuntu0.14.04.1]
Table 'sql679933.COURSE_INFO' doesn't exist

Whenever I provide a right dataset, the data in table course_info will be updated. However, the programs stops at line pstmt.executeQuery(); if wrong dataset is provided.

Eg.

correct data: select * from course_info where course_code='SSK3100'..... (works fine)

incorrect data: select * from course_info where course_code='SSK333'......(cannot execute query)

This query works fine in local db, so what's goes wrong here? :( Please help.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Sue
  • 125
  • 1
  • 2
  • 14
  • Read the error message. It doesn't have anything to do with wrong parameters. It simply tells you that the table COURSE_INFO doesn't even exist. Whatever parameter you pass, selecting from an unexisting table will never work. – JB Nizet Jun 07 '15 at 08:58
  • Hi @JBNizet, that's the weird part I've met! :( If all the parameters provided are all perfect, data in the `course_info` table will be updated. (Means this table exists) Error only appear if wrong parameter is passed. :( – Sue Jun 07 '15 at 09:07
  • Is there any issue in this situation? @JBNizet – Sue Jun 07 '15 at 09:08
  • 1
    Your question doesn't make much sense. You're executing a *select* query. A select query doesn't update information. It selects information. Post the complete stack trace of the exception you get. – JB Nizet Jun 07 '15 at 09:10
  • Sorry @JBNizet, that's my mistake. Here's my full `update` code. And I only get 1 exception which is stated at the question. :) – Sue Jun 07 '15 at 09:13
  • I don't care about the number of exceptions you get. I care about the full stack trace of the exception you get. – JB Nizet Jun 07 '15 at 09:14
  • @JBNizet I'm so sorry.:( Do you mean this? `Log In failed: An Exception has occurred! java.sql.SQLException: [MySQL][ODBC 5.3(a) Driver][mysqld-5.5.43-0ubuntu0.14.04.1]Table 'sql679933.COURSE_INFO' doesn't exist` – Sue Jun 07 '15 at 09:25
  • Or this? `catch (Exception ex) { System.out.println("Log In failed: An Exception has occurred! " + ex); }` Sorry I'm still learning. – Sue Jun 07 '15 at 09:27
  • No. That's not a stack trace. Read http://stackoverflow.com/questions/3988788/what-is-a-stack-trace-and-how-can-i-use-it-to-debug-my-application-errors. You can get it by calling `e.printStackTrace()` on the exception `e` you get. – JB Nizet Jun 07 '15 at 09:28
  • Unlike forum sites, we don't use "Thanks", or "Any help appreciated", or signatures on [so]. See "[Should 'Hi', 'thanks,' taglines, and salutations be removed from posts?](http://meta.stackexchange.com/questions/2950/should-hi-thanks-taglines-and-salutations-be-removed-from-posts). BTW, it's "Thanks in advance", not "Thanks in advanced". – John Saunders Jun 09 '15 at 01:49

0 Answers0