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