How to Return a Random Reply from an External Database

This sample demonstrates how to return a random reply from an external database.

Files used in this sample are located in the folder:

    C:\Program Files\CodeSegment\SMS Studio\Samples\InfoService\Jokes

Upon receiving an incoming message, the script GetRandomJoke.vbs selects a random joke from the jokes database and sends it back to the user. You can use the AddJoke.vbs script to enter a new joke into the database.

The contents of the GetRandomJoke.vbs file:

'-------------------------------------------------------------------------------------------------------------------------------
' This script demonstrates returning a reply message from external database.
' WARNING: Error checking omitted for clarity.
'
' Use the following application parameters in the Info Rule dialog:
' "%PipeName%" "%MsgID%"
'-------------------------------------------------------------------------------------------------------------------------------
if WScript.Arguments.Count = 2 then
   
    ' Collect the arguments
    PipeName = WScript.Arguments(0)                         ' Info pipe name
    MsgID = WScript.Arguments(1)                            ' Incoming message ID
   
    if PipeName <> "" then                                  ' Pipe name must be specified in the Info Setup dialog
       
        ' Open the database
        strDSN = "SMS Jokes Database"
        strUser = ""
        strPass = ""
       
        set database = CreateObject("ADODB.Connection")
        database.Open strDSN, strUser, strPass              ' Open the database
       
        ' Message to return when no joke could be found
        JokeText = "Sorry, no jokes today."
       
        ' Algorithm:
        ' Randomly choose a number between 1 and the maximum ID value that exists in the table
        ' This algorithm is not very efficient if there is a large number of deleted jokes
        ' Renumbering the ID values may be required in such cases
       
        ' Find the maximum ID value
        set R = database.Execute("SELECT MAX(ID) AS MaxID FROM Jokes")
        if not R.EOF then
           
            MaxID = R("MaxID")
            Randomize
           
            Success = false
            do
                JokeID = Int(1+Rnd*MaxID)
                ' Try to get the joke with the randomly selected ID value
                set R = database.Execute("SELECT JokeText FROM Jokes WHERE ID = " & JokeID)
                if not R.EOF then
                   
                    JokeText = R("JokeText")
                    Success = true
                   
                end if
            loop while not Success
           
        end if
       
        database.Close                                      ' Close the database
       
        ' Sending the joke back to the user via the Info pipe
        set fso = CreateObject("Scripting.FileSystemObject")
        set pipe = fso.CreateTextFile("\\.\pipe\" & PipeName)
       
        pipe.WriteLine("ReplyToID:" & MsgID)
        pipe.WriteLine("Body:" & JokeText)
        pipe.WriteLine("<Send>")
        pipe.Close
       
        WScript.Quit(0)                                     ' Success
       
    else
       
        MsgBox "Error: Pipe name is missing.", vbCritical, "SMS Studio Jokes Sample"
        WScript.Quit(1)                                     ' Error
       
    end if
   
else
   
    MsgBox "Error: Wrong number of arguments.", vbCritical, "SMS Studio Jokes Sample"
    WScript.Quit(1)                                         ' Error
   
end if

'-------------------------------------------------------------------------------------------------------------------------------
' Copyright (c) 2002-2006 CodeSegment. All rights reserved.                                          http://www.codesegment.com/
'-------------------------------------------------------------------------------------------------------------------------------

How to run this sample

  1. Create a new ODBC Data Source and set its name to SMS Jokes Database. See How to Create an ODBC Data Source for detailed instructions.
  2. Open Windows Explorer and locate the CreateTables.vbs file. Double-click on this file to run the script. When asked for the name of the Data Source enter SMS Jokes Database. A new table will be created in the database filled with some sample data.
  3. Click the New button on the main toolbar and select the Info service option in the New Service dialog.
  4. Click the Setup button on the main toolbar to display the Info Setup dialog.
  5. In the General tab of the Info Setup dialog click the Add button to display the Add Rule dialog.
  6. In the General tab of the Add Rule dialog enter the following parameters:
    Name: Random Joke
    Pattern: Joke
    Action: Execute external application
  7. In the Execute tab of the Add Rule dialog enter the following parameters:
    External application: GetRandomJoke.vbs - use the selection button ( ... ) to locate and select the external application.
    Application parameters: "%PipeName%" "%MsgID%"
  8. Click the Add button to close the Add Rule dialog.
  9. In the Advanced tab of the Info Setup dialog enter the name of the pipe:
    Pipe name: JokesPipe
  10. Click the OK button to close the Info Setup dialog.
  11. Click the Start button on the main toolbar to start the Info service.
  12. Use Tools / Receive Message option in the main menu to display the Receive Message dialog. Fill in the Text field with the text Joke and click the Receive button. The Info service will receive this message and run the GetRandomJoke.vbs application which will select a random joke and send it back as a reply which will be placed in the Outbox.

Related topics

Named Pipe

 

Copyright © 2002-2007 CodeSegment. All rights reserved.

   www.codesegment.com