From: KUBO Takehiro <kubo@...>
Date: 2003-04-27T01:17:43+09:00
Subject: Re: DBI/OCI8 & binary data

Ollivier Robert <roberto@REMOVETHIS.eu.org> writes:

>> 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.

I ran your code. But there was no error in my environment. It is
almost same with your environment. I couldn't guess the reason.
But even though no error, the stored data was truncated (about 7800
bytes).

I've added BLOB locator support to Ruby/OCI8 and DBD::OCI8.
Please get from:
   http://www.jiubao.org/ruby-oci8/ruby-oci8-0.1.3-pre1.tar.gz

I'll put ruby-oci8-0.1.3.tar.gz in a few days.

>   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

To use BLOB support of Ruby/OCI8, modify above code as following.

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

  blob_sql = <<-"EOR"
select object from prs_photo where rowid = ?
  EOR

  begin
    dbh.transaction do
      # insert one row.
      sth = dbh.execute(req, matricule, "image/jpeg")
      # get the rowid of inserted row.
      rowid = sth.func(:rowid) # call driver specific code.
      # get a BLOB locator
      loc = dbh.select_one(blob_sql, rowid)[0] # 1st row, 1st column
      # write data to the locator.
      loc.write(image_raw)
    end
  rescue DBI::DatabaseError => err
    $stderr.puts "Error: #{err.err} #{err.errstr}"
    exit 1
  end
-------------------------------

To insert BLOB data:
  1st: insert 'EMPTY_BLOB()' to the BLOB column.
  2nd: get rowid of the inserted row.
  3rd: select the inserted BLOB column as a BLOB locator.
  4th: write data to the locator.

To update BLOB data:
  1st: select a BLOB column which you want to update as a BLOB locator.
  2rd: write data to the locator by OCI8::BLOB#write(data).
  3nd: fix the length of its content by OCI8::BLOB#truncate(data.size).
       If you forget to truncate the content and old data is longer
       than new one, garbage remains at the end.

To select BLOB data:
  1st: select a BLOB column as a BLOB locator.
  2nd: read its content by OCI8::BLOB#read

To delete BLOB data:
  just delete the row. :-p

Cheers
-- 
KUBO Takehiro