Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Pl. Help Urgent

 
Bhuvana Bala
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic