Using the DotNetZip Library with Pervasive EZScript

We are often required to work with compressed files in our Pervasive Data Integrator processes. We receive files that are compressed, we use file compression for archiving and we provide files to our clients that we compress prior to transmitting them.

The DotNetZip Library is great free file-compression library that you can use with your own applications or script. Used with Pervasive's EZScript, you can add zipping and unzipping functionality to any Data Integrator process.

To use the library I had to first install .Net Framework 3.5.1 Feature on our Windows Server 2008 R2 that hosts Pervasive Data Integrator. Next, I downloaded the DotNetZipUtils-v1.9.msi to install the runtime DLL. Finally, I used this excellent PowerShell script written by "Fred" that I lifted from here to install the Ionic.Zip.dll into the Global Assembly Cache.

I've included a couple of functions below to get you started with using file compression in EZScript.

ZIP function:

Function ZipFile(fileName, zipFileName, password)
 Dim oZipFile As Object
 Dim fileNames(), i

 ReDim fileNames(UBound(Split(fileName, ";")))
 fileNames = Split(fileName, ";")

 Set oZipFile = CreateObject("Ionic.Zip.ZipFile")
 '"using AES256 encryption...") 
 'oZipFile.Encryption = 3  

 ' same password all items
 If Len(password) <> 0 Then
  oZipFile.Password = password
 End If

 For i = 0 To UBound(fileNames)
  'oZipFile.AddItem(fileName)  
  oZipFile.AddItem_2(Trim(fileNames(i)), "") 
 Next

 oZipFile.Name = zipFileName
 LogMessage("INFO", "Zipping file: " & zipFilename)
 oZipFile.Save()
 LogMessage("INFO", "File zipped: " & zipFilename)

 oZipFile.Dispose()
 Set oZipFile = Nothing

End Function

Unzip function:

' ExtractExistingFileAction: 0= Throw; 1= OverwriteSilently; 2=DoNotOverwrite 
Function UnZipFile(zipFileName, password, unZipDirectory, ExtractExistingFileAction)
 Dim oZipFile As Object
 
 Set oZipFile = CreateObject("Ionic.Zip.ZipFile")
 '"using AES256 encryption...") 
 'oZipFile.Encryption = 3  

 oZipFile.Initialize(zipFileName)

 ' same password all items
 If Len(password) <> 0 Then
  oZipFile.Password = password
 End If

 oZipFile.ExtractAll_2(unZipDirectory, ExtractExistingFileAction)

 LogMessage("INFO", "Files extracted to " & unZipDirectory)

 oZipFile.Dispose()
 Set oZipFile = Nothing

End Function

And an example of how to call the functions:

Option Explicit

Dim zipFileName, password

' an example of zipping multiple files
ZipFile("\\fileshare\myFolder\myAccessFile.mdb;\\fileshare\myFolder\myTextFile.txt", _
 "\\fileshare\myFolder\myFile.zip", "Super Secret Password")

Dim unZipDirectory, overwriteAction
zipFileName = "\\fileshare\myFolder\myZippedFile.zip"
password = ""
unZipDirectory = "\\fileshare\myFolder\tmpZIP\"
' overwriteAction: 0=throw error; 1=overwrite silently; 2=do not overwrite
overwriteAction = 1

UnZipFile(zipFileName, password, unZipDirectory, overwriteAction)

Hopefully these examples can help you get started with an easy-to-use file compression (and decompression) in your Pervasive Data Integrator processes.

Sending Emails with Pervasive EZScript

We use emails in our Pervasive Data Integrator processes to alert ourselves when there are errors in a process or when a process has successfully completed. Often we're running a query and sending results in an email.

The out of the box way to accomplish this using EZScript, is by creating an "SendMail-SMTP" DJComponent for the SMTP object, a DJMessage for the email body and, optionally, a DJMessage for an attachment. This works well for simple applications, but fell short for us when needing some additional functionality like CC and BCC, multiple recipients, etc. 

