Aw: Re: Re: Re: Re: SQLBOX doesnt store SMS in database

Thu, 23 May 2013 02:01:04 -0700

Hello spameden,
 
with your changes, the storing of information in SQL works ! Thank you very much.
 
Now I have and additional question. Before I ask I will give you an example.
 
I sent the message via this HTTP call:
http://xxx.xxx.xxx.xxx:13003/cgi-bin/sendsms?username=user&password=pass&from=1234566&to=10101010110&text=sqlbox+test+1
 
After the delivery the database looks like this:
+--------+------+---------+-----------------+---------+---------------+------------+---------+---------+---------+------+----------+--------+------+--------+----------+----------+----------+----------+---------+------+---------+------+---------+---------+-------+
| sql_id | momt | sender  | receiver        | udhdata | msgdata       | time       | smsc_id | service | account | id   | sms_type | mclass | mwi  | coding | compress | validity | deferred | dlr_mask | dlr_url | pid  | alt_dcs | rpi  | charset | boxc_id | binfo |
+--------+------+---------+-----------------+---------+---------------+------------+---------+---------+---------+------+----------+--------+------+--------+----------+----------+----------+----------+---------+------+---------+------+---------+---------+-------+
|      1 | MT   | 1234566 | 10101010110  | NULL    | sqlbox test 1 | 1369233519 | NULL    | user   | NULL    | NULL |        2 |   NULL | NULL |      0 |     NULL |     NULL |     NULL |     NULL | NULL    | NULL |    NULL | NULL | NULL    | smsbox1 | NULL  |
+--------+------+---------+-----------------+---------+---------------+------------+---------+---------+---------+------+----------+--------+------+--------+----------+----------+----------+----------+---------+------+---------+------+---------+---------+-------+
 
Some fields are filled with 'NULL'.
That is no no problem for most of the fields. 
But I need the field 'smsc_id' to be filled.

How can I get this field to be filled ?
For accounting purposes I need to know which SMSC was used for sending a specific message because they are differently in costs.
 
Thank you
 
Gesendet: Mittwoch, 22. Mai 2013 um 15:55 Uhr
Von: spameden <spame...@gmail.com>
An: "hans joachim" <cosmos...@web.de>
Cc: "users@kannel.org" <users@kannel.org>
Betreff: Re: Re: Re: Re: SQLBOX doesnt store SMS in database
do not include sqlbox.conf in the kannel.conf
 
as for smsbox you'd need to copy kannel.conf with bearerbox-port = 13010
 
in kannel.conf should be bearerbox-port = 13001
 
start smsbox with:
smsbox /etc/kannel/smsbox.conf after sqlbox and bearerbox
 
2013/5/22 hans joachim <cosmos...@web.de>
 
I don't want to use INSERT statement.
I send my messages via HTTP call.
 
I get this messages when i try to start SQLBOX:
 
2013-05-22 15:41:23 [19204] [1] ERROR: connect failed
2013-05-22 15:41:23 [19204] [1] ERROR: System error 111: Connection refused
2013-05-22 15:41:23 [19204] [1] ERROR: error connecting to server `localhost' at port `13001'
 
 
1st I start kannel itselt (bearerbox)
2nd I start SQLBOX 
3rd I want to start smsbox.
 
 
Here are my config files (perhaps something is worng with my ports?):
 
kannel.conf:
 
#
# Sample configuration file for Kannel bearerbox on Debian.
# See the documentation for explanations of fields.
#
# HTTP administration is disabled by default. Make sure you set the
# password if you enable it.
 
# MAIN CONFIG
group = core
admin-port = 13000
smsbox-port = 13001
admin-password = 123
access-log = "/var/log/kannel/sms-trace.log"
access-log-clean = true
access-log-format= "SMS ID="%F" TS="%t" TYPE="%l" SMSC="%i" FROM="%p" TO="%P" ENCODING="%c" TEXT="%b" UDH="%u""
log-file = "/var/log/kannel/bearerbox.log"
log-level = 0
dlr-storage = mysql
include = "/etc/kannel/sqlbox.conf"
 
#Fake SMSC for Testing
#group = smsc
#smsc = fake
#smsc-id = Fake smsc
#port = 13009
#connect-allow-ip = 127.0.0.1
 
# GSM-MODEM 1 
group = smsc
smsc = at
smsc-id = gsm1 

