Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

JDBC ResultSet Question

I have a program that executes the following in a thread.

Result result = stmt.execute();
while( result.next() )
{
  ..
  Send out email based on the data I have.
  If OK then go to deleteRecord( conn )
}

deleteRecord( Connection conn )
{
  PreparedStatement stmt  = conn.createPreparedStatement(..);
  stmt.exucute();
  stmt.close();
}

Is it ok to delete that record while the result set from the function above still has it?

Also.  I'm using SQL Server and when I execute the PreparedStatement it adds another connection to the user ( USing the sp_who2, etc to see how many connections the user has ).
But when I call the close it doesnt release that connection. These functions are being executed in a separate thread so I wonder if using Statement would solve the problem.

I'm using SQL Server 2000, JTDS driver.

Any ideas?

Thanks in advance
0
borg48
Asked:
borg48
  • 6
  • 3
1 Solution
 
sfotexCommented:
An updatable result set allows modification to data in a table through the result
set. If the database does not support updatable result sets, the result sets returned
from executeQuery() will be read-only. To get updatable results, the Statement object
used to create the result sets must have the concurrency type
ResultSet.CONCUR_UPDATABLE.

The query of an updatable result set must specify the primary key as one of the
selected columns and select from only one table. For some drivers, `SELECT * FROM
my_table' will return a read-only result set, so make sure that you specify the
column names.

Also, closing the statement is different then closing the connection. The connection keeps alive until you call con.close();
0
 
sfotexCommented:
oh,
also make sure you commit if you have autocommit turned off.... I wish I could count ho many times thats screwed me up...
0
 
borg48Author Commented:
What about this question:
Also.  I'm using SQL Server and when I execute the PreparedStatement it adds another connection to the user ( USing the sp_who2, etc to see how many connections the user has ).
But when I call the close it doesnt release that connection. These functions are being executed in a separate thread so I wonder if using Statement would solve the problem.

I'm using SQL Server 2000, JTDS driver.

Is there a reason why its not being closed?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sfotexCommented:

the stmt.close() method does not close a
prepared statement. They remain until the connection is closed. Also, remember that the coonection we see is in a wrapper, so there might be a slight delay.....
0
 
sfotexCommented:
also, make sure make sure your pstatment is closed in a finally clause:
 finally {
       if (pss != null)
                pss.close();
        }
0
 
sfotexCommented:
also,
If you're dealing with pooled connections,
 when you close a pooled Connection it  doesn't necessarily close the physical connection and associated PreparedStatements may be chached...

So what you might be seeing is either: a bug, or jdbc doing its thing... I would wager that if your not seeing erros java side (connections timing out) or dbase side...
connections timing out, jdbc is just doing its thing...




0
 
borg48Author Commented:
Thanks allot for the info.  One other question is does a PreparedStatement open another connection on the database?  Cause thats what it seems like its doing.  
0
 
sfotexCommented:
Are you using connection pooling?

Also,
The was your using prepared statments might not be optimal...
You probally want to do it something like this:

myclass{

PreparedStatment ps;

public void stuff{
ps=....
Query stuff here
result set stuff here
}

private void deleteStuff(Conn conn)
{
ps.setField(1, "Amount");
conn.execute(ps);
}

}//end myclass

Otherwise your  creating a new prepared statment every time to execute the same thing...
0
 
borg48Author Commented:
Thanks for you help
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now