My Sql jdbc problem

bazcaz

Im having a problem trying to write some code in java to manipulate a mysql database,basically im taking an int and string field out of one table and trying to insert the int field without modifying it along with a substring of the original string into consecutive rows in another table.By the way theres about 75000 rows so im going to be executing statement.executeUpdate(update) 75000 times.

Heres the code anyway it works inserting the first set of int and substring but then throws an exception and stops.

I make a query on the table messagere and put the result into a resultset,its this resultset i work with to get the int and substring i need for my new table.

s.executeUpdate("CREATE TABLE MESSAGEREREMOVED (mid int(10),subject text)");

rs=s.executeQuery("select * from MESSAGERE");

while(rs.next())
{
int mid=rs.getInt(1);
String normal=rs.getString(2);
String substring=normal.substring(4,normal.length());
String s3="insert into MESSAGEREREMOVED values ("+mid+",\'"+substring+"\')";
s.executeUpdate(s3);
}

seamus

What's the text of the exception that you catch?

bazcaz

i just did printStackTrace() and its just a load of numbers

seamus

Oh aye, it'll do that.

What you need to do is catch the thrown exception, then print it to the screen.

The executeUpdate() method throws and SQLException, so you need to enclose it in a try..catch block, i.e.

try {
	s.executeUpdate(s3);
} catch(Exception e) {
	System.out.print(e.getMessage())
}

madramor

bazcaz wrote:

s.executeUpdate("CREATE TABLE MESSAGEREREMOVED (mid int(10),subject text)");

rs=s.executeQuery("select * from MESSAGERE");

while(rs.next())
{
int mid=rs.getInt(1);
String normal=rs.getString(2);
String substring=normal.substring(4,normal.length());
String s3="insert into MESSAGEREREMOVED values ("+mid+",\'"+substring+"\')";
s.executeUpdate(s3);
}

you are using the same statement to do all the sql this will cause you problems also you should use a prepared statement

        Statement st;
        ResultSet rs;
        PreparedStatement pps;
        String txt = "insert into MESSAGEREREMOVED values(?,?);";
        try{            
            st = con.createStatement();
            rs = st.executeQuery("select * from MESSAGERE");
            pps = con.prepareStatement(txt);
            while(rs.next()){
                pps.setInt(1,rs.getInt(1));
                String tmp = rs.getString(2);
                pps.setString(2,tmp.substring(4,tmp.length()));
                pps.executeUpdate();
            }
            rs.close();
            st.close();
            pps.close();
        }
        catch(Exception e){
            System.out.println(e);
        }

bazcaz

It prints out "Operation not allowed after ResultSet closed" but that doesnt make any sense because it should stay inside the while loop because rs.next() should be true 75000 odd times but its only executing the insert statement once then throwing the Exception.

bazcaz

ya i tried prepared statements didnt make any difference i think the problem is with the resultset to be honest it prints out in the exception that the resultset is closed for some reason??

madramor

bazcaz wrote:

ya i tried prepared statements didnt make any difference i think the problem is with the resultset to be honest it prints out in the exception that the resultset is closed for some reason??

that code i posted works so

post the full code formatted that is causing you the problem

also prepared statements make a huge difference compared to normal statements especially when performing the same task many times.

bazcaz

Madramor,

If i put in your code i get the first two lines inserted into the table and then it throws the exception string index out of range :-1

madramor

bazcaz wrote:

Madramor,

If i put in your code i get the first two lines inserted into the table and then it throws the exception string index out of range :-1

try

String tmp = rs.getString(2);
// put code in here to make sure string is correct length
if(tmp.length() < 4)
     pps.setString(2,tmp);
else
     pps.setString(2,tmp.substring(4,tmp.length()));

bazcaz

Ya i reckon thats it alrite seems to be running now.

Cheers