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 |
+--------+------+---------+-----------------+---------+---------------+------------+---------+---------+---------+------+----------+--------+------+--------+----------+----------+----------+----------+---------+------+---------+------+---------+---------+-------+
| 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
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 = 130102013/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 failed2013-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 SQLBOX3rd 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 CONFIGdlr-storage = mysql
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
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 1smsc-id = gsm1
group = smsc
smsc = at
modemtype = auto
device = /dev/ttyS0
sim-buffering = true
log-file= "/var/log/kannel/smsc.log"
log-level=0# HEXONET HTTP-APIsmsc-id = hexonet
group = smsc
smsc = httpsend-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"
system-type = generic
port = 13004
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 = 19200group = smsboxbearerbox-port = 13010
smsbox-id = smsbox1
bearerbox-host = localhost
sendsms-port = 13003
log-file= "/var/log/kannel/smsbox.log"group = sendsms-userpassword = pass
username = user# MYSQL DELIVERY REPORTSgroup = mysql-connection
id = mydlr
host = localhost
username = root
password = root
database = kannel
max-connections = 10group = 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_idand 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 Connectionusername = root
group = mysql-connection
id = sqlbox-db
host = localhost
password = root
database = kannelGesendet: Mittwoch, 22. Mai 2013 um 13:43 Uhr Cc: "users@kannel.org" <users@kannel.org>, "Florian Klein" <fl.kl...@googlemail.com>
Betreff: Re: Re: Re: SQLBOX doesnt store SMS in databaseYou 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 database2013/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):- Datetime field in sent_sms table- Recipientreceiver
- SMSCsmsc_id- Messagemsgdata (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....ByeGesendet: 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 databasePlease 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- MessageI 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....Bye2013/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 = 19200group = smsboxsmsbox-id = smsbox1
bearerbox-host = localhost
sendsms-port = 13003
log-file= "/var/log/kannel/smsbox.log"group = sendsms-user
username = user1
password = pass1SQLBOX.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