asked on

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

sfotex

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();

sfotex

oh,
also make sure you commit if you have autocommit turned off.... I wish I could count ho many times thats screwed me up...

borg48

ASKER

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?

sfotex

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.....

sfotex

also, make sure make sure your pstatment is closed in a finally clause:
finally {
if (pss != null)
pss.close();
}

sfotex

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...

borg48

ASKER

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.

ASKER CERTIFIED SOLUTION

sfotex

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

borg48

ASKER

Thanks for you help