I am not a developer so you can correct everything i say here!
But i needed to correct a problem on my web server and i asked some help to a developer friend who wrote this script for me (he did not know hmailserver and vbscript at all, so there must be better ways to do it).
I needed to check whether a deleted EML file in the /hMailServer/Data folder was still recorded in hMailServer DB: if it was still there, I wanted to delete this useless record, to keep the DB updated and consistent.
So the script simply updates the DB by deleting records to files that do no exist anymore (files that were manually deleted for some reason/mistake or deleted by an antivirus, which is the main reason i wanted to do this).
You can save the script on a .vbs file and run it from a command line by symply typing: cscript myscript.vbs
This script was only tested with hMailServer 4.4.1 B273 (on Windows Server 2008).
Since we did not know on what hMailServer event run it, I just added it on the Windows Task manager (it runs every hour). No external antivirus is set, but only the Kaspersky resident protection.
sub log(logfile, message)
wscript.echo(message)
logfile.writeLine(message)
end sub
'declare the variables
Dim ConnectionString
Dim Recordset
Dim SQL
Dim cn
set objFSO = CreateObject("Scripting.FileSystemObject")
set LogFile = objfso.opentextfile("mail-check.log",2,True)
'declare the SQL statement that will query the database
SQL = "SELECT MESSAGEID, MESSAGEFILENAME FROM HM_MESSAGES"
'define the connection string, specify database driver
set cn = CreateObject("ADODB.Connection")
set Recordset = CreateObject("ADODB.Recordset")
cn.connectionstring = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;PORT=3307;DATABASE=HMAILSERVER;UID=root;PASSWORD=<YOURPASSWORD>"
cn.open
'Open the recordset object executing the SQL statement and return records
Recordset.Open SQL,cn
call log(LogFile, "START CHECKING MAILS AND FILES")
'first of all determine whether there are any records
If Recordset.EOF Then
call log(LogFile, "No records returned.")
Else
'if there are records then loop through the fields
Do While NOT Recordset.Eof
id = Recordset("MESSAGEID")
filename = Recordset("MESSAGEFILENAME")
if Not objFSO.FileExists(filename) then
deleteSQL = "DELETE FROM HM_MESSAGES WHERE MESSAGEID = '" & id & "'"
call log(LogFile, "ID: " & id & " - " & filename & " > DELETED")
'cn.execute deleteSQL
end if
Recordset.MoveNext
Loop
End If
'close the connection and recordset objects freeing up resources
Recordset.Close
Set Recordset=nothing
cn.Close
Set cn=nothing
call log(LogFile, "DONE")
If you run the script as it is here, NO RECORDS will be deleted from your DB, because the following line is commented:
Once you uncomment this line, all the records to inexistent files will be deleted without asking confirmation.'cn.execute deleteSQL
**************************************************
It worked perfectly for me on a production server but:
PLEASE DO NOT USE ON PRODUCTION SERVER BEFORE TESTING!
**************************************************
If you have critics and suggestion, or if some good developer out there wants to improve it and make it more useful for the crowd... well, it would be nice.
thanks