This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes Oracle/OAS and the fly likes SMS sending functionality working when hard coded. but not working when called through a trigger Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "SMS sending functionality working when hard coded. but not working when called through a trigger" Watch "SMS sending functionality working when hard coded. but not working when called through a trigger" New topic
Author

SMS sending functionality working when hard coded. but not working when called through a trigger

Pramod Krishna Murthy
Ranch Hand

Joined: Jun 30, 2009
Posts: 37

HI,
I need help in solving this issue. i am trying to send sms when a new record is inserted into the table. this activates a trigger and trigger will call the servlet with message sending parameters . the parameters are sent to sms gateway and gateway is responsible for sending the sms.
All this cycle is happening when i hard code the values. but when the trigger calls the same method in the servlet with the same parameters it is not sending. i am getting the parameters from the trigger. it is calling the method correctly. but it is not hitting the sms gateway.


"Nothing is forever except Change"
Ravi Kiran Va
Ranch Hand

Joined: Apr 18, 2009
Posts: 2234

Hi,

trigger will call the servlet


Could you please tell me , as how to call a Servlet from a Trigger ??


Save India From Corruption - Anna Hazare.
david meng
Greenhorn

Joined: Sep 17, 2007
Posts: 4
I also want to know how to trigger a servlet in a trigger
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

It depends on the database but some supply mechanisms to make HTTP requests. Its a somewhat unusual design I have to admit, but possible.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Pramod Krishna Murthy
Ranch Hand

Joined: Jun 30, 2009
Posts: 37

Thanks for the reply...

Here is what is happening.
we are using oracle 10g as data base.

this is the trigger we have written..

create or replace
TRIGGER REG_WORK_TRIGGER AFTER
INSERT ON REGISTRATION_WORK FOR EACH row DECLARE soap_request VARCHAR2
(
32767
);
--URI VARCHAR2;
http_req utl_http.req;
http_resp utl_http.resp;
-- v_ccno varchar2;

BEGIN
--dbms_output.put_line('hello');
soap_request := 'The Soap request as it was generated by JDeveloper';

--select pk_credit_card_no into v_ccno from registration_work where pk_credit_card_no =:new.pk_credit_card_no;
--select to_char(:new.pk_credit_card_no) into v_ccno from registration_work;
--insert into test(ccno,mobile_no) values(to_char(:new.pk_credit_card_no),:new.mobile_no);
--dbms_output.put_line(v_ccno);
--URI='http://127.0.0.1:7101/testsms-Project1-context-root/testsms?smsaction=registration'
http_req := utl_http.begin_request
(
'http://192.168.105.24:7101/sms-Project2-context-root/testsms.sms?smsaction=registration&cardnumber='||to_char(:new.pk_credit_card_no)||'&mobile='||to_char(:new.mobile_no) ,'GET' ,'HTTP//1.1'
)
;
utl_http.set_header
(
http_req, 'Content-Type', 'text/html'
)
;
Utl_Http.Set_Header
(
Http_Req, 'Content-Length', LENGTH(Soap_Request)
)
;
utl_http.set_header
(
http_req, 'GET', ''
)
;
utl_http.write_text
(
http_req, soap_request
)
;
http_resp:= utl_http.get_response
(
http_req
)
;
utl_http.end_response
(
http_resp
)
;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line
(
'Error : '||sqlerrm
)
;
END;


Here is the servlet code. I have given the code which will send the sms when a new record is inserted into the the db. whenever a new record is inserted trigger will be called , and it will call the servlet . the servlet will get the parameters mobile no and card no from request and will do the send redirect to the sms gateway. the sms gateway is responsible for sending the sms.


public void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException,
IOException {
System.out.println("MSG RECEIVED FROM TRIGGER");
String s1="registration";
// String s1=request.getParameter("smsaction");
// System.out.println("request SMS Action obtained...."+s1);

String s2="4000400040004121";
String s3="8970887147";

if(s1.equals("registration")){
// String s2=request.getParameter("cardnumber");
// String s3=request.getParameter("mobile");

System.out.println("request cardnumber obtained...."+s2);
System.out.println("request mobile obtained...."+s3);

sendingsms sms=new sendingsms();
str2=sms.registration(s2,s3);
uri=sms.sendsms1(string, string1);

// System.out.println("doget method....."+uri);

String dir="http://www.google.co.in/";
response.sendRedirect("http://unicel.in/SendSMS/smspost.php?uname=TCL&pass=tcl&send=cctest&dest=919620662649&msg=Dear%20Customer%20,Thanks%20for%20registering%20with%20Canara%20Bank%20.Your%20account%20will%20be%20activated%20in%20next%2024%20hours.%20Thank%20You.&type=1");
System.out.println("after redirect...");

}
}

here the uri is fetched from database which fives the Actual url called for the sms gateway.

the code for fetching the uri is as follows...


public String sendsms1(String string, String string1) {
try {

con=getDBConn();
Statement stmt=con.createStatement();

rss2=stmt.executeQuery("select COLUMSMS_GW_STD_URL,SMS_GW_UNAME,SMS_GW_PWD,SMS_GW_SENDER from SYS_PARAMETERS");
while(rss2.next()){

uri =rss2.getString("COLUMSMS_GW_STD_URL")+"?uname="+rss2.getString("SMS_GW_UNAME")+"&pass="+rss2.getString("SMS_GW_PWD")+"&send="+rss2.getString("SMS_GW_SENDER")+"&dest=91" + mobilenumber +"&msg="+ message +"&type=1";

}


} catch (SQLException e) {


}

return uri;

}
Ravi Kiran Va
Ranch Hand

Joined: Apr 18, 2009
Posts: 2234

Thanks Pramod , dont know how much it had helped you , but it was very helpful for me .
Ireneusz Kordal
Ranch Hand

Joined: Jun 21, 2008
Posts: 423
Read this article, it is about why you should avoid using triggers to sending sms, mails, invoking web services etc:
http://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html
Pramod Krishna Murthy
Ranch Hand

Joined: Jun 30, 2009
Posts: 37

Thanks
Pramod Krishna Murthy
Ranch Hand

Joined: Jun 30, 2009
Posts: 37

But then what could be the work around. because we need to have a trigger. we have a database to which data will be inserted from different databases whenever a transaction occurs. so whenever data is inserted into db we need to capture the event using a trigger. and the sms needs to be sent to the respective user.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

The normal pattern to watch for new data without clouding your data model logic with triggers is to write a process that polls the table for updates.
Pramod Krishna Murthy
Ranch Hand

Joined: Jun 30, 2009
Posts: 37

Thanks for the reply and the time you have taken to help me out.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

You are welcome
 
 
subject: SMS sending functionality working when hard coded. but not working when called through a trigger
 
Similar Threads
send and receive sms from java program
to send sms.
Send a Message to the Mobile using gateway
SMS Sending Issue
sms gateway(queue sms)