?
Solved

JDBC ResultSet Question

Posted on 2003-02-21
9
Medium Priority
?
344 Views
Last Modified: 2008-02-01
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
Comment
Question by:borg48
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 2

Expert Comment

by:sfotex
ID: 7995925
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
 
LVL 2

Expert Comment

by:sfotex
ID: 7996194
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
 

Author Comment

by:borg48
ID: 7997603
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 2

Expert Comment

by:sfotex
ID: 8001521

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
 
LVL 2

Expert Comment

by:sfotex
ID: 8001920
also, make sure make sure your pstatment is closed in a finally clause:
 finally {
       if (pss != null)
                pss.close();
        }
0
 
LVL 2

Expert Comment

by:sfotex
ID: 8001940
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
 

Author Comment

by:borg48
ID: 8011149
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
 
LVL 2

Accepted Solution

by:
sfotex earned 240 total points
ID: 8022058
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
 

Author Comment

by:borg48
ID: 8514054
Thanks for you help
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Suggested Courses
Course of the Month8 days, 14 hours left to enroll

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question