modemtype = auto 
device = /dev/ttyS0
sim-buffering = true
log-file= "/var/log/kannel/smsc.log"
log-level=0
 
# HEXONET HTTP-API

group = smsc
smsc = http
smsc-id = hexonet 

system-type = generic
port = 13004
send-url = "" href="http://xxx.xxx.xxx.xx/mobile1.php?s_id=user&s_pw=pass&service=fast&mobilenr=%p&text=%b" target="_blank">http://xxx.xxx.xxx.xx/mobile1.php?s_id=user&s_pw=pass&service=fast&mobilenr=%p&text=%b"
log-file = "/var/log/kannel/hexo.log"
status-success-regex = "code: 100"
 
group = modems
id = SIEMENS MC35i 
name = "Siemens MC35i"
detect-string = "SIEMENS"
detect-string2 = "MC35i"
init-string = "AT+CPMS="SM""
speed = 19200
 
group = smsbox
smsbox-id = smsbox1
bearerbox-host = localhost
sendsms-port = 13003
bearerbox-port = 13010
log-file= "/var/log/kannel/smsbox.log"
 
group = sendsms-user
username = user
password = pass
 
# MYSQL DELIVERY REPORTS
group = mysql-connection
id = mydlr
host = localhost
username = root 
password = root 
database = kannel
max-connections = 10
 
group = dlr-db
id = mydlr
table=logs
field-smsc=smsc
field-timestamp=date
field-destination=recipient
field-source=source
field-service=service
field-url=""> field-mask=mask
field-status=status
field-boxc-id=boxc_id
 
 
and the sqlbox.conf:
 
group = sqlbox
id = sqlbox-db
smsbox-id = sqlbox1
#global-sender = ""
bearerbox-host = localhost
bearerbox-port = 13001
smsbox-port = 13010
#smsbox-port-ssl = false
sql-log-table = sent_sms
sql-insert-table = send_sms
log-file = "/var/log/kannel/sqlbox.log"
log-level = 0
#ssl-client-certkey-file = ""
#ssl-server-cert-file = ""
#ssl-server-key-file = ""
#ssl-trusted-ca-file = ""
 
# Database connection examples. Please uncomment as needed
# Example MYSQL Connection
group = mysql-connection
id = sqlbox-db
host = localhost
username = root
password = root
database = kannel
 
 
 
Gesendet: Mittwoch, 22. Mai 2013 um 13:43 Uhr

Von: spameden <spame...@gmail.com>
An: "hans joachim" <cosmos...@web.de>
Cc: "users@kannel.org" <users@kannel.org>, "Florian Klein" <fl.kl...@googlemail.com>
Betreff: Re: Re: Re: SQLBOX doesnt store SMS in database
You need to use INSERT statement and directly insert into send_sms table or alternatively you can put sqlbox between smsbox and bearerbox, this way message sent through smsbox (HTTP call) should be logged into sqlbox.
 
2013/5/22 hans joachim <cosmos...@web.de>
 
Hello,
 
I know what the different fields in sent_sms table mean.
But there is no data in the fields when I send a message via HTTP-Call.
 
The database table is not filled. That is the problem.
 
 
Gesendet: Mittwoch, 22. Mai 2013 um 12:08 Uhr
Von: spameden <spame...@gmail.com>
An: "hans joachim" <cosmos...@web.de>
Cc: "Ali Kashif" <syedalikas...@gmail.com>, "users@kannel.org" <users@kannel.org>, "Florian Klein" <fl.kl...@googlemail.com>
Betreff: Re: Re: SQLBOX doesnt store SMS in database
 
 
2013/5/22 hans joachim <cosmos...@web.de>
Hello Ali,
 
I am aware of the feature of bulk message sending with SQL Injection of SQLBOX.
 
But that is not the feature I want. I only need the storing of information about outgoing messages for accounting purposees.
That means I want the following information in the database (Just like the information in the access.log):
 
- Date
time field in sent_sms table
- Recipient
 receiver
 
- SMSC
smsc_id
 
- Message
msgdata (url-encoded)
 
I don't know how to accomplish this goal. If SQLBOX is not the right way, what can i do ?
DLR's are depending of the provider I think....
 
Bye
 
