Database delivery log

This section contains scripts that hMailServer has contributed with. hMailServer 5 is needed to use these.
Post Reply
User avatar
martin
Developer
Developer
Posts: 6842
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Database delivery log

Post by martin » 2009-01-20 13:05

The delivery log script is used to log every delivered email message to the database. External software can then connect to the hMailServer database and access the message information. This external software also must empty the delivery log database table from time to time, since you may run out of disk space otherwise. Also, if the table is never emptied, the database will become big and backups and other operations on the database will become increasingly slower.

The feature existed in hMailServer 4.x and previous versions but was removed in v5.0 since a lot of users enabled the functionality without knowing what it actually did, which caused problems to these users. The script in this thread re-implements the database delivery log feature.

The zip file contains a script which will extract information from messages as they are received and store this information in the database. The zip also contains SQL scripts to create the tables for the supported databases. If you've upgraded from v4, you will already have the tables in your database.
Attachments
DeliveryLog.0.1.zip
(1.96 KiB) Downloaded 3337 times

westdam
Senior user
Senior user
Posts: 728
Joined: 2006-08-01 21:24
Location: Padova, Italy
Contact:

Re: Database delivery log

Post by westdam » 2009-01-21 13:41

is it possible to choose another database to store all this info?
like a .ini file to configure the db and or the username/password?

so this wont hung or mess the hmailserver database.

i'm only asking if it's possible..

User avatar
martin
Developer
Developer
Posts: 6842
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Re: Database delivery log

Post by martin » 2009-01-21 13:44

Not using this script in its current form. The script could be modified so that it opens a connection to another database and inserts the data into that one. The current version of the script assumes that you want to insert into the hMailServer database.

westdam
Senior user
Senior user
Posts: 728
Joined: 2006-08-01 21:24
Location: Padova, Italy
Contact:

Re: Database delivery log

Post by westdam » 2009-01-21 18:27

mmm, ok i've understand. so next question it's quite related.
will this change to your script slow-down the server?
i mean, hmailserver had to write into 2 database and in the delivery log there's a lot of stuff to handle.

i think will affect the performance a bit..


anyway i like your idea martin. :)

User avatar
martin
Developer
Developer
Posts: 6842
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Re: Database delivery log

Post by martin » 2009-01-21 18:54

So what you're asking is "will doing more take longer time than doing less?", and the answer to that question is yes.

cristian_pop
New user
New user
Posts: 4
Joined: 2009-05-05 17:37

Re: Database delivery log

Post by cristian_pop » 2009-05-11 01:11

Have tried the script and it does not work. (on version 5.1)

I've updated CreateTablesMYSQL file to create the 2 new tables (and it does).
I've updated EventHandlers file with the script you posted.
I've enabled scripts, saved and reload the scripts, in hMS.

Whenever a user gets a new msg, info about it can be seen in hm_messages but there is no info in hm_deliverylog table.
And the reason I`m posting is because I need the content of 'subject' and 'body' fields to be dynamically updated in the database.

I have also tried with an older version of hMS (4.4.2) and no extra scripting, and still no success.
Please give me a hint.

User avatar
mattg
Moderator
Moderator
Posts: 22028
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Database delivery log

Post by mattg » 2009-05-11 07:22

cristian_pop wrote:Have tried the script and it does not work. (on version 5.1)
The script works for me

Ver 5.1.1 341

Is there any error log?
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

Khurram
Normal user
Normal user
Posts: 38
Joined: 2007-12-02 09:21

Re: Database delivery log

Post by Khurram » 2009-06-02 08:51

I believe this script is for incoming messages only. Am I right? If so, is there a script for outgoing messages?

User avatar
mattg
Moderator
Moderator
Posts: 22028
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Database delivery log

Post by mattg » 2009-06-02 09:35

Mine logs all message, both incoming, and outgoing
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

Onni
New user
New user
Posts: 6
Joined: 2008-04-24 09:42

Re: Database delivery log

Post by Onni » 2009-08-08 15:02

I cant get it to work.

I upgraded from 4.x to 5.2 and put the *.txt file in the Events dir.
Changed the admin password in the file and hit reload scripts.
When that didnt work I changed it to *.vbs but that didnt work either.

Any suggestions?

^DooM^
Site Admin
Posts: 13861
Joined: 2005-07-29 16:18
Location: UK

Re: Database delivery log

Post by ^DooM^ » 2009-08-08 15:36

Copy and paste all that code to your EventHandler.vbs file that already exists for hMail.
If at first you don't succeed, bomb disposal probably isn't for you! ヅ

Onni
New user
New user
Posts: 6
Joined: 2008-04-24 09:42

Re: Database delivery log

Post by Onni » 2009-08-08 22:18

Thank you kindly! That works ;)

luci
Normal user
Normal user
Posts: 70
Joined: 2008-02-29 14:29
Location: Romania

Re: Database delivery log

Post by luci » 2009-09-15 17:23

There is a simple way to get a message SENT status ?

I am thinking to create such script in PHP and integrate it in hmailadmin. This would list all incoming/outgoing messages of the logged in user with a status indicator. If the message failed to be sent, a explanation message taken from the log would inform the user about the problem.

This will inform the user about the status of a certain message. (especially useful for sent messages).

The only way I can think of it is to parse the daily SMTP log.
Can you help me with a few infos about the tab delimited fields? Can I uniquely identify a message ?
Radical Image Optimization Tool developer
Project Manager at CRIOSWEB

anuragsharma9
New user
New user
Posts: 1
Joined: 2013-01-14 21:05

Re: Database delivery log

Post by anuragsharma9 » 2013-06-30 19:50

Dear Sir

I am facing a problem in delivery log. i got so many information in this script but i also want to add eml file location where it finally save . my purpose is i can catch if a problem find in mail system


Please help if you have any solution for it.

percepts
Senior user
Senior user
Posts: 5282
Joined: 2009-10-20 16:33
Location: Sceptred Isle

Re: Database delivery log

Post by percepts » 2013-06-30 22:56

you will need to modify the code yourself.

User avatar
Nime
Normal user
Normal user
Posts: 163
Joined: 2009-03-12 11:50
Contact:

Re: Database delivery log

Post by Nime » 2013-07-11 07:46

I wrote a query which one reports the usage statistics. It counts the daily outgoing mails by accounts.

Code: Select all

SELECT
        deliveryaccount,
        DATEDIFF(d,[deliverytime], getdate()) AS [ago],
        COUNT(DISTINCT [hm_deliverylog].[deliveryid]) AS [session]
        ,COUNT( [hm_deliverylog_recipients].[deliveryid]) AS [recipients]
 
 
FROM [hm_deliverylog]
 
LEFT JOIN [hm_deliverylog_recipients] ON [hm_deliverylog_recipients].[deliveryid] = [hm_deliverylog].[deliveryid]
 
WHERE DATEDIFF(d,[deliverytime], getdate()) < 3
--and [deliveryaccount] = 'info@domain.com'
 
GROUP BY
        [deliveryaccount],
         DATEDIFF(d,[deliverytime], getdate())
         
ORDER BY [ago], [recipients] DESC
I've renamed the table names & included the necessary indexes to make query execution way faster.

Image

jollero
New user
New user
Posts: 7
Joined: 2014-05-27 12:46

Re: Database delivery log

Post by jollero » 2014-06-20 09:28

Hi,

This thread seems to be quite old. But has anyone been able to make this work on the latest version of hmailserver? I was not able to.

Thanks a lot!

Regards,
Joseph

User avatar
mattg
Moderator
Moderator
Posts: 22028
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Database delivery log

Post by mattg » 2014-06-20 10:48

It should work on latest version

Are you getting any errors or is there an error log generated?
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

Steviebones
New user
New user
Posts: 18
Joined: 2014-07-16 21:42

Re: Database delivery log

Post by Steviebones » 2014-07-22 16:57

I am trying to fire your script so I can get a handle on editing it... Whenever the script fires its returns this error

"Script Error: Source: Microsoft VBScript compilation error - Error: 800A0400 - Description: Expected statement - Line: 6 Column: 0 - Code: End Settings"

below is a copy of the script file as loaded...

My VBS is rusty to say the least but I copied the script as downloaded... what am I missing?

Code: Select all

Option Explicit

Settings
Private Const setting_username = "Administrator"
Private Const setting_password = "********"
End Settings


'   Sub OnClientConnect(oClient)
'   End Sub

'   Sub OnSMTPData(oClient, oMessage)
'   End Sub

Sub OnAcceptMessage(oClient, oMessage) 
   CreateDeliveryLogEntry(oMessage)
   
End Sub


'   Sub OnDeliveryStart(oMessage)
'   End Sub

'   Sub OnDeliverMessage(oMessage)
'   End Sub

'   Sub OnBackupFailed(sReason)
'   End Sub

'   Sub OnBackupCompleted()
'   End Sub

'   Sub OnError(iSeverity, iCode, sSource, sDescription)
'   End Sub

'   Sub OnDeliveryFailed(oMessage, sRecipient, sErrorMessage)
'   End Sub

'   Sub OnExternalAccountDownload(oFetchAccount, oMessage, sRemoteUID)
'   End Sub

Sub CreateDeliveryLogEntry(oMessage)
   Dim obDatabase
   Set obDatabase = GetDatabaseObject
   
   Dim sFrom, sFilename, sTime, sSubject, sBody
   
   sFrom = oMessage.From
   sFilename = oMessage.Filename
   sTime = GetTimestamp(obDatabase)
   sSubject = oMessage.Subject
   sBody = oMessage.Body
   
   sFrom = Mid(sFrom, 1, 255)
   sFilename = Mid(sFilename, 1, 255)
   sSubject = Mid(sSubject, 1, 255)
   
   if (Len(sBody) > 1000000) Then
      sBody = Mid(sBody, 1, 1000000)
   End If
   
   sFrom = Escape(obDatabase, sFrom)
   sFilename = Escape(obDatabase, sFilename)
   sSubject = Escape(obDatabase, sSubject)
   sBody = Escape(obDatabase, sBody)
   
   Dim sSQL
   sSQL = "insert into hm_deliverylog (deliveryfrom, deliveryfilename, deliverytime, deliverysubject, deliverybody) values ('" & sFrom & "', '" & sFilename & "', " & sTime & ", '" & sSubject & "', '" & sBody & "')"
   
   dim iID
   iID = obDatabase.ExecuteSQLWithReturn(sSQL)
   
   dim obRecipients
   set obRecipients = oMessage.Recipients
   
   dim iRecipientCount
   iRecipientCount = obRecipients.Count
   
   dim i
   for i = 0 to iRecipientCount -1
      dim sRecipientAddress
      sRecipientAddress = obrecipients.Item(i).Address
      sRecipientAddress = Escape(obDatabase, sRecipientAddress)
      
      sSQL = "insert into hm_deliverylog_recipients (deliveryid, deliveryrecipientaddress) values (" & CStr(iID) & ", '" & sRecipientAddress & "')"
      
      Call obDatabase.ExecuteSQL(sSQL)
   Next
End Sub

Function GetDatabaseObject()
   Dim obApp
   Set obApp = CreateObject("hMailServer.Application")

	Call obApp.Authenticate(setting_username, setting_password)
   
   Set GetDatabaseObject = obApp.Database
End Function

Function Escape(obDatabase, value)

   value = Replace(value, "'", "''")

   Select Case obDatabase.DatabaseType
      Case 1: ' MySQL
         value = Replace(value, "\", "\\")
      Case 3: ' PGSQL
         value = Replace(value, "\", "\\")
   End Select
   
   Escape = value
End Function

Function GetTimestamp(obDatabase)
   Select Case obDatabase.DatabaseType
      Case 1: ' MySQL
         GetTimestamp = "NOW()"
      Case 2: ' MSSQL
         GetTimestamp = "GETDATE()"
      Case 3: ' PGSQL
         GetTimestamp = "current_timestamp"
      Case 4: ' SQL CE
         GetTimestamp = "GETDATE()"
   End Select
End Function

User avatar
mattg
Moderator
Moderator
Posts: 22028
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Database delivery log

Post by mattg » 2014-07-22 23:13

In the original script the words 'settings' and 'End Settings' were commented out

They are NOT VB commands

They should be commented out.
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

User avatar
SorenR
Senior user
Senior user
Posts: 5530
Joined: 2006-08-21 15:38
Location: Denmark

Re: Database delivery log

Post by SorenR » 2014-08-18 20:29

Anyone else seeing this error?

Code: Select all

"ERROR"	2232	"2014-08-17 19:58:42.149"	"Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Incorrect string value: '\xF0\x9F\x98\x84 h...' for column 'deliverybody'
It appears to be some sort of Emoticon used in emails from Twitter...
http://apps.timwhitlock.info/emoji/tables/unicode
https://www.drupal.org/node/1314214 Reported 3 years ago and aparently fixed 2 months ago - MySQL 5.5 InnoDB but aparently not without issues.

I use Martins original script (first post in thread) and did the quick solution and Googled :wink:

PHP have a very nice function that I found people to use
mixed preg_replace ( mixed $pattern , mixed $replacement , mixed $subject [, int $limit = -1 [, int &$count ]] )
and after some Googling I found a VBScript version. https://gist.github.com/wqweto/6569291
I only include two of the functions from the above into my EventHandler.vbs

Code: Select all

   Private Function preg_init(find_re)
      Set preg_init = New RegExp
      With preg_init
         .Global = True
         If Left(find_re, 1) = "/" Then
            Dim pos: pos = InStrRev(find_re, "/")
            .Pattern = Mid(find_re, 2, pos - 2)
            .IgnoreCase = (InStr(pos, find_re, "i") > 0)
            .Multiline = (InStr(pos, find_re, "m") > 0)
         Else
            .Pattern = find_re
         End If
      End With
   End Function

   Function preg_replace(find_re, replace_arg, text)
      preg_replace = preg_init(find_re).Replace(text, replace_arg)
   End Function
and I modified the original script here... to replace the emoticons with "--".

Code: Select all

   ...
   ...
   sBody = oMessage.Body
   If (Len(sBody) > 1000000) Then
      sBody = preg_replace("'/[\x00-\x08\x10\x0B\x0C\x0E-\x19\x7F]'" & "." &_
                           "'|[\x00-\x7F][\x80-\xBF]+'" & "." &_
                           "'|([\xC0\xC1]|[\xF0-\xFF])[\x80-\xBF]*'" & "." &_
                           "'|[\xC2-\xDF]((?![\x80-\xBF])|[\x80-\xBF]{2,})'" & "." &_
                           "'|[\xE0-\xEF](([\x80-\xBF](?![\x80-\xBF]))|(?![\x80-\xBF]{2})|[\x80-\xBF]{3,})/S'", "--", sBody)
      sBody = Mid(sBody, 1, 1000000)
   End If
   sFrom = Escape(obDatabase, sFrom)
   ...
   ...
I now get Twitter updates in the log.
SørenR.

There are two types of people in this world:
1) Those who can extrapolate from incomplete data

aoverton07
New user
New user
Posts: 25
Joined: 2014-03-28 00:56

Re: Database delivery log

Post by aoverton07 » 2014-10-15 03:39

Thanks for the script! I have successfully implemented the delivery log for the latest build of hm (5.4).

****For any new visitors, the only thing that I had trouble with (mostly due to my own lack of knowledge lol) was making sure that you use "Administrator" for the setting_username constant, and NOT your account name that you would use to send an email.

One question:

I noticed it saves the filename for each message, yet the files are not present in the hmailServer/Data folder. They are deleted as soon as the message is delivered. Is there a way to keep these files around?

I realize I should use caution here because saving all those files can potentially take up a lot of disk space, but for my application it is necessary to save these files so that we can extract the attachments if need be.....

User avatar
mattg
Moderator
Moderator
Posts: 22028
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Database delivery log

Post by mattg » 2014-10-15 05:03

aoverton07 wrote:They are deleted as soon as the message is delivered. Is there a way to keep these files around? .
Your mail clients connect via POP3. IMAP keeps messages on the server.

If you want to keep all messages, then active the mirror feature
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

sheshman
New user
New user
Posts: 15
Joined: 2022-04-21 07:30

Re: Database delivery log

Post by sheshman » 2022-04-21 07:33

Using this script on 5.6.8 - B2574 which works perfectly fine, I'm using Hmail as relay server on my LAN, i was wondering is it possible to log sender's ip address too?

User avatar
RvdH
Senior user
Senior user
Posts: 2312
Joined: 2008-06-27 14:42
Location: The Netherlands

Re: Database delivery log

Post by RvdH » 2022-04-21 09:46

sheshman wrote:
2022-04-21 07:33
Using this script on 5.6.8 - B2574 which works perfectly fine, I'm using Hmail as relay server on my LAN, i was wondering is it possible to log sender's ip address too?
Yes, expand the database table to include the field for ipaddress and populate it with oClient.IPAddress within the script

before

Code: Select all

Sub OnAcceptMessage(oClient, oMessage) 
   CreateDeliveryLogEntry(oMessage)
   
End Sub
after

Code: Select all

Sub OnAcceptMessage(oClient, oMessage) 
   CreateDeliveryLogEntry(oClient, oMessage)
   
End Sub
Insert query

Code: Select all

Dim sSQL
sSQL = "insert into hm_deliverylog (deliveryfrom, deliveryfilename, deliveryipaddress, deliverytime, deliverysubject, deliverybody) values ('" & sFrom & "', '" & sFilename & "', '" & oClient.IPAddress & "', " & sTime & ", '" & sSubject & "', '" & sBody & "')"
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

sheshman
New user
New user
Posts: 15
Joined: 2022-04-21 07:30

Re: Database delivery log

Post by sheshman » 2022-04-21 13:02

RvdH wrote:
2022-04-21 09:46
sheshman wrote:
2022-04-21 07:33
Using this script on 5.6.8 - B2574 which works perfectly fine, I'm using Hmail as relay server on my LAN, i was wondering is it possible to log sender's ip address too?
Yes, expand the database table to include the field for ipaddress and populate it with oClient.IPAddress within the script

before

Code: Select all

Sub OnAcceptMessage(oClient, oMessage) 
   CreateDeliveryLogEntry(oMessage)
   
End Sub
after

Code: Select all

Sub OnAcceptMessage(oClient, oMessage) 
   CreateDeliveryLogEntry(oClient, oMessage)
   
End Sub
Insert query

Code: Select all

Dim sSQL
sSQL = "insert into hm_deliverylog (deliveryfrom, deliveryfilename, deliveryipaddress, deliverytime, deliverysubject, deliverybody) values ('" & sFrom & "', '" & sFilename & "', '" & oClient.IPAddress & "', " & sTime & ", '" & sSubject & "', '" & sBody & "')"
Thanks man, added your codes to my eventhandler file and saved, disabled scripts and re-enabled again but deliveryipaddress shows empty on database, should i restart hmail service to be apply ?

Screenshot and eventhandler file as attached.
Attachments
EventHandlers.zip
(1.15 KiB) Downloaded 21 times
SCR001.png

User avatar
RvdH
Senior user
Senior user
Posts: 2312
Joined: 2008-06-27 14:42
Location: The Netherlands

Re: Database delivery log

Post by RvdH » 2022-04-21 13:10

You forgot a important part, as you need to pass the oClient object to the Sub, eg:

change:

CreateDeliveryLogEntry(oMessage)

to:

CreateDeliveryLogEntry(oClient, oMessage)

and rename:

Sub CreateDeliveryLogEntry(oClient, oMessage)
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

sheshman
New user
New user
Posts: 15
Joined: 2022-04-21 07:30

Re: Database delivery log

Post by sheshman » 2022-04-21 16:25

RvdH wrote:
2022-04-21 13:10
You forgot a important part, as you need to pass the oClient object to the Sub, eg:

change:

CreateDeliveryLogEntry(oMessage)

to:

CreateDeliveryLogEntry(oClient, oMessage)

and rename:

Sub CreateDeliveryLogEntry(oClient, oMessage)
Made the changes you provided but now it doesn't save logs to db at all, forgive me vbscript is not my strong suit probably i did something wrong but don't know what i did wrong.

Also getting attached error

here is my handler;

Code: Select all

'   Sub OnClientConnect(oClient)
'   End Sub

'   Sub OnSMTPData(oClient, oMessage)
'   End Sub

'   Sub OnAcceptMessage(oClient, oMessage)
'   End Sub

'   Sub OnDeliveryStart(oMessage)
'   End Sub

'   Sub OnDeliverMessage(oMessage)
'   End Sub

'   Sub OnBackupFailed(sReason)
'   End Sub

'   Sub OnBackupCompleted()
'   End Sub

'   Sub OnError(iSeverity, iCode, sSource, sDescription)
'   End Sub

'   Sub OnDeliveryFailed(oMessage, sRecipient, sErrorMessage)
'   End Sub

'   Sub OnExternalAccountDownload(oFetchAccount, oMessage, sRemoteUID)
'   End Sub
Option Explicit

' Settings
Private Const setting_username = "*****"
Private Const setting_password = "*****"

' End settings.

Sub OnAcceptMessage(oClient, oMessage) 
   CreateDeliveryLogEntry(oClient, oMessage)
   
End Sub
Sub CreateDeliveryLogEntry(oClient, oMessage)
   Dim obDatabase
   Set obDatabase = GetDatabaseObject
   
   Dim sFrom, sFilename, sTime, sSubject, sBody
   
   sFrom = oMessage.From
   sFilename = oMessage.Filename
   sTime = GetTimestamp(obDatabase)
   sSubject = oMessage.Subject
   sBody = oMessage.Body
   
   sFrom = Mid(sFrom, 1, 255)
   sFilename = Mid(sFilename, 1, 255)
   sSubject = Mid(sSubject, 1, 255)
   
   if (Len(sBody) > 1000000) Then
      sBody = Mid(sBody, 1, 1000000)
   End If
   
   sFrom = Escape(obDatabase, sFrom)
   sFilename = Escape(obDatabase, sFilename)
   sSubject = Escape(obDatabase, sSubject)
   sBody = Escape(obDatabase, sBody)
   
   Dim sSQL
sSQL = "insert into hm_deliverylog (deliveryfrom, deliveryfilename, deliveryipaddress, deliverytime, deliverysubject, deliverybody) values ('" & sFrom & "', '" & sFilename & "', '" & oClient.IPAddress & "', " & sTime & ", '" & sSubject & "', '" & sBody & "')"   
   dim iID
   iID = obDatabase.ExecuteSQLWithReturn(sSQL)
   
   dim obRecipients
   set obRecipients = oMessage.Recipients
   
   dim iRecipientCount
   iRecipientCount = obRecipients.Count
   
   dim i
   for i = 0 to iRecipientCount -1
      dim sRecipientAddress
      sRecipientAddress = obrecipients.Item(i).Address
      sRecipientAddress = Escape(obDatabase, sRecipientAddress)
      
      sSQL = "insert into hm_deliverylog_recipients (deliveryid, deliveryrecipientaddress) values (" & CStr(iID) & ", '" & sRecipientAddress & "')"
      
      Call obDatabase.ExecuteSQL(sSQL)
   Next
End Sub

Function GetDatabaseObject()
   Dim obApp
   Set obApp = CreateObject("hMailServer.Application")

	Call obApp.Authenticate(setting_username, setting_password)
   
   Set GetDatabaseObject = obApp.Database
End Function

Function Escape(obDatabase, value)

   value = Replace(value, "'", "''")

   Select Case obDatabase.DatabaseType
      Case 1: ' MySQL
         value = Replace(value, "\", "\\")
      Case 3: ' PGSQL
         value = Replace(value, "\", "\\")
   End Select
   
   Escape = value
End Function

Function GetTimestamp(obDatabase)
   Select Case obDatabase.DatabaseType
      Case 1: ' MySQL
         GetTimestamp = "NOW()"
      Case 2: ' MSSQL
         GetTimestamp = "GETDATE()"
      Case 3: ' PGSQL
         GetTimestamp = "current_timestamp"
      Case 4: ' SQL CE
         GetTimestamp = "GETDATE()"
   End Select
End Function
Attachments
Screenshot_18.png
Screenshot_18.png (10.79 KiB) Viewed 2379 times

User avatar
RvdH
Senior user
Senior user
Posts: 2312
Joined: 2008-06-27 14:42
Location: The Netherlands

Re: Database delivery log

Post by RvdH » 2022-04-21 17:11

And it worked before? Mmmm weird, parentheses can be used with one argument but not with two?

Anyway...try this (should work)

Sub OnAcceptMessage(oClient, oMessage)
Call CreateDeliveryLogEntry(oClient, oMessage)

End Sub
Last edited by RvdH on 2022-04-21 17:20, edited 1 time in total.
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

sheshman
New user
New user
Posts: 15
Joined: 2022-04-21 07:30

Re: Database delivery log

Post by sheshman » 2022-04-21 17:20

RvdH wrote:
2022-04-21 17:11
And it worked before? Mmmm weird, parentheses can be used with one argument but not with two?

Anyway...try this

Sub OnAcceptMessage(oClient, oMessage)
Call CreateDeliveryLogEntry(oClient, oMessage)

End Sub
It worked, i owe you guys a cold beer :) Many thanks
Attachments
SCR002.png
SCR002.png (4.92 KiB) Viewed 2369 times

User avatar
RvdH
Senior user
Senior user
Posts: 2312
Joined: 2008-06-27 14:42
Location: The Netherlands

Re: Database delivery log

Post by RvdH » 2022-04-21 17:22

Great, enjoy!
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

User avatar
RvdH
Senior user
Senior user
Posts: 2312
Joined: 2008-06-27 14:42
Location: The Netherlands

Re: Database delivery log

Post by RvdH » 2022-04-23 18:09

PS, you might want to change (at least) 2 columns to allow emoji characters

Code: Select all

ALTER hm_deliverylog MODIFY deliverysubject varchar(255) CHARACTER SET utf8mb4 NOT NULL;
ALTER hm_deliverylog MODIFY deliverybody text CHARACTER SET utf8mb4 NOT NULL;
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

sheshman
New user
New user
Posts: 15
Joined: 2022-04-21 07:30

Re: Database delivery log

Post by sheshman » 2022-04-25 19:33

RvdH wrote:
2022-04-23 18:09
PS, you might want to change (at least) 2 columns to allow emoji characters

Code: Select all

ALTER hm_deliverylog MODIFY deliverysubject varchar(255) CHARACTER SET utf8mb4 NOT NULL;
ALTER hm_deliverylog MODIFY deliverybody text CHARACTER SET utf8mb4 NOT NULL;
great update man thanks :)

So what about Turkish characters like "ÇŞİĞÜÖ çşığüö", the only problem i have now is the Turkish characters on sender or recepient names, it stores like "Ay#e" but it should be "Ayşe", table language allows to store Turkish caharacters i mean when i add record manually Turkish characters shows just fine.

User avatar
RvdH
Senior user
Senior user
Posts: 2312
Joined: 2008-06-27 14:42
Location: The Netherlands

Re: Database delivery log

Post by RvdH » 2022-04-26 12:42

sheshman wrote:
2022-04-25 19:33
RvdH wrote:
2022-04-23 18:09
PS, you might want to change (at least) 2 columns to allow emoji characters

Code: Select all

ALTER hm_deliverylog MODIFY deliverysubject varchar(255) CHARACTER SET utf8mb4 NOT NULL;
ALTER hm_deliverylog MODIFY deliverybody text CHARACTER SET utf8mb4 NOT NULL;
great update man thanks :)

So what about Turkish characters like "ÇŞİĞÜÖ çşığüö", the only problem i have now is the Turkish characters on sender or recepient names, it stores like "Ay#e" but it should be "Ayşe", table language allows to store Turkish caharacters i mean when i add record manually Turkish characters shows just fine.
I said 'at least' for those 2 column, so you are free to try to set utf8mb4 CHARACTER SET for deliveryfrom and deliveryrecipientaddress in their respective tables, although deliveryrecipientaddress only holds the emailaddress if i am not mistaken whereas deliveryfrom can hold both name and emailaddress
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

User avatar
RvdH
Senior user
Senior user
Posts: 2312
Joined: 2008-06-27 14:42
Location: The Netherlands

Re: Database delivery log

Post by RvdH » 2022-08-17 12:25

Code: Select all

create table hm_deliverylog
(
	deliveryid bigint auto_increment not null, primary key(deliveryid), unique(deliveryid),
	deliveryfrom varchar(255) not null,
	deliveryfilename varchar(255) not null,
	deliverytime datetime not null,
	deliverysubject varchar(255) not null,
	deliverybody text not null
) DEFAULT CHARSET=utf8;

create table hm_deliverylog_recipients
(
	deliveryrecipientid int auto_increment not null, primary key(deliveryrecipientid), unique(deliveryrecipientid),
   deliveryid  int not null,
	deliveryrecipientaddress varchar(255) not null
) DEFAULT CHARSET=utf8;
vbs sub contains:

Code: Select all

Sub CreateDeliveryLogEntry(oMessage)

   ...

   if (Len(sBody) > 1000000) Then
      sBody = Mid(sBody, 1, 1000000)
   End If

   ...

End Sub
Today i got:
"Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Data too long for column 'deliverybody'"

MySQL TEXT field can hold up 65535 characters maximum, not?
The standard TEXT data object is sufficiently capable of handling typical long-form text content. TEXT data objects top out at 64 KB (expressed as 2^16 -1) or 65,535 characters and requires a 2 byte overhead.
If yes, should that not read like:

Code: Select all

' MySQL TEXT Field can hold up 65535 characters maximum
if (Len(sBody) > 65535 ) Then
	sBody = Mid(sBody, 1, 65535 )
End If
Or do we have to reserve them 2 overhead bytes as well?
:?:
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

User avatar
RvdH
Senior user
Senior user
Posts: 2312
Joined: 2008-06-27 14:42
Location: The Netherlands

Re: Database delivery log

Post by RvdH » 2022-08-17 13:39

65535 works with plain ANSI chars, insert a single emoij and it craps out again, eg: "Severity: 2 (High), Code: HM5032, Source: DALConnection::Execute, Description: MySQL: Data too long for column 'deliverybody'"

Code: Select all

2252	"2022-08-17 13:24:28.471"	"CreateDeliveryLogEntry Len(sBody) Before:74050"
2252	"2022-08-17 13:24:28.471"	"CreateDeliveryLogEntry Len(sBody) After:65535"
with deliverybody column modified to being utf8mb4, where each character needs 4 bytes, would the maximum be 16383 :?:

[edit] MMmmmmm...

Code: Select all

4820	"2022-08-17 13:47:45.192"	"CreateDeliveryLogEntry Len(sBody) Before:74050"
4820	"2022-08-17 13:47:45.192"	"CreateDeliveryLogEntry Len(sBody) After:16383"
Now it stores it successfully, but also if i use 16384, and with 16384 i would expect it to crap out again, not?
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

User avatar
RvdH
Senior user
Senior user
Posts: 2312
Joined: 2008-06-27 14:42
Location: The Netherlands

Re: Database delivery log

Post by RvdH » 2022-08-17 15:08

Got a workaround, sort off... :lol:

Code: Select all

Sub CreateDeliveryLogEntry(oMessage)

   ...
   
	' count utf8mb4 bytes
	dim count
	count = match_count("([^\x00-\x7F]+)", sBody)

	' MySQL TEXT Field can hold up 65535 characters maximum
	If (Len(sBody) > 65535 ) Then
		sBody = Mid(sBody, 1, ( 65535 - (count * 4)))
	End If

   ...

End Sub
	
Function match_count(sp,ss) : match_count = 0
    Set regex = New regexp
    With regex
        .pattern = sp
        .ignorecase = True
        .global = True
    End With
    Set matches = regex.execute(ss)
    match_count = matches.count
End Function
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

Post Reply