Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

update query doesn't run?

 
Pranit Sonawane
Ranch Hand
Posts: 172
Java MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!



Please help me solving this!
Thanks!
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21581
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:


 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:




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
Ranch Hand
Posts: 172
Java MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 172
Java MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 172
Java MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 172
Java MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Okay here is my new code



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
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21581
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 172
Java MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 172
Java MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok Done!
Now I have made a slight change to my code



got an exception too many connections.
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:

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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic