wood burning stoves*
The moose likes JDBC 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 OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Pl. Help Urgent" Watch "Pl. Help Urgent" New topic
Author

Pl. Help Urgent

Bhuvana Bala
Greenhorn

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,
Thanx
Bhuvana
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 (rsup.next()) {
System.out.println(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, rsup.getInt(1));
pstmt.setString(3, casebirthno);
pstmt.executeUpdate();
pstmt.close();
}


psup.close();
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);
System.out.println(str);
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.

Jamie

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

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.
Bhuvana
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Pl. Help Urgent