ruby-dbi-devel-cvs Mailing List for Ruby DBI (Page 3)

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

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

Modified Files:
	db2.rb 
Log Message:
added Songsu Yun's patch


Index: db2.rb
===================================================================
RCS file: /cvsroot/ruby-dbi/subprojects/ruby-db2/samples/db2.rb,v
retrieving revision 1.1
retrieving revision 1.2
diff -u -r1.1 -r1.2

--- db2.rb	5 Sep 2002 09:57:14 -0000	1.1
+++ db2.rb	20 Dec 2002 10:07:31 -0000	1.2
@@ -3,87 +3,54 @@
 
 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
+  module DB2Util
     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)
+      SQLFreeHandle(@handle_type, @handle)
     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]) 
+      if rc != SQL_SUCCESS and rc != SQL_SUCCESS_WITH_INFO
+         case rc
+            when SQL_NEED_DATA
+                 rcText = 'SQL_NEED_DATA'
+            when SQL_NO_DATA_FOUND
+                 rcText = 'SQL_NO_DATA'
+            when SQL_STILL_EXECUTING
+                 rcText = 'SQL_STILL_EXECUTING'
+            when SQL_ERROR
+                 rcText = 'SQL_ERROR'
+            when SQL_INVALID_HANDLE
+                 rcText = 'SQL_INVALID_HANDLE'
+         end
+         puts "DB2 return code is #{rcText}"
+         rec = 1
+         loop do
+           a = SQLGetDiagRec(@handle_type, @handle, rec, 500)
+           break if a[0] != SQL_SUCCESS
+           puts a[3] if a[3] != nil and a[3] != ''          #display message
+           rec += 1
+         end
+         raise "ERROR From DB2"
       end
     end
-  end # class Handle
-
+  end # module DB2Util
 
-  class Environment < Handle
+  class Environment
+    include DB2Util
 
     def initialize
-      super(SQL_HANDLE_ENV, SQL_NULL_HANDLE)
-    end
-
-    def dataSources
-      data_sources_buffer
-    end
-
-
-    def createConnection(*a)
-      Connection.new(self, *a)
+      @handle_type = SQL_HANDLE_ENV
+      rc, @handle = SQLAllocHandle(@handle_type, SQL_NULL_HANDLE)
+      check_rc(rc)
     end
 
-
-    private
-
-    def data_sources_buffer(buffer_length = 1024)
+    def data_sources(buffer_length = 1024)
       retval = []
       max_buffer_length = buffer_length
 
@@ -97,44 +64,58 @@
 
         retval << [a[1], a[3]]
         max_buffer_length = [max_buffer_length, a[4]].max
-      end 
-     
+      end
+
       if max_buffer_length > buffer_length then
-        data_sources_buffer(max_buffer_length)
+        get_data_sources(max_buffer_length)
       else
         retval
       end
     end
 
-
   end # class Environment
 
 
+  class Connection
+    include DB2Util
 
-  class Connection < Handle
-
-    def initialize(environment, *conn_params)
+    def initialize(environment)
       @env = environment
+      @handle_type = SQL_HANDLE_DBC
+      rc, @handle = SQLAllocHandle(@handle_type, @env.handle)
+      check_rc(rc)
+    end
 
-      super(SQL_HANDLE_DBC, @env.handle)
+    def connect(server_name, user_name="", auth="")
+      rc = SQLConnect(@handle, server_name, user_name, auth)
+      check_rc(rc)
+    end
 
-      @connected = false
+    def set_auto_commit(switch)
+      switch.upcase!
+      if switch != 'ON' and switch != 'OFF'
+         raise 'set_auto_commit: Specify either "ON" or "OFF"'
+      end
 
-      unless conn_params.empty?
-        connect(*conn_params)
+      if switch == 'ON'
+         rc = SQLSetConnectAttr(@handle, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_ON)
+      else
+         rc = SQLSetConnectAttr(@handle, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF)
       end
+      check_rc(rc)
     end
 
-    def connect(server_name, user_name="", auth="")
-      rc = SQLConnect(@handle, server_name, user_name, auth) 
+    def set_connect_attr(attr, value)
+      if value.type == String
+         value += "\0"
+      end
+      rc = SQLSetConnectAttr(@handle, attr, value)
       check_rc(rc)
-      @connected = true
     end
 
     def disconnect
       rc = SQLDisconnect(@handle)
       check_rc(rc)
-      @connected = false
     end
 
     def rollback
@@ -147,109 +128,270 @@
       check_rc(rc)
     end
 
