class Cms

Public Class Methods

get_dbi_connection(hostname, user, password) click to toggle source

Open database connection

# File lib/cms_test.rb, line 15
def self.get_dbi_connection(hostname, user, password)
        connection = DBI.connect("DBI:OCI8:" + hostname, user, password)
        return connection
end
query(sql) click to toggle source

query database TODO pass connection as an argument

# File lib/cms_test.rb, line 22
def self.query(sql)
        type = sql.split()[0].downcase
       begin
               # TODO get rid of this line
               connection = self.get_dbi_connection($host, $user, $pass)

               # process request
               request = nil
               if type != 'insert'
               request = connection.prepare(sql)
               request.execute
       else
               request = connection.do(sql)
       end

       # run select queries
       if type == 'select'
                       fetched = []
               request.fetch do |row|
                       fetched << row.to_h
               end
               request.finish
               return fetched
       end

       # finish updates
       if type == 'update'
               request.finish
       end

       # commit inserts and updates
       if type != 'select'
               connection.commit
       end

    # rescue db errors
       rescue DBI::DatabaseError => e
       puts "ERROR: #{e.err} - #{e.errstr}"
       if type != 'select'
               connection.rollback
       end
       end
end
query2table(query) click to toggle source
# File lib/cms_test.rb, line 66
def self.query2table(query)
        keys = query[0].keys
        table = []
        table.push(keys)
        lengths = []
        # get max length for each column
        keys.each do |key|
                column = [key.length]
                query.each do |row|
                        column.push(row[key].to_s.length)
                end
                lengths.push(column.max)
        end
        # add rows to table
        query.each do |row|
                table.push(row.values)
        end
        lines = []
        table.length.times do |row_index|
                row = table[row_index]
                line = []
                row.length.times do |column_index|
                        column = row[column_index]
                        length = lengths[column_index]
                        line.push("%-#{length}s" % column)
                end
                lines.push(line)
        end
        lines.each do |line|
                puts line.join(' | ')
        end
end