Excellent work Alex. This has been long overview. Thanks on behalf of everyone who uses SQL box for doing this.

I had a quick question is the time column on the sqlbox send_sms table purely a label or it can be used to schedule delivery. If not do you see any challenges with turning the time column into a basic scheduler.

Regards,

ehi

Ehi,

The “time” column it’s just a timestamp column. It is ignored on send_sms, and it gets a numeric timestamp on sent_sms.

Regarding scheduling, though I’m still doing a lot of cleanup on the code, it’s one of the issues I’m evaluating for future versions so stay tuned.

Regards,

Alex

Hi Alex:

After I successfully created my kannel RPM and installed, I am trying my way now on this sqlbox but it spits an error message when trying to “make”:

Note: all errors when running make, this is the excerpts of the messages:

sqlbox.c: At top level:
sqlbox.c:652: error: expected â)â before â*â token
sqlbox.c: In function âmainâ:
sqlbox.c:727: error: âOctstrâ undeclared (first use in this function)
sqlbox.c:727: error: âfilenameâ undeclared (first use in this function)
sqlbox.c:729: warning: implicit declaration of function âgwlib_initâ
sqlbox.c:731: warning: implicit declaration of function âget_and_set_debugsâ
sqlbox.c:735: warning: implicit declaration of function âoctstr_createâ
sqlbox.c:739: error: âcfgâ undeclared (first use in this function)
sqlbox.c:739: warning: implicit declaration of function âcfg_createâ
sqlbox.c:743: warning: implicit declaration of function âcfg_add_hooksâ
sqlbox.c:743: error: âsqlbox_is_allowed_in_groupâ undeclared (first use in this function)
sqlbox.c:743: error: âsqlbox_is_single_groupâ undeclared (first use in this function)
sqlbox.c:745: warning: implicit declaration of function âcfg_readâ
sqlbox.c:750: warning: implicit declaration of function âreport_versionsâ
sqlbox.c:752: warning: implicit declaration of function âinit_sqlboxâ
sqlbox.c:756: warning: implicit declaration of function âcfg_destroyâ
sqlbox.c:761: error: âstruct server_typeâ has no member named âsql_leaveâ
sqlbox.c:762: warning: implicit declaration of function âgwlib_shutdownâ
make[2]: *** [sqlbox.o] Error 1
make[2]: Leaving directory `/usr/local/share/sqlbox-cvs/gw’
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory `/usr/local/share/sqlbox-cvs’
make: *** [all] Error 2

I am using the latest build of sqlbox found at http://www.kannel.org/~aguerrieri/

What am I missing? Thanks!

It spits me this msg:

sqlbox.c:66:25: error: gwlib/gwlib.h: No such file or directory
sqlbox.c:67:26: error: gwlib/dbpool.h: No such file or directory
sqlbox.c:68:20: error: gw/msg.h: No such file or directory
sqlbox.c:69:23: error: gw/shared.h: No such file or directory
sqlbox.c:70:19: error: gw/bb.h: No such file or directory
…

Where do i place the extracted sqlbox files?

Please advice. thanks!

Did you install the kannel-devel RPM package?

Regards,

Alex

hi alex!

i didn’t know that I still need to install kannel-devel RPM .. thanks for this info..

Anyway, I got it to work, what I did was (since i didn’t install the kannel-devel)
1. Uninstall the CVS RPM version via rpm -e (this is way cool! so easy to uninstall)
2. install the kannel cvs via old fashion (./configure make make install)
3. install sqlbox
4. success.

Will try again removing my installation of kannel and use the RPM files (CVS and devel)

Many thanks alex! By the way, can sqlbox be used in production server already?

Romulo,

Of course, compiling and installing Kannel from source also installs the header files. On the RPM those files are split on a different package because they’re not really part of the “binary” package and are not required unless you want to develop applications linking to Kannel.

Yes, sqlbox has been used on production for many years using the MySQL connector and probably the Postgresql as well.

Consider the new DB connectors (Oracle, Sqlite, Sqlite3 and Sdb) as “experimental”, though, because I’ve just released it and I didn’t make extensive tests with them.

Regards,

Alex

Thanks for your insights on this, really appreciate your answers on all my queries… Glad there’s someone (after all these years) that will make the lives of others much simpler and easier!

Hi Alex:

I just created a fresh install again of my Centos 5.2 Server and followed this procedure:

1. Install kannel-cvs and kannel-devel-cvs via my RPM packages. OK
2. Trying to install sqlbox and gave me this error while ./configure:

checking for cfg_create in -lgwlib… no
configure: error: Kannel gwlib is required!

I already installed the devel rpm package and i got this error.

How should I go about this?

Evidently Sqlbox is not finding libgwlib. Try running:

make distclean
./configure

and see if that solves it.

Otherwise, check if you have a folder on /usr/lib/kannel with libgwlib.a in it, or if the files are on other place. In that case, use the –with-kannel-dir switch for ./configure to point it to the location of the libraries.

You can see all options running ./configure –help

Regards,

Alex

Still, same error occurs:

after running ./bootstrap, the make distclean doesn’t help much as it outputs this message:

[root@centos-test sqlbox-cvs]# make distclean
make: *** No rule to make target `distclean’. Stop.
[root@centos-test sqlbox-cvs]# ls /usr/lib/kannel
libgw.a libgwlib.a libwap.a

I do have libgwlib.a located on the correct folder. Am I missing something here?

Try this:

./configure –with-kannel-dir=/usr

Regards,

Alex

Still, no luck with the above command:

-I/usr/include/mysql
checking for cfg_create in -lgwlib… no
configure: error: Kannel gwlib is required!

Romulo,

I’ve just tried this on a CentOS box installing the RPM’s and worked without any issues. You’re probably having problems with remnants from a previous install or uncommon file locations. Please make sure that the .a and .h are on /usr/lib/kannel and /usr/include/kannel.

Also check if you have the gw-config file on /usr/bin (or any other place).

Regards,

Alex

Here are my files:

[root@centos-test build]# ls /usr/bin/gw-config
/usr/bin/gw-config
[root@centos-test build]# ls /usr/lib/kannel
libgw.a libgwlib.a libwap.a
[root@centos-test build]# ls /usr/include/kannel
gw gw-config.h gwlib wap
[root@centos-test build]# ls /usr/bin/gw-config
/usr/bin/gw-config
[root@centos-test build]#

The way I installed was:
1. Fresh Install Centos 5.2 via VMWare
2. yum update
3. Install kannel RPMs (cvs and devel) — I already created these rpms before ff your steps
— RPM installation SUCCESS
4. wget sqlbox 0.71, cd to /usr/share/local, tar xvf sqlbox*
5. cd /sqlbox*
6. ./bootstrap
7. ./configure
8. Same error as mentioned earlier on my post

another route:
4. rm -Rf sqlbox-cvs
5. tar xvf sqlbox
6. ./bootstrap
7. ./configure –with-kannel-dir=/usr
8. Same error as mentioned earlier on my post

Pretty weird, the very same steps under CentOS 5.2 worked for me. Are you sure the ./bootstrap’s not yielding any errors?

Wait, im creating again my clean vmware image for centos and will log my result here following the above steps again.

Thanks Alex for your support here..

It worked now. Thanks for pointing out my error.

Alex

1. Does any status column (on sqlbox database) get updated automatically, in real time, when as DLR gets to kannel?

2. If i add a new column(s) to the table for my billing/accounting purposes, will the operation of SQLBOX be affected in any way?

1. No, there’s not such a column.

2. You could add extra columns, but I wouldn’t recommend you to do so, since you’d be depending on the application behaviour and column names to work. Better to add a second table instead.

Alex,

Thanks for you reply. Is there any way to manage DLR with SQLbox?

Sqlbox can store DLR’s just like regular messages. However, if what you need is to handle the DLR’s as they reach Kannel, you should use Kannel’s built-in functionality for that.

Hi Alex,

I realise this is an old thread but wanted to ask you a question. First of all thank you immensely for the great tutorials, I have used them throughout the implementation of our Kannel Servers.

What I wanted to know related to sqlbox and mysql dlr-storage.

We have a 2 way bulk sms account and wanting to configure mysql-dlr and sqlbox to save outgoing sms’s and dlrs for reporting purposes.

I pretty much know how this is done apart from a few things which I will iron out when I build a test system.

What I wanted to know is if it is possible to to have multiple send-sms users in my kannel conf and somehow attahce a unique message id to every outgoing message and if the recipient receives the sms allow them to reply to the sms and have it show up in the send-sms users receives sms profile somehow.

Example.

user1 send sms to 0834445556 via kannel and recipient receives with our bulk sms msisdn. The recipient replies to the sms and kannel receives it and inserts the sms into user1 received sms somehow. Also then when user 1 views his sent sms’s he has a link to view the dlr for that sms via a web interface which we can build?

Thanks In Advance

You can have many send-sms users of course, however, there’s no “native” way to tie MO responses to MT messages. This is not a Kannel limitation, but a limitation on the protocols used to transfer messages from/to the SMSC’s.

Usually that logic is done on the application layer by defining some sort of “session” that it’s kept open for a given amount of time.

Thanks Alex,

One more Thing, I am trying to install sqlbox after I have installed kannel cvs using your rpm guide but I am unsuccessful. I noticed in a previous post that you need the devel package as well. Do i need to install and if so do I anstall the same way as i did with the cvs?

Bash

Yes, you need the kannel-devel packages installed in order to compile sqlbox. You may need to add the –with-kannel-dir switch if the ./configure script cannot find out your kannel installation.

Thanks Alex,

assuming it would be /usr/src/redhat/BUILD/kannel-cvs/ for the kannel DIR for use with ./configure?

Thanks again for the quick response…

Hi,
I have free TDS installed on myserver.
Can u please guide me to configure sqlbox to support MSSQL SERVER.

sqlbox uses kannel’s DB interfaces, so you need to add support for FreeTDS on your kannel installation first. Recompile kannel using –with-mssql and then do the same when compiling sqlbox.

Alex

Hi Alex

i know this is a pretty basic question but i understand sqlbox can handle outgoing sms messages but what about incoming messages? We have a SMPP account and i was able to configure Kannel with sqlbox for all outgoing messages but i am not sure how to route sms from kannel into the database. Is this something the sqlbox can handle? if it can then how?

Thanks
Karan

Karan,

Yes, SqlBox can handle MO Traffic as well. To do that, you need to put SqlBox between smsbox and bearerbox, so all traffic intented for smsbox would pass thru SqlBox before.

To achieve that, you need to configure smsbox to use the SqlBox port instead of bearerbox’s. You need to start SqlBox before smsbox, otherwise smsbox would fail to start.

Hi all

I have problem with sqlbox

—
2009-12-05 12:09:32 [19392] [0] INFO: Starting to log to file /var/log/sms/sqlbox.log level 3
2009-12-05 12:09:32 [19392] [0] INFO: Added logfile `/var/log/sms/sqlbox.log’ with level `3′.
2009-12-05 12:09:32 [19392] [0] INFO: MYSQL: Connected to server at localhost.
2009-12-05 12:09:32 [19392] [0] INFO: MYSQL: server version 5.0.77, client version 5.0.77.
2009-12-05 12:09:32 [19391] [0] ERROR: Caught child PID (19392) which died due to signal 11
2009-12-05 12:09:32 [19391] [0] INFO: Child process crashed while starting. Exiting…
2009-12-05 12:09:32 [19391] [0] INFO: Going into gwlib_shutdown…

Please help me

Start by setting log-level to 0, it’ll give you a much better idea of the causes.

Hi

sqlbox starting (create pid file)
but process not listed in ps -ax

– sqlbox start from line
2009-12-05 15:22:15 [19572] [0] INFO: Starting to log to file /var/log/sms/sqlbox.log level 0
2009-12-05 15:22:15 [19572] [0] INFO: Added logfile `/var/log/sms/sqlbox.log’ with level `0′.
2009-12-05 15:22:15 [19572] [0] INFO: MYSQL: Connected to server at localhost.
2009-12-05 15:22:15 [19572] [0] INFO: MYSQL: server version 5.0.77, client version 5.0.77.

—-
but sqlbox not working
maybe not correct configuration

There should be more info on the log file. Set log-level to 0, start from the command line (without –daemonize) and you’d be able to see why is it failing.

–>Yes, SqlBox can handle MO Traffic as well. To do that, you need to put SqlBox between
–>smsbox and bearerbox, so all traffic intented for smsbox would pass thru SqlBox before.

–>To achieve that, you need to configure smsbox to use the SqlBox port instead of
–>bearerbox’s. You need to start SqlBox before smsbox, otherwise smsbox would fail to start

Hi Alex,

I am trying to get MO traffic into sqlbox in vain. Please clarify how this is done. I have set smsbox-port of core group and that on sqlbox similar, originally it was core smsbox-port=sqlbox bearerbox-port. Clearly I am doing something wrong cause SQLbox won’t even start up anyways for obvious reasons. And I have always started sqlbox before smsbox

This is because you need separate configuration files for bearerbox, smsbox and sqlbox. Under “normal” circumstances, bearerbox and smsbox could share the configuration file, but if you intend to put sqlbox in the middle, you need smsbox to point to sqlbox’s port and not bearerbox:

For example, to use this setup:
bearerbox < --[13001]--> sqlbox < --[13005]--> smsbox

You’d need something like this:

kannel.conf (to use with bearerbox):
group = core
smsbox-port = 13001
…

sqlbox.conf:
group = sqlbox
bearerbox-host = localhost
bearerbox-port = 13001
smsbox-port = 13005
…

smsbox.conf:
group = core
smsbox-port = 13005
…

smsbox.conf can be exactly the same file as kannel.conf, you only need to change the smsbox-port.

Hope this helps clarifying how it works.

Regards,

Alex

Hi Alex,

I did that and MO’s still do not end up in DB. I start the bearerbox, sqlbox then smsbox. Bearerbox and smsbox config are exactly the same file, with only the smsbox-port differing. Do I need to do any re-routing in the config?

Hi,

I got it to work in the end thanks. One more thing, I compile kannel witl –enable-localtime. This doesn’t seem to mean anything to sqlbox as the timestamps in the db are off. Anything I can change to fix this?

No, boxes are separated and doesn’t share that kind of configuration.

Hello Alex,

Does the new version of SQL box (SVN) support scheduling now?

Your comment on November 16th, 2008 at 10:47 am.
“Regarding scheduling, though I’m still doing a lot of cleanup on the code, it’s one of the issues I’m evaluating for future versions so stay tuned.”

Regards
Tapan Thapa

Not yet, but I think it will be soon. Stay tuned

Hey Alex,
I can’t find the source code for sqlbox anywhere. Could you please post a direct link to it?

It’s on the blog archive, among other places. Check here:

http://www.blogalex.com/archives/143

Hi Alex,

I start up sqlbox before smsbox to store all the incoming messages in a db. Now I need a way to filter out messages that go into the DB, I use a modem and often the operator sends a text to my MSISDN which I do not need and I manually have to remove them from the DB periodically. Is there a way to achieve this? I figured if I was using a sms-service I would use allowed-receiver-prefix (messages from the network come with the operator name as the sender ID most of the time). But I doubt this applies to my setup. Please advise

That’s not possible. Sqlbox stores everything it receives without applying any logic to it.

Hi Alex

Thanks for this. I got answers to practically all my questions on sqlbox.

Please is smppbox an smpp server for kannel? Where can I get smpp and documentation on it?

Thanks.

Akeko

Where can I get current stable smppbox and its documentation?

what exactly the smsbox do…. if i connect sqlbox directly to bearebox whats going to happen… please help me

i possible can u please explain in brief how kannel works…. programming point of view …so that i can make changes

Hi Alex!

I’m trying build a rpm from svn rev 48 but I have the following error:

+ /usr/bin/make
make[1]: Entering directory `/root/rpmbuild/BUILD/sqlbox-cvs’
/usr/bin/make all-recursive
make[2]: Entering directory `/root/rpmbuild/BUILD/sqlbox-cvs’
Making all in gw
make[3]: Entering directory `/root/rpmbuild/BUILD/sqlbox-cvs/gw’
gcc -DHAVE_CONFIG_H -I. -I.. -I../gw -I../gw -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector –param=ssp-buffer-size=4 -m64 -mtune=generic -D_XOPEN_SOURCE=600 -D_BSD_SOURCE -I/usr/include/openssl -I/usr/include/kannel -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector –param=ssp-buffer-size=4 -m64 -mtune=generic -D_XOPEN_SOURCE=600 -D_BSD_SOURCE -D_LARGE_FILES= -I/usr/include/libxml2 -I/usr/include/openssl -I/usr/include/mysql -MT sqlbox.o -MD -MP -MF .deps/sqlbox.Tpo -c -o sqlbox.o sqlbox.c
sqlbox.c: In function ‘run_sqlbox’:
sqlbox.c:450: warning: cast from pointer to integer of different size
sqlbox.c: In function ‘wait_for_connections’:
sqlbox.c:490: warning: cast to pointer from integer of different size
sqlbox.c: In function ‘sqlboxc_run’:
sqlbox.c:616: warning: cast from pointer to integer of different size
mv -f .deps/sqlbox.Tpo .deps/sqlbox.Po
gcc -DHAVE_CONFIG_H -I. -I.. -I../gw -I../gw -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector –param=ssp-buffer-size=4 -m64 -mtune=generic -D_XOPEN_SOURCE=600 -D_BSD_SOURCE -I/usr/include/openssl -I/usr/include/kannel -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector –param=ssp-buffer-size=4 -m64 -mtune=generic -D_XOPEN_SOURCE=600 -D_BSD_SOURCE -D_LARGE_FILES= -I/usr/include/libxml2 -I/usr/include/openssl -I/usr/include/mysql -MT sqlbox_mssql.o -MD -MP -MF .deps/sqlbox_mssql.Tpo -c -o sqlbox_mssql.o sqlbox_mssql.c
mv -f .deps/sqlbox_mssql.Tpo .deps/sqlbox_mssql.Po
gcc -DHAVE_CONFIG_H -I. -I.. -I../gw -I../gw -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector –param=ssp-buffer-size=4 -m64 -mtune=generic -D_XOPEN_SOURCE=600 -D_BSD_SOURCE -I/usr/include/openssl -I/usr/include/kannel -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector –param=ssp-buffer-size=4 -m64 -mtune=generic -D_XOPEN_SOURCE=600 -D_BSD_SOURCE -D_LARGE_FILES= -I/usr/include/libxml2 -I/usr/include/openssl -I/usr/include/mysql -MT sqlbox_mysql.o -MD -MP -MF .deps/sqlbox_mysql.Tpo -c -o sqlbox_mysql.o sqlbox_mysql.c
sqlbox_mysql.c: In function ‘mysql_fetch_msg’:
sqlbox_mysql.c:143: error: ‘struct sms’ has no member named ‘meta_data’
sqlbox_mysql.c: In function ‘mysql_save_msg’:
sqlbox_mysql.c:201: error: ‘struct sms’ has no member named ‘meta_data’
sqlbox_mysql.c:201: warning: operation on ‘stuffcount’ may be undefined
sqlbox_mysql.c:201: warning: operation on ‘stuffcount’ may be undefined
sqlbox_mysql.c:201: warning: operation on ‘stuffcount’ may be undefined
sqlbox_mysql.c:201: warning: operation on ‘stuffcount’ may be undefined
sqlbox_mysql.c:201: warning: operation on ‘stuffcount’ may be undefined
sqlbox_mysql.c:201: warning: operation on ‘stuffcount’ may be undefined
sqlbox_mysql.c:201: warning: operation on ‘stuffcount’ may be undefined
sqlbox_mysql.c:201: warning: operation on ‘stuffcount’ may be undefined
sqlbox_mysql.c:201: warning: operation on ‘stuffcount’ may be undefined
sqlbox_mysql.c:201: warning: operation on ‘stuffcount’ may be undefined
sqlbox_mysql.c:201: warning: operation on ‘stuffcount’ may be undefined
sqlbox_mysql.c:201: warning: operation on ‘stuffcount’ may be undefined
sqlbox_mysql.c:201: warning: operation on ‘stuffcount’ may be undefined
sqlbox_mysql.c:201: warning: operation on ‘stuffcount’ may be undefined
sqlbox_mysql.c:201: warning: operation on ‘stuffcount’ may be undefined
sqlbox_mysql.c:201: warning: operation on ‘stuffcount’ may be undefined
sqlbox_mysql.c:201: warning: operation on ‘stuffcount’ may be undefined
sqlbox_mysql.c:201: warning: operation on ‘stuffcount’ may be undefined
sqlbox_mysql.c:201: warning: operation on ‘stuffcount’ may be undefined
sqlbox_mysql.c:201: warning: operation on ‘stuffcount’ may be undefined
sqlbox_mysql.c:201: warning: operation on ‘stuffcount’ may be undefined
sqlbox_mysql.c:201: warning: operation on ‘stuffcount’ may be undefined
sqlbox_mysql.c:201: warning: operation on ‘stuffcount’ may be undefined
sqlbox_mysql.c:201: warning: operation on ‘stuffcount’ may be undefined
make[3]: *** [sqlbox_mysql.o] Error 1
make[3]: Leaving directory `/root/rpmbuild/BUILD/sqlbox-cvs/gw’
make[2]: *** [all-recursive] Error 1
make[2]: Leaving directory `/root/rpmbuild/BUILD/sqlbox-cvs’
make[1]: *** [all] Error 2
make[1]: Leaving directory `/root/rpmbuild/BUILD/sqlbox-cvs’
error: Bad exit status from /var/tmp/rpm-tmp.6137 (%build)

RPM build errors:
Bad exit status from /var/tmp/rpm-tmp.6137 (%build)
make: *** [rpm] Error 1

Do you have any idea what can be cause this error? Any help will be welcome. Thanks in advance.

Hi Alex, your work has allowed hours of coding for me Really thanks .. It would be really helpful if you can include sql file with the releases so that users can make databases more quickly . Thanks