Gesendet: Mittwoch, 22. Mai 2013 um 11:28 Uhr
Von: "Ali Kashif" <syedalikas...@gmail.com>
An: "Florian Klein" <fl.kl...@googlemail.com>
Cc: "users@kannel.org" <users@kannel.org>
Betreff: Re: SQLBOX doesnt store SMS in database
Please always CC to users.
 
On Wed, May 22, 2013 at 8:30 AM, Florian Klein <fl.kl...@googlemail.com> wrote:
Hello Ali,
 
I am aware of the feature of bulk message sending with SQL Injection of SQLBOX.
 
But that is not the feature I want. I need the storing of information about outgoing messages only for accounting purposees.
That means I want the following information in the database:
 
- Date
- Recipient
- SMSC
- Message
 
I don't know how to accomplish this goal. If SQLBOX is not the right way, what can i do ?
DLR's are depending of the provider I think....
 
Bye
 
2013/5/21 Ali Kashif <syedalikas...@gmail.com>
you need to insert data to in sqlbox `send_sms` table. and it will send the message through kannel and then copy the message to 'sent_sms'.
 
sqlbox is just link any other smsbox connected to kannel.
 
 
 
On Tue, May 21, 2013 at 4:03 PM, hans joachim <cosmos...@web.de> wrote:
Hello,
 
I want SQLBOX to store information about every outgoing sms in the database. I send my SMS via HTTP-Call like  "http://xxx.xxx.xxx.xx:xxxxx/cgi-bin/sendsms?username=user&password=pass&from=1234566&to=123456789&text=test" but no information is stored in my database.
 
SQLBOX connects and created the tables (send_sms & sent_sms). I see the SQLBOX in the Kannel-Webinterface too.
 
Can you please help me to find the error ?
 
KANNEL.CONF
 
# MAIN CONFIG
group = core
admin-port = 13000
smsbox-port = 13001
admin-password = 123
access-log = "/var/log/kannel/sms-trace.log"
access-log-clean = true
access-log-format= "SMS ID="%F" TS="%t" TYPE="%l" SMSC="%i" FROM="%p" TO="%P" ENCODING="%c" TEXT="%b" UDH="%u""
log-file = "/var/log/kannel/bearerbox.log"
log-level = 0
 
#Fake SMSC for Testing
#group = smsc
#smsc = fake
#smsc-id = Fake smsc
#port = 13009
#connect-allow-ip = 127.0.0.1
 
# GSM-MODEM 1 
group = smsc
smsc = at
smsc-id = GSM1
modemtype = auto 
device = /dev/ttyS0
sim-buffering = true
log-file= "/var/log/kannel/smsc.log"
log-level=0
 
# HEX HTTP-API
group = smsc
smsc = http
smsc-id = HEX
system-type = generic
port = 13004
send-url = "" href="http://xx.xx.xx.xx/api.php?s_id=user&s_pw=password&service=fast&mobilenr=%p&text=%b" target="_blank">http://xx.xx.xx.xx/api.php?s_id=user&s_pw=password&service=fast&mobilenr=%p&text=%b"
status-success-regex = "ok"
log-file = "/var/log/kannel/hex.log"
 
group = modems
id = SIEMENS MC35i 
name = "Siemens MC35i"
detect-string = "SIEMENS"
detect-string2 = "MC35i"
init-string = "AT+CPMS="SM""
speed = 19200
group = smsbox
 
smsbox-id = smsbox1
bearerbox-host = localhost
sendsms-port = 13003
log-file= "/var/log/kannel/smsbox.log"
 
group = sendsms-user
username = user1
password = pass1
 
 
SQLBOX.CONF:
 
group = sqlbox
id = sqlbox-db
smsbox-id = sqlbox1
#global-sender = ""
bearerbox-host = localhost
bearerbox-port = 13001
smsbox-port = 13010
#smsbox-port-ssl = false
sql-log-table = sent_sms
sql-insert-table = send_sms
log-file = "/var/log/kannel/sqlbox.log"
log-level = 0
#ssl-client-certkey-file = ""
#ssl-server-cert-file = ""
#ssl-server-key-file = ""
#ssl-trusted-ca-file = ""
 
# Database connection examples. Please uncomment as needed
# Example MYSQL Connection
group = mysql-connection
id = sqlbox-db
host = localhost
username = user
password = pass 
database = kannel
 
 
 
 

Reply via email to