Solved

Why am I getting this error with my SQL statement

Posted on 2010-11-16
5
379 Views
Last Modified: 2012-08-14
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
Comment
Question by:bhession
5 Comments
 
LVL 3

Accepted Solution

by:
indigo23 earned 250 total points
ID: 34147137
try creating a different statement object for the update query
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 34147328
Storing a date type as a string is guaranteed to give you problems, as well as being inefficient
0
 
LVL 2

Expert Comment

by:jzkidding
ID: 34154629
in place on newdate in the update statement  try todate('+newdate+','dd/mm/yy')
0
 
LVL 10

Expert Comment

by:gordon_vt02
ID: 34155454
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
 

Author Closing Comment

by:bhession
ID: 34281547
Yeah that sorted the problem alright. Thanks sorry for the late reply
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
stackato and cloud 4 75
Update in Sql 7 30
How to insert gas or chemical names in SQL server. For e.g. CO2 to CO₂. 5 67
java stored proc example 9 24
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now