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

[Pramod Krishna Murthy]

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.

[Ravi Kiran Va]

Hi,

trigger will call the servlet

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

[david meng]

I also want to know how to trigger a servlet in a trigger

[Paul Sturrock]

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

[Pramod Krishna Murthy]

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]

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

[Ireneusz Kordal]

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]

Thanks

[Pramod Krishna Murthy]

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]

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]

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

[Paul Sturrock]

You are welcome