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.