Thursday, September 4, 2014

Updated Script

Apparently, people still care about Powershell, and people still care about Documentum.

Consider this my final post on Powershell and Documentum, as I have moved on in my professional life from both technologies.

I was able to find the final version of the script that I created so many years ago. I removed the particulars that are unique to our organization, and removed comments, but it should still work (assuming EMC hasn't changed the way idql32.exe works).

To use this code, put it in a file called, say, "DctmHelpers.ps1". Then, where you want to use it, put at the top of the script, ". DctmHelpers.ps1" making sure to put the full path if it is different from the current path. Then, call it like this:

$AllTheObjects = g-do "select * from dm_document"

Here is the script, in all its glory:

function Get-DocumentumObject {
  function GetContentType ($a_content_type){
  switch ($a_content_type)
    {
    "crtext"
      {
      $extension = ".txt"
      $format = "Plain text"
      }
    "excel5book"
      {
      $extension = ".xls"
      $format = "Excel spreadsheet"
      }
    "excel8book"
      {
      $extension = ".xls"
      $format = "Excel spreadsheet"
      }
    "excel2sheet"
      {
      $extension = ".xls"
      $format = "Excel spreadsheet"
      }
    "excel"
      {
      $extension = ".xls"
      $format = "Excel spreadsheet"
      }
    "msw8"
      {
      $extension = ".doc"
      $format = "Word document"
      }
    "msw6"
      {
      $extension = ".doc"
      $format = "Word document"
      }
    "doc"
      {
      $extension = ".doc"
      $format = "Word document"
      }
    "msw"
      {
      $extension = ".doc"
      $format = "Word document"
      }
    "msw8template"
      {
      $format = "Word template"
      $extension = ".dot"
      }
    "pdf"
      {
      $format = "Portable Document Format (PDF)"
      $extension = ".pdf"
      }
    "html"
      {
      $extension = ".htm"
      $format = "HyperText Markup Language (HTML)"
      }
    "jpeg"
      {
      $format = "Joint Photographic Experts Group (JPEG)"
      $extension = ".jpg"
      }
    "tiff"
      {
      $format = "Tagged Image File Format (TIFF)"
      $extension = ".tif"
      }
    "png"
      {
      $extension = ".png"
      $format = "Portable Network Graphics (PNG)"
      }
    "bmp"
      {
      $extension = ".bmp"
      $format = "Bitmap"
      }
    "gif"
      {
      $extension = ".gif"
      $format = "Graphics Interchange Format (GIF)"
      }
    "rtf"
      {
      $extension = ".rtf"
      $format = "Rich Text Format (RTF)"
      }
    "wp6"
      {
      $extension = ".wp6"
      $format = "WordPerfect6"
      }
    "pro"
      {
      $extension = ".pro"
      $format = "WordPerfect 6 (PRO)"
      }
    "ppt8"
      {
      $extension = ".ppt"
      $format = "PowerPoint"
      }
    "msg"
      {
      $extension = ".msg"
      $format = "Outlook message"
      }
    "entourage_msg"
      {
      $extension = ".msg"
      $format = "Entourage message"
      }
    "zip"
      {
      $extension = ".zip"
      $format = "Compressed ZIP file"
      }
    "123w"
      {
      $extension = ".123"
      $format = "Lotus 123"
      }
    default
      {
      $extension = ""
      $format = "Unknown"
      }
    }
  write-output $extension
  write-output $format
  }

    $dqlrun = $args[0]

  $ContentBoo = $false
  if ($dqlrun.contains("a_content_type"))
    {
    $ContentBoo = $true
    }
  $SelectStatement = @()
  $SelectStatement += "$dqlrun"
  $SelectStatement += "go"
  $presult = [path to idql32.exe] [repository name, credentials, etc.] $selectstatement | -U -P -n | where-object {$_ -notlike "*row affected*"} | where-object {$_ -notlike "*rows affected*"}
  if ($presult -eq $null)
    {
    write-warning "There was an error - you did not enter a valid DQL statement"
    write-output $null
    break
    }

  if ($presult.length -eq 2)
    {
    write-warning "No entries matching that statement"
    write-output $null
    break
    }

  $result = @()
  $split = @($presult[1].split(" ",[StringSplitOptions]::RemoveEmptyEntries))

  $headers = @()
  $numbers = @()
  $start = 0
  for ($i=0; $i -lt $split.length)
    {
    $length = $split[$i].length
    $column = $presult[0].substring($start,$length)
    $column = $column.trimend()
    $headers += $column
    $temp = @($start,$length)
    $numbers += $temp
    $start = $start + $length + 2
    $i++
    }

  for ($a=2; $a -lt $presult.length)
    {
    $listitem = new-object psobject
    $f = 0
    for ($i=0; $i -lt $headers.length)
      {
      $value = $presult[$a].substring($numbers[$f],$numbers[$f+1])
      $value = $value.trimend()
      $value = $value.replace("'","''")
      $listitem | add-member -membertype noteproperty -name $headers[$i] -value $value
      $i++
      $f = $f+2
      }
    if ($contentboo)
      {
      $contenttype = $listitem.a_content_type
      $ContentInfo = GetContentType $contenttype
      $listitem | add-member -membertype noteproperty -name "Extension" -value $ContentInfo[0]
      $listitem | add-member -membertype noteproperty -name "FormatInfo" -value $ContentInfo[1]
      }
    $result += $listitem
    $a++
    }

  write-output $result
}

new-alias -name g-do -value get-documentumobject