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. Thu Mar 28, 2024 22:35
SMSTools3 Community » Help and support Bottom

[answered] smstools under windows with ms sql

Login and Post Reply

Page:  1

Author Post
Member
Registered:
May 2011
Location: Lima, Peru
Hi again;

I've been testing smstools with excellent results about what I wanted I mean eventhandler and regular run working with MySql under Debian Linux.

Everything is great.

Then I succeeded in "migrating" smstools from Linux to window$ but my new "homework" is getting eventhandler and regular run working with ms sql under window$.

Can you tell me if I need new drivers, new libraries, or if this is possible? :S

Or I should get anyway to "migrate" the full code of smstools to window$ so I can use smstools like any window$ application and then I can use the window$ libraries for connecting ms sql? :S

If there is any example of how to connect to a ms sql from smstools(cygwin) please tell me, thanks!

Gustavo :P

Administrator
Registered:
May 2009
Location: Jyväskylä, Finland
Smstools does not compile under Windows, because it uses Unix style daemonizing etc. But there is no need to compile it, because current version does not have database interface built-in.

You can use MS SQL with eventhandler, if you have suitable command line interface available. I do have I do not have examples about those, because most systems runs under Linux. Also, you can use MySQL as database, and connect to it from your front-end Windows application.

You could create an eventhandler with Visual Studio, so you do not need to change anything on the smstools side, and with eventhandler you can do anything which is required.


« Last edit by keke on Thu Aug 04, 2011 10:35, 154 months ago. »
Member
Registered:
May 2011
Location: Lima, Peru
Topic owner
Ok;

Well actually I have to say that I'm newbie at programming. So that's why smstools is really useful for me.
I already use mysql database, but I need using smstools under windows(specifically 2003 server) and read&write from Ms SQL database located in the same server.

As I see, definitely you know how to do this. Well I connected before to Ms Sql using visual basic code; my question is, if I put this code in eventhandler, will it work without any other driver under cygwin? Or maybe it gives error?

Thanks!

Gustavo

Member
Registered:
May 2011
Location: Lima, Peru
Topic owner
Hi again;

Well I tried to do by "linux" side I mean I installed freetds, unixODBC, python.
I tried to install pyodbc and pymssql without success and I couldnt succed in cygwin for connecting sql server :(

Then I tried visual studio, actually I used a code for VB 2005 and it worked on windows side, but when I put it like an eventhandler for smstools under cygwin, if I give a path like /home/myapp.vbs then in smsd.log I see that it tries to execute each line of code and obviously it get errors because it's not a prog language known for cygwin.

When I give C:\directorytest\myapp.vbs I see in smsd.log the message "C:\directorytest\myapp.vbs is not executable for smsd

Definitely I guess I'm being really a big noob but can't succeed into it.

keke you mentioned "You can use MS SQL with eventhandler, if you have suitable command line interface available. I do have examples about those, because most systems runs under Linux" so please can you post those examples please?

I wanted to use python because I already tested extracting info from sms with it, but I guess it's not possible under cygwin.

Thanks in advance!

Administrator
Registered:
May 2009
Location: Jyväskylä, Finland
tavillo wrote
Well I connected before to Ms Sql using visual basic code; my question is, if I put this code in eventhandler, will it work without any other driver under cygwin? Or maybe it gives error?

From Cygwin you can call any Windows program, and vice versa.

tavillo wrote
When I give C:\directorytest\myapp.vbs I see in smsd.log the message "C:\directorytest\myapp.vbs is not executable for smsd

.vbs is not an application, try myapp.exe.

tavillo wrote
keke you mentioned "You can use MS SQL with eventhandler, if you have suitable command line interface available. I do have examples about those, because most systems runs under Linux" so please can you post those examples please?

Sorry, it's a typo, single important word was missing. What I meant to say, was "I do not have...". :oops:

Member
Registered:
May 2011
Location: Lima, Peru
Topic owner
You're right, thanks a lot!

Actually I used an .exe file and it works.

Now I have a new "homework" How to deal with sending and receiving sms in smstools through vb?

For sending well I'll have to look for info in order to create a file with certain format located in var/spool/sms/outgoing using VB.

But when eventhandler(new sms is received), what does smstools do? Because in the example in python the code never opens the file(sms received) it just check If $1 = Received, and now it's a different language, running outside cygwin(I mean the .exe app created) So how could I "translate" the python code to vb, what considerations should I have?

Please could you give me any tip for this?

Thanks!

Administrator
Registered:
May 2009
Location: Jyväskylä, Finland
tavillo wrote
For sending well I'll have to look for info in order to create a file with certain format located in var/spool/sms/outgoing using VB.

Yes. For example, see this topic: smstest.php. It's written in PHP, but shows how outgoing message files could be created.

tavillo wrote
But when eventhandler(new sms is received), what does smstools do? Because in the example in python the code never opens the file(sms received) it just check If $1 = Received, and now it's a different language, running outside cygwin(I mean the .exe app created) So how could I "translate" the python code to vb, what considerations should I have?

I have not seen your Python code, so I cannot say anything about it.

You need to create an eventhandler script, which is called by smstools. The script is running under Cygwin, and it can do certain operations like scripts usually do. For MS SQL you need to create Visual Basic Console Application which does the job on that side. Call this application from the eventhandler script, with arguments which are required. At least $1 is required (type of SMS) and $2 (filename). It could make sense if parameters to SQL Server are passed as arguments too (username, database, etc). As you already had VB application, your "homework" is almost done ;). If you need some samples for VB & MS SQL, put this to Google search: visual basic console application sql server. With a quick view some useful samples were found.

