[Ruby-DBI-devel-CVS] CVS: subprojects/ruby-db2/samples db2.rb,NONE,1.1 sqlsh.rb,NONE,1.1 test.rb,NONE,1.1 test2.rb,NONE,1.1

[Michael N. <mne...@us...>]

Update of /cvsroot/ruby-dbi/subprojects/ruby-db2/samples
In directory usw-pr-cvs1:/tmp/cvs-serv2947/ruby-db2/samples

Added Files:
	db2.rb sqlsh.rb test.rb test2.rb 
Log Message:
initial import from local CVS repository


--- NEW FILE: db2.rb ---

require "db2/db2cli.rb"

module DB2

  FINALIZE_INFO = {}
  FINALIZE_PROC = proc do |id|
    info =  FINALIZE_INFO[id]
    if $DEBUG then
      $stderr.puts "freeing handle <#{info[1]}> (#{info[0]})"
    end
    DB2CLI::SQLFreeHandle(info[0], info[1]) 
  end 


  class CLIError < Exception
    attr_reader :sql_state, :native_error, :message_text

    def initialize(sql_state, native_error, message_text)
      @sql_state    = sql_state
      @native_error = native_error
      @message_text = message_text
    end
    
    def message
      @message_text
    end    
  end

  class Handle
    include DB2CLI

    def initialize(handle_type, parent_handle)
      @handle_type = handle_type
      @handle = parent_handle

      rc, handle = SQLAllocHandle(@handle_type, @handle)
      check_rc(rc)
      @handle = handle
      add_finalizer
     end

    def free
      rc = SQLFreeHandle(@handle_type, @handle)
      check_rc(rc)
    end

    def handle
      @handle
    end

    private

    def add_finalizer
      DB2::FINALIZE_INFO[self.id] = [@handle_type.dup, @handle.dup]  
      ObjectSpace.define_finalizer(self, DB2::FINALIZE_PROC)
    end

    def check_rc(rc)
      if rc != SQL_SUCCESS and rc != SQL_SUCCESS_WITH_INFO then 
        err = SQLGetDiagRec(@handle_type, @handle, 1, 1024)
        raise CLIError.new(err[1], err[2], err[3]) 
      end
    end
  end # class Handle


  class Environment < Handle

    def initialize
      super(SQL_HANDLE_ENV, SQL_NULL_HANDLE)
    end

    def dataSources
      data_sources_buffer
    end


    def createConnection(*a)
      Connection.new(self, *a)
    end


    private

    def data_sources_buffer(buffer_length = 1024)
      retval = []
      max_buffer_length = buffer_length

      a = SQLDataSources(@handle, SQL_FETCH_FIRST, SQL_MAX_DSN_LENGTH+1, buffer_length)
      retval << [a[1], a[3]]
      max_buffer_length = [max_buffer_length, a[4]].max

      loop do
        a = SQLDataSources(@handle, SQL_FETCH_NEXT, SQL_MAX_DSN_LENGTH+1, buffer_length)
        break if a[0] == SQL_NO_DATA_FOUND

        retval << [a[1], a[3]]
        max_buffer_length = [max_buffer_length, a[4]].max
      end 
     
      if max_buffer_length > buffer_length then
        data_sources_buffer(max_buffer_length)
      else
        retval
      end
    end


  end # class Environment



  class Connection < Handle

    def initialize(environment, *conn_params)
      @env = environment

      super(SQL_HANDLE_DBC, @env.handle)

      @connected = false

      unless conn_params.empty?
        connect(*conn_params)
      end
    end

    def connect(server_name, user_name="", auth="")
      rc = SQLConnect(@handle, server_name, user_name, auth) 
      check_rc(rc)
      @connected = true
    end

    def disconnect
      rc = SQLDisconnect(@handle)
      check_rc(rc)
      @connected = false
    end

    def rollback
      rc = SQLEndTran(@handle_type, @handle, SQL_ROLLBACK)
      check_rc(rc)
    end

    def commit
      rc = SQLEndTran(@handle_type, @handle, SQL_COMMIT)
      check_rc(rc)
    end

    def createStatement
      Statement.new(self)
    end

  end # class Connection


  class Statement < Handle

    def initialize(connection)
      @conn = connection
      super(SQL_HANDLE_STMT, @conn.handle)
    end

    def prepare(sql)
      rc = SQLPrepare(@handle, sql)
      check_rc(rc)
      self
    end

    def freeStmt(options)
      rc = SQLFreeStmt(@handle, options) 
      ckeck_rc(rc)
    end

    def execute
      rc = SQLExecute(@handle)
      check_rc(rc)
      self
    end

    def execDirect(sql)
      rc = SQLExecDirect(@handle, sql)
      check_rc(rc)
      self
    end

    def tables(schema_name="%", table_name="%", table_type="TABLE") 
      rc = SQLTables(@handle, "", schema_name, table_name, table_type)
      check_rc(rc)
      self 
    end

    def rowCount
      rc, rowcount = SQLRowCount(@handle)
      check_rc(rc)
      rowcount
    end

    def numResultCols
      rc, cols = SQLNumResultCols(@handle)
      check_rc(rc)
      cols
    end

    def colNames
      rc, nr_cols = SQLNumResultCols(@handle)
    
      (1..nr_cols).collect do |c| 
        rc, name, bl, type, col_sz = SQLDescribeCol(@handle, c, 1024)
        name
      end 
    end

    def fetchAll
      if block_given?
        while (row=fetchRow) do
          yield row
        end
      else
        res = []
        while (row=fetchRow) do
          res << row
        end
        res
      end
    end

    def fetchRow(orientation=SQL_FETCH_NEXT, offset=0)
      rc, nr_cols = SQLNumResultCols(@handle)
    
      cols = (1..nr_cols).collect do |c| 
        rc, name, bl, type, col_sz = SQLDescribeCol(@handle, c, 1024)
        [name, type, col_sz]
      end 

      rc = SQLFetchScroll(@handle, orientation, offset)

      return nil if rc == SQL_NO_DATA_FOUND
      raise "ERROR" unless rc == SQL_SUCCESS 
    
      retval = [] 
      cols.each_with_index do |c, i|
        rc, content = SQLGetData(@handle, i+1, c[1], c[2]) 
        retval << content
      end 

      return retval
    end

    alias :fetch :fetchRow

  end # class Statement