-    def createStatement
-      Statement.new(self)
-    end
 
   end # class Connection
 
 
-  class Statement < Handle
+  class Statement
+    include DB2Util
 
     def initialize(connection)
       @conn = connection
-      super(SQL_HANDLE_STMT, @conn.handle)
+      @handle_type = SQL_HANDLE_STMT
+      @parms = []                           #yun
+      @sql = ''                             #yun
+      @numParms = 0                         #yun
+      @prepared = false                     #yun
+      @parmArray = []                       #yun. attributes of the parameter markers
+      rc, @handle = SQLAllocHandle(@handle_type, @conn.handle)
+      check_rc(rc)
     end
 
     def prepare(sql)
+      @sql = sql
       rc = SQLPrepare(@handle, sql)
       check_rc(rc)
+      rc, @numParms = SQLNumParams(@handle) #number of question marks
+      check_rc(rc)
+      #--------------------------------------------------------------------------
+      # parameter attributes are stored in instance variable @parmArray so that
+      # they are available when execute method is called.
+      #--------------------------------------------------------------------------
+      if @numParms > 0            #get parameter marker attributes
+         1.upto(@numParms) do     #parameter number starts from 1
+            | i |
+            rc, type, size, decimalDigits = SQLDescribeParam(@handle, i)
+            check_rc(rc)
+            @parmArray << Parameter.new(type, size, decimalDigits)
+         end
+      end
+      @prepared = true;
       self
     end
 
-    def freeStmt(options)
-      rc = SQLFreeStmt(@handle, options) 
-      ckeck_rc(rc)
-    end
+    def execute(*parms)
+      if @prepared == false
+         raise "The statement was not prepared"
+      end
+
+      if parms.size == 1 and parms[0].type == Array
+         parms = parms[0]
+      end
+
+      if (@numParms != parms.size)
+         raise "Number of parameters supplied does not match with the SQL statement"
+      end
+
+      if @numParms > 0            #need to bind parameters
+         #--------------------------------------------------------------------
+         #calling bindParms may not be safe. Look comment below.
+         #--------------------------------------------------------------------
+         #bindParms(parms)        #may not be safe
+
+         valueArray = []
+         1.upto(@numParms) do     #parameter number starts from 1
+            | i |
+            type = @parmArray[i - 1].type
+            size = @parmArray[i - 1].size
+            decimalDigits = @parmArray[i - 1].decimalDigits
+
+            if parms[i - 1].type == String
+               valueArray << parms[i - 1]
+            else
+               valueArray << parms[i - 1].to_s
+            end
+
+            rc = SQLBindParameter(@handle, i, type, size, decimalDigits, valueArray[i - 1])
+            check_rc(rc)
+         end
+      end
 
-    def execute
       rc = SQLExecute(@handle)
       check_rc(rc)
+
+      if @numParms != 0
+         SQLFreeStmt(@handle, SQL_RESET_PARAMS)    #reset parameters
+         check_rc(rc)
+      end
+
       self
     end
 
-    def execDirect(sql)
+    #-------------------------------------------------------------------------------
+    # The last argument(value) to SQLBindParameter is a deferred argument, that is,
+    # it should be available when SQLExecute is called. Even though "value" is
+    # local to bindParms method, it seems that it is available when SQLExecute
+    # is called. I am not sure whether it would still work if garbage collection
+    # is done between bindParms call and SQLExecute call inside the execute method
+    # above.
+    #-------------------------------------------------------------------------------
+    def bindParms(parms)        #This is the real thing. It uses SQLBindParms
+       1.upto(@numParms) do     #parameter number starts from 1
+          | i |
+          rc, dataType, parmSize, decimalDigits = SQLDescribeParam(@handle, i)
+          check_rc(rc)
+          if parms[i - 1].type == String
+             value = parms[i - 1]
+          else
+             value = parms[i - 1].to_s
+          end
+          rc = SQLBindParameter(@handle, i, dataType, parmSize, decimalDigits, value)
+          check_rc(rc)
+       end
+    end
+ 
+    #------------------------------------------------------------------------------
+    # bind method does not use DB2's SQLBindParams, but replaces "?" in the
+    # SQL statement with the value before passing the SQL statement to DB2.
+    # It is not efficient and can handle only strings since it puts everything in
+    # quotes.
+    #------------------------------------------------------------------------------
+    def bind(sql, args)                #does not use SQLBindParams
+      arg_index = 0
+      result = ""
+      tokens(sql).each { |part|
+        case part
+        when '?'
+          result << "'" + (args[arg_index]) + "'"  #put it into quotes
+          arg_index += 1
+        when '??'
+          result << "?"
+        else
+          result << part
+        end
+      }
+      if arg_index < args.size
+        raise "Too many SQL parameters"
+      elsif arg_index > args.size
+        raise "Not enough SQL parameters"
+      end
+      result
+    end
+
+    ## Break the sql string into parts.
+    #
+    # This is NOT a full lexer for SQL.  It just breaks up the SQL
+    # string enough so that question marks, double question marks and
+    # quoted strings are separated.  This is used when binding
+    # arguments to "?" in the SQL string.  Note: comments are not
+    # handled.
+    #
+    def tokens(sql)
+      toks = sql.scan(/('([^'\\]|''|\\.)*'|"([^"\\]|""|\\.)*"|\?\??|[^'"?]+)/)
+      toks.collect {|t| t[0]}
+    end
+
+
+    def exec_direct(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)
+
+    def set_cursor_name(name)
+      rc = SQLSetCursorName(@handle, name)
+      check_rc(rc)
+      self
+    end
+
+    def get_cursor_name
+      rc, name = SQLGetCursorName(@handle)
       check_rc(rc)
