update query doesn't run?

[Pranit Sonawane]

Hi All I'm writing an Update query...It did printed the valuer being carried from one table to another, but doesn't actually update the table's coolumn named Vibhag value. Here's the code!

try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection cn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mnpac?charset=UTF-8", "root", "pranit");
        PreparedStatement ps = cn.prepareStatement("select * from opbal");
        ResultSet rs = ps.executeQuery();
        rs.beforeFirst();
        String fon, fov = "";
        while (rs.next()) {
            fon = rs.getString("name");
            fov = rs.getString("vibhag");
            out.println(fon+" = "fov+"<br>");
            PreparedStatement ps1 = cn.prepareStatement("update salaries set vibhag = '" + fov + "' where name = '" + fon + "'");
            ps1.executeUpdate();
        }       
             
    } catch (Exception e) {
        out.println(e.getMessage());
    }        

Please help me solving this!
Thanks!

[Dave Tolls]

Since you're using a PreparedStatement concatenating your second query together makes little sense.
Use bind variables instead.

Also, you need to really open and close your connection, statement and resultset objects in try/catch blocks (or using the with-resources style of try/catch) since you aren't closing any of them and you'll eventually hit a resource wall.

And finally, for the problem itself, without an explicit commit() call there's no guarantee anything is committed to the db.

[Paul Clapham]

Its also worth considering that updating zero records may be correct, if there's nothing in the table matching the "fon" variable. This could happen if the "fon" variable isn't what you think it is, or if the data in that column isn't what you think it is. Commonly this happens when one of the two has whitespace that the other one didn't have. So using this code could help you with that:

int recordsUpdated = ps1.executeUpdate();
System.out.println("Records updated: " + recordsUpdated);

[Dave Tolls]

Paul Clapham wrote:Its also worth considering that updating zero records may be correct, if there's nothing in the table matching the "fon" variable. This could happen if the "fon" variable isn't what you think it is, or if the data in that column isn't what you think it is. Commonly this happens when one of the two has whitespace that the other one didn't have. So using this code could help you with that:

int recordsUpdated = ps1.executeUpdate();
System.out.println("Records updated: " + recordsUpdated);

And riffing off that a bit, print out the query and paste it into something like MySQL workbench and see what it comes back with.
As Paul says, 0 updates may be a perfectly valid result.

[Pranit Sonawane]

now can you believe something! I mean this is not good! I have been working on this query for a whole day! Dint get any positive reply! Which irritated me a lot!
But I saw your reply people!
Thanks a lot to each n every one of you guys for your great help honestly I'm please to be a member of this forum!
Anyways the current status is that I pasted the same query again and also updated the code and added Paul's Suggestion. And finally at the end it prints "1"

Not working

[Pranit Sonawane]

But there is one thing I have forgot to tell you all. Please tell me how many columns are allowed in a MySQL table?

The columns which is to be updated is the 61st column of the table!

Does that really matter?

[Pranit Sonawane]

Also I'm looking for a suggestion about the Database Software?
Like couple of my next projects are going to need a good or you can say a great database software. Its going to be my bank's project. Now you all can imagine that I'm gonna need a better database system. Currently am using MySQL. Your suggestions will be really helpful for me.! I request you to please suggest me something awesome!
Many Thanks!

[Dave Tolls]

Pranit Sonawane wrote:now can you believe something! I mean this is not good! I have been working on this query for a whole day! Dint get any positive reply! Which irritated me a lot!
But I saw your reply people!
Thanks a lot to each n every one of you guys for your great help honestly I'm please to be a member of this forum!
Anyways the current status is that I pasted the same query again and also updated the code and added Paul's Suggestion. And finally at the end it prints "1"

Not working

What does your code now look like with your fixes in it?

Also, MySQL's column limit is actually a row limit, that is a single row can only hold some 64k.
From the docs.
You would get an error if you exceeded that, though.

[Pranit Sonawane]

Okay here is my new code

request.setCharacterEncoding("utf-8");

    try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection cn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mnpac?charset=UTF-8", "root", "pranit");
        PreparedStatement ps = cn.prepareStatement("select * from opbal");
        ResultSet rs = ps.executeQuery();
        rs.beforeFirst();
        String fon, fov = "";
        int i = 0;
        while (rs.next()) {
            fon = rs.getString("name");
            fov = rs.getString("vibhag");
            out.println(fon + " = " + fov + "<br>");
            Connection cn1 = DriverManager.getConnection("jdbc:mysql://localhost:3306/mnpac?charset=UTF-8", "root", "pranit");
            PreparedStatement ps1 = cn1.prepareStatement("update salaries set vibhag = '" + fov + "' where name = '" + fon + "'");            
            i = ps1.executeUpdate();
            out.println(i);
            ps1.close();
            cn1.close();
        }
        out.println(i);
        ps.close();     
        rs.close();
        cn.close();
    } catch (Exception e) {
        out.println(e.getMessage());
    }

I have tried closing prepared statement and result set also as suggested!

and for you this reply

Also, MySQL's column limit is actually a row limit, that is a single row can only hold some 64k.
From the docs.
You would get an error if you exceeded that, though.