end # module DB2

--- NEW FILE: sqlsh.rb ---

require "readline"
require "db2"

class ReadlineControl
  
  def initialize
    @keywords = []
    set_prompt
    Readline.completion_proc = proc {|str| complete(str) }
  end

  def add_keywords(arr)
    @keywords += arr
  end

  def complete(str)
    @keywords.grep(/^#{Regexp.escape(str)}/i)
  end

  def set_prompt(prompt="> ")
    @prompt = prompt
  end

  def readline
    Readline.readline(@prompt, true)
  end

end

if ARGV.size < 1 or ARGV.size > 3
  puts
  puts "USAGE: #{$0} database [, user [, password] ]"
  puts
  exit 1
else
  DB   = ARGV.shift
  USER = ARGV.shift || ""
  PASS = ARGV.shift || ""
end

puts
begin
  print "  CONNECT TO #{DB}" 
  print " USER '#{USER}'" unless USER.empty?
  print " PASSWORD '#{PASS}'" unless PASS.empty?
  puts

  Env  = DB2::Environment.new
  Conn = Env.createConnection("SAMPLE")

rescue DB2::CLIError => err
    puts
    puts err.message
    puts
end

puts


PROMPT      = "db2 => "
PROMPT_CONT = "db2 =| "

SQL_KEYWORDS = %w(
  INSERT DELETE UPDATE SELECT FROM WHERE IN LIKE SET VALUES INTO
  CREATE TABLE DROP 
  COMMIT ROLLBACK
  CHAR VARCHAR INT INTEGER NUMBER FLOAT REAL LONG CLOB BLOB DECIMAL DBCLOB DBBLOB
)

rd = ReadlineControl.new
rd.add_keywords SQL_KEYWORDS
rd.set_prompt(PROMPT)


def output_table(rows)
  collength = [] 
 
  rows.each {|r| r.each_with_index {|c,i|  
    r[i] = c.to_s.chomp("\000")
    c = r[i]
    collength[i] = c.size if collength[i].nil? or c.size > collength[i] 
  } }

  split_line = " +"
  collength.each {|col|
    split_line << "-" * (col+2) + "+"
  }
  
  puts
  puts split_line

  # output table
  rows.each_with_index {|r, rn|
    print " |"
    r.each_with_index {|c,i|
      print sprintf(" %*2$s |", c.to_s, -(collength[i]))
    }
    puts
    puts split_line if rn == 0
  }
  puts split_line
  puts
end


loop {
  line = rd.readline
  line.chomp!
  next if line.empty?
 
  begin

    if line =~ /^\\/ then
      if line =~ /^\\list tables/i then
        stmt = Conn.createStatement.tables
      elsif line =~ /^\\list views/i then
        stmt = Conn.createStatement.tables("%", "%", "VIEW")
      elsif line =~ /^\\quit/i then
        break 
      else
        puts
        puts "Unknown command!"
        puts
        next
      end
    else
      # multi-line
      if line[-1].chr == "\\" then
        line.chop!
        rd.set_prompt(PROMPT_CONT)
        loop {
          ln = rd.readline
          line.chomp!
          next if line.empty?
           
          if ln[-1].chr == "\\" then
            line += ln.chop
          else
            line += ln
            break
          end
        }
      end

      rd.set_prompt(PROMPT)
      stmt = Conn.createStatement.execDirect(line)
    end

    head = stmt.colNames 
    next if head.empty? 
    rows = stmt.fetchAll 
    rows[0,0] = [head]

    output_table(rows)
  rescue DB2::CLIError => err
    puts
    puts err.message
    puts
  end
}

Conn.disconnect
Conn.free
Env.free



--- NEW FILE: test.rb ---

require "db2/db2cli.rb"

include DB2CLI

rc, env  = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE); p rc