A much more powerful way to send emails using EZScript is to use CDO (Collaboration Data Objects), a Microsoft technology that is meant to make sending messages (i.e. emails) relatively simple. There are many, many examples on the web on how to use the CDO.Message object with VBScript. This post is mainly to alert you to how you can use it with EZScript.

I wrote most of the script below for use with other projects over the years and it required very little, if any, changes to use as an EZScript. It supports sending emails to multiple recipients, CC and BCC recipients and adding multiple file attachments. There are five functions that make up the script, with the first being the AdvancedCreateCDOMessage function:

' Added Pervasive server's IP to Exchange Server 2003; 
' Must allow anonymous SMTP hosts to relay mail by adding their IP address(es) in 
' SMTP Virtual Server Properties | Access tab | Relay Restrictions

Option Explicit

Private mcdoMessage As Object

' constant
Private CrLf
CrLf = Chr(13) & Chr(10)

' Send Using
Private cdoSendUsingPickup, cdoSendUsingPort
cdoSendUsingPickup = 1  ' Send message using the local SMTP service pickup directory. 
cdoSendUsingPort = 2  ' Send the message using the network (SMTP over the network). Required for Delivery Notification. 

' Type of Authentication
Private cdoAnonymous, cdoBasic, cdoNTLM
cdoAnonymous = 0  ' Do not authenticate
cdoBasic = 1    ' Basic (clear-text) authentication
cdoNTLM = 2   ' NTLM

' Delivery Status Notifications
Private cdoDSNDefault, cdoDSNNever, cdoDSNFailure, cdoDSNSuccess, cdoDSNDelay, cdoDSNSuccessFailOrDelay
cdoDSNDefault = 0   ' None
cdoDSNNever = 1   ' None
cdoDSNFailure = 2   ' Failure
cdoDSNSuccess = 4   ' Success
cdoDSNDelay = 8   ' Delay
cdoDSNSuccessFailOrDelay = 14  ' Success, failure or delay

Public Function AdvancedCreateCDOMessage( _
 toName, toAddress, _
 ccName, ccAddress, _
 bccName, bccAddress, _
 fromName, fromAddress, _
 subject, body, attachedFileName, _
 userName, password, _
 smtpServer, smtpPort)

 Dim cdoConfig As Object
 Dim configSchema, attachments(), i

 If Len(smtpPort) == 0 Then
  smtpPort = "25"
 End If

 Set cdoConfig = CreateObject("CDO.Configuration")
 
 configSchema = "http://schemas.microsoft.com/cdo/configuration/"

 ' Type of authentication: NONE, Basic (Base64 encoded), NTLM
 cdoConfig.Fields.Item(configSchema & "smtpauthenticate") = cdoBasic
 'Your UserID and password on the SMTP server
 cdoConfig.Fields.Item(configSchema & "sendusername") = userName
 cdoConfig.Fields.Item(configSchema & "sendpassword") = password

 ' Remote SMTP server configuration 
 cdoConfig.Fields.Item(configSchema & "sendusing") = 2 'cdoSendUsingPort
 cdoConfig.Fields.Item(configSchema & "smtpserverport") = smtpPort
 cdoConfig.Fields.Item(configSchema & "smtpserver") = smtpServer 
 ' Use SSL for the connection (False or True)
 cdoConfig.Fields.Item(configSchema & "smtpusessl") = False
 ' Connection Timeout in seconds
 cdoConfig.Fields.Item(configSchema & "smtpconnectiontimeout") = 60

 cdoConfig.Fields.Update

 toAddress = FormatAddresses(toName, toAddress)
 fromAddress = FormatAddresses(fromName, fromAddress)
 ccAddress = FormatAddresses(ccName, ccAddress)
 bccAddress = FormatAddresses(bccName, bccAddress)

 Set mcdoMessage = CreateObject("CDO.Message")

 mcdoMessage.Configuration = cdoConfig

 mcdoMessage.To = toAddress
 mcdoMessage.From = fromAddress  ' Chr(34) & TheName & Chr(34) & "<" & TheAddress & ">"
 mcdoMessage.Cc = ccAddress
 mcdoMessage.Bcc = bccAddress
 mcdoMessage.Subject = subject 
 mcdoMessage.TextBody = body
 'mcdoMessage.HTMLBody = body
 'mcdoMessage.Fields("urn:schemas:mailheader:disposition-notification-to") = "me@my.com"
 'mcdoMessage.Fields("urn:schemas:mailheader:return-receipt-to") = "me@my.com" 
 'mcdoMessage.DSNOptions = cdoDSNSuccessFailOrDelay
 'mcdoMessage.Fields.Update

 If Len(attachedFileName) <> 0 Then
  ReDim attachments(UBound(Split(attachedFileName, ";")))
  attachments = Split(attachedFileName, ";")
  For i = 0 To UBound(attachments)
   If Len(attachments(i)) <> 0 Then 
    AddAttachment(Trim(attachments(i)))
   End If
  Next
 End If

 Set cdoConfig = Nothing
 
