[DBI] How to write BLOBs in a Postgres DB?

ruby-talk
1

Are there any (working) examples of writing a BLOB into a
Postgres db via DBI?

2

Are there any (working) examples of writing a BLOB into a
Postgres db via DBI?

pigeon% cat b.rb
#!/usr/bin/ruby
require 'dbi'

DBI.connect('dbi:Pg:dbname=test', 'ts', '') do |dbd|
   dbd.do("drop table blob_test") rescue true
   dbd.do("create table blob_test (name VARCHAR(30), data OID)")
   data = File.readlines("cor1.jpg", nil)[0]
   puts "size : #{data.size}"
   dbd.do("insert into blob_test (name, data) VALUES (?,?)",
          "test", DBI::Binary.new(data))
   dbd.select_all("select name, data from blob_test") do |name, res|
      if dbd.func(:blob_read, res) == data
         puts "ok"
      end
   end
end
pigeon%

pigeon% b.rb
size : 4402656
ok
pigeon%

Guy Decoux

3

# ./b.rb
size : 5750
NOTICE: ROLLBACK: no transaction in progress
/usr/local/lib/ruby/site_ruby/1.6/DBD/Pg/Pg.rb:425:in `execute': can't open large object (DBI::ProgrammingError)

Well, apparently you have a problem with your installation. I use

postgresql-7.2.3 (compiled with default options)
ruby-postgres-0.7.0
ruby-dbi-all-0.0.18

Guy Decoux

4

Stefan Scholl wrote:

···

ts decoux@moulon.inra.fr wrote:

Are there any (working) examples of writing a BLOB into a
Postgres db via DBI?

pigeon% b.rb
size : 4402656
ok
pigeon%

./b.rb

size : 5750
NOTICE: ROLLBACK: no transaction in progress
/usr/local/lib/ruby/site_ruby/1.6/DBD/Pg/Pg.rb:425:in execute': can't open large object (DBI::ProgrammingError) from /usr/local/lib/ruby/site_ruby/1.6/dbi/dbi.rb:1005:in execute’
from /usr/local/lib/ruby/site_ruby/1.6/dbi/dbi.rb:1010:in do' from /usr/local/lib/ruby/site_ruby/1.6/dbi/dbi.rb:613:in do’
from ./b.rb:9
from ./b.rb:4:in connect' from /usr/local/lib/ruby/site_ruby/1.6/dbi/dbi.rb:367:in connect’
from ./b.rb:4

Update to 0.0.18!

0.0.17 had problems with some Postgres databases regarding transaction handling.

Regards,

Michael

5

./b.rb

size : 5750
NOTICE: ROLLBACK: no transaction in progress
/usr/local/lib/ruby/site_ruby/1.6/DBD/Pg/Pg.rb:425:in execute': can't open larg e object (DBI::ProgrammingError) from /usr/local/lib/ruby/site_ruby/1.6/dbi/dbi.rb:1005:in execute’
from /usr/local/lib/ruby/site_ruby/1.6/dbi/dbi.rb:1010:in do' from /usr/local/lib/ruby/site_ruby/1.6/dbi/dbi.rb:613:in do’
from ./b.rb:9
from ./b.rb:4:in connect' from /usr/local/lib/ruby/site_ruby/1.6/dbi/dbi.rb:367:in connect’
from ./b.rb:4

That’s with
Ruby/DBI 0.0.17
Ruby Postgres 0.7.0
Ruby 1.6.7
Postgres 7.2.3

···

ts decoux@moulon.inra.fr wrote:

Are there any (working) examples of writing a BLOB into a
Postgres db via DBI?

pigeon% b.rb
size : 4402656
ok
pigeon%

6

This isn’t mentioned in the Changelog.

OK, thanks. The test code works with 0.0.18. And after adding
transaction it remembers the BLOB after the connection is closed.

···

Michael Neumann uu9r@rz.uni-karlsruhe.de wrote:

Stefan Scholl wrote:

ts decoux@moulon.inra.fr wrote:

Are there any (working) examples of writing a BLOB into a
Postgres db via DBI?
pigeon% b.rb
size : 4402656
ok
pigeon%

./b.rb

size : 5750
NOTICE: ROLLBACK: no transaction in progress
/usr/local/lib/ruby/site_ruby/1.6/DBD/Pg/Pg.rb:425:in `execute’: can’t open large object (DBI::ProgrammingError)

Update to 0.0.18!
0.0.17 had problems with some Postgres databases regarding transaction handling.