Re: mysql-dlr storage problem

[testn4n1]

Yes, that problem is solved. But now i am having another problem.

When i insert into the database using the following query, the dlr_url is
called instantly and it is called as-it-is. i.e. the %i, %p etc. values are
not replced with actual values. After the actual delivery report arrives,
the same url is called again without actual values i.e %i, %t, %P etc. are
passed. Please help.

INSERT INTO send_sms (momt, sender, smsc_id, receiver, msgdata, sms_type,
dlr_mask, dlr_url, boxc_id) VALUES ('MT', '1234', 'smpp-clickatell',
'919067184864', 'Abbe russshuuuu', 2, 31, '
http://192.168.111.151:8085/Kannel_Test/Receive_dlr?message=%25a&timestamp=%25t&sender=%25p&receiver=%25P&smsc_id=%25i&dlr_value=%25d&msg_id=%25F&sendsms_user=%25n
'
, 'box1');


On 11 June 2013 19:29, spameden <spame...@gmail.com> wrote:

> Quoting Rene on this one:
>
> On Wed, Aug 1, 2012 at 3:05 PM, Rene Kluwen <rene.kluwen at chimit.nl 
> <http://www.kannel.org/mailman/listinfo/users>> 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
>
>
>
>
> 2013/6/11 testn4n1 <testn...@gmail.com>
>
>> Hi,
>>
>> I am facing some problems.
>> When i insert into the database using the following query, the dlr_url is
>> called instantly and it is called as-it-is. i.e. the %i, %p etc. values are
>> not replced with actual values. After the actual delivery report arrives,
>> the same url is called again without actual values i.e %i, %t, %P etc. are
>> passed. Please help.
>>
>> INSERT INTO send_sms (momt, sender, smsc_id, receiver, msgdata, sms_type,
>> dlr_mask, dlr_url, boxc_id) VALUES ('MT', '1234', 'smpp-clickatell',
>> '919067184864', 'Abbe russshuuuu', 2, 31, '
>> http://192.168.111.151:8085/Kannel_Test/Receive_dlr?message=%25a&timestamp=%25t&sender=%25p&receiver=%25P&smsc_id=%25i&dlr_value=%25d&msg_id=%25F&sendsms_user=%25n',
>> 'box1');
>>
>>
>> On 11 June 2013 13:12, testn4n1 <testn...@gmail.com> wrote:
>>
>>> I am getting this error:
>>>
>>> 2013-06-11 12:27:07 [4669] [7] WARNING: DLR[mysql]: DLR from SMSC<smpp>
>>> for DST<919067184864> not found.
>>> 2013-06-11 12:27:07 [4669] [7] ERROR: SMPP[smpp]: got DLR but could not
>>> find message or was not interested in it id<400000007077826>
>>> dst<919067184864>, type<1>
>>>
>>> I read on a blog where it says "try the smsc group parameter
>>> "msg-id-type = 0x01"
>>>
>>> In my configuration, i have already set this but its not working.
>>>
>>>
>>>
>>> On 11 June 2013 12:12, Jacob Eiler <jacob.ei...@apide.com> wrote:
>>>
>>>> Hi James
>>>>
>>>> It is only the query parameter values part that should be encoded:
>>>>
>>>>
>>>> http://192.168.111.151:8085/Kannel_Test/Recieve_dlr?message=%25a&timestamp=%25tetc
>>>>
>>>> Jacob Eiler
>>>> Apide ApS
>>>> e: jacob.ei...@apide.com
>>>> t: +45 2374 0486
>>>> w: apide.com
>>>>
>>>>
>>>> Den 11/06/2013 kl. 08.26 skrev testn4n1 <testn...@gmail.com>:
>>>>
>>>> Hi,
>>>>
>>>> Hi tried the following insert query with the encoded url:
>>>>
>>>> INSERT INTO send_sms (momt, sender, receiver, msgdata, sms_type,
>>>> dlr_mask, dlr_url, boxc_id) VALUES ('MT', '1234', '919067184864', 'Abbe
>>>> russshuuuu', 2, 31,
>>>> 'http%3A%2F%2F192.168.111.151%3A8085%2FKannel_Test%2FReceive_dlr%3Fmessage%3D%25a%26timestamp%3D%25t%26sender%3D%25p%26receiver%3D%25P%26smsc_id%3D%25i%26dlr_value%3D%25d%26msg_id%3D%25F%26sendsms_user%3D%25n',
>>>> 'box1');
>>>>
>>>>
>>>> Kannel gives the following error:
>>>>
>>>> ERROR: URL
>>>> <http%3A%2F%2F192.168.111.151%3A8085%2FKannel_Test%2FReceive_dlr%3Fmessage%3D%25a%26timestamp%3D%25t%26sender%3D%25p%26receiver%3D%25P%26smsc_id%3D%25i%26dlr_value%3D%25d%26msg_id%3D%25F%26sendsms_user%3D%25n>
>>>> doesn't start with `http://' nor `https://'
>>>>
>>>>
>>>> Regards,
>>>> James
>>>>
>>>> On 10 June 2013 19:27, spameden <spame...@gmail.com> wrote:
>>>>
>>>>> check page 91 of the guide (Table 6-9. Parameters (Escape Codes))
>>>>>
>>>>>
>>>>> 2013/6/10 testn4n1 <testn...@gmail.com>
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> I read the user-guide. It says about url encoding in the MO Messages
>>>>>> section. but it does not specify what are the key and values for the
>>>>>> parameters. i.e. which are the common parameters we need to pass in the
>>>>>> dlr-url.
>>>>>> Only an example is given like this:
>>>>>>
>>>>>> http://localhost/myscript?...&meta-data=%D&;...
>>>>>>
>>>>>> can you please tell me which are the other parameters and their
>>>>>> values?
>>>>>> also, it specifies %D as value for the meta-data key. Is it something
>>>>>> that kannel replaces %D with some other value and then calls the 
>>>>>> specified
>>>>>> url? if yes, then what are the other such values for the keys?
>>>>>>
>>>>>> Regards,
>>>>>> James
>>>>>>
>>>>>>
>>>>>> On 10 June 2013 18:23, spameden <spame...@gmail.com> wrote:
>>>>>>
>>>>>>> Yes, you need to:
>>>>>>>
>>>>>>> 1) URLENCODE URL before inserting into database
>>>>>>> 2) pass parameters into URL, i.e.
>>>>>>>
>>>>>>> example:
>>>>>>> http%3A%2F%2F192.168.111.151%3A8085%2FKannel_Test%2FReceive_dlr%3Fsmsc-id%3D%25i%22%22
>>>>>>>
>>>>>>> Once again, read user-guide.
>>>>>>>
>>>>>>>
>>>>>>> 2013/6/10 testn4n1 <testn...@gmail.com>
>>>>>>>
>>>>>>>> ok...the url i provided gets called the second i insert data into
>>>>>>>> the send_sms table but no parameters are passed. i printed all the 
>>>>>>>> request
>>>>>>>> parameters but got nothing.
>>>>>>>>
>>>>>>>>
>>>>>>>> On 10 June 2013 17:58, spameden <spame...@gmail.com> wrote:
>>>>>>>>
>>>>>>>>> To achieve this you need to put smsbox in between of the sqlbox
>>>>>>>>> and bearerbox or enforce smsbox id into boxc_id field, i.e.:
>>>>>>>>>
>>>>>>>>> INSERT INTO send_sms (momt, sender, receiver, msgdata, sms_type,
>>>>>>>>> dlr_mask, dlr_url, boxc_id) VALUES ('MT', '1234', '919067184864', 
>>>>>>>>> 'Abbe
>>>>>>>>> russshuuuu', 2, 31, '
>>>>>>>>> http://192.168.111.151:8085/Kannel_Test/Receive_dlr',
>>>>>>>>> 'smsbox_id');
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> 2013/6/10 testn4n1 <testn...@gmail.com>
>>>>>>>>>
>>>>>>>>>> i am trying to send sms by inserting data into send_sms table and
>>>>>>>>>> want to process dlrs with script using dlr_url. So, i have changed 
>>>>>>>>>> the
>>>>>>>>>> configuration to the following but i cannot receive anything in my
>>>>>>>>>> application:
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> group = sendsms-user
>>>>>>>>>> username = tester
>>>>>>>>>> password = foobar
>>>>>>>>>> forced-smsc = smpp-nexmo
>>>>>>>>>> name = users
>>>>>>>>>> dlr-url = http://192.168.111.151:8085/Kannel_Test/Receive_dlr
>>>>>>>>>>
>>>>>>>>>> #user-deny-ip = "*.*.*.*"
>>>>>>>>>> user-allow-ip = "*.*.*.*"
>>>>>>>>>> max-messages = 3
>>>>>>>>>> concatenation = 1
>>>>>>>>>> omit-empty = 1
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> I am executing the following query for inserting the data:
>>>>>>>>>>
>>>>>>>>>> INSERT INTO send_sms (momt, sender, receiver, msgdata, sms_type,
>>>>>>>>>> dlr_mask, dlr_url) VALUES ('MT', '1234', '919067184864', 'Abbe 
>>>>>>>>>> russshuuuu',
>>>>>>>>>> 2, 31, 'http://192.168.111.151:8085/Kannel_Test/Receive_dlr');
>>>>>>>>>>
>>>>>>>>>> The dlr is not routed to my specified url. Can you please tell me
>>>>>>>>>> what is wrong here?
>>>>>>>>>>
>>>>>>>>>> Regards,
>>>>>>>>>> James
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> On 10 June 2013 17:06, spameden <spame...@gmail.com> wrote:
>>>>>>>>>>
>>>>>>>>>>> 2013/6/10 testn4n1 <testn...@gmail.com>
>>>>>>>>>>>
>>>>>>>>>>>> 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 <spame...@gmail.com> 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 <testn...@gmail.com>
>>>>>>>>>>>>>
>>>>>>>>>>>>>> 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.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>