File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Aborting sql query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Aborting sql query" Watch "Aborting sql query" New topic
Author

Aborting sql query

mitrship gupta
Greenhorn

Joined: Jul 25, 2005
Posts: 7
hello all
my prob is tht i want to abort sql query running in a thread through different thread
my program looks like this
public class test
{





public static void main(String args[]) throws Exception
{
String driverName = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc racle:thin:@192.168.103.46:1521:test";
Class.forName(driverName); // load the driver
Connection connection = DriverManager.getConnection(url, "scott", "tiger");


ConnThread connThread = new ConnThread();
connThread.stmt = connection.prepareCall("{call NEWOBJECT(?)}");
connThread.stmt.registerOutParameter(1,OracleTypes.CURSOR);
connThread.stmt.setQueryTimeout(1);
connThread.start();
System.out.println ("waiting");
Thread.sleep(20);
System.out.println ("waiting done");
System.out.println ("closing conn");
connThread.stmt.cancel();
System.out.println ("closing conn done");
}

}

class ConnThread extends Thread
{
CallableStatement stmt = null;

public void run()
{

try
{

System.out.println ("Executing query");

stmt.executeQuery();
System.out.println ("Executing query done");
}
catch(Exception e)
{
e.printStackTrace();
}

}
}

i have achieved tht for normal sql queries. but i want to the samr for sql procedures. can someone tell me is it even possible???
also i want to know whether can be done for ORACLE, MS SQL and MS ACCESS
Thanking in advance

regards
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
Hello,

I am bit confused when you say

i have achieved tht for normal sql queries. but i want to the samr for sql procedures. can someone tell me is it even possible???


Well yes if the database and driver support it then it is. But I am confused because in your example you use a CallableStatement. CallableStatements are StoredProcedures so I am not sure if I am missing something. Is that code not working for you?

Your query timeout is also pretty low... only 1 second.

I can't tell you about Oracle. But this is possible with MS SQL Server. I am 99% sure this won't work with Access. Access stored procedures are pretty lame though anyway.
mitrship gupta
Greenhorn

Joined: Jul 25, 2005
Posts: 7
Originally posted by Maximilian Stocker:
Hello,

I am bit confused when you say



Well yes if the database and driver support it then it is. But I am confused because in your example you use a CallableStatement. CallableStatements are StoredProcedures so I am not sure if I am missing something. Is that code not working for you?

Your query timeout is also pretty low... only 1 second.

I can't tell you about Oracle. But this is possible with MS SQL Server. I am 99% sure this won't work with Access. Access stored procedures are pretty lame though anyway.


sorry mate but that was the code i was trying for stored procedure. For normal sql queries i used PreparedStatement and tht worked. Now i have to abort the sql procedure if in case its taking too much of a time.
and the stored procedure is running a seperate thread as i have shown in the example.
Also plz ignore the query timeout as i was trying to see whether it worked on not.
sorry for the confusion
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Aborting sql query
 
Similar Threads
Stored proc not found...
Creating JAR
Problem while inserting data into MS SQL table
Everytime getting an empty ResultSet
connecting to Webtrends reporting DB using JDBC-ODBC