File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes database coloumn updation problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "database coloumn updation problem" Watch "database coloumn updation problem" New topic
Author

database coloumn updation problem

Sujay Nadkarni
Ranch Hand

Joined: Jul 03, 2008
Posts: 34
I have written the below code in jsp. All the database coloumns are getting updated except one. this is the code

this is how the database looks
mcap wt
100 0.5
100 0.5
all other computations are done perfectly except the wt!!
wt is obtained by mcap/sum(mcap)..
Help me out!! Please this is my final year college project
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3700
    
    5

If I haven't said this a million times all ready, NEVER PUT JDBC CODE INSIDE A JSP PAGE.


My Blog: Down Home Country Coding with Scott Selikoff
Sujay Nadkarni
Ranch Hand

Joined: Jul 03, 2008
Posts: 34
the database wt is now getting updated but the loop is getting executed only once.
i want the wt to come down to 0.20 at one go.


please help me out.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Wow. I don't want to seem too critical - I know you are just learing this stuff - but that code is much harder to follow than it need be. Aside from it being inside a JSP (please head Scott's advice) the choice of variable names is terrible. And there are no comments. And no encasuplation.

So, what is your code doing:
1. You get a connection (using the JDBC-ODBC bridge - why?).
2. You create a Statement (which you then don't use for 20 lines)
3. You define two Strings that represent SQL (which you then don't use for 20 lines)
4. You get another connection to the same database.
5. You create another Statement
6. You define a bunch of variables, including three more bits of what looks like SQL
7. You get a ResultSet using the first connection from the SQL "Select wt from Nifty"
8. You get a ResultSet using the second connection from the SQL "Select SUM(mcap) as t from Nifty" (why are you using more than one connection? why did you choose to do the sum in SQL?)
9. You define a couple of empty Strings
10. You step through the first ResultSet assigning the value wt to the variable weight.
11. if the weight is greater than some value you do some maths and create what looks like it might be more SQL
12. if the weight is less than some value you do some more maths and create some more of what looks like it might be SQL
13. otherwise you break out of the loop (so if weight == 0.20 the loop will end).
14. Now you start another loop through the second ResultsSet
15. You update a table many times overwriting each value for wt (Why do all the wt values have to be the same? And if they do, why not just issue one SQL statement?)
16. You execute two more bits of SQL

Now, the answer to your question is somewhere in the above. I could just have cut to the chase and told you outright, but I hade to understand all of the above first to work out what might be going wrong. This is a big failing in the code you've posted: I suggest you have a read through your course notes for the term "encapsulation and see if you can improve your code. You will find it far quicker and easier to work if you do.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Sujay Nadkarni
Ranch Hand

Joined: Jul 03, 2008
Posts: 34
ok i understand!! may be i am beating around the bush.. but the problem is that the loop is gettin executed only once.
I want the loop that is while(resultset.next()) to be executed till all the values in the table are below 0.20.. This is a stock related project. It is getting executed only once. Kindly tell me how do i do that. by the way i read the encapsulation link that you sent me.. was very helpful. Thanks alot. Please help me with this loop thing. Thank you
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3700
    
    5

Hi Surjay,

JavaRanch is not a code mill. I think Paul's suggestions were quite helpful, I recommend taking some time to read them over and try implementing them yourself.

-Scott
Sujay Nadkarni
Ranch Hand

Joined: Jul 03, 2008
Posts: 34
I understand. I am not asking for any code!!
I have wriiten the code. Its not that I have done nothing and asking for help. The problem here is that
result.next() reaches the last row and breaks out of the loop. I just want it to go back to the first row. I dont understand how to do it. I tried using resultset.first() as well. I am very lucky to get guidance from you guys.
But i really really need help. Please.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


The problem here is that
result.next() reaches the last row and breaks out of the loop.

That is how ResultSets are supposed to work. Why do you want to go back to the first row?
Sujay Nadkarni
Ranch Hand

Joined: Jul 03, 2008
Posts: 34
i want to go back to the first row. because the database after the execution of the while loop still contains weight greater than 0.20. I want the loop to run till the all the weights in the database are less than or equal to 0.20..
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3700
    
    5

Then you need to re-run the query and/or find a better way to manage your data in memory.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: database coloumn updation problem
 
Similar Threads
Percentage issue
Getting an Average
Problem in Divison
Aggregate functions problem in jsp
toString