Running a PeopleSoft Query with Ruby over HTTP

I had to pull some data from PeopleSoft recently into a Ruby program. I only had HTTP connectivity to the PeopleSoft server so I decided to pull this data using an HTTP request and PeopleSoft Query. It turned out to be be pretty easy.

Query Structure

First you need to define your PeopleSoft query. For this example, I created a private query that is called PSROLEDEFN and has the following SQL.

SELECT A.ROLENAME, A.DESCR, A.ROLESTATUS, A.DESCRLONG 
  FROM PSROLEDEFN A

It is just a simple export of the PSROLEDEFN table to keep things simple for this example.

Ruby psQueryRunner class

I then created a Ruby Module and Class that would do the following.

• Login into a PeopleSoft web server and get an authentication cookie
• Perform an HTTP request to get the XML results of the query

This module was developed for some short-term usage so I did not spend much time optimizing the code in this module. There is much room for improvement. The source code for this module can be found in the last section of this article.

It is also important to note here that integration broker was NOT used.

Ruby Script Example

This is a test script that will use the psQueryRunner class to call the private PSROLEDEFN query. It does the following:

• Parse the returned http body to xml
• Loops through each row and prints the ROLENAME and DESCR field from the result.

# test.rb 
require './PsQueryRunner.rb'
require 'rexml/document'

psqry = PsQueryRunner::PsQueryRunner.new("VP1","VP1","peoplesoft.cedarhillsgroup.com", "hrtst")

qryResults = psqry.getQueryResults("PRIVATE.PSROLEDEFN")

doc = REXML::Document.new(qryResults)

doc.root.elements.each('/query/row/') do |row|
  puts "Role Name: #{row.elements['ROLENAME'].text} - #{row.elements['DESCR'].text}"
end

psqry.logout

Ruby psQueryRunner source code

require 'net/https'

module PsQueryRunner

  class PsQueryRunner
    attr_reader :psoftCookies, :lastResponseBody
    def initialize(psoftUserName, psoftPassword, psoftHost, psoftSiteName )

      @psoftUserName = psoftUserName
      @psoftPassword = psoftPassword
      @psoftHost = psoftHost
      @psoftSiteName = psoftSiteName
      @psoftCookies = ""
      self.login
      @lastResponseBody = ""
    end


    def login

      begin
        http = Net::HTTP.new("#{@psoftHost}", 443)
        http.use_ssl = true
        http.start do |http|
          # GET AUTH COOKIE 
          req = Net::HTTP::Post.new("/psp/#{@psoftSiteName}/?cmd=login")
          req.set_form_data({"userid" => @psoftUserName, "pwd" => @psoftPassword})
          req["User-Agent"] = "ruby"
          response = http.request(req)

          @psoftCookies = response['set-cookie']

        end
      rescue SocketError
        raise "Host " + host + " No connect"
      end
    end

    def logout
       begin
         puts '****logging out********'
         http = Net::HTTP.new("#{@psoftHost}", 443)
         http.use_ssl = true
         http.start do |http|
           # GET AUTH COOKIE 
           req = Net::HTTP::Post.new("/psp/#{@psoftSiteName}/?cmd=logout")
           req.set_form_data({"userid" => @psoftUserName, "pwd" => @psoftPassword})
           req["User-Agent"] = "ruby"
           req["Cookies"] = @psoftCookies
           response = http.request(req)

           @psoftCookies = response['set-cookie']

         end
       rescue SocketError
         raise "Host " + host + " No connect"
       end

    end
    def getQueryResults(queryName) 

      begin
        http = Net::HTTP.new("#{@psoftHost}", 443)
        http.use_ssl = true
        headerHash = {"User-Agent" =>'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/534.13 (KHTML, like Gecko) Chrome/9.0.597.98 Safari/534.13', 
          "Cookie" => @psoftCookies, 
          "accept" => "application/xml,application/xhtml+xml"}
        http.start do |http|
          req = Net::HTTP::Get.new("/psc/#{@psoftSiteName}/EMPLOYEE/HRMS/q/?ICAction=ICQryNameXMLURL=#{queryName}",                                 headerHash)
          response = http.request(req) 

          if response.kind_of?(Net::HTTPRedirection) 
            redirectURL = response['location']
            req = Net::HTTP::Get.new(redirectURL,headerHash)
            response = http.request(req) 
          end

          if response.kind_of?(Net::HTTPSuccess) and response.content_type = 'application/download'
             return response.body
          else
            puts " Did not get an http success and xml data back"
            puts "-------------"
            puts response.body
            puts "-------------"
            puts "-------------"
            @lastResponseBody = response.body

          end


        end 
      rescue SocketError
        raise "Host " + host + " No connect"


      end # rescue end
    end # get Query Result End

  end # class End
end # Module End