File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Pl. Help Urgent Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Pl. Help Urgent" Watch "Pl. Help Urgent" New topic

Pl. Help Urgent

Bhuvana Bala

Joined: Jul 19, 2001
Posts: 10
Hi all,
In the given code :
1. I fetch integer records from the database.
2. In the same method I am trying to access trying to update a record with the aboove obtained value.
I have a problem,
I am able to fetch records from the database.But the resultse doesnt move to the next record. The line before executeUpdate is printing where as the line after the executeUpdate() is not printingand it gives NO data Found Error. Pl. help,
public boolean updateOtherLawyers(String casebirthno, int status)
boolean check = false;
PreparedStatement pstmt = null;
PreparedStatement psup = null;
ResultSet rsup = null;
try {
psup =conn.prepareStatement(
"select [lawportal].[dbo].[caseflow].[CAF_lawid] from [lawportal].[dbo].[caseflow] where [lawportal].[dbo].[caseflow].[CAF_casebirthno]=? ");
psup.setString(1, casebirthno);

rsup = psup.executeQuery();
while ( {
pstmt =conn.prepareStatement("update [lawportal].[dbo].[caseflow] set [lawportal].[dbo].[caseflow].[CAF_lawstatus]=? where [lawportal].[dbo].[caseflow].[CAF_lawid]=? and [lawportal].[dbo].[caseflow].[CAF_casebirthno]=?);
pstmt.setInt(1, status);
pstmt.setInt(2, rsup.getInt(1));
pstmt.setString(3, casebirthno);

check = true;
} finally {

return check;
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

2 possible problems:
1. some drivers (like the jdbc dbc bridge) only allow 1 statement per connection. By opening the second statement you are implicitly closing the first statement(and in turn, closing the resultset). Check your drivers specs to see if this limitation applies to you.
2. some drivers have a limitation that a recordset value can only be read once:
String str = rsup.getInt(1);
pstmt =conn.prepareStatement("update [lawportal].[dbo].[caseflow] set [lawportal].[dbo].[caseflow].[CAF_lawstatus]=? where [lawportal].[dbo].[caseflow].[CAF_lawid]=? and [lawportal].[dbo].[caseflow].[CAF_casebirthno]=?);
pstmt.setInt(1, status);
pstmt.setInt(2, str);
pstmt.setString(3, casebirthno);
try the above 2, and let me know if it goes deeper than that.
**just a side note: You will get much better performance from your second prepared statement if you prepare it outside of the while loop. That way you can just re-use the preparedStatement instead of re-creating the same one each iteration through the loop. Also move the pstmt.close(); outside the loop.


[This message has been edited by Jamie Robertson (edited September 18, 2001).]
Bhuvana Bala

Joined: Jul 19, 2001
Posts: 10
hi Jamie,
Thanx for ur suggestions. I also tried the second method u have mentioned and closing pstmt never works.
The problem could be with the driver.
The same procedure works in the Mysql database.
thanx a lot.
It is sorta covered in the JavaRanch Style Guide.
subject: Pl. Help Urgent
jQuery in Action, 3rd edition