Dear Spameden, As I have already mentioned in my above mail that I want to store both the MT and MO PDU in Mysql database. Currently we get the MT PDU in sent_sms table in the database however there is no entry in the MO PDU.
Kindly let me know where I will get the MO response (delivery_sm) status like delivered, undelivered, expired. Thanks On Mon, Oct 22, 2012 at 2:45 PM, spameden <spame...@gmail.com> wrote: > sqlbox by default stores DLR, MT, MO in sent_sms messages after message > is being sent or received. > > for monitoring you might want to consider kannel monitor included in the > latest svn package or writing it yourself > > > 2012/10/22 Ravindra Gupta // Viva <ravin...@vivainfomedia.com> > >> Dear Spameden, >> >> We have configured sqlbox in one of our Linux servers, We require to >> store MT and MO pdu in mysql database. >> >> How we can do for the same. >> >> Ravindra Gupta B: +91.22.6785 6785 Server AdministratorD: +91.22.6785 >> 6754 ravin...@vivaconnect.in M: +91.98199 92242 www.vivaconnect.in >> Blog <http://blog.vivaconnect.in/> >> LinkedIn<http://www.linkedin.com/company/viva-infomedia-pvt-ltd> >> Twitter <https://twitter.com/vivaconnect1> >> Facebook<https://www.facebook.com/pages/VivaConnect/181323090987> >> YouTube<https://www.youtube.com/watch?v=MoLPYhTvNlg&feature=player_embedded> >> >> *Viva Infomedia Pvt. Ltd,* 242, Oshiwara Industrial Centre, New Link >> Road, Goregaon (West), Mumbai- 400104. >> >> DISCLAIMER: This e-mail and any files transmitted with it are for the >> sole use of the intended recipient(s) and may contain confidential and >> privileged information. If you are not the intended recipient, please >> contact the sender by reply e-mail and destroy all copies and the original >> message. Any unauthorized review, use, disclosure,dissemination, >> forwarding, printing or copying of this email or any action taken in >> reliance on this e-mail is strictly prohibited and may be unlawful. The >> recipient acknowledges that Viva Infomedia Pvt.Ltd. or its subsidiaries >> and associated companies are unable to exercise control or ensure or >> guarantee the integrity of/over the contents of the information contained >> in e-mail transmissions and further acknowledges that any views expressed >> in this message are those of the individual sender and no binding nature of >> the message shall be implied or assumed unless the sender does so expressly >> with due authority of Viva Infomedia Pvt.Ltd. Before opening any >> attachments please check them for viruses and defects. >> >> >> >> On Sat, Oct 13, 2012 at 8:11 AM, spameden <spame...@gmail.com> wrote: >> >>> There is no "WEB panel" for monitoring SQLBox, but you can code one, >>> it's very easy I think. >>> >>> Right now I'm using this code just to monitor what happens with kannelvia >>> the WEB. >>> >>> It gets current pending queue on multiple sqlbox'es and displays >>> kannel's status page. You can extend it to allow kannel's different >>> commands. >>> >>> Here is what I'm using so you'll get an idea: >>> >>> <? >>> require_once('class_mysql.php'); >>> include_once('curl.class.php'); >>> $DB=NEW DB_MYSQL('kannel_db', 'kannel_user', 'password', 'localhost'); >>> $DB->query("show tables like 'send_sms%'"); >>> $tables = $DB->collect("Tables_in_kannel (send_sms%)"); >>> $DB->free_result(); >>> >>> foreach($tables as $i => $table) { >>> $DB->query("SELECT count(*) AS cnt FROM `".$table."` WHERE >>> time<=UNIX_TIMESTAMP(NOW())"); >>> $DB->free_result(); >>> $result[$i]['count'] = $DB->next_record('cnt'); >>> $result[$i]['name'] = $table; >>> } >>> $DB->close(); >>> $get = new cURL(); >>> ?> >>> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> >>> <html> >>> <title>Kannel Status</title> >>> <body> >>> Current Pending Queue:<br> >>> <? foreach($result as $tmp) { ?> >>> <?=$tmp['name']?>: <?=(is_null($tmp['count']) ? 0:'<b>'.$tmp[ >>> 'count'].'</b>')?><br> >>> <? } >>> >>> ?> >>> <pre> >>> <?=$get->get('http://127.0.0.1:13000/status')?> >>> </pre> >>> </body> >>> </html> >>> >>> >>> >>> 2012/8/29 Ravindra Gupta // Viva <ravin...@vivainfomedia.com> >>> >>>> Dear Rene >>>> >>>> We have successfully configured SQLBOX on our Linux server, I have some >>>> query as given below. >>>> >>>> 1) Any web panel in SQLBOX where we will be Monitored SMS bulk queue >>>> like kannel. >>>> >>>> >>>> On Wed, Aug 8, 2012 at 9:13 PM, Rene Kluwen <rene.klu...@chimit.nl>wrote: >>>> >>>>> Sorry… Now I see what is happening.**** >>>>> >>>>> You should comment out line 597 of the original gw/sqlbox.c as well.** >>>>> ** >>>>> >>>>> ** ** >>>>> >>>>> == Rene**** >>>>> >>>>> ** ** >>>>> >>>>> *From:* Ravindra Gupta // Viva [mailto:ravin...@vivainfomedia.com] >>>>> *Sent:* Wednesday, 08 August, 2012 15:51 >>>>> *To:* Rene Kluwen >>>>> *Cc:* us...@vm1.kannel.org >>>>> *Subject:* Re: dlr-url call on sqlbox**** >>>>> >>>>> ** ** >>>>> >>>>> ** ** >>>>> >>>>> Dear Rene,**** >>>>> >>>>> ** ** >>>>> >>>>> We have commented the code in sqlbox.c file and found the row in >>>>> sent_sms table.**** >>>>> >>>>> ** ** >>>>> >>>>> */* if (msg_type(msg) == sms) {***** >>>>> >>>>> * debug("sqlbox", 0, "smsbox_to_bearerbox: sms received");* >>>>> **** >>>>> >>>>> * msg_escaped = msg_duplicate(msg);***** >>>>> >>>>> * gw_sql_save_msg(msg_escaped, octstr_imm("MT")); ***** >>>>> >>>>> * msg_destroy(msg_escaped);***** >>>>> >>>>> *} */***** >>>>> >>>>> ** ** >>>>> >>>>> */* if (msg_type(msg) == sms) {x", 0, "smsbox_to_bearerbox: sms >>>>> received");***** >>>>> >>>>> * msg_escaped = msg_duplicate(msg);***** >>>>> >>>>> * gw_sql_save_msg(msg_escaped, octstr_imm("MT")); ***** >>>>> >>>>> * msg_destroy(msg_escaped);***** >>>>> >>>>> * }***** >>>>> >>>>> **/***** >>>>> >>>>> ** ** >>>>> >>>>> *mysql**> select count(*) from sent_sms;* >>>>> >>>>> *+----------+* >>>>> >>>>> *| count(*) |* >>>>> >>>>> *+----------+* >>>>> >>>>> *| 10 | * >>>>> >>>>> *+----------+* >>>>> >>>>> *1 row in set (0.00 sec)* >>>>> >>>>> * * >>>>> >>>>> ** ** >>>>> >>>>> ** ** >>>>> >>>>> On Wed, Aug 8, 2012 at 6:14 PM, Rene Kluwen <rene.klu...@chimit.nl> >>>>> wrote:**** >>>>> >>>>> Hello Ravindra,**** >>>>> >>>>> **** >>>>> >>>>> 1. The sent_sms table is a log table. It records all smsmessages >>>>> that have been sent, either through a connected >>>>> smsbox or via an insert in send_sms. Entries are never deleted. If >>>>> you don’t need a log of your sms messages, you can comment out the >>>>> line “gw_sql_save_msg(msg, octstr_imm("MT"));” in the gw/sqlbox.c >>>>> file.**** >>>>> >>>>> 2. If you don’t need them, you can just delete everything from >>>>> the sent_sms table, for instance in a cron table. Or otherwise, see 1. >>>>> **** >>>>> >>>>> 3. sqlbox doesn’t manage an internal queue. If you need >>>>> information about the sqlbox queue size, just apply a “select count(*) >>>>> from send_sms” and it will give you the number of messages still in the >>>>> queue.**** >>>>> >>>>> 4. There’s several options. If you don’t need a log of the >>>>> messages that are sent, comment out the line that saves sent messages (see >>>>> 1.). Also, if you want to increase speed of inserting messages in >>>>> send_sms, >>>>> you can look at the mysql syntax “INSERT… DELAYED”. This is what your >>>>> client program should do. Another thing is to adjust the >>>>> SQLBOX_MYSQL_SELECT_QUERY query in gw/sql_mysql.h. You can increase >>>>> LIMIT 0,1 to a bigger value. I never bothered to test this out on high >>>>> volumes. So please feel free to test and share your experiences in the >>>>> list. >>>>> Also there’s a number of things you can do to increase mysql speed, >>>>> which is beyond the scope of this mailinglist.**** >>>>> >>>>> **** >>>>> >>>>> **** >>>>> >>>>> Hope this helps,**** >>>>> >>>>> **** >>>>> >>>>> Rene**** >>>>> >>>>> **** >>>>> >>>>> *From:* Ravindra Gupta // Viva [mailto:ravin...@vivainfomedia.com] >>>>> *Sent:* Wednesday, 08 August, 2012 09:34**** >>>>> >>>>> >>>>> *To:* Rene Kluwen >>>>> *Cc:* us...@vm1.kannel.org >>>>> *Subject:* Re: dlr-url call on sqlbox**** >>>>> >>>>> **** >>>>> >>>>> Dear Rene,**** >>>>> >>>>> **** >>>>> >>>>> While working on sqlbox, I have come across some queries :- **** >>>>> >>>>> 1. When we insert on send_sms table, it invokes and sends it to >>>>> sent_sms which further submits it to the Operator and it deletes the >>>>> entry >>>>> from send_sms. However when we receive the deliver_sm from operator, >>>>> it doesn't >>>>> deletes the entry from sent_sms table.**** >>>>> 2. If the above scenario continues it would increase the size of >>>>> sent_sms table and will affect the mysql performance.**** >>>>> 3. How we will manage queue in sqlbox, is there any patch >>>>> available which will give us the queue related information.**** >>>>> 4. How to increase insertion speed to send_sms tables or sent_sms >>>>> table. **** >>>>> >>>>> ** ** >>>>> >>>>> On Wed, Aug 1, 2012 at 6:24 PM, Rene Kluwen <rene.klu...@chimit.nl> >>>>> wrote:**** >>>>> >>>>> I don’t understand quite what you mean with your questions.**** >>>>> >>>>> But to answer:**** >>>>> >>>>> **** >>>>> >>>>> 1. Yes, you can send bulk sms through smsbox. It should work >>>>> better than sending them via smsbox.**** >>>>> >>>>> 2. Performance issues are dependant of a number of things. >>>>> Check the complete chain (smsbox -> sqlbox -> mysql -> bearerbox -> >>>>> smsc). Usually sqlbox is not the cause.**** >>>>> >>>>> **** >>>>> >>>>> == Rene**** >>>>> >>>>> **** >>>>> >>>>> *From:* Ravindra Gupta // Viva [mailto:ravin...@vivainfomedia.com] >>>>> *Sent:* Wednesday, 01 August, 2012 12:29 >>>>> *To:* Rene Kluwen >>>>> *Cc:* us...@vm1.kannel.org**** >>>>> >>>>> >>>>> *Subject:* Re: dlr-url call on sqlbox**** >>>>> >>>>> **** >>>>> >>>>> **** >>>>> >>>>> Dear Rene,**** >>>>> >>>>> **** >>>>> >>>>> Thanks for your response.**** >>>>> >>>>> **** >>>>> >>>>> We have successfully configured SQLBOX in our server.**** >>>>> >>>>> **** >>>>> >>>>> We need some query as given below.**** >>>>> >>>>> **** >>>>> >>>>> 1. Can we send bulk load using SQLBOX i.e ( 5 to 10 Lac sms).**** >>>>> 2. SQLBOX totally on database so any performance issue occurred >>>>> when we have inserted 1 Lac to 10 Lac sms.**** >>>>> 3. We have used mysql 5.5 version.**** >>>>> >>>>> Kindly share your expert sugesstion for the same.**** >>>>> >>>>> **** >>>>> >>>>> On Wed, Aug 1, 2012 at 3:05 PM, Rene Kluwen <rene.klu...@chimit.nl> >>>>> wrote:**** >>>>> >>>>> The error is self-explanatory.**** >>>>> >>>>> You should just dlrencode your parameters in the dlr-url. Not the url >>>>> itself.**** >>>>> >>>>> **** >>>>> >>>>> So insert: >>>>> http://10.0.0.1/sms/dlrNEW.php?dlr=%25d%26to%3D%25p%26smsID%3D%25A%26BatchID%3Dravi123-5d12d21asdq1asravi >>>>> **** >>>>> >>>>> **** >>>>> >>>>> == Rene**** >>>>> >>>>> **** >>>>> >>>>> **** >>>>> >>>>> **** >>>>> >>>>> ** ** >>>>> >>>> >>>> >>> >> >