Class: JDBCHelpers::QueryResultsToJSONFile

Inherits:
Base
  • Object
show all
Defined in:
lib/jdbc_helpers.rb

Overview

class

Instance Method Summary collapse

Methods inherited from Base

#cleanse_statement, #convert_to_string_classes

Constructor Details

#initialize(db_connect, statement, file_object, formatter = nil, logger = nil) ⇒ QueryResultsToJSONFile

runs a SQL query, then writes the results as JSON objects to a provided file object. by use of the formatter parameter, you can change the output to any format you desire (CSV, XML, etc) see json_formatter method for an example of a proc to perform formatting

Parameters:

  • db_connect (Object) —

    active connection against which to execute statement

  • statement (String) —

    SQL statement text

  • file_object (IO) —

    IO object to receive the formatted results

  • formatter (proc) (defaults to: nil) —

    proc to handle the actual formatting, defaults to JSON if nil

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

    object otherwise will default to new Logger



225
226
227
228
229
230
231
232
233
234
235
 
# File 'lib/jdbc_helpers.rb', line 225

def initialize(db_connect, statement, file_object, formatter = nil, logger = nil)
  @logger = logger ? logger : Logger.new(STDOUT)
  stmt = db_connect.create_statement
  @logger.info "executing query: #{cleanse_statement(statement)}"
  start = Time.new.utc
  rs = stmt.execute_query(statement)
  @logger.info "query executed initial results #{Time.new.utc - start} seconds"
  record_count = rs_to_json_file(rs, file_object, formatter)
  @logger.info "query export time #{Time.new.utc - start} seconds"
  @logger.info "#{record_count} rows exported"
end

Instance Method Details

#json_formatter ⇒ proc

proc must handle two inputs, |file_object, record hash|

Returns:

  • (proc) —

    returns proc to output json



274
275
276
 
# File 'lib/jdbc_helpers.rb', line 274

def json_formatter
  Proc.new { |f,h| f.puts h.to_json }
end

#rs_to_json_file(rs, file_object, formatter) ⇒ Object

outputs a JDBC result set to a formatted file formatter defaults to JSON output unless you provide your own proc

Parameters:

  • rs (Object) —

    JDBC result set

  • file_object (IO) —

    IO object to receive the formatted results

  • formatter (proc) —

    proc to handle the actual formatting, defaults to JSON if nil



242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
 
# File 'lib/jdbc_helpers.rb', line 242

def rs_to_json_file(rs, file_object, formatter)
  # default formatter outputs json objects for each row
  formatter = json_formatter unless formatter

  # get basic metadata for the recordset
  meta = rs.getMetaData
  cols = meta.getColumnCount.to_i
  
  record_count = 0
  # loop through the records to add them into hash
  while rs.next do

    # 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

    # formatter handles output of r to file
    formatter.call(file_object, r)
    record_count += 1
  end # while
  return record_count
end