Skip to content

Instantly share code, notes, and snippets.

@bf4

bf4/jdbc_sample.rb

Forked from rwjblue/jdbc_sample.rb
Created April 18, 2018 17:36
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save bf4/1ef13eb1fa65a1cc762d0f7b07d42a4d to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save bf4/1ef13eb1fa65a1cc762d0f7b07d42a4d to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. bf4 revised this gist Apr 18, 2018. 1 changed file with 88 additions and 46 deletions.
    134 changes: 88 additions & 46 deletions jdbc_sample.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -1,55 +1,97 @@
    require 'java'
    require 'jt400'
    require 'date'
    require 'bigdecimal'
    require './lib/adsjdbc-10.10.0.28.jar'
    java_import java.sql.Driver
    java_import java.sql.DriverManager
    java_import 'com.extendedsystems.jdbc.advantage.ADSDriver'

    java_import 'com.ibm.as400.access.AS400JDBCDriver'
    class AdsAdapter

    def get_records
    @connection ||= java.sql.DriverManager.get_connection("jdbc:as400://127.0.0.1/",'username','password')
    rs = @connection.createStatement.executeQuery("SELECT * FROM RANDOM_TABLE")
    puts resultset_to_hash(rs).inspect
    end
    def initialize(connect_string)
    @connect_string = connect_string
    end

    def establish_connection(connect_string = @connect_string)
    ::DriverManager.getConnection(connect_string)
    end

    def connection
    @connection ||= establish_connection
    end

    def execute(sql)
    stmt = connection.createStatement
    rs = stmt.executeQuery(sql)
    resultset_to_hash(rs)
    ensure
    stmt.close if stmt
    end

    def resultset_to_hash(resultset)
    rows = []

    while resultset.next
    rows << row_to_hash(resultset)
    end

    def resultset_to_hash(resultset)
    meta = resultset.meta_data
    rows = []
    rows
    end

    while resultset.next
    def row_to_hash(resultset)
    column_count = resultset.meta_data.column_count
    row = {}

    (1..meta.column_count).each do |i|
    name = meta.column_name i
    row[name] = case meta.column_type(i)
    when -6, -5, 5, 4
    # TINYINT, BIGINT, INTEGER
    resultset.get_int(i).to_i
    when 41
    # Date
    resultset.get_date(i)
    when 92
    # Time
    resultset.get_time(i).to_i
    when 93
    # Timestamp
    resultset.get_timestamp(i)
    when 2, 3, 6
    # NUMERIC, DECIMAL, FLOAT
    case meta.scale(i)
    when 0
    resultset.get_long(i).to_i
    else
    BigDecimal.new(resultset.get_string(i).to_s)
    end
    when 1, -15, -9, 12
    # CHAR, NCHAR, NVARCHAR, VARCHAR
    resultset.get_string(i).to_s
    else
    resultset.get_string(i).to_s
    end
    (1..column_count).each do |column_index|
    name = get_column_name(resultset, column_index)
    value = get_column_value(resultset, column_index)
    row[name] = value
    end

    rows << row
    row
    end

    def inspect_column(resultset, column_index)
    meta = resultset.meta_data
    [
    meta.get_column_type_name(column_index),
    meta.get_column_name(column_index),
    meta.get_column_label(column_index),
    meta.get_column_class_name(column_index),
    meta.get_column_display_size(column_index),
    meta.get_column_type(column_index)
    ]
    end

    def get_column_name(resultset, column_index)
    resultset.meta_data.get_column_name(column_index)
    end
    rows
    end

    # https://gist.github.com/rwjblue/1366047
    def get_column_value(resultset, column_index)
    meta = resultset.meta_data
    case meta.get_column_type(column_index)
    when -6, -5, 5, 4 # TINYINT, BIGINT, INTEGER
    resultset.get_int(column_index).to_i
    when 41, 91 # Date
    resultset.get_date(column_index)&.toString
    when 92 # Time
    resultset.get_time(column_index).toString
    when 93 # Timestamp
    resultset.get_timestamp(column_index)&.toString
    when 2, 3, 6 # NUMERIC, DECIMAL, FLOAT
    if meta.get_scale(column_index).zero?
    resultset.get_long(column_index).to_i
    else
    resultset.get_string(column_index).to_s.to_f
    end
    when 1, -15, -9, 12
    # CHAR, NCHAR, NVARCHAR, VARCHAR
    resultset.get_string(column_index).to_s
    when 8 # [Double, double]
    resultset.get_string(column_index).to_s
    when -7 # Logical, boolean
    resultset.get_string(column_index).to_s
    else
    p [:unknown, inspect_column(resultset, column_index)]
    resultset.get_string(column_index).to_s
    end
    end
    end
  2. Robert Jackson created this gist Nov 15, 2011.
    55 changes: 55 additions & 0 deletions jdbc_sample.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,55 @@
    require 'java'
    require 'jt400'
    require 'date'
    require 'bigdecimal'

    java_import 'com.ibm.as400.access.AS400JDBCDriver'

    def get_records
    @connection ||= java.sql.DriverManager.get_connection("jdbc:as400://127.0.0.1/",'username','password')
    rs = @connection.createStatement.executeQuery("SELECT * FROM RANDOM_TABLE")
    puts resultset_to_hash(rs).inspect
    end

    def resultset_to_hash(resultset)
    meta = resultset.meta_data
    rows = []

    while resultset.next
    row = {}

    (1..meta.column_count).each do |i|
    name = meta.column_name i
    row[name] = case meta.column_type(i)
    when -6, -5, 5, 4
    # TINYINT, BIGINT, INTEGER
    resultset.get_int(i).to_i
    when 41
    # Date
    resultset.get_date(i)
    when 92
    # Time
    resultset.get_time(i).to_i
    when 93
    # Timestamp
    resultset.get_timestamp(i)
    when 2, 3, 6
    # NUMERIC, DECIMAL, FLOAT
    case meta.scale(i)
    when 0
    resultset.get_long(i).to_i
    else
    BigDecimal.new(resultset.get_string(i).to_s)
    end
    when 1, -15, -9, 12
    # CHAR, NCHAR, NVARCHAR, VARCHAR
    resultset.get_string(i).to_s
    else
    resultset.get_string(i).to_s
    end
    end

    rows << row
    end
    rows
    end