Re: dlr-url call on sqlbox
actually I've just checked contrib/kannel-monitor it works just fine too and have advanced features. I might extend it later to show sqlbox queue's as well. 2012/10/26 Ravindra Gupta // Viva > > Dear Spameden, > > Thanks for your response. > > > > > On Fri, Oct 26, 2012 at 3:42 PM, spameden wrote: > >> I've attached zip archive with complete code >> >> You'd need to change few parameters in kannel_status.php to get it >> working. >> >> >> 2012/10/26 Ravindra Gupta // Viva >> >>> >>> Dear Spameden, >>> >>> We have created one page in sqlbox server and I have found there is >>> missing two files i.e (class_mysql.php, curl.class.php).Kindly provide >>> me both the php code for the same. >>> >>> --- >>> >>> >> require_once('class_mysql.php'); >>> include_once('curl.class.php'); >>> $DB=NEW DB_MYSQL('kannel_db', 'kannel', 'password', 'sqladmin'); >>> $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(); >>> ?> >>> >>> >>> Kannel Status >>> >>> Current Pending Queue: >>> var_dump($result); >>> >>> : '.$tmp[ >>> 'count'].'')?> >>> >> >>> ?> >>> >>> get('http://180.149.242.80:13000/status')?> >>> >>> >>> >>> >>> >>> we have got the below error. >>> >>> PHP Warning: require_once(class_mysql.php): failed to open stream: No >>> such file or directory in /var/www/html/monitor.php on line 2 >>> PHP Fatal error: require_once(): Failed opening required >>> 'class_mysql.php' (include_path='.:/usr/share/pear:/usr/share/php') in >>> /var/www/html/monitor.php on line 2 >>> >>> >>> >>> >>> On Sat, Oct 13, 2012 at 8:11 AM, spameden 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(); >>>> ?> >>>> >>>> >>>> Kannel Status >>>> >>>> Current Pending Queue: >>>> >>>> : '.$tmp[ >>>> 'count'].'')?> >>>> >>> >>>> ?> >>>> >>>> get('http://127.0.0.1:13000/status')?> >>>> >>>> >>>> >>>> >>>>
Re: dlr-url call on sqlbox
Dear Spameden, Thanks for your response. On Fri, Oct 26, 2012 at 3:42 PM, spameden wrote: > I've attached zip archive with complete code > > You'd need to change few parameters in kannel_status.php to get it > working. > > > 2012/10/26 Ravindra Gupta // Viva > >> >> Dear Spameden, >> >> We have created one page in sqlbox server and I have found there is >> missing two files i.e (class_mysql.php, curl.class.php).Kindly provide >> me both the php code for the same. >> >> --- >> >> > require_once('class_mysql.php'); >> include_once('curl.class.php'); >> $DB=NEW DB_MYSQL('kannel_db', 'kannel', 'password', 'sqladmin'); >> $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(); >> ?> >> >> >> Kannel Status >> >> Current Pending Queue: >> var_dump($result); >> >> : '.$tmp[ >> 'count'].'')?> >> > >> ?> >> >> get('http://180.149.242.80:13000/status')?> >> >> >> >> >> >> we have got the below error. >> >> PHP Warning: require_once(class_mysql.php): failed to open stream: No >> such file or directory in /var/www/html/monitor.php on line 2 >> PHP Fatal error: require_once(): Failed opening required >> 'class_mysql.php' (include_path='.:/usr/share/pear:/usr/share/php') in >> /var/www/html/monitor.php on line 2 >> >> >> >> >> On Sat, Oct 13, 2012 at 8:11 AM, spameden 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(); >>> ?> >>> >>> >>> Kannel Status >>> >>> Current Pending Queue: >>> >>> : '.$tmp[ >>> 'count'].'')?> >>> >> >>> ?> >>> >>> get('http://127.0.0.1:13000/status')?> >>> >>> >>> >>> >>> >>> >>> 2012/8/29 Ravindra Gupta // Viva >>> >>>> 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 wrote: >>>> >>>>> Sorry… Now I see what is happening. >>>>> >>>>> You should comment out line 597 of the original gw/sqlbox.c as well.** >>>>> ** >>>>> >>>
Re: dlr-url call on sqlbox
;> 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(); >>>>> ?> >>>>> >>>>> >>>>> Kannel Status >>>>> >>>>> Current Pending Queue: >>>>> >>>>> : '.$tmp[ >>>>> 'count'].'')?> >>>>> >>>> >>>>> ?> >>>>> >>>>> get('http://127.0.0.1:13000/status')?> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> 2012/8/29 Ravindra Gupta // Viva >>>>> >>>>>> 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 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);* >>>>>>> >>>>>>> *
Re: dlr-url call on sqlbox
'count'] = $DB->next_record('cnt'); >>>> $result[$i]['name'] = $table; >>>> } >>>> $DB->close(); >>>> $get = new cURL(); >>>> ?> >>>> >>>> >>>> Kannel Status >>>> >>>> Current Pending Queue: >>>> >>>> : '.$tmp[ >>>> 'count'].'')?> >>>> >>> >>>> ?> >>>> >>>> get('http://127.0.0.1:13000/status')?> >>>> >>>> >>>> >>>> >>>> >>>> >>>> 2012/8/29 Ravindra Gupta // Viva >>>> >>>>> 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 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 >>>>>> 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
Re: dlr-url call on sqlbox
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 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 > >> 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 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(); >>> ?> >>> >>> >>> Kannel Status >>> >>> Current Pending Queue: >>> >>> : '.$tmp[ >>> 'count'].'')?> >>> >> >>> ?> >>> >>> get('http://127.0.0.1:13000/status')?> >>> >>> >>> >>> >>> >>> >>> 2012/8/29 Ravindra Gupta // Viva >>> >>>> Dear Rene >>>> >>>> We have successfully configured SQLBOX on our Linux server, I have some >>>> quer
Re: dlr-url call on sqlbox
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 > 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 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 kannel >> via 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(); >> ?> >> >> >> Kannel Status >> >> Current Pending Queue: >> >> : > 0:''.$tmp['count'].'')?> >> > >> ?> >> >> get('http://127.0.0.1:13000/status')?> >> >> >> >> >> >> >> 2012/8/29 Ravindra Gupta // Viva >> >>> 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 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.o
Re: dlr-url call on sqlbox
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 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 kannel via > 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(); > ?> > > > Kannel Status > > Current Pending Queue: > > : 0:''.$tmp['count'].'')?> > > ?> > > get('http://127.0.0.1:13000/status')?> > > > > > > > 2012/8/29 Ravindra Gupta // Viva > >> 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 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);* >>> >
Re: dlr-url call on sqlbox
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 kannel via 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: 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(); ?> Kannel Status Current Pending Queue: : '.$tmp['count'].'')?> get('http://127.0.0.1:13000/status')?> 2012/8/29 Ravindra Gupta // Viva > 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 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 >> wrote: >> >> Hello Ravindra, >> >> >> >> 1. The sent_sms table is a log table. It records all sms messages >> 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
Re: dlr-url call on sqlbox
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 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 wrote: > > > Hello Ravindra, > > > > 1. The sent_sms table is a log table. It records all sms messages > 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 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. Perform
RE: dlr-url call on sqlbox
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 wrote: Hello Ravindra, 1. The sent_sms table is a log table. It records all sms messages 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 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 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
RE: dlr-url call on sqlbox
What I suspect is that you were still running the old binary or maybe sqlbox didn't compile well after you made the changes. It's a little bit hard to tell, from your email. Maybe you can post some logs? == 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 wrote: Hello Ravindra, 1. The sent_sms table is a log table. It records all sms messages 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 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 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
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 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 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 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 > > > > > > ** ** >
RE: dlr-url call on sqlbox
Hello Ravindra, 1. The sent_sms table is a log table. It records all sms messages 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 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 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
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 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 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 > > > > ** ** >
RE: dlr-url call on sqlbox
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 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
RE: dlr-url call on sqlbox
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 From: Ravindra Gupta // Viva [mailto:ravin...@vivainfomedia.com] Sent: Wednesday, 01 August, 2012 10:17 To: Rene Kluwen Cc: users-requ...@vm1.kannel.org; us...@vm1.kannel.org Subject: Re: dlr-url call on sqlbox Dear Rene, Dear Rene, We have inserted asmsbox value in boxc_id, after then we have submitted the sms and found the error on smsbox.log. INSERT INTO send_sms (momt, sender, receiver, msgdata, sms_type, smsc_id, dlr_mask, dlr_url, boxc_id) VALUES ('MT', 'TEST', '91XX', 'SQLBOX TESTING SERVER plz ignore.', 2, 'test', 31, 'http%3A//10.0.0.1/sms/dlrNEW.php%3Fdlr%3D%25d%26to%3D%25p%26smsID%3D%25A%26 BatchID%3Dravi123-5d12d21asdq1asravi', 'asmsbox');
RE: dlr-url call on sqlbox
You should connect an smsbox with smsbox-id = smsbox-id. In that case, the dlr will be sent to that smsbox instance. And the dlr-url will get called. Of course, you are free to enter a custom smsbox-id. == Rene From: Ravindra Gupta // Viva [mailto:ravin...@vivainfomedia.com] Sent: Monday, 30 July, 2012 18:25 To: Rene Kluwen Cc: users-requ...@vm1.kannel.org; us...@vm1.kannel.org Subject: Re: dlr-url call on sqlbox Dear Rene, We have got below error. INSERT INTO send_sms (momt, sender, receiver, msgdata, sms_type, smsc_id, dlr_mask, dlr_url, boxc_id) VALUES ('MT', 'JMFINA', '91XX', 'SQLBOX TESTING SERVER', 2, 'test', 31, 'http://10.0.0.1/sms/dlrNEW.php%3Fdlr%3D%25d%26to%3D%25p%26smsID%3D%25A%26Ba tchID%3Dravi123-5d12d25be2a00b', 'smsbox-id' ) WARNING: Could not route message to smsbox id , smsbox is gone! 2012-07-31 03:25:22 [16354] [6] DEBUG: SMPP PDU 0x2c004d30 dump: 2012-07-31 03:25:22 [16354] [6] DEBUG: type_name: submit_sm 2012-07-31 03:25:22 [16354] [6] DEBUG: command_id: 4 = 0x0004 2012-07-31 03:25:22 [16354] [6] DEBUG: command_status: 0 = 0x 2012-07-31 03:25:22 [16354] [6] DEBUG: sequence_number: 265 = 0x0109 2012-07-31 03:25:22 [16354] [6] DEBUG: service_type: NULL 2012-07-31 03:25:22 [16354] [6] DEBUG: source_addr_ton: 5 = 0x0005 2012-07-31 03:25:22 [16354] [6] DEBUG: source_addr_npi: 0 = 0x 2012-07-31 03:25:22 [16354] [6] DEBUG: source_addr: "test" 2012-07-31 03:25:22 [16354] [6] DEBUG: dest_addr_ton: 1 = 0x0001 2012-07-31 03:25:22 [16354] [6] DEBUG: dest_addr_npi: 1 = 0x0001 2012-07-31 03:25:22 [16354] [6] DEBUG: destination_addr: "91XX" 2012-07-31 03:25:22 [16354] [6] DEBUG: esm_class: 3 = 0x0003 2012-07-31 03:25:22 [16354] [6] DEBUG: protocol_id: 0 = 0x 2012-07-31 03:25:22 [16354] [6] DEBUG: priority_flag: 0 = 0x 2012-07-31 03:25:22 [16354] [6] DEBUG: schedule_delivery_time: NULL 2012-07-31 03:25:22 [16354] [6] DEBUG: validity_period: NULL 2012-07-31 03:25:22 [16354] [6] DEBUG: registered_delivery: 1 = 0x0001 2012-07-31 03:25:22 [16354] [6] DEBUG: replace_if_present_flag: 0 = 0x 2012-07-31 03:25:22 [16354] [6] DEBUG: data_coding: 0 = 0x 2012-07-31 03:25:22 [16354] [6] DEBUG: sm_default_msg_id: 0 = 0x 2012-07-31 03:25:22 [16354] [6] DEBUG: sm_length: 21 = 0x0015 2012-07-31 03:25:22 [16354] [6] DEBUG: short_message: 2012-07-31 03:25:22 [16354] [6] DEBUG:Octet string at 0x2c004a60: 2012-07-31 03:25:22 [16354] [6] DEBUG: len: 21 2012-07-31 03:25:22 [16354] [6] DEBUG: size: 22 2012-07-31 03:25:22 [16354] [6] DEBUG: immutable: 0 2012-07-31 03:25:22 [16354] [6] DEBUG: data: 53 51 4c 42 4f 58 20 54 45 53 54 49 4e 47 20 53 SQLBOX TESTING S 2012-07-31 03:25:22 [16354] [6] DEBUG: data: 45 52 56 45 52 ERVER 2012-07-31 03:25:22 [16354] [6] DEBUG:Octet string dump ends. 2012-07-31 03:25:22 [16354] [6] DEBUG: SMPP PDU dump ends. 2012-07-31 03:25:22 [16354] [6] DEBUG: SMPP[test]: throughput (1.00,50.00) 2012-07-31 03:25:22 [16354] [6] DEBUG: SMPP[test]: throughput (1.00,50.00) 2012-07-31 03:25:22 [16354] [6] DEBUG: SMPP[test]: Got PDU: 2012-07-31 03:25:22 [16354] [6] DEBUG: SMPP PDU 0x2c004d30 dump: 2012-07-31 03:25:22 [16354] [6] DEBUG: type_name: submit_sm_resp 2012-07-31 03:25:22 [16354] [6] DEBUG: command_id: 2147483652 = 0x8004 2012-07-31 03:25:22 [16354] [6] DEBUG: command_status: 0 = 0x 2012-07-31 03:25:22 [16354] [6] DEBUG: sequence_number: 265 = 0x0109 2012-07-31 03:25:22 [16354] [6] DEBUG: message_id: 2012-07-31 03:25:22 [16354] [6] DEBUG:Octet string at 0x2c004a60: 2012-07-31 03:25:22 [16354] [6] DEBUG: len: 36 2012-07-31 03:25:22 [16354] [6] DEBUG: size: 37 2012-07-31 03:25:22 [16354] [6] DEBUG: immutable: 0 2012-07-31 03:25:22 [16354] [6] DEBUG: data: 33 31 64 39 61 33 38 63 2d 31 62 32 65 2d 34 30 31d9a38c-1b2e-40 2012-07-31 03:25:22 [16354] [6] DEBUG: data: 64 36 2d 61 38 33 35 2d 36 66 34 65 39 38 65 35 d6-a835-6f4e98e5 2012-07-31 03:25:22 [16354] [6] DEBUG: data: 62 38 30 39 b809 2012-07-31 03:25:22 [16354] [6] DEBUG:Octet string dump ends. 2012-07-31 03:25:22 [16354] [6] DEBUG: SMPP PDU dump ends. 2012-07-31 03:25:22 [16354] [6] DEBUG: DLR[internal]: Adding DLR smsc=test, ts=31d9a38c-1b2e-40d6-a835-6f4e98e5b809, src=test, dst=91XX, mask=31, boxc=smsbox-id 2012-07-31 03:25:22 [16354] [6] DEBUG: SMSC[test]: creating DLR message 2012-07-31 03:25:22 [16354] [6] DEBUG: SMSC[test]: DLR = http://10.0.0.1/sms/dlrNEW.php%3Fdlr%3D%25d%26to%3D%25p%26smsID%3D%25A%26Bat chID%3Dravi123-5d12d25be2a00b 2012-07-31 03:25:22 [16354] [6] WARNING: Could not route message to smsbox id , smsbox is gone! 2012-07-31 03:25:22 [16354] [6] DEBUG: SMPP[test]: thr
Re: dlr-url call on sqlbox
; php%3Fdlr%3D%25d%26to%3D%25p%26smsID%3D%25A%26 > BatchID%3D123-5d1Xa', 'smsbox-id' ); > > Where 'smsbox-id' is a random instance of any smsbox that you have > connected. > > > From: Ravindra Gupta // Viva [mailto:ravin...@vivainfomedia.com] > Sent: Monday, 30 July, 2012 16:43 > To: Rene Kluwen > Cc: users-requ...@vm1.kannel.org; us...@vm1.kannel.org > Subject: Re: dlr-url call on sqlbox > > Dear Rene, > > Can send some sample example. > > > On Mon, Jul 30, 2012 at 8:08 PM, Rene Kluwen > wrote: > This is a FAQ. > > Sqlbox doesn’t call dlr-url’s, since it’s sql based and not http based. > > The workaround is as follows: > > Insert as your “boxc_id” the smsbox id of your connected smsbox. > > == Rene > > From: users-boun...@vm1.kannel.org [mailto:users-boun...@vm1.kannel.org] > On > Behalf Of Ravindra Gupta // Viva > Sent: Monday, 30 July, 2012 16:31 > To: users-requ...@vm1.kannel.org; us...@vm1.kannel.org > Subject: dlr-url call on sqlbox > > Dear Team, > > How to call dlr-url on sqlbox. > > Kindly find the mysql query as given below. > > INSERT INTO send_sms (momt, sender, receiver, msgdata, sms_type, smsc_id, > dlr_mask, dlr_url) VALUES ('MT', 'DEMO', '919X', 'SQLBOX TESTING > SERVER', 2, 'test', 31, > 'http%3A//10.0.0.1/sms/dlrNEW. > php%3Fdlr%3D%25d%26to%3D%25p%26smsID%3D%25A%26 > BatchID%3D123-5d1Xa<http://10.0.0.1/sms/dlrNEW.php%3Fdlr%3D%25d%26to%3D%25p%26smsID%3D%25A%26 > BatchID%3D123-5d1Xa>' ); > > > >
RE: dlr-url call on sqlbox
INSERT INTO send_sms (momt, sender, receiver, msgdata, sms_type, smsc_id, dlr_mask, dlr_url, boxc_id) VALUES ('MT', 'DEMO', '919X', 'SQLBOX TESTING SERVER', 2, 'test', 31, 'http%3A//10.0.0.1/sms/dlrNEW.php%3Fdlr%3D%25d%26to%3D%25p%26smsID%3D%25A%26 BatchID%3D123-5d1Xa', 'smsbox-id' ); Where 'smsbox-id' is a random instance of any smsbox that you have connected. From: Ravindra Gupta // Viva [mailto:ravin...@vivainfomedia.com] Sent: Monday, 30 July, 2012 16:43 To: Rene Kluwen Cc: users-requ...@vm1.kannel.org; us...@vm1.kannel.org Subject: Re: dlr-url call on sqlbox Dear Rene, Can send some sample example. On Mon, Jul 30, 2012 at 8:08 PM, Rene Kluwen wrote: This is a FAQ. Sqlbox doesnt call dlr-urls, since its sql based and not http based. The workaround is as follows: Insert as your boxc_id the smsbox id of your connected smsbox. == Rene From: users-boun...@vm1.kannel.org [mailto:users-boun...@vm1.kannel.org] On Behalf Of Ravindra Gupta // Viva Sent: Monday, 30 July, 2012 16:31 To: users-requ...@vm1.kannel.org; us...@vm1.kannel.org Subject: dlr-url call on sqlbox Dear Team, How to call dlr-url on sqlbox. Kindly find the mysql query as given below. INSERT INTO send_sms (momt, sender, receiver, msgdata, sms_type, smsc_id, dlr_mask, dlr_url) VALUES ('MT', 'DEMO', '919X', 'SQLBOX TESTING SERVER', 2, 'test', 31, 'http%3A//10.0.0.1/sms/dlrNEW.php%3Fdlr%3D%25d%26to%3D%25p%26smsID%3D%25A%26 BatchID%3D123-5d1Xa' );
Re: dlr-url call on sqlbox
Dear Rene, Can send some sample example. On Mon, Jul 30, 2012 at 8:08 PM, Rene Kluwen wrote: > This is a FAQ. > > ** ** > > Sqlbox doesn’t call dlr-url’s, since it’s sql based and not http based.*** > * > > ** ** > > The workaround is as follows: > > ** ** > > Insert as your “boxc_id” the smsbox id of your connected smsbox. > > ** ** > > == Rene > > ** ** > > *From:* users-boun...@vm1.kannel.org [mailto:users-boun...@vm1.kannel.org] > *On Behalf Of *Ravindra Gupta // Viva > *Sent:* Monday, 30 July, 2012 16:31 > *To:* users-requ...@vm1.kannel.org; us...@vm1.kannel.org > *Subject:* dlr-url call on sqlbox > > ** ** > > Dear Team, > > ** ** > > How to call dlr-url on sqlbox. > > ** ** > > Kindly find the mysql query as given below. > > ** ** > > INSERT INTO send_sms (momt, sender, receiver, msgdata, sms_type, smsc_id, > dlr_mask, dlr_url) VALUES ('MT', 'DEMO', '919X', 'SQLBOX TESTING > SERVER', 2, 'test', 31, 'http%3A// > 10.0.0.1/sms/dlrNEW.php%3Fdlr%3D%25d%26to%3D%25p%26smsID%3D%25A%26BatchID%3D123-5d1Xa' > ); > > ** ** >
RE: dlr-url call on sqlbox
This is a FAQ. Sqlbox doesn't call dlr-url's, since it's sql based and not http based. The workaround is as follows: Insert as your "boxc_id" the smsbox id of your connected smsbox. == Rene From: users-boun...@vm1.kannel.org [mailto:users-boun...@vm1.kannel.org] On Behalf Of Ravindra Gupta // Viva Sent: Monday, 30 July, 2012 16:31 To: users-requ...@vm1.kannel.org; us...@vm1.kannel.org Subject: dlr-url call on sqlbox Dear Team, How to call dlr-url on sqlbox. Kindly find the mysql query as given below. INSERT INTO send_sms (momt, sender, receiver, msgdata, sms_type, smsc_id, dlr_mask, dlr_url) VALUES ('MT', 'DEMO', '919X', 'SQLBOX TESTING SERVER', 2, 'test', 31, 'http%3A//10.0.0.1/sms/dlrNEW.php%3Fdlr%3D%25d%26to%3D%25p%26smsID%3D%25A%26 BatchID%3D123-5d1Xa' );
dlr-url call on sqlbox
Dear Team, How to call dlr-url on sqlbox. Kindly find the mysql query as given below. INSERT INTO send_sms (momt, sender, receiver, msgdata, sms_type, smsc_id, dlr_mask, dlr_url) VALUES ('MT', 'DEMO', '919X', 'SQLBOX TESTING SERVER', 2, 'test', 31, 'http%3A// 10.0.0.1/sms/dlrNEW.php%3Fdlr%3D%25d%26to%3D%25p%26smsID%3D%25A%26BatchID%3D123-5d1Xa' );