This week's book giveaway is in the OCAJP forum.
We're giving away four copies of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) and have Khalid A Mughal & Rolf W Rasmussen on-line!
See this thread for details.
Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Aborting sql query

 
mitrship gupta
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 381
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic