Visual Basic
Web Scripting

Active Server Pages
Java


Mailing List
Receive free code snippets and notices when this site is updated.

Contact

Introduction To The SQL Txtlet

In the past creating SMS based database applications meant writing code. With the SQL Txtlet all of this can be done in the txtlet.xml configuration file.

For an introduction to txtlets see the article “Introduction To Txtlets”.

In this article we will create a simple SMS based information service using SQL Server.

Create the Database

Step one is to create the database table. For this example I’m assuming you are running MSSQL on your local computer.

1.  In enterprise manager create a new database called “sms_info”
2. Create a table called “sms_content” with the following fields

Field name Data type Purpose
service_name char(5) Contains the text that will be used when requesting this service.e.g. “HOR” for horoscopes or “WEA” for weather
pull_code char(5) This text that will be sent in the request SMS to specify what data we want. E.g. “ARI” for the aries horoscope
sms_text varchar(160) The text that will be returned via SMS

3. Add some test data. For example

service_name  pull_code  sms_text
HOR ARI  It will be a good day for Aries
HOR  VIR  Don’t risk too much today Virgo
WEA LON  In London it is raining

Edit Txtlet.xml

Now that the database is created the next step is to configure the SQL Txtlet. Add the following to the <txtlets> section of txtlet.xml.

<txtlet name="infoServiceTxtlet" class="TxtletServer.StdExt.Txtlets.SqlTxtlet,sqltxtlet">
  <description>Executes select statements to fetch content</description>
  <parameters> <connectionstring>Provider=SQLOLEDB;server=localhost;trusted_connection=true;database= sms_info;uid=sa</connectionstring>
  <selectsql><![CDATA[
  SELECT sms_text FROM content WHERE pull_code = '#request.GetParameter("service")#'
  AND level2='#request.GetParameter("pullcode")#'
  ]]></selectsql>
  <message>#sms_text#</message>
  <srcmsisdn>#request.message.Source#</srcmsisdn>
  <destmsisdn>#request.message.Source#</destmsisdn>

  <nodatamessage>Sorry there is no data in our database</nodatamessage>
  <nodatasrcmsisdn>099021</nodatasrcmsisdn>
  <nodatadestmsisdn>#request.Message.Source#</nodatadestmsisdn>
  </parameters>
</txtlet>

Now add a mapping for your new txtlet. This will match any message send with a body of “ABC<space>ABC“

<txtlet-mapping name="infoServiceTxtlet">
<parameters>
<text><![CDATA[(?<service>[a-z|A-Z| ]+) (?<pullcode>[a-z|A-Z| ]+)]]></text>
</parameters>
</txtlet-mapping>

Trying it out

From another phone send an SMS to the mobile number of the phone connected to the PC.

I.E

Phone Number: 22323234
SMS Text: HOR ARI

The SMS:

“It will be a good day for aries”

should be returned to you.

How it works

The Txtlet Mapping matches the SMS sent against a regular expression. If it matches then it splits the SMS into two groups called “service” and “pullcode” that can latter be extracted using the GetParameter method of the request object.

Next the SQL defined in the selectsql parameter is executed. Visual Basic, C# or Jscript code that is between # signs will be executed and the output used to query the database. The SQL Txtlet defaults to Visual Basic but can be changed by setting the scriptinglanguage parameter to vb, csharp or jscript. 

Any commas that come from a string returned by code in between ‘##’ are escaped to prevent SQL injection attacks. I.E ‘#” hi how’s it”’# would place ‘hi how’’s it’ into the SQL.

For each row returned from the database the templates: -

  • message – the body of the sms to send
  • srcmsisdn – the source phone number to use in the message
  • destmsisdn – the person to send the sms to

Will be called. Fields from the database are available to the template. For example
this template prints out the data from sms_text database field.

<message>#sms_text#</message>

Because these template are scripts We could easily uppercase all messages before sending them by using this template.

<message>#sms_text.UpperCase()#</message>

The template 

<destmsisdn>#request.message.Source#</destmsisdn>

Will send the sms back to the person that sent the request.

If the SQL returns no data then then these template will be executed and the results used to send an SMS to a phone.

  • nodatamessage
  • nodatasrcmsisdn
  • nodatadestmsisdn

If you don’t want an SMS to be sent when there is no data then simply remove these parameters.

Next Time

In my next article I will be discussing the ScriptTxtlet.


References

Flipmind
The makers of Txtlet Server

Nokia Forum
Information on nokia phones that support data cables

Microsoft .NET Framework Software Development Kit