Member
Registered:
May 2011
Location: Lima, Peru
Topic owner
Ok keke thanks and sorry I didn't paste the python code I used, actually is the one from this forum :)

My confussion is the next one...

$1 is the same variable for VBS?
$2 the same variable for VBS?

I get a little bit confused about how smstools works. I already made 2 different vb scripts for eventhandler and for regular run, and both run.

But, I want to know, this $1(flag for received) where does the script read it from?

If there are more than 1 sms in incoming folder, how will this script know which one is the new sms?

Sorry for so many questions :(




#!/bin/sh

# Please read the description in the manual of SMS Server Tools.

#run this script only when a message was received.
if [ "$1" != "RECEIVED" ]; then exit; fi;

#Define the database parameters
SQL_HOST=localhost
SQL_USER=user
SQL_PASSWORD=password
SQL_DATABASE=mydatabase
SQL_TABLE=tablemysql

#Extract data from the SMS file
FROM=`formail -zx From: < $2`
TEXT=`formail -I "" <$2 | sed -e"1d"`

#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"

#Do the SQL Query
#mysql $SQL_ARGS "UPDATE $SQL_TABLE SET stat=\"1\" WHERE cel=\"$FROM\";"
mysql $SQL_ARGS "insert into $SQL_TABLE (cel,txt) values (substring(\"$FROM\",-9),\"$TEXT\");"

#Create an answer SM with the amount
#FILENAME=`mktemp /var/spool/sms/outgoing/answerXXXXXX`
#echo "To: $FROM" >$FILENAME
#echo "" >> $FILENAME
#echo "Your amount is $AMOUNT" >>$FILENAME

Administrator
Registered:
May 2009
Location: Jyväskylä, Finland
Okay, the script is Shell script, and has nothing to do with Python.

Forget the VBS (Visual Basic Script). Create a traditional application with VB.

Try that Google search mentioned, and browse the results. There are some sample codes which show how command line arguments (similar than $1 and so on) can be accessed in VB, and also there is shown how SQL connection is done and used.

Every time when single SMS is sent or received, eventhandler is called. Second argument is the filename. You do not need to take care about files in incoming folder.

Member
Registered:
May 2011
Location: Lima, Peru
Topic owner
Hi again;

I am still in the sending part, I don't attack the receiving yet :(

Well I already created an .exe file which I use in regular_run and it connects to SQL, read data from it and creates a text file in the outgoing directory with the next format

To: 9999999

This is a Test or whatever


My problem is that this text file created as soon as it's in outgoing directory goes to Failed directory, log says No destination defined and when I open the text file in Failed directory it says

□□□□>□□□□□□□□□□□□□□□□□□□□□□This is a Test or whatever

So obviously there is no destination

When I create the text file manually every thing is ok.

Please can you tell me what difference can you find between this 2 text files with exactly the same info? :S

Text File(Manually created) 38 bytes

http://www.bigupload.com/en/file/11075/gsm1.7rc7ah.html

Text File(.exe created) 80 bytes

http://www.bigupload.com/en/file/11077/gsm1.qamfmn.html


Please tell me what difference you find so I can then look into the code but actually the code creates the text file :shock:

Thanks!

Administrator
Registered:
May 2009
Location: Jyväskylä, Finland
Larger file is created using Unicode encoding, but ANSI or UTF-8 encoding must be used.

If UTF-8 is used on Windows, the first line of a file cannot be used for real headers. In this case create a file like:

a: a
To: 9999999

This is a test.

Member
Registered:
May 2011
Location: Lima, Peru
Topic owner
Thanks man!

Finally I succeded in doing this.

VBS creates textfiles with unicode encoding by default so it was necessary to create the textfile using ANSI encoding.

And yes, there are 2 arguments that I had to capture, the only "trouble" was that the argument that gives the location of file is in unix format I mean /var/spool/sms/incoming/XXXX.XXXXXX
So it was necessary to put it in wind@w$ format \var\spool\sms...

Really interesting days being an absolutelly noob, lol! Thanks a lot for the tips man!

Now my last game it's only to work with 2 modems one for sending and one for receiving SMS configuring them with the required incoming = yes and outgoing = no, and viceversa.


Thanks again man!

Member
Registered:
Jun 2013
Location: lahore, Pakistan
Very thanks keke for your help every time,

i am using MS Sql Server 2005 in most of applications. how can i log my sms in SQL server DB. Plz Must Reply

Login and Post Reply

Page:  1

SMSTools3 Community » Help and support Top

 
Time in this board is UTC.  

Privacy Policy   SMS Server Tools 3 Copyright © Keijo Kasvi.