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.

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.

Source code

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-2009 CodeSegment. All rights reserved.                                          http://www.codesegment.com/
'-------------------------------------------------------------------------------------------------------------------------------

Related topics

Info Rules, Named Pipe