Thanks, but this is not my question. You forgot to copy users, so doing it.
Also thanks for the trigger tip, I completely forgot about it. See below testn4n1. 2013/6/10 Ali Kashif <[email protected]> > default SQLBox stores the DLR in sent_sms, > you have these options > > 1) Modify sqlbox source [proper but difficult option, atleast for me] (as > spameden suggested) > 2) Create a script (may be php) which copies all DLRs from one table to an > other and run script in Cronjob > > *3) create Mysql Trigger, on sent_sms table (which i did, but some for > other purpose. and if you need DLRs immediately in some other table)* > > you need to do something like this (the trigger i am using is for totally > different purpose. you need to create yours, and i think it will AFTER > INSERT (i am using BEFORE INSERT), and again this is a way around, proper > is MOD SqlBox. > > CREATE > TRIGGER `copy_DLR` BEFORE INSERT ON `sent_sms` > FOR EACH ROW BEGIN > IF new.momt='DLR' THEN > INSERT INTO > `kannel`.`dlr1`(`smsc`,`boxc_id`,`ts`,`source`,`destination`,`service`,`url`,`mask`,`status`,`timestamp`,`changed`) > > VALUE(new.service,new.boxc_id,new.dlr_url,new.sender,new.receiver,new.time,new.dlr_url,new.dlr_mask,0,NULL,NOW()); > END IF; > > Hope that helps > > > On Mon, Jun 10, 2013 at 2:36 PM, spameden <[email protected]> wrote: > >> 2013/6/10 testn4n1 <[email protected]> >> >>> ok...but then why are there entries in my sent_sms table like the >>> following: >>> >>> 12 | DLR | 1234 | 919067184864 | NULL | >>> id%3A288230376265477711+sub%3A001+dlvrd%3A001+submit+date%3A1306061034+done+date%3A1306060504+stat%3ADELIVRD+err%3A000+text%3Anone++++++++++++++++ >>> | 1370495099 | smpp-nexmo | NULL | 51dc0ffb | NULL | 3 | NULL | >>> NULL | NULL | NULL | NULL | NULL | 1 | NULL | NULL >>> | NULL | NULL | NULL | sqlbox | NULL | ?smpp?dlr_err=000& | >>> >>> what are the other ways to handle dlr instead of modifying the source? >> >> >> because sqlbox moves everything into sent_sms table. >> >> you can process DLRs with a script supplied to dlr_url >> >> but it would be easier and more proper to modify sqlbox code to store >> where you want them. >> >> >> >>> >>> >>> On 10 June 2013 16:55, spameden <[email protected]> wrote: >>> >>>> dlr table only used to track which messages are awaiting reports >>>> >>>> you need to modify sqlbox source if you need to store DLR entries >>>> separately >>>> >>>> >>>> 2013/6/10 testn4n1 <[email protected]> >>>> >>>>> Hi, >>>>> >>>>> I am able to send sms using sqlbox and able to receive dlr reports >>>>> also. But, these reports do not go in the table that i specify. Instead, >>>>> the reports are stored in sent_sms table. Below is my configuration: >>>>> >>>>> Kannel.conf >>>>> ------------------ >>>>> >>>>> group = core >>>>> admin-port = 13000 >>>>> smsbox-port = 13001 >>>>> admin-password = bar >>>>> dlr-storage = mysql >>>>> >>>>> >>>>> group = smsbox >>>>> bearerbox-host = 127.0.0.1 >>>>> sendsms-port = 13013 >>>>> global-sender = 13013 >>>>> >>>>> >>>>> group = sendsms-user >>>>> username = tester >>>>> password = foobar >>>>> >>>>> >>>>> group = sms-service >>>>> keyword = nop >>>>> text = "You asked nothing and I did it!" >>>>> >>>>> >>>>> group = smsc >>>>> smsc = smpp >>>>> smsc-id = server1 >>>>> host = smpp0.net >>>>> port = 8000 >>>>> transceiver-mode = true >>>>> throughput = 10 >>>>> smsc-username = ********** >>>>> smsc-password = ********** >>>>> >>>>> >>>>> group = sendsms-user >>>>> username = tester >>>>> password = foobar >>>>> forced-smsc = server1 >>>>> name = users >>>>> #user-deny-ip = "*.*.*.*" >>>>> user-allow-ip = "*.*.*.*" >>>>> max-messages = 3 >>>>> concatenation = 1 >>>>> omit-empty = 1 >>>>> >>>>> >>>>> group = mysql-connection >>>>> id = mydlr >>>>> host = localhost >>>>> username = root >>>>> password = root >>>>> database = kannel >>>>> >>>>> >>>>> group = dlr-db >>>>> id = mydlr >>>>> table = dlr >>>>> field-smsc = smsc >>>>> field-timestamp = ts >>>>> field-source=source >>>>> field-destination = destination >>>>> field-service = service >>>>> field-url = url >>>>> field-mask = mask >>>>> field-status = status >>>>> field-boxc-id = boxc >>>>> >>>>> >>>>> ------------------------------ >>>>> --------------------------------------------- >>>>> >>>>> sqlbox.conf >>>>> ------------------ >>>>> >>>>> group = sqlbox >>>>> id = sqlbox-db >>>>> smsbox-id = sqlbox >>>>> bearerbox-host = localhost >>>>> bearerbox-port = 13001 >>>>> smsbox-port = 13005 >>>>> smsbox-port-ssl = false >>>>> sql-log-table = sent_sms >>>>> sql-insert-table = send_sms >>>>> >>>>> >>>>> >>>>> # MYSQL Connection >>>>> group = mysql-connection >>>>> id = sqlbox-db >>>>> host = localhost >>>>> username = root >>>>> password = root >>>>> database = kannel >>>>> >>>>> >>>>> --------------------------------------------------------------------------- >>>>> >>>>> The sms are sent successfully from send_sms and transferred to the >>>>> table sent_sms but the delivery reports are also stored in the same table >>>>> and the dlr table is always empty. >>>>> >>>>> If anyone has come accross this problem, please help me. >>>>> >>>> >>>> >>> >> >
