Re: DBI/OCI8 & binary data

In article <[e-mail]>,
KUBO Takehiro  <[e-mail]> wrote:
> I re-read my codes. I forgot many of them. :-)
> DBI::Binary is not support by DBD::OCI8.
> Please use binary data stored in a string.

Right.
 
> 'insert' with 'where' clause is not allowed.

You're right, brainfart on my side.
 
> >      sth.bind_param(2, image, type => OCI8::RAW)
> 
> Please change to
>        sth.bind_param(2, image, type => DBI::SQL_BINARY)

Hmmm, now it is failing with that:

/usr/lib/ruby/1.6/oci8.rb:300: `ORA-01461: can bind a LONG value only for insert into a LONG column' (OCIError)
        from /usr/lib/ruby/1.6/oci8.rb:300:in `exec'
        from /usr/lib/ruby/1.6/DBD/OCI8/OCI8.rb:121:in `execute'
        from /usr/lib/ruby/site_ruby/1.6/dbi/dbi.rb:743:in `execute'
        from insert-photo.rb:67:in `main'
        from insert-photo.rb:62:in `transaction'
        from insert-photo.rb:62:in `main'
        from insert-photo.rb:77

Putting DBI::SQL_BINARY, DBI::SQL_BLOB or nothing doesn't change the error
code.

> But I have not tested your code and my modified code yet. :-<
> I've just read and thought. I'll test them on next weekend.

Arigato Kubo-san.

-=-=-
#! /usr/bin/env ruby 
#
# $Id: //depot/caerdonn/roberto/src/ruby/eproject/test-ba.rb#3 $

# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
# Misc. VCS stuff
#
RCS_ID = %q$Id$
RCS_REV = (RCS_ID.split[1]).to_s.split(/#/)[1]
MYNAME = File.basename($0)

# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
# Non standard packages
#
require "oci8"
require "dbi"

# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
# XXX CONFIG XXX
#
DBD = "OCI8"
DB = "AMITEST"
USER = "oradmin"
PASS = "*********"

LOGO_PATH = "../"

# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
# Global variables
#
$dbh = nil

def connect_acb
  begin
    dbh = DBI.connect("dbi:#{DBD}:#{DB}", USER, PASS)
    $stderr.puts("Connect to #{DB}")
  rescue DBI::DatabaseError => err
    $stderr.puts("Error: #{err.errstr}")
    exit 1
  end
  return dbh
end

def main(argv)
  dbh = connect_acb
  mime_type = "image/jpeg"

  matricule = argv[0].to_s
  path = LOGO_PATH + argv[1].to_s
  begin
    image_raw = File.open(path).read
  rescue => msg
    $stderr.puts("Error: #{msg}")
    exit 1
  end

  req = <<-"EOR"
insert into prs_photo (c_matricule, mime_type, object) values (?,?,?)
  EOR

  begin
    dbh.transaction do
      sth = dbh.prepare(req)
      sth.bind_param(1, matricule)
      sth.bind_param(2, "image/jpeg")
      sth.bind_param(3, image_raw)      # or :type => DBI::SQL_BINARY
      sth.execute
    end
  rescue DBI::DatabaseError => err
    $stderr.puts "Error: #{err.err} #{err.errstr}"
    exit 1
  end
  return 0
end

if $0 == __FILE__ then
  exit(main(ARGV) || 1)
end
-=-=-
-- 
Ollivier ROBERT   -=- Eurocontrol EEC/ITM -=-   [e-mail]
Usenet Canal Historique                   FreeBSD: The Power to Serve!