| Keywords: Mode: All keywords (AND) |
|
Mon Aug 27, 2012 16:33
|
bill1798: Hello Keke,
Thanks for your advice, it's been a while since I tackled the script language in SMS Tools so I am out of practice. As it turned out my Test program was not executable and so it wasn't doing anything once I resolved that then it didn't take too long to work out the correct syntax for SQL Lite. I have now changed my MYSMSD event handler to work with SQL and it seems to work well using the SQLCMD program. If it helps anyone else here is what the modified event handler looks like.
#!/bin/sh
# This is an example script that logs all events into an SQL database
# You need a MYSQL database as described in the documentation.
# Please read the documentation before using this script.
SQL_HOST=.\\SQLEXPRESS
SQL_USER=billm
SQL_PASSWORD=??????
SQL_DATABASE=sms
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`
SUBJECT=`formail -zx Subject: < $2`
SENT=`formail -zx Sent: < $2`
#Text is not used but could be used
TEXT=`formail -I "" <$2 | sed -e '/^$/d'`
#NUMA=`formail -I "" <$2 | sed -e '/^$/d' -e 's/[^0-9][^0-9]*//'`
#NUM=`echo "$NUMA" | cut -c 1-3`
if [ "$SQL_PASSWORD" != "" ]; then
SQL_ARGS="-p$SQL_PASSWORD";
else
SQL_ARGS="";
fi
SQL_ARGS="-S $SQL_HOST -d $SQL_DATABASE -Q"
#Insert a new entry into the SQL table
if [ "$1" = "FAILED" ] || [ "$1" = "SENT" ]; then
sqlcmd $SQL_ARGS "insert into $SQL_TABLE (type,sent,sender,receiver,msgid,body) values ('$1','$DATE','$FROM','$TO','$3','$TEXT')"
elif [ "$1" = "RECEIVED" ]; then
#TEXT=`formail -I "" <$2 | sed -e '/^$/d' -e 's/192/bill.muirhead@piller.com/' -e 's/191/bill.cronkrite@piller.com/' -e 's/194/angie.still@piller.com/'`
NUMA=`formail -I "" <$2 | sed -e '/^$/d' -e 's/[^0-9][^0-9]*//'`
NUM=`echo "$NUMA" | cut -c 1-3`
sqlcmd $SQL_ARGS "insert into $SQL_TABLE (type,sent,received,sender,receiver,body,receive) values ('RECEIVED','$SENT','$DATE','$FROM','$SUBJECT','$TEXT','$NUM')"
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=`sqlcmd $SQL_ARGS "select id from $SQL_TABLE where receiver='$FROM' and type='$SENT' and msgid='$MSGID' order by id desc limit 1"`
# sqlcmd $SQL_ARGS "update $SQL_TABLE set received='$DISCHARGE',status='$STATUS' where id='$ID'"
# fi
fi
As you can see SQL uses single quotes around the table column values and no / delimiters, it also doesn't like the ;"; at the end of the SQL strings preferring just a " . The SQL_ARGS string uses different switches from MYSQL but apart from that everything is pretty much the same.
Regards
Bill M
|
|
Sun Aug 26, 2012 22:15
|
bill1798: Windows 7 64Bit:
3.1.14:
Smsd installed from: sources / package repository / from elsewhere...
Huwei:
USB: serial / USB / some adapter...
Hi Keke,
I'm trying to use the SQLCMD Utility to add and read records from an MS SQL Lite database. I can get it to work from the Cygwin command line but cannot get it to run from an SMSD script.
If I Use this format directly in Cygwin it works no problem;
sqlcmd -S .\\SQLEXPRESS -d SMS -Q "insert into parts (Part_No, Part_Description, Q_Description) VALUES ('4827771902','DVR','DVR')"
Using the following in a script;
sqlcmd -S .\\SQLEXPRESS -d SMS -Q "insert into parts (Part_No, Part_Description, Q_Description) VALUES ("'4827771988'","'DVRS'","'DVRS'")"
Or this;
sqlcmd -S .\\SQLEXPRESS -d SMS -Q "insert into parts (Part_No, Part_Description, Q_Description) VALUES (/"4827771988/",/"DVRS/",/"DVRS/")"
Doesn't add any records.
I am testing at the moment so the above is the only line in the script, which is called Test and I am running it directly from the cygwin command line.
#!/bin/sh
sqlcmd -S .\\SQLEXPRESS -d SMS -Q "insert into parts (Part_No, Part_Description, Q_Description) VALUES ("'4827771988'","'DVRS'","'DVRS'")"
I'm sure there is a simple solution but cannot get there. Any ideas to help?
Regards
|
|
Tue Jan 26, 2010 16:22
|
bill1798: Thanks Keke,
I looked everywhere for the number of allowable event handlers and couldn't find the answer, maybe it is obvious to those who are more used to Bash scripting.
Of course the masterchk.sh is the perfect answer, I've used the source (. /) command to call the other scripts which appears to do the trick :P. I've included my code to maybe help others, I hope that it's OK.
Regards
Bill.
#!/bin/sh
if [ "$1" != "RECEIVED" ]; then
exit
fi
#Check message text and retrieve sender and request
from=`formail -zx From: < $2`
text=`formail -I "" <$2 | sed -e"1d"`
if echo "$text" | grep -i "man"; then
. /usr/local/bin/mankitchk.sh
elif echo "$text" | grep -i "cir"; then
. /usr/local/bin/cirkitchk.sh
elif echo "$text" | grep -i "edi"; then
. /usr/local/bin/edikitchk.sh
elif echo "$text" | grep -i "lon"; then
. /usr/local/bin/lonkitchk.sh
else
smsfile=`mktemp /var/spool/sms/outgoing/send_XXXXXX`
echo "To: $from" >> $smsfile
echo "" >> $smsfile
echo "The keyword $text was not recognised" >> $smsfile
fi
|
|
Mon Jan 25, 2010 22:05
|
bill1798: Operating system name and version: Win Xp
Version of smsd: 3.0
Smsd installed from: sources
Name and model of a modem / phone: Huwei E169
Interface: USB
Hello All,
Are there any limits for the number of eventhandlers? I am trying to run 4 at the moment but I find that SMSD only parses the last one in the list! They are supposed to look for a keyword, then send an SQL query before returning the answer by SMS. Should I have all of the keywords and queries into one handler??
smsc=447785016005
eventhandler = /usr/local/bin/mankitchk.sh
eventhandler = /usr/local/bin/cirkitchk.sh
eventhandler = /usr/local/bin/edikitchk.sh
eventhandler = /usr/local/bin/lonkitchk.sh
Is there a limit of one or two or am I missing something simple.
Regards
Bill.
|
|
Thu Jan 14, 2010 12:30
|
bill1798: Hello Keke,
Sorry for the delay in replying.
The double-quotation marks solved the problem with the SQL call, there was one more typo in the -p command of the sql log on string where there was a space between -p and the password text (-p xxxx instead of -pxxxxx) once this was changed the checkhandler worked like a dream.
I have noticed that there are a lot of tyos in the SMS Tools manual which makes it difficult when you are trying to learn from it, :( but I will continue on and hopefully work things out.
Thanks for your help and the answers to my questions regarding the [ -z $result ] they have been really useful.
Best regards
Bill.
|
|
Tue Jan 12, 2010 11:12
|
bill1798: Hello Keke
Thanks for your reply, I feel embarrassed at making such a basic error and I noticed that I had also missed a ; from the end of the line. I have checked all of the script and it looks good to me but now it does the opposite of before and lets every SMS through regardless of the number.
To try and test this script I have made a test script which is the same as the checkhandler.sh but I have removed the if statement from the end and replaced it with echo "$result". When I run this from the Cygwin command line I get the following error
$ test.sh
c:\Program Files\MySQL\MySQL Server 5.0\bin\mysql.exe Ver 14.12 Distrib 5.0.45,
Usage: c:\Program Files\MySQL\MySQL Server 5.0\bin\mysql.exe [OPTIONS] [databas
C:\my.ini C:\my.cnf C:\WINDOWS\my.ini C:\WINDOWS\my.cnf c:\Program Files\MySQL\
show-warnings FALSE00847216E)efault value)---------------------re read
Does this mean that I have an error in the mysql call?
The checkhandler script is simply an edited version of the script on page 227 of Stephan Fring's manual so I think that it should be more or less correct yet it never seems to return the correct exit code.
My understanding is that [ -z "$result" ] is true if the string is empty and so then the if statement should return the exit code 1 and the checkhandler should send the message to the Failed folder. I would think that if the mysql call is wrong or if the number does not exist then $result will always be empty and so no messages will be sent so why does the opposite seem to happen?
Again any advice you can give would be good.
Regards
Bill.
|
|
Mon Jan 11, 2010 21:15
|
bill1798: Operating system name and version: Windows XP
Version of smsd: 3.0
Smsd installed from: sources
Name and model of a modem / phone: Huwei E169
Interface: USB
I have started to use and event handler to send out information based on a code that is sent by the users to the server. This works fine and so I want to limit the outgoing SMS messages to users with mobile numbers that are stored in an SQL database. I looked in the SMS Tools handbook and there is a Checkhandler example for checking users details from an SQL database so I modified it to meet my needs.
I just want to check that the senders number is in the database and if it is then the response message is allowed to go out.
This is my Checkhandler.sh file:
#!/bin/sh
#Extract data from the SMS file
to=`formail -zx To: < $1`
#log into mysql
SQL_ARGS="-h host100 -u user99 -p xxxxxx -D sms -s -N -B -e"
#Check result against phone number column in database
result=`mysql $SQL_ARGS select number from users where number = \"$to\"
if [ -z "$result" ]; then
exit 1
fi
The database is called SMS the table is called USERS and the searched column is NUMBER, all of the numbers are in the format 44778512345
A typical response message after the event handler has processed the senders request looks like this:
To: 447785123456
1st Bill Muirhead 07785135790
2nd John Moulson 0778524680
The problem is that the checkhandler filters everything and so no messages are allowed out and the log file says that the checkhandler has disallowed the message. I guess I am missing something simple here but I can't figure it out. :roll:
I would be grateful for some advice.
Thanks
Bill.
|