-      self 
+      name
     end
 
-    def rowCount
+    def row_count
       rc, rowcount = SQLRowCount(@handle)
       check_rc(rc)
       rowcount
     end
 
-    def numResultCols
+    def num_result_cols
       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
+    def fetch_all
       if block_given?
-        while (row=fetchRow) do
+        while (row=fetch) do
           yield row
         end
       else
         res = []
-        while (row=fetchRow) do
+        while (row=fetch) do
           res << row
         end
         res
       end
     end
 
-    def fetchRow(orientation=SQL_FETCH_NEXT, offset=0)
+
+    def fetch
       rc, nr_cols = SQLNumResultCols(@handle)
-    
-      cols = (1..nr_cols).collect do |c| 
+
+      cols = (1..nr_cols).collect do |c|
         rc, name, bl, type, col_sz = SQLDescribeCol(@handle, c, 1024)
         [name, type, col_sz]
-      end 
+      end
 
-      rc = SQLFetchScroll(@handle, orientation, offset)
+      rc = SQLFetch(@handle)
 
-      return nil if rc == SQL_NO_DATA_FOUND
-      raise "ERROR" unless rc == SQL_SUCCESS 
-    
-      retval = [] 
+      if rc == SQL_NO_DATA_FOUND
+         #SQLCloseCursor(@handle)
+         SQLFreeStmt(@handle, SQL_CLOSE)           #close cursor
+         return nil
+      end
+
+      raise "ERROR" unless rc == SQL_SUCCESS
+
+      retval = []
       cols.each_with_index do |c, i|
-        rc, content = SQLGetData(@handle, i+1, c[1], c[2]) 
+        rc, content = SQLGetData(@handle, i+1, c[1], c[2] + 1) #yun added 1 to c[2]
         retval << content
-      end 
+      end
 
       return retval
     end
 
-    alias :fetch :fetchRow
+    def fetch_as_hash
+      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 = SQLFetch(@handle)
+
+      if rc == SQL_NO_DATA_FOUND
+         #SQLCloseCursor(@handle)
+         SQLFreeStmt(@handle, SQL_CLOSE)           #close cursor
+         SQLFreeStmt(@handle, SQL_RESET_PARAMS)    #reset parameters
+         return nil
+      end
+
+      raise "ERROR" unless rc == SQL_SUCCESS
+
+      retval = {}
+      cols.each_with_index do |c, i|
+        rc, content = SQLGetData(@handle, i+1, c[1], c[2] + 1)   #yun added 1 to c[2]
+        retval[c[0]] = content
+      end
+
+      return retval
+    end
 
   end # class Statement
 
+  class Parameter
+     attr_reader :type, :size, :decimalDigits
+     def initialize(type, size, decimalDigits)
+        @type = type
+        @size = size
+        @decimalDigits = decimalDigits
+     end
+  end
 
 end # module DB2





2002	Jan	Feb (3)	Mar	Apr (12)	May (11)	Jun (2)	Jul (37)	Aug (3)	Sep (24)	Oct (31)	Nov	Dec (4)
2003	Jan (3)	Feb (7)	Mar	Apr (11)	May (15)	Jun (7)	Jul	Aug	Sep	Oct	Nov (4)	Dec
2004	Jan	Feb	Mar	Apr (1)	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec

ruby-dbi-devel-cvs Mailing List for Ruby DBI (Page 3)

ruby-dbi-devel-cvs — Ruby-DBI CVS commit list