End Function

The next function, FormatAddresses, takes an array of names and address and formats them for use with the message object. This is called for each type of address (i.e. To, From, CC and BCC):

Function FormatAddresses(emailName, emailAddress)
 Dim addresses(), names(), i

 If Len(emailName) <> 0 Then
  ReDim names(UBound(Split(emailName, ";")))
  ReDim addresses(UBound(Split(emailAddress, ";")))

  If UBound(names) == UBound(addresses) Then
   names = Split(emailName, ";")
   addresses = Split(emailAddress, ";")

   For i = 0 To UBound(addresses)
    If Len(Trim(names(i))) <> 0 Then
     addresses(i) = Chr(34) & Trim(names(i)) & Chr(34) & " <" & Trim(addresses(i)) & ">"
    End If
   Next
  End If

  Return Join(addresses, ";")
 Else
  Return emailAddress
 End If

End Function

After the CDO.Message object is created, you can then call the AddAttachment function below, repeatedly, for as many attachments as you need to add:

Public Function AddAttachment(filePath)
 
 If FileExists(filePath) Then
  mcdoMessage.AddAttachment filePath
 Else
  mcdoMessage.TextBody = mcdoMessage.TextBody & CrLf & "Attached file did not exist: " & filePath
 End If

End Function

Finally, once the message is complete, we can send it using the AdvancedSendCDOMessage function:

Public Function AdvancedSendCDOMessage()
 Dim x

 mcdoMessage.Send

 x = LogMessage("INFO", "Sent email to: " & mcdoMessage.To)

 Set mcdoMessage = Nothing

End Function

