SMS Server Tools 3
This site is hosted by Kekekasvi.com
 Menu
Basic information:
Additional information:
Support:
Get SMS Server Tools 3:
Additional Options

 Sponsored links

 Search
Custom Search

 Visitor locations
 
 SMS Server Tools 3 Community
Welcome, Guest. Please login or register. Sun Apr 11, 2021 12:56
SMSTools3 Community » Help and support Bottom

[answered] Getting mySQL to work with sms server tool

  This topic is locked

Page:  1

Author Post
Member
Registered:
May 2011
Location: calgary AB, Canada
Operating system name and version: Ubuntu 9.04
Version of smsd: 3.1.14
Smsd installed from: sources
Name and model of a modem / phone: Siemens TC65 Quad Band
Interface: serial (via USB interface)

I am just curious to find out how I should run the script file. Do I run it with "./sql_demo" or do I place the file into a particular location for the sms server tool to work with?

thanks

Administrator
Registered:
May 2009
Location: Jyväskylä, Finland
In the smsd.conf, define:
eventhandler = /usr/local/bin/smsd_eventhandler.sh

With this setting smsd will execute the script every time when SMS is sent, received, or failed to send.

If you are playing with "amounts" as in the sql_demo is done, save the content as smsd_eventhandler.sh. See also this topic: Sample eventhandler to store messages into SQL database.

Member
Registered:
May 2011
Location: calgary AB, Canada
Topic owner
Again. I appreciate your reply. Thank you very much.

Member
Registered:
May 2011
Location: calgary AB, Canada
Topic owner
First of all, thanks to you Keke, sms server tool is now logging received sms to mySQL db.

Nonetheless, I don't think the sms server tool is logging sent sms.

I ran: sudo sendsms ####### 'ping'

Even though I receive a text to my mobile phone from the sms server tool, I don't see a logged line in my mySQL.

sudo cat /usr/local/bin/smsd_eventhandler.sh:
#!/bin/bash

# This is an example script that logs all events into an SQL database

SQL_HOST=localhost
SQL_USER=root
SQL_PASSWORD="********"
SQL_DATABASE=smsd
SQL_TABLE=sms_log

DATE=`date +"%Y-%m-%d %H:%M:%S"`
#Extract data from the SMS file
FROM=`formail -zx From: < $2 | sed 's/"//g'`
TO=`formail -zx To: < $2`
#Remove plus sign, spaces, minus and short number prefix
TO=`echo "$TO" | sed 's/ //g' | sed 's/+//g' | sed 's/s//g' | sed 's/-//g'`
SUBJECT=`formail -zx Subject: < $2`
SENT=`formail -zx Sent: < $2`
TEXT=`sed -e '1,/^$/ d' < $2`
TEXTSQL=$(echo "$TEXT" | sed 's/\\/\\\\/g' | sed 's/\"/\\\"/g')

#Set some SQL parameters
if [ "$SQL_PASSWORD" != "" ]; then
  SQL_ARGS="-p$SQL_PASSWORD";
else
  SQL_ARGS="";
fi
SQL_ARGS="-h $SQL_HOST -u $SQL_USER $SQL_ARGS -D $SQL_DATABASE -s -e"

#Insert a new entry into the SQL table

if [ "$1" = "FAILED" ] || [ "$1" = "SENT" ]; then
  mysql $SQL_ARGS "insert into $SQL_TABLE (\`index\`,\`type\`,\`sent\`,\`sender\`,\`receiver\`,\`msgid\`,\`text\') values (NULL,\"$1\",\"$DATE\",\"$FROM\",\"$TO\",\"$3\",\"$TEXTSQL\");";
elif [ "$1" = "RECEIVED" ]; then
   echo "RECEIVED!!"
   mysql $SQL_ARGS "insert into $SQL_TABLE (\`
index\`,\`type\`,\`sent\`,\`received\`,\`sender\`,\`receiver\`,\`text\`) values (NULL,\"RECEIVED\",\"$SENT\",\"$DATE\",\"$FROM\",\"$SUBJECT\",\"$TEXTSQL\");";
elif [ "$1" = "REPORT" ]; then
   #Extract more data from the status report file
   DISCHARGE=`
sed -e 1,/SMS\ STATUS/d < $2 | formail -zx Discharge_timestamp:`
   MSGID=`
sed -e 1,/SMS\ STATUS/d < $2 | formail -zx Message_id:`
   STATUS=`
sed -e 1,/SMS\ STATUS/d < $2 | formail -zx Status: | cut -f1 -d,`

   if [ "$MSGID" != "" ]; then
     ID=`
mysql $SQL_ARGS "
select id from $SQL_TABLE where receiver=\"$FROM\" and type=\"SENT\" and msgid=\"$MSGID\" order by id desc limit 1;"`
     mysql $SQL_ARGS "
update $SQL_TABLE set received=\"$DISCHARGE\",status=\"$STATUS\" where id=\"$ID\";"
   fi

fi
 
'bash' Syntax Highlight powered by GeSHi


What am I doing wrong?

Administrator
Registered:
May 2009
Location: Jyväskylä, Finland
Your log should say that there is an error in SQL syntax. Just because of this kind of issues, smsd will log if eventhandler says something, or if eventhandler prints something to the standard error output...

Please check and fix the apostrophe after text field title.

  This topic is locked

Page:  1

SMSTools3 Community » Help and support Top

 
Time in this board is UTC.  

Privacy Policy   SMS Server Tools 3 Copyright © Keijo Kasvi.