Thursday 15 October 2015

Schedule a basic oracle script

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

I keep forgetting

image

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

No comments: