Class: JDBCHelpers::QueryResultsToHashOfArrays

Inherits:

Base

• Object
• Base
• JDBCHelpers::QueryResultsToHashOfArrays

Defined in:

lib/jdbc_helpers.rb

Overview

Creates a hash of arrays of hashes from a SQL query

Instance Attribute Summary

• #results ⇒ Object

  Returns the value of attribute results.

Instance Method Summary

• #initialize(db_connect, statement, key_field, logger = nil) ⇒ QueryResultsToHashOfArrays constructor

  A new instance of QueryResultsToHashOfArrays.

• #rs_to_hash(rs, index_key_field, multi_val) ⇒ Hash

  converts a JDBC recordset to an array of hashes, with one hash per record creates a hash from a jdbc record set index_key_field is the field you want to use as the top level hash key…

Methods inherited from Base

#cleanse_statement, #convert_to_string_classes

Constructor Details

#initialize(db_connect, statement, key_field, logger = nil) ⇒ QueryResultsToHashOfArrays

Returns a new instance of QueryResultsToHashOfArrays.

Parameters:

• db_connect (Object) —

  active connection against which to execute statement

• statement (String) —

  SQL statement text

• key_field (String) —

  SQL result set field containing the key to be used in the top level of hash

• logger (Logger) (defaults to: nil) —

  object otherwise will default to new Logger

# File 'lib/jdbc_helpers.rb', line 158

def initialize(db_connect, statement, key_field, logger = nil)
  @logger = logger ? logger : Logger.new(STDOUT)
  stmt = db_connect.create_statement
  @logger.info "executing query in thread #{Thread.current.object_id}:\n#{cleanse_statement(statement)}"
  start = Time.new.utc
  rs = stmt.execute_query(statement)
  @logger.info "query executed #{Time.new.utc - start} seconds"
  @results=rs_to_hash(rs, key_field, true)
end

Instance Attribute Details

#results ⇒ Object

Returns the value of attribute results.

# File 'lib/jdbc_helpers.rb', line 152

def results
  @results
end

Instance Method Details

#rs_to_hash(rs, index_key_field, multi_val) ⇒ Hash

converts a JDBC recordset to an array of hashes, with one hash per record creates a hash from a jdbc record set index_key_field is the field you want to use as the top level hash key… and should exist in the record set multi_val=true will create an array below each index_key_filed, false will create a hash as the child

Parameters:

• rs (Object) —

  JDBC result set object

• index_key_field (String) —

  field to use as top level hash keys

Returns:

• (Hash) —

  Hash of Arrays of Hashes for each record

# File 'lib/jdbc_helpers.rb', line 177

def rs_to_hash(rs, index_key_field, multi_val)
  # setting default hash value is necessary for appending to arrays
  hash=Hash.new{ |h, k| h[k] = [] }

  # get basic metadata for the recordset
  meta = rs.getMetaData
  cols = meta.getColumnCount.to_i

  # loop through the records to add them into hash
  while rs.next do
    # if multi_val is not true... create new hash value as an empty hash if it doesn't already exist
    hash[rs.getString(index_key_field)]={} if (!hash[rs.getString(index_key_field)] and !multi_val)

    # if multi_val is true... create new hash value as an empty array if it doesn't already exist
    hash[rs.getString(index_key_field)]=[] if (!hash[rs.getString(index_key_field)] and multi_val)

    # r is a temporary hash for the row being processed
    r=Hash.new

    # add each row to r
    (1..cols).each do |c|
      r[meta.get_column_name(c)] = rs.getObject(c)
      if convert_to_string_classes.include?(r[meta.get_column_name(c)].class)
        r[meta.get_column_name(c)] = r[meta.get_column_name(c)].to_s
      end
    end # each cols

    # set hash value to r if not multi_val
    hash[rs.getString(index_key_field)] = r if !multi_val

    # append hash to r if multi_val
    hash[rs.getString(index_key_field)] << r if multi_val
  end # while

  # completed hash is returned
  return hash
end