This week's book giveaway is in the OCAJP 8 forum.
We're giving away four copies of OCA Java SE 8 Programmer I Study Guide and have Edward Finegan & Robert Liguori on-line!
See this thread for details.
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 OCA Java SE 8 Programmer I Study Guide this week in the OCAJP 8 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.
I agree. Here's the link:
subject: Pl. Help Urgent
It's not a secret anymore!