ClearSCADA: Configuring the Active User Email Lookup

Use a ClearSCADA script to read Server.UserName, locate that user in the database, and return one of four configured email addresses. The function returns the selected address as a variant, -1 when the query fails, or -2 when the query returns no user row.

Define the lookup behavior

The script joins CDBUSER to CDBUSERCONTACTCONFIG through their ID fields. It retrieves EMAILADDRESS through EMAILADDRESS4 for the active username. A zero-row result means the active account has no matching contact configuration; the supplied script notes that this may occur for the super user.

Implement the active-user query

The imported query contains a damaged username token. The implementation below restores it as currentUser, matching the variable assigned from Server.UserName and the stated requirement to search by the current username.

Function ObtainUserEmail(emailNum)
  currentUser = Server.UserName

  query = "SELECT U.ID, U.NAME, C.EMAILADDRESS, " & _
          "C.EMAILADDRESS2, C.EMAILADDRESS3, C.EMAILADDRESS4 " & _
          "FROM CDBUSER AS U " & _
          "JOIN CDBUSERCONTACTCONFIG AS C ON U.ID = C.ID " & _
          "WHERE NAME='" & currentUser & "'"

  Set results = Server.Query(query)

  If results.Error Then
    MsgBox "Whoops, we had an error." & Chr(10) & results.ErrorMessage
    ObtainUserEmail = -1
  ElseIf results.RowCount = 0 Then
    MsgBox "Your user doesn't have an email. You may be the super user"
    ObtainUserEmail = -2
  Else
    Select Case emailNum
      Case 1: column = 2
      Case 2: column = 3
      Case 3: column = 4
      Case 4: column = 5
      Case Else: column = 2
    End Select

    queryRows = results.Rows
    ObtainUserEmail = queryRows(0, column)
  End If
End Function

Handle query and user errors

Check results.Error before accessing rows and expose results.ErrorMessage during diagnosis. Then check results.RowCount; return -2 when it is zero. Only read results.Rows after both checks pass.

Verify each return path

1. Run the function as an active user with configured contact data and confirm that selections 1 through 4 return the corresponding email fields.
2. Pass a value outside 1 through 4 and confirm that the function selects the first email field.
3. Run with an account that produces no matching row and confirm the -2 return. For a query failure, confirm the message contains results.ErrorMessage and the function returns -1.

FAQ