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

bill1798 wrote

Or this;
sqlcmd -S .\\SQLEXPRESS -d SMS -Q "insert into parts (Part_No, Part_Description, Q_Description) VALUES (/"4827771988/",/"DVRS/",/"DVRS/")"

It should work with backslashes:

sqlcmd -S .\\SQLEXPRESS -d SMS -Q "insert into parts (Part_No, Part_Description, Q_Description) VALUES (\"4827771988\",\"DVRS\",\"DVRS\")"

If it still does not work, what is the error message when you run it from the command line?

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