CODEWITHSUNDEEP
javajdbc
Harish
Harish

Reputation: 9

how to extract data from one table and insert into another table

i am writing a code to retrieve two columns from a table and store it in another table.i am using MS Accesses as a database. the code is as follows

public void connectDb(){
   try{

  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  Connection con=DriverManager.getConnection("jdbc:odbc:harish","scott","tiger");
  Statement st= con.createStatement();
  ResultSet rs=st.executeQuery("select sev,al from pgw_alarm where sev='000'");
  while(!rs.next())
  {
   String sev= rs.getString("sev");
   String al= rs.getString("al");
   st.executeUpdate("insert into info_alarm values('"+sev+"','"+al+"')");
  }
}catch(Exception e){e.printStackTrace();
    System.out.println("some error");}
}

the following stack trace 

java.sql.SQLException: [Microsoft][ODBC Driver Manager] Invalid cursor state
    at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6957)

    at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114)
    at sun.jdbc.odbc.JdbcOdbc.SQLGetDataString(JdbcOdbc.java:3907)
    at sun.jdbc.odbc.JdbcOdbcResultSet.getDataString(JdbcOdbcResultSet.java:5698)
    at sun.jdbc.odbc.JdbcOdbcResultSet.getString(JdbcOdbcResultSet.java:354)
    at sun.jdbc.odbc.JdbcOdbcResultSet.getString(JdbcOdbcResultSet.java:411)

please help me in solving this problem.....

Upvotes: 1

Views: 11019

Answers (3)

Jan Zyka
Jan Zyka

Reputation: 17898

This should be possible within a single query:

INSERT INTO info_alarm (sev, al)
  SELECT sev, al FROM pgw_alarm where sev='000';

It will be cleaner and way faster.

The ! in while (!rs.next()) is probably just a typo, right?

Edit: To answer you question, looking into JavaDoc:

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.

Basically the ResultSet is still reading from the statement but you you closed it by executing another query on the same Statement. If you want to do it this way you need to use two separate statements.

Upvotes: 3

Andreas Dolk
Andreas Dolk

Reputation: 114757

Pretty sure that the problem is caused by reusing the Statement instance. It says in JavaDoc:

All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.

So when you execute the insert statement, the actual result set is closed, and that may throw the exception. ("invalid cursor state" is another hint)

Create to different Statement instances, one for the read, the other one for the multiple inserts. That should fix this issue.

Upvotes: 0

Brian Beckett
Brian Beckett

Reputation: 4900

 while(!rs.next())

Why are you checking for !rs.next()? ResultSet.next() returns true if the cursor is pointing at a valid row and false if no more rows exist. If your query is returning no results, rs.next() will return false. !rs.next() -> not false -> true -> your while block will execute on an empty result set.

This is almost certainly what your problem is. Change it to while(rs.next())

Upvotes: 0

Related Questions