• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • Last Modified:

Why am I getting this error with my SQL statement

Hi,
I am getting an error with my JDBC program, for some reason or another I get the below error can someone tell me what I need to do to correct this. Thanks. The code is attached and the error is below

Welcome to JDBC program!
05/01/10
Error: Error retrieving data!
java.sql.SQLException: Operation not allowed after ResultSet closed
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
      at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:794)
      at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:7145)
      at JDBCApp.ConvertData(JDBCApp.java:62)
      at JDBCApp.main(JDBCApp.java:120)
import java.sql.*;


public class JDBCApp 
{
    Connection link;
    Statement statement =null;
    ResultSet results;
    String dbURL = "blahblah";
	String username = "blah";
	String password = "blah";
	int ID;
	String Date;
    
    /** Creates a new instance of JDBCApp */
    public JDBCApp() 
    {
    }
    public void Connect(){
    	   System.out.println("Welcome to JDBC program!");
           try 
           {
               Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
               link = DriverManager.getConnection(dbURL,username,password);
   	}
   	catch(ClassNotFoundException e)
   	{
   		System.out.println("Error: Unable to load driver!");
   		System.exit(1);
   	}
   	catch(SQLException e)
   	{
   		System.out.println("Error: Cannot connect to database!");
   		System.exit(1);
   	}
    }
    
    public void ConvertData(){
    	try
    	{
    		statement = link.createStatement();
    		results = statement.executeQuery("SELECT id,date FROM wind_data WHERE date LIKE '%2010'");
    	}
    	catch(SQLException e)
    	{
    		System.out.println("Error: Cannot execute query!");
    		e.printStackTrace();
    		System.exit(1);
    	}

    	try
    	{
    		while (results.next())
    		{
    			ID = (results.getInt("ID"));
    			Date = (results.getString("DATE"));
    			
    			String newdate = FormatDate(Date);
    			System.out.println(newdate);
    			
    			try
    			{
    				//statement = link.prepareStatement("UPDATE wind_data    SET date = '"+newdate+"'   WHERE ID = '"+ID+"'");
    				statement.executeUpdate("UPDATE wind_data    SET date = '"+newdate+"'   WHERE ID = '"+ID+"'"); 
    				//System.out.println("Getting Data");
    			}
    			catch(SQLException e)
    			{
    				System.out.println("Error: Cannot execute query!");
    				e.printStackTrace();
    				System.exit(1);
    			
    		}
    		}
    	}
    	catch(SQLException e)
    	{
    		System.out.println("Error: Error retrieving data!");
    		e.printStackTrace();
    		System.exit(1);
    	}
    }
    
    public void Disconnect(){
    	try
    	{
    		link.close();
    	}
    	catch(SQLException e)
    	{
    		System.out.println("Error: Unable to disconnect!");
    		e.printStackTrace();
    	}
    }
    
    public String FormatDate(String Date){
		String day = Date.substring(0, 2);
		String month = Date.substring(3,5);
		String year = Date.substring(8,10);
		String newdate = day+"/"+month+"/"+year;
		return newdate;
		
	}
    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) 
    {
    	JDBCApp a = new JDBCApp();
     a.Connect();
     a.ConvertData();
     a.Disconnect();

	

	
    }
    
   
}

Open in new window

0
bhession
Asked:
bhession
1 Solution
 
indigo23Commented:
try creating a different statement object for the update query
0
 
CEHJCommented:
Storing a date type as a string is guaranteed to give you problems, as well as being inefficient
0
 
jzkiddingCommented:
in place on newdate in the update statement  try todate('+newdate+','dd/mm/yy')
0
 
gordon_vt02Commented:
As indigo said, you need a new Statement object for the UPDATE query.  If you reuse the existing 'statement' object to run executeUpdate(), it will close the ResultSet from the SELECT query, causing the exception you are seeing.

You should probably consider moving your Statement and ResultSet variable declarations into the ConvertData() method as there is really no need for them to be class members.
0
 
bhessionAuthor Commented:
Yeah that sorted the problem alright. Thanks sorry for the late reply
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now