No I haven't yet made mistake there. so finally there should not be any problem for the 61st column?

[Dave Tolls]

From my first post in this thread:
"And finally, for the problem itself, without an explicit commit() call there's no guarantee anything is committed to the db."

I don't know what the MySQL driver does on a close, but the JDBC docs do not say that a commit is required on a close.

In addition I'm not sure why you now have two connections?
Surely this is a single transaction, so requires a single connection to work with?

And you are still not handling opening and closing resources properly.
See this tutorial page.

[Paul Clapham]

Dave, from the MySQL documentation:

By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent.

So the commit issue doesn't apply here, I don't think. There are other possibilities such as updating the table in schema X, then looking at the same table in schema Y and finding it not updated. This sort of error is quite likely as the posted code does update the table -- if the executeUpdate() method returns 1, which we haven't been told. I would also recommend using PreparedStatement correctly (i.e. with ? parameters to set) instead of using the error-prone string concatenation method.

[Pranit Sonawane]

Thanks for your reply people but Dave you said

In addition I'm not sure why you now have two connections?
Surely this is a single transaction, so requires a single connection to work with?

I have to run the update query for 800 records so I thought it would be better to open and close connection every time, else I'll get the Too Many Connections exception! So I did that.

[Dave Tolls]

Paul Clapham wrote:Dave, from the MySQL documentation:

By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent.

So the commit issue doesn't apply here, I don't think.

If it's anything like the environments I'm in, the autocommit is disabled.
Autocommit is a sin...;)
But fair point.

Paul Clapham wrote:
There are other possibilities such as updating the table in schema X, then looking at the same table in schema Y and finding it not updated. This sort of error is quite likely as the posted code does update the table -- if the executeUpdate() method returns 1, which we haven't been told. I would also recommend using PreparedStatement correctly (i.e. with ? parameters to set) instead of using the error-prone string concatenation method.

Good point, should have probably brought that up first, but I dislike commit assumptions, and the lack of one here leapt out at me.

[Dave Tolls]

Pranit Sonawane wrote:Thanks for your reply people but Dave you said

In addition I'm not sure why you now have two connections?
Surely this is a single transaction, so requires a single connection to work with?

I have to run the update query for 800 records so I thought it would be better to open and close connection every time, else I'll get the Too Many Connections exception! So I did that.

Doing it your way you have more connections open, though.
With the single connection you have 1 connection open while processing the first result set.
With the extra connection you have (at a minimum) 2 open.
And with your currently incorrect handling of your resources a single exception will now leave 2 connections hanging there.

[Pranit Sonawane]

Ok Done!
Now I have made a slight change to my code

request.setCharacterEncoding("UTF-8");

    try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection cn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mnpac?charset=UTF-8", "root", "pranit");
        PreparedStatement ps = cn.prepareStatement("select * from opbal");
        ResultSet rs = ps.executeQuery();
        rs.beforeFirst();
        String fon, fov, ac = "";
        //int i = 0;
        while (rs.next()) {
            fon = rs.getString("name");
            fov = rs.getString("vibhag");
            ac = rs.getString("acno");
            //out.println(fon + " = " + fov + "<br>");
            Connection cn1 = DriverManager.getConnection("jdbc:mysql://localhost:3306/mnpac?charset=UTF-8", "root", "pranit");
            PreparedStatement ps1 = cn1.prepareStatement("select * from salaries where name = '" + fon + "'");
            ResultSet rs1 = ps1.executeQuery();
            rs1.beforeFirst();
            while (rs1.next()) {
                if (fon.equals(rs1.getString("name")) || fon == rs1.getString("name")) {
                    Connection cn2 = DriverManager.getConnection("jdbc:mysql://localhost:3306/mnpac?charset=UTF-8", "root", "pranit");
                    PreparedStatement ps2 = cn2.prepareStatement("update salaries set vibhag = ? where name = '" + fon + "' and acno = '" + ac + "'");
                    ps2.setString(1, fov);
                    ps2.executeUpdate();
                    ps2.close();
                    cn2.close();
                } else {
                    out.println("skipped<br>");                    
                }                
            }           
            rs1.close();
            ps1.close();
            cn1.close();
        }
        ps.close();
        rs.close();
        cn.close();
       
    } catch (Exception e) {
        out.println(e.getMessage());
    }

got an exception too many connections.

[Dave Tolls]

Oh come on.
You now have 3 connections on the go at once.

Why do you feel you need these extra connections?

And please, use the correct structure for your connections, statements and result sets.
The following assumes you have a 1.7+ JDK:

try (Connection con = DriverManager.getConnection(etc etc)) {
    try (PreparedStatement ps2 = cn2.prepareStatement(etc etc)) {
        // execute in here.
    }
}

Otherwise an exception will result in left over connections.
I have mentioned this before.

And also use a PreparedStatement properly, with bind parameters. You seem to be doing that in one place, but not the others.

Finally, this looks to me like it should be a single update SQL.
update salaries s
set vibhag = (select o.fov from opbal etc)

Something like that.