Schedule a basic oracle script

This is for me when I need to do this again in a week…

I keep forgetting

c:\windows\syswow64\wscript.exe //e:vbscript d:\myriad\scripts\CheckForCorruptVersionsInProd.vbs

The contents of the script:

The script is totally RAD, it does a query (nice), but also emails results and uses some basic password encryption for prying eyes.

'Username, password and URL settings
'Modify what is required to get this running, username, password (create read only innocuous users)
FromEmailAddress="jde@au.myriad.com"
Const strSmartHost = "smtpserver.com.au"
Company="myriad"
RecipientNames = ARRAY("smoir@au.myriad.com", "shannon.moir@myriad-it.com")
'
'The password below has been encrypted with a top secret encryption key
'If the JDE password changes, then you'll need to reencrypt the new password
'This can be done by calling EncryptPasswordToFile fCrypt("newPAsswordText","Something99"),"passwordFile"

EncryptedPassword="ThingsGoHere"

PasswordString="Password=" &  fCrypt(EncryptedPassword, "Something99") & ";"

'EncryptPasswordToFile encrypted,"passwordFile"

Dim Oracon
set oraccon = wscript.createobject("ADODB.Connection")
Dim recset 
set recset = wscript.createobject("ADODB.Recordset")
Dim cmd 
set cmd = wscript.createobject("ADODB.Command")
Set Oracon = wscript.CreateObject("ADODB.Connection")

Oracon.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
                          "Data Source=e1sys;" & _
                          "User ID=jde;" & _
                          PasswordString

Oracon.Open
Set cmd.ActiveConnection = Oracon
'cmd.CommandText = "Select count(*) from dvfindta.f0101"
cmd.CommandText = "SELECT  vrpid, vrvers, vrjd, vrved FROM pd910.f983051 where vrvers not like 'XJDE%'  and vrvers not like 'ZJDE%' and vrpodata is null and vrdstnm > ' '"
Set recset = cmd.Execute
i=0
'Note that often this is not supported by the DB, so...  Need to use a variable
if recset.recordcount = -1 then
  counter=500
else
  counter=recset.recordcount
end if
emailBody=chr(10) & chr(13) & "***   This Script contains a list of versions that should not have NULL PO's (500 max)  ***" & chr(10) & chr(13)
emailBody=emailBody & encrypted & chr(10) & chr(13)
emailBody=emailBody & cmd.CommandText & chr(10) & chr(13)
emailBody = emailBody & "vrpid, vrvers, vrjd, vrved" & chr(10) & chr(13)
while ((i < counter) and (recset.EOF <> true))
  for each field in recset.fields
    tuple=tuple & """" & field.value & ""","
  next
  i=i+1
  recset.MoveNext
  emailBody=emailBody & chr(10) & chr(13) & tuple
  tuple=" "
wend

'wscript.echo emailBody
i=sendMail("WeeklySummary of NULL POs", emailBody,"") 

set recset = nothing
set cmd = nothing
set oracon = nothing


'#####################################################################
'FUNCTION:sendMail
'#####################################################################
function sendMail(subjectText, emailBody, attachment)
  on error resume next
  Dim iMsg    
  Dim iConf    
  Dim Flds    
  Const cdoSendUsingPort = 2    
 
  for each name in RecipientNames
    'Create the message object    
    Set iMsg = CreateObject("CDO.Message")       
    'Create the configuration object    
    Set iConf = iMsg.Configuration        
    'Set the fields of the configuration object to send using SMTP via port 25.    
    With iConf.Fields       
      .item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
      .item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strSmartHost
      .Update    
    End With   

    'Set the message to,from,subject,body properties.    
    if strlen(attachment) > 1 then
      With iMsg
        .AddAttachment attachment
        .To = name    
        .From = FromEmailAddress       
        .Subject = subjectText &  " " & now()       
        .TextBody = chr(1)  & " " & emailBody       
        .Send    
      End With    
    else
      With iMsg
        .To = name    
        .From = FromEmailAddress       
        .Subject = subjectText  & " " & now()       
        .TextBody = emailBody       
        .Send    
      End With    
    end if
    set iMsg = Nothing 
  next

end function

Function fCrypt(sPlainText, sPassword)
 'This function will encrypt or decrypt a string using the RSA's RC4 algorithm.
  Dim aBox(255), aKey(255), sTemp, a, b, c, i, j, k, iCipherBy, sTempswap, iLength, sO
  i = 0:j = 0:b = 0
  iLength = Len(sPassword)
   For a = 0 To 255
    aKey(a) = Asc(Mid(sPassword, (a Mod iLength)+1, 1))
    aBox(a) = a
   Next
   For a = 0 To 255
    b = (b + aBox(a) + aKey(a)) Mod 256
    sTempswap = aBox(a)
    aBox(a) = aBox(b)
    aBox(b) = sTempswap
   Next
   For c = 1 To Len(sPlainText)
    i = (i + 1) Mod 256
    j = (j + aBox(i)) Mod 256
    sTemp = aBox(i)
    aBox(i) = aBox(j)
    aBox(j) = sTemp
    k = aBox((aBox(i) + aBox(j)) Mod 256)
    iCipherBy = Asc(Mid(sPlainText, c, 1)) Xor k
    sO = sO & Chr(iCipherBy)
   Next
  fCrypt = sO
End Function

Function EncryptPasswordToFile(szPassword, filename)
  Set wshShell = CreateObject( "WScript.Shell" )
  tmpDirectory = wshShell.ExpandEnvironmentStrings( "%TMP%" )
  set wshShell = Nothing
  Const ForAppending = 8
  logfile=tmpDirectory  & "\" & filename & ".txt"
   'Tempates

 Set objFSO = CreateObject("Scripting.FileSystemObject")
 set objTextFile = objFSO.OpenTextFile(logfile, ForAppending, True)

 objTextFile.WriteLine(szPassword)
  objTextFile.Close

end function