Solved

HTTP connection in Java Stored Procedure

Posted on 2006-11-16
14
384 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now