HTTP connection in Java Stored Procedure

AFAIK

you cannot use post/get in a DB2 Java Stored procedure (they don't run on web application server)

but you can write a servlet which passes parameters to stored procedure and then displays the result or call another stored procedure based on results.

hi

i guess the best answer is, just try it as you would in a regular java program and see what happens, the question is what are you trying to achive ? why would you want to post a url from within a stored procedure,
do you want to wait for output from the http request ?

post and get are http requests so you don't have to run on a web server in order to use them, you just need to open a socket with a web server and send it the requests

Hi All,

To be more exact to the problem i'm facing right now, I'm actually doing a schedule task on DB2 which runs stored procedure on the schedule time. I'm thinking to use java stored procedure to call a sevlet which execute action(sending email etc) within my application.
The stored procedure does not do anything, not even a need of any result, just to make a call to sevlet which will do a batch process.Any advice?I'm new to both JAVA and DB2 -.-!

How can i import/use the org.apache.commons.httpclient library in Java Stored Procedure?

What if i make a class to do the http get and compiled it into a jar file, then call this jar file from a java stored procedure.Can this be done?How can i do it?

hi
let me get you straight
the only reason you are using db2 is for it's scheduling ability ?
if that is the situation, i think it would be better for you to write a stand-alone application in java that will handle all your needs, and this way you won't depend on db2's availability in order to operate
java has mechanisms for scheduling tasks as well, so i think this approach will make your life much eaiser

momi

then why do you need stored procedure ???

download a small utility called CURL and use db2 task scheduler or OS scheduer to schedule it


http://curl.haxx.se/download.html

Hi,

too bad i can't change the design now already.And in order to use any other new library or utility we have to get approval due to their security policy.

Currently i place the httpclient jar file at C:\Program Files\IBM\SQLLIB\java\jdk\jre\lib\ext and import the library into the java stored procedure and i get no error building it.But when i run the stored procedure, i hit this error,

A database manager error occurred.[IBM][CLI Driver][DB2/NT] SQL4302N  Procedure or user-defined function "SPP.TEST1", specific name "TEST1" aborted with an exception "org/apache/commons/httpclient/HttpClient".  SQLSTATE=38501

What are the steps i need to do to import a jar files to be used in my java stored procedure?

maybe you did import the jar correctly
maybe the class it self throws an exception

can you post your code ?

Here is my code,

import org.apache.commons.httpclient.HttpClient;
import org.apache.commons.httpclient.HttpException;
import org.apache.commons.httpclient.methods.GetMethod;
import java.io.IOException;

public class Test1
{
    public static void test1 (  ) throws SQLException, Exception
    {
     
      HttpClient client = new HttpClient();
      String url="http://localhost:9081/SPPIIPS/SchedulerTaskSevlet/";
      GetMethod get=new GetMethod(url);

  try {
   int executeResult=client.executeMethod(get);
    System.out.println("result=" + executeResult );
  }
  catch (HttpException e) {
    e.printStackTrace();
  }
  catch (IOException e) {
    e.printStackTrace();
  }

    }
}  

i have no problem when i build the stored procedure.But when i run it, i hit this error the first time:

SPP.TEST1 - Run started.
Data returned in result sets is limited to the first 100 rows.
Data returned in result set columns is limited to the first 20 bytes or characters.
SPP.TEST1 - Exception occurred while running:
A database manager error occurred.[IBM][CLI Driver][DB2/NT] SQL4302N  Procedure or user-defined function "SPP.TEST1", specific name "TEST1" aborted with an exception "org/apache/commons/logging/LogFactory".  SQLSTATE=38501

SPP.TEST1 - Roll back completed successfully.
SPP.TEST1 - Run failed.


Then i when i run the second time, i hit a different error:
SPP.TEST1 - Run started.
Data returned in result sets is limited to the first 100 rows.
Data returned in result set columns is limited to the first 20 bytes or characters.
SPP.TEST1 - Exception occurred while running:
A database manager error occurred.[IBM][CLI Driver][DB2/NT] SQL4302N  Procedure or user-defined function "SPP.TEST1", specific name "TEST1" aborted with an exception "org/apache/commons/httpclient/HttpClient".  SQLSTATE=38501

SPP.TEST1 - Roll back completed successfully.
SPP.TEST1 - Run failed.

It doesn't seems to recognise the jar file.What are the steps i need to do to make the jar files usable to my java stored procedure?

i actually copied the jar file into C:\Program Files\IBM\SQLLIB\java\jdk\jre\lib\ext so that i get no error when i build it.

Hi Momi,

Thanks for being so helpful! i got the things work already. I should used java.net API instead to ease the work! Thanks so much!