puts "Datasources: "
p SQLDataSources(env, SQL_FETCH_FIRST, SQL_MAX_DSN_LENGTH+1, 200) 

loop { 
  a = SQLDataSources(env, SQL_FETCH_NEXT, SQL_MAX_DSN_LENGTH+1, 200)
  break if a[0] == SQL_NO_DATA_FOUND
  p a
}
puts "--------------------"
readline

rc, dbc  = SQLAllocHandle(SQL_HANDLE_DBC, env); p rc

p SQLConnect(dbc, "SAMPLE", "", "")

rc, stmt = SQLAllocHandle(SQL_HANDLE_STMT, dbc); p rc



p SQLPrepare(stmt, "SELECT * FROM EMPLOYEE")

p SQLExecute(stmt)

DELIM = "  "

rc, COLS = SQLNumResultCols(stmt)

cols = (1..COLS).collect {|c| 
  rc, name, bl, type, col_sz = SQLDescribeCol(stmt, c, 100)
  [name, type, col_sz]
}

cols.each {|c| print c[0], DELIM }
puts

while SQLFetch(stmt) == SQL_SUCCESS do

  cols.each_with_index {|c, i|
    rc, content = SQLGetData(stmt, i+1, c[1], 100) 
    print content, DELIM 
  }
  puts 
end

p SQLFreeStmt(stmt, SQL_CLOSE)

################
def get_row_as_hash(stmt)
  rc, nr_cols = SQLNumResultCols(stmt)
  
  cols = (1..nr_cols).collect do |c| 
    rc, name, bl, type, col_sz = SQLDescribeCol(stmt, c, 100)
    [name, type, col_sz]
  end 

  rc = SQLFetch(stmt)

  return nil if rc == SQL_NO_DATA_FOUND
  raise "ERROR" unless rc == SQL_SUCCESS 
  
  retval = {}
  cols.each_with_index do |c, i|
    rc, content = SQLGetData(stmt, i+1, c[1], c[2]) 
    retval[c[0]] = content
  end 

  return retval
end
################

puts "HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH"
p SQLTables(stmt, "", "", "%", "")
while (c=get_row_as_hash(stmt))
  p c
end
puts "HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH"


p SQLFreeStmt(stmt, SQL_CLOSE)

p SQLExecDirect(stmt, "SELECT 2, 2.3+3.4, 'Hallo' FROM EMPLOYEE")
#p SQLExecDirect(stmt, "SELECT YEARS FROM STAFF WHERE YEARS IS NULL")

p "*************************************"
p SQLColAttribute(stmt, 2, SQL_DESC_TYPE_NAME, 200)
p SQLColAttribute(stmt, 2, SQL_DESC_TYPE, nil)
p "*************************************"


p SQLFetch(stmt)


puts "=================================="
p SQLGetData(stmt, 1, SQL_INTEGER)
p SQLGetData(stmt, 1, SQL_SMALLINT)

p SQLGetData(stmt, 2, SQL_DOUBLE)
#p SQLGetData(stmt, 2, SQL_FLOAT)
#p SQLGetData(stmt, 2, SQL_REAL)

p SQLGetData(stmt, 3, SQL_INTEGER, 20000)
puts "=================================="


p SQLFreeStmt(stmt, SQL_CLOSE)


puts "-----111111111111111-----------"


p SQLExecDirect(stmt, "INSERT INTO meintable (test,hallo) VALUES (2,3)")

p SQLGetDiagRec(SQL_HANDLE_STMT, stmt, 1, 1000)

p SQLRowCount(stmt)

p SQLEndTran(SQL_HANDLE_DBC, dbc, SQL_ROLLBACK)
p SQLFreeHandle(SQL_HANDLE_STMT, stmt)




p SQLDisconnect(dbc)
p SQLFreeHandle(SQL_HANDLE_DBC, dbc)
p SQLFreeHandle(SQL_HANDLE_ENV, env)


--- NEW FILE: test2.rb ---
require "db2.rb"
include DB2

env = Environment.new
conn = env.createConnection("SAMPLE")


stmt = conn.createStatement
stmt.prepare("SELECT * FROM EMPLOYEE").execute
p stmt.fetchAll

conn.disconnect