The Oracle Mailbox - Example of using Exchange and SQL with an WSS event sink    Download scripts

The following piece of code was done for a bit of a laugh to demonstrate how you can use WSS event sinks to integrate Exchange 2000 with SQL. The basic premise of this script is you send an email to a mailbox called "The Oracle" with a subject of "Enlighten Me". This will fire the WSS event sink which will perform a query of an ODBC database of wise sayings. Using a random number generated by the event sink it will select one of the sayings from the database and return it to the sender.

Setup for this Script

Before you can use this script you first need a database of Wise sayings for it to select from, you can use any ODBC database (default for this script was a SQL DB). You need 1 database (default name: Wisesayings) and a 1 table (default name: Wisdom) within the table there needs to be 2 columns the first is an identity column with a incremental number starting from one. The second column is filled with your wise sayings. There are many sites around you can get these saying from the one I used was http://www.wiseoldsayings.com/ .

How it works

I've used a two step approach to firing this event that adds a layer of abstraction for the Exchange event sinks. the event sink code looks as follows, This uses a Async On_Save event sink

<SCRIPT LANGUAGE="VBScript">

Sub ExStoreEvents_OnSave(pEventInfo, bstrURLItem, lFlags)

Stm = bstrURLItem
set WshShell = CreateObject("WScript.Shell")
strrun = WshShell.run ("c:\evtsink\maildb.vbs " & stm)
set WshShell = nothing
End Sub

</SCRIPT>

This piece of code spawns a process that starts the main script and passes the URL of the email  that caused the event to fire. The main script works by first generating a random number between 1 and 61 (this is because I had 61 wise sayings in my database). It then queries the database for the wise saying where the random number matches the identity column .Then creates an Email to respond to the user with the wise saying placed in the subject.

Dim Rec,Rs,strURLInbox,msgobj,msgobj1,flds,objArgs,strView
on error resume next
Dim MyValue, Response
Dim Cnxn, strCnxn
Dim rsmailq, strmailq
Set objArgs = WScript.Arguments
For I = 0 to objArgs.Count - 1
   if I = 0 then
	inbstr = objArgs(I)
   else
	inbstr = inbstr & " " & objArgs(I)
   end if
Next
Set msgobj = CreateObject("CDO.Message")
Set Rec = CreateObject("ADODB.Record")
Set Rs = CreateObject("ADODB.Recordset")
set msgobj = CreateObject("CDO.Message")
set msgobj1 = CreateObject("CDO.Message")
msgobj.DataSource.Open inbstr 
if msgobj.subject = "Enlighten Me" then
	Randomize   ' Initialize random-number generator.
	MyValue = Int((61 * Rnd) + 1)   ' Generate random value 
	' open connection
	Set Cnxn = CreateObject("ADODB.Connection")
	strCnxn = "Provider=sqloledb;Data Source=youserver;Initial Catalog=wisesayings;User Id=sa;Password=;"
	Cnxn.Open strCnxn    
	Set rsmailq = CreateObject("ADODB.Recordset")
	strmailq = "SELECT * FROM wisdom Where rnumber = " & MyValue
	Set rs = Cnxn.Execute(strmailq) 
	If Rs.RecordCount <> 0 Then
  		Rs.MoveFirst
		msgobj1.subject = "The Oracle says: " & rs.Fields("sayings").value
		msgobj1.To = msgobj.From
		msgobj1.From = "The.Oracle@ad1"
		msgobj1.send	
	End if
End If
set msgobj1 = nothing
set msgobj = nothing
set rec = nothing
set rs = nothing

Registering this script

For details on installing and registering this script see my previous article Using VBS Event Sink scripts with the Web Storage System. Or have a look in the ESDK search for regevent.vbs.    Download scripts