Alternately, we can avoid directly using all of the above functions and send a quick email, using some defaults, with the SendCDOMessage function (note, rather than hard coding the defaults, you should use global macros that you have set up in Pervasive:

Public Function SendCDOMessage(toAddress, ccAddress, subject, body, attachedFileName)
 Dim fromName, fromAddress, userName, password, smtpServer, smtpPort

 ' TODO: point the following at global macros
 fromName = "Pervasive"
 fromAddress = "DoNotReply@yourdomainnamehere.com"
 userName = "PervasiveUser"
 password = "PervasiveUserPassword"
 smtpServer = "myserver.domain.local"
 smtpPort = ""  ' defaults to "25"

 AdvancedCreateCDOMessage( _
  "", toAddress, _
  "", ccAddress, _
  "", "", _
  fromName, fromAddress, _
  subject, body, attachedFileName, _
  userName, password, _
  smtpServer, smtpPort)
 
 AdvancedSendCDOMessage()

End Function

You can call the script using something like the following:

SendCDOMessage("me@mydomain.com", _
    "", _
    "Data Integration Job Status", _
    "This email was sent from Pervasive's EZ Script.", _
    "\\serverfileshare\myfolder\test.txt")

Hopefully the above gets you started with sending your own emails using CDO from Pervasive.

Writing SQL Results to Text in Pervasive

One way that we use to alert ourselves of what has run or is running in Pervasive Data Integrator is to run SQL queries within a process and send the results in an email to ourselves.

By creating an EZScript Library function, we can have the results of a SQL query nicely formatted so that it appears similarly to writing results to text in SQL Server Query Analyzer or Management Studio.

The WriteSQLResultsToString function below works best with diagnostic queries which will not return too many (i.e. thousands of) records:

Public Function WriteSQLResultsToString(adapterName, connectionString, sqlStatement)
 Dim imp As DJImport
 Dim maxRecords, fieldCount, r, c, returnString, fieldType, colWidths(), CrLf, debug

 debug = 0

 CrLf = Chr(13) & Chr(10)

 Set imp = new DJImport (adapterName) ' e.g.:"SQL Server 2000"

 imp.ConnectString = connectionString

On Error Resume Next
 imp.SQLStatement = sqlStatement
On Error GoTo 0

 If imp.AtEOF == 0 Then

  fieldCount = imp.FieldCount

  ReDim colWidths(fieldCount)

  For c = 1 To fieldCount

   fieldType = imp.Fields(c).TypeName
  
   ' Note, switch can only handle 7 conditions
   colWidths(c) = Switch( _
    fieldType == "bit", 1, _
    fieldType == "tinyint", 3, _
    fieldType == "smallint", 6, _
    fieldType == "int", 11, _
    fieldType == "bigint", 20, _
    fieldType == "money", 21, _
    fieldType == "float", 53 _
   )
 
   If IsNull(colWidths(c)) Then
    colWidths(c) = Switch( _
     fieldType == "real", 24, _
     fieldType == "decimal", imp.Fields(c).Length + 2, _
     fieldType == "numeric", imp.Fields(c).Length + 2, _
     fieldType == "char", imp.Fields(c).Length, _
     fieldType == "varchar", imp.Fields(c).Length _
    )
   End If

   ' To accommodate NULL
   If colWidths(c) < 4 Then colWidths(c) = 4

   colWidths(c) = MaxVal(Len(imp.Fields(c).Name), colWidths(c)) + 1
   returnString = returnString & RPad(imp.Fields(c).Name, " ", colWidths(c))
  Next c

  If debug == -1 Then
 
   returnString = returnString & CrLf

   For c = 1 To fieldCount
    returnString = returnString & RPad(imp.Fields(c).TypeName & "(" & imp.Fields(c).Length & ")", " ", colWidths(c))
   Next c

  End If

  returnString = returnString & CrLf

  ' Divider
  For c = 1 To fieldCount
   returnString = returnString & String(colWidths(c) - 1, "-") & " "
  Next c
 
  On Error Resume Next

  maxRecords = 1000

  'While Not imp.AtEOF
  For r = 1 To maxRecords

   imp.RecordNumber = r

   If Err <> 0 Then Exit For

   returnString = returnString & CrLf
 
   For c = 1 To fieldCount
    If Not IsNull(imp.Fields(c).Value) Then
     returnString = returnString & RPad(imp.Fields(c).Value, " ", colWidths(c))
    Else
     returnString = returnString & RPad("NULL", " ", colWidths(c))
    End If
   Next c    
  'WEnd
  Next r

  If r > maxRecords Then 
   returnString = returnString & CrLf & CrLf
   returnString = returnString & "Output truncated at " & maxRecords & " records."
  End If

  On Error GoTo 0

 Else
  returnString = "No records returned."
 End If

 Set imp = Nothing

 Return returnString

End Function

'Returns the maximum of two values
Function MaxVal(number1, number2)

 If number1 > number2 Then
  Return number1
 Else
  Return number2
 End If

End Function

Public Function RPad(text, character, length)
 
 If Len(text) < length Then
  text = text & String(length - Len(text), character)  
 End If

 Return text

End Function

I've also included the MaxVal and RPad functions which are referenced from other EZScript Libraries that I've created.

And an example of how to call it:

Option Explicit

Dim adapterName, connectionString, sqlStatement, results

adapterName = "SQL Server 2000"
connectionString = "your connection string goes here"
sqlStatement = "SELECT * FROM DatabaseName.dbo.ViewName"

results = WriteSQLResultsToString(adapterName, connectionString, sqlStatement)

By writing the results to a string, we can now add it to an email and send it to the appropriate parties notifying them of the status or the results of a process.