Results 1 to 5 of 5
  1. #1
    santa's Avatar
    santa is offline Senior Member
    Join Date
    Nov 2009
    Location
    Sweden
    Posts
    208
    Rep Power
    5

    Default I have 2 questions =P

    First question: How can i get rid of this exception ? its comming from the update mehod ... but i dident close anything =/

    Java Code:
    java.sql.SQLException: Operation not allowed after ResultSet closed
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
            at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:794)
            at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:7145)
            at Database.addBooks(Database.java:167)
            at GUI$4.actionPerformed(GUI.java:137)
            at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
            at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
            at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
            at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
            at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
            at java.awt.Component.processMouseEvent(Component.java:6267)
            at javax.swing.JComponent.processMouseEvent(JComponent.java:3267)
            at java.awt.Component.processEvent(Component.java:6032)
            at java.awt.Container.processEvent(Container.java:2041)
            at java.awt.Component.dispatchEventImpl(Component.java:4630)
            at java.awt.Container.dispatchEventImpl(Container.java:2099)
            at java.awt.Component.dispatchEvent(Component.java:4460)
            at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4577)
            at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4238)
            at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4168)
            at java.awt.Container.dispatchEventImpl(Container.java:2085)
            at java.awt.Window.dispatchEventImpl(Window.java:2478)
            at java.awt.Component.dispatchEvent(Component.java:4460)
            at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
            at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
            at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
            at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
            at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
            at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
            at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)
    Secound question: Is there a quick way to convert my querys to " if database excist only check for diffirences in tables,columns and row?


    Java Code:
    import javax.swing.*;
    import java.sql.*;
    import java.util.Date;
    import java.text.DateFormat;
    import java.text.SimpleDateFormat;
    import java.util.Calendar;
    import java.util.*;
    
    
    public class Database {
    private static Statement stmt;
    public static String database,allt3[] = new String[10];
    public static int card,count=0;
    public static boolean blank = false,login = false,update = false;
    public static ArrayList<String> kvitto = new ArrayList<String>();
    private static int logcardnr,ty=1;
    
     public static void connectDatabase(){
    
     Connection con = null;
     if(database.equals("")){
         JOptionPane.showMessageDialog(null, "Can't run without database!");
         System.exit(0);}
    
        try {
          Class.forName("com.mysql.jdbc.Driver").newInstance();
          con = DriverManager.getConnection("jdbc:mysql://",  // Using anonymous cause i feel like it =)
            "root", "test");
    
    
          if(!con.isClosed())
            System.out.println("Duude youre connected " +
              "trough TCP/IP...");
    
          stmt = con.createStatement();
          try{
          stmt.executeUpdate("USE " + database);
          System.out.println("Used old database!");
            }catch (Exception e){
               stmt.executeUpdate("CREATE DATABASE " + database);
               stmt.executeUpdate("USE " + database);
               System.out.println("Database created!");
            }
          stmt.executeUpdate ("CREATE TABLE Borrowers ("
                  + "cardnr INT,"
                  + "name CHAR(60), "
                  + "street CHAR(40), "
                  + "zip INT,"
                  + "town CHAR(50))");
    
                  stmt.executeUpdate ("CREATE TABLE GoodBooks ("
             + " ISBN long,"
            + " CopyNumber INT,"
            + " Title VARCHAR(60),"
            + " Author VARCHAR(30),"
            + " Publisher VARCHAR(50),"
            + " Year INT,"
            + " Statistics INT,"
            + " BorrowDate INT,"
            + " ReturnDate INT,"
            + " LibraryCardNumber INT)");
       for(int j = 0 ; j < Borrowers.borrow.length; j++){
                     stmt.executeUpdate("INSERT INTO Borrowers "
                             +"(cardnr, name, street, zip, town) "
                             + "VALUES "
                             + "('"+ Borrowers.borrow[j].LibraryCardNumber + "',"
                             + "'"+ Borrowers.borrow[j].Name + "',"
                             + "'"+ Borrowers.borrow[j].Street + "',"
                             + "'"+ Borrowers.borrow[j].ZipCode + "',"
                             + "'"+ Borrowers.borrow[j].Town + "')");
            }
    
    
    
    
    
    
    
    for(int j = 0 ; j <Bookobjects.bookO.length ; j++){
                     stmt.executeUpdate("INSERT INTO goodbooks "
                             +"(ISBN, CopyNumber, Title, Author, Publisher, Year, Statistics,"
                             + " BorrowDate, ReturnDate, LibraryCardNumber) "
                             + "VALUES "
                             + "('"+ Bookobjects.bookO[j].ISBN + "',"
                             + "'"+ Bookobjects.bookO[j].CopyNumber + "',"
                             + "'"+ Bookobjects.bookO[j].Title + "',"
                             + "'"+ Bookobjects.bookO[j].Author + "',"
                             + "'"+ Bookobjects.bookO[j].Publisher + "',"
                             + "'"+ Bookobjects.bookO[j].Year + "',"
                             + "'"+ Bookobjects.bookO[j].Statistics + "',"
                             + "'"+ Bookobjects.bookO[j].BorrowDate + "',"
                             + "'"+ Bookobjects.bookO[j].ReturnDate + "',"
                             + "'"+ Bookobjects.bookO[j].LibraryCardNumber + "')");
            }
      
    
    
    
    
        } catch(Exception e) {
          System.err.println("Fail: " + e.getMessage());
          System.out.println("Fail: " + e);
        } /*finally {
          try {
            if(con != null)
               System.out.println("Connecton succesfully closed!");
              con.close();
          } catch(SQLException e) {}
        }*/
    
    
        }
    public static void findUser(){
       
        try{
            if(GUI.tmp.equals("")){
                JOptionPane.showMessageDialog(null, "I don't accept blanks! Enter a number.");
             blank = true;
            }
          
         if(!blank){
            card = Integer.parseInt(GUI.tmp);
            ResultSet RS = stmt.executeQuery("SELECT * FROM borrowers WHERE cardnr = '"+ card + "'");
              
                while (RS.next()) {
                   count++;
                     JOptionPane.showMessageDialog(null,"Your name is "+RS.getString(2)
                             + " ! You can now borrow books!" );
                     login = true;
                     logcardnr = card;
                     if(ty == 1){
                     kvitto.add("YOUR RECEIPT: \n "+ "Name: " + RS.getString(2)+ " Library card number: "
                             +RS.getString(1)+ " Street: " +RS.getString(3)
                             + " Zip-Code: " + RS.getString(4)+ " Town: " +RS.getString(5));
                    ty++;
                     }
                    
                         }
            if(count == 0){
                JOptionPane.showMessageDialog(null, "We were unable to find you."
                        + " Please contact the expedition");
                login = false;
             }
             }
        }catch(Exception e) {
          System.err.println("Fail: " + e.getMessage());
         e.printStackTrace();
    
        }
        blank = false;
        count=0;
        }
    public static void addBooks(){
    
        try{
        ResultSet RS = stmt.executeQuery("SELECT * FROM goodbooks WHERE ISBN = '"+ GUI.tmp2 + "'");
    
                while (RS.next()) {
                    if (RS.getString(10).equals("0")){
    
                     JOptionPane.showMessageDialog(null,"Book is available!\nYou added: "+RS.getString(3) +" "+ RS.getString(4)
                            +" "+ RS.getString(5) + " " + RS.getString(6)
                            +"!");
    
                     kvitto.add("\n--------------------"
                             + "---------------------------------------------------"
                             + "---------------------------------------------------\n");
        String kv ="\n"+ RS.getString(1)+ " "+RS.getString(2)+" "+RS.getString(3)
    +" "+RS.getString(4)+" "+RS.getString(5)+ " "+RS.getString(6)
                             +" "+RS.getString(7)+" "+RS.getString(8)+" "
                             + RS.getString(9) +" "+RS.getString(10);
        kvitto.add(kv);
                     update();
                     
      
    
                    }else {
                        JOptionPane.showMessageDialog(null, "The book you wish to "
                                + "borrow is currently not in stock.\n"
                                + "It will be returned: " + RS.getString(9) );
                    }
                   
                       
                     
    
                         }
        }catch (Exception e){
            System.err.println("Fail: " + e.getMessage());
            e.printStackTrace();
        }
    
    
    
    
    
    
    
        }
     private static String getDateTime() {
            DateFormat dateFormat = new SimpleDateFormat("yyMMdd");
            Date date = new Date();
            return dateFormat.format(date);
        }
     private static String returnDate(){
         Calendar cal = Calendar.getInstance();
         cal.add(Calendar.DATE, 14);
         DateFormat dateFormat = new SimpleDateFormat("yyMMdd");
         Date today =cal.getTime();
         String newDate = dateFormat.format(today);
    
    
    
         return newDate;
    
     }
     private static void update(){
    
         String date = getDateTime();
                     String tdate = returnDate();
                     JTextArea outText=new JTextArea();
    
    
    for(int index = 0; index < kvitto.size(); index++)
    {
    outText.append( kvitto.get(index) );
    }
    
    
    JOptionPane.showMessageDialog( null , outText );
    
    
                     
                     try{
                       stmt.executeUpdate("UPDATE goodbooks SET borrowdate='"+ date + "',"
                             + " returndate='"+ date + "'  WHERE ISBN='"+ GUI.tmp2 + "'");
         }catch(Exception e){
              System.err.println("Fail: " + e.getMessage());
            e.printStackTrace();
         }
                     
     }
    }
    Thank you for your help =)

  2. #2
    Junky's Avatar
    Junky is offline Grand Poobah
    Join Date
    Jan 2011
    Location
    Dystopia
    Posts
    3,755
    Rep Power
    7

    Default

    Quote Originally Posted by santa View Post
    First question: How can i get rid of this exception ? its comming from the update mehod ... but i dident close anything =/
    No offense but I'm more likely to believe the exception. From the API:

    A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

    Maybe your code is doing one of the above.

  3. #3
    santa's Avatar
    santa is offline Senior Member
    Join Date
    Nov 2009
    Location
    Sweden
    Posts
    208
    Rep Power
    5

    Default

    Thanks for the reply =) I was hoping that someone could tell me a little more detaild about whats going on =P

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,450
    Rep Power
    19

    Default

    While looping through your resultset you call the update() method.
    Inside that mehtod you execute another SQL statement, using the same stmt object.
    This results in the closing of any resultsets related to the previous execution.

    This is why, generally, you don't share Statements around, if they are running different queries.

    Open the Statement when you need it, and close it when you are finished with it.

  5. #5
    santa's Avatar
    santa is offline Senior Member
    Join Date
    Nov 2009
    Location
    Sweden
    Posts
    208
    Rep Power
    5

Similar Threads

  1. Questions
    By Csharks in forum New To Java
    Replies: 8
    Last Post: 01-19-2011, 04:11 AM
  2. Need help with some questions
    By El_Davidos in forum New To Java
    Replies: 5
    Last Post: 11-23-2010, 10:13 AM
  3. Some Questions
    By MuslimCoder in forum New To Java
    Replies: 2
    Last Post: 02-25-2009, 04:01 PM
  4. questions for 1yr exp
    By rahaman.athiq in forum Java Servlet
    Replies: 2
    Last Post: 11-26-2008, 01:13 AM
  5. I have Questions -_-
    By ChazZeromus in forum New To Java
    Replies: 5
    Last Post: 09-13-2008, 08:08 PM

Posting Permissions

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