Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

HTTP connection in Java Stored Procedure

Posted on 2006-11-16
14
Medium Priority
?
422 Views
Last Modified: 2011-10-03
Hi Expert,

Is it possible to do HTTP post/get within a java stored procedure in DB2 Version 8?I need to post/get an url to invoke a servlet within an application server.How can i do so?Any recommendation or example??


Thanks in advanced for any reply from experts!
0
Comment
Question by:sinwee80
  • 7
  • 5
  • 2
14 Comments
 
LVL 7

Expert Comment

by:sachinwadhwa
ID: 17955881
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.
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17955918
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 ?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17956171
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
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:sinwee80
ID: 17956406
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 -.-!
0
 

Author Comment

by:sinwee80
ID: 17956446
How can i import/use the org.apache.commons.httpclient library in Java Stored Procedure?
0
 

Author Comment

by:sinwee80
ID: 17956505
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?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17956642
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
0
 
LVL 7

Expert Comment

by:sachinwadhwa
ID: 17956681
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
0
 

Author Comment

by:sinwee80
ID: 17973171
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?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17974155
maybe you did import the jar correctly
maybe the class it self throws an exception

can you post your code ?
0
 

Author Comment

by:sinwee80
ID: 17977062
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?
0
 

Author Comment

by:sinwee80
ID: 17977064
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.
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 600 total points
ID: 17977290
ok
can you add some System.out.println commands to your procedure ?
just so we can know exactly where it aborts ?
can you add them after each line ?

0
 

Author Comment

by:sinwee80
ID: 17978864
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!
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

971 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