Re: [Ruby-DBI-devel] Storing Binary Data....

[Michael N. <uu...@rz...>]

On Thu, Jun 13, 2002 at 04:32:00AM -0700, Sean Chittenden wrote:
> How is it possible to store binary data in PostgreSQL via DBI?  When I
> insert binary data (ex: Marhsal.dump({"uga"=>"booga"})) into the
> database, it escapes the data improperly.  I'm storing the data in a
> bytea column type.

Below is a fix for bytea values. It's in -current. 
Note that you still have to encode the bytea values, only decoding 
works automatically.

Regards,

  Michael




Index: Pg.rb
===================================================================
RCS file: /cvsroot/ruby-dbi/src/lib/dbd_pg/Pg.rb,v
retrieving revision 1.18
diff -c -r1.18 Pg.rb
*** Pg.rb	17 Apr 2002 13:38:38 -0000	1.18
--- Pg.rb	13 Jun 2002 15:43:52 -0000
***************
*** 8,14 ****
    module DBD
      module Pg
        
!       VERSION          = "0.2.1"
        USED_DBD_VERSION = "0.2"
        
        class Driver < DBI::BaseDriver
--- 8,14 ----
    module DBD
      module Pg
        
!       VERSION          = "0.3.0"
        USED_DBD_VERSION = "0.2"
        
        class Driver < DBI::BaseDriver
***************
*** 285,291 ****
  
  	def load_type_map
  	  @type_map = Hash.new
!           @coerce = DBI::SQL::BasicQuote::Coerce.new
  
  	  res = send_sql("SELECT typname, typelem FROM pg_type")
  
--- 285,291 ----
  
  	def load_type_map
  	  @type_map = Hash.new
!           @coerce = PgCoerce.new
  
  	  res = send_sql("SELECT typname, typelem FROM pg_type")
  
***************
*** 298,303 ****
--- 298,304 ----
  	    when '_float4','_float8'         then :as_float
              when '_timestamp'                then :as_timestamp
              when '_date'                     then :as_date
+             when '_bytea'                    then :as_bytea
              else                                  :as_str
  	    end
  	  }
***************
*** 358,363 ****
--- 359,379 ----
            raise DBI::DatabaseError.new(err.message) 
          end
  
+         #
+         # encodes a string as bytea value.
+         #
+         # for encoding rules see:
+         #   http://www.postgresql.org/idocs/index.php?datatype-binary.html
+         #
+         def __encode_bytea(str)
+           a = str.split(/\\/, -1).collect! {|s|
+             s.gsub!(/'/,    "\\\\047")  # '  => \\047 
+             s.gsub!(/\000/, "\\\\000")  # \0 => \\000  
+             s
+           }
+           a.join("\\\\")                # \  => \\
+         end
+ 
        end # Database
  
        ################################################################
***************
*** 478,484 ****
  	end
  
        end # Tuples
!       
      end # module Pg
    end # module DBD
  end # module DBI
--- 494,515 ----
  	end
  
        end # Tuples
! 
!       ################################################################
!       class PgCoerce < DBI::SQL::BasicQuote::Coerce
!         #
!         # for decoding rules see:
!         #   http://www.postgresql.org/idocs/index.php?datatype-binary.html
!         #
!         def as_bytea(str)
!           a = str.split(/\\\\/, -1).collect! {|s|
!             s.gsub!(/\\[0-7][0-7][0-7]/) {|o| o[1..-1].oct.chr}  #  \### => chr(###)
!             s
!           }
!           a.join("\\")  # \\ => \
!         end
!       end
! 
      end # module Pg
    end # module DBD
  end # module DBI