I am unable to insert binary values into a Postgres bytea column using the
JDBC version of do_postgres (I'm using JRuby 1.6.7.2 in 1.9 mode). I have
an InterviewForm model that I am trying to create records for, which has a
formImage column that is a bytea (it's actually a custom Postgres type
"ImageT" that wraps bytea). This is the model definition:
class InterviewForm
include DataMapper::Resource
storage_names[:default] = 'InterviewForm'
property :interview_id, String, :field => 'interviewId', :key => true
property :form_seq_no, Integer, :field => 'formSeqNo', :key => true
property :file_name, String, :field => 'fileName'
property :size, Integer
property :sha_256_hash, String, :field => 'sha256Hash', :length => 64
property :form_image, Binary, :field => 'formImage'
end
I tried using the usual DataMapper InterviewForm.create method:
require 'open-uri'
blob_string = open('http://www.adobe.com/misc/pdfs/sssheep_chptr3.pdf'){|f|
f.read}
InterviewForm.create(:interview_id => 1,
:form_seq_no => 1,
:file_name => "foo.pdf"
:size => blob_string.size,
:sha_256_hash
=> Digest::SHA256.new.hexdigest(blob_string),
:form_image => ::Extlib::ByteArray.new(blob_string))
that initially gave me a validation error - "Form image must be at most 50
characters long," so I added :length => 2000000000 and :lazy => trueproperties
to the
:form_image property. After that, I got the following error for the same
statement:
DataObjects::SQLError: ERROR: column "formImage" is of type "ImageT" but
expression is of type character varying
Hint: You will need to rewrite or cast the expression.
I tried all kinds of variations of wrapping the :form_image value in
::Extlib::ByteArray but have not been able to get around this error.
Therefore, I decided to drop down into a raw prepared statement:
require 'open-uri'
blob_string = open('http://www.adobe.com/misc/pdfs/sssheep_chptr3.pdf'){|f|
f.read}
InterviewForm.repository.adapter.execute('INSERT INTO "InterviewForm"
("interviewId", "formSeqNo", "fileName", "size", "sha256Hash", "formImage")
VALUES(?, ?, ?, ?, ?, ?)',
1,
1,
"foo.pdf",
blob_string.size,
Digest::SHA256.new.hexdigest(blob_string),
::Extlib::ByteArray.new(blob_string))
However this also errors out. The error is:
DataObjects::SQLError: ERROR: invalid input syntax for type bytea (code: 0,
sql state: 22P02, query: INSERT INTO "InterviewForm" ("interviewId",
"formSeqNo", "fileName", "size", "sha256Hash", "formImage") VALUES('1',
'1', 'foo.pdf', '869245',
'34abd7142491c988bd15515ee74ab5b0ef426994477363508c820634a6edc962', '<bunch
of binary data>'), uri: )
This error leads me to believe that prepared statements for ByteArrays are
not being escaped as I can replicate the above error by just trying to
insert a single \ character:
InterviewForm.repository.adapter.execute('INSERT INTO "InterviewForm"
("interviewId", "formSeqNo", "fileName", "size", "sha256Hash", "formImage")
VALUES(?, ?, ?, ?, ?, ?)',
1,
1,
"foo.pdf",
blob_string.size,
Digest::SHA256.new.hexdigest(blob_string),
::Extlib::ByteArray.new("\\"))
If I use the plain prepared statements, am I responsible for escaping the
data on my own? What is the easiest way to do that - try and find the
right method from the JDBC driver itself? Postgres has some vendor-specific
escaping<http://www.postgresql.org/docs/9.0/static/datatype-binary.html#AEN5047>
for
bytea columns so I'd rather not have to re-invent it myself if possible.
My Gemfile looks like this:
# Postgres databases
gem "pg", "~> 0.14.0", :platforms => :ruby
gem "jdbc-postgres", "~> 9.1.901", :platforms => :jruby
# Use pre-release (1.3.0.beta) versions of DM due to multi_json dependency
issue
gem "data_mapper"
gem "dm-postgres-adapter", :git =>
'git://github.com/datamapper/dm-postgres-adapter.git'
gem "dm-do-adapter", :git =>
'git://github.com/datamapper/dm-do-adapter.git'
gem "dm-core", :git => 'git://github.com/datamapper/dm-core.git'
gem "dm-aggregates", :git =>
'git://github.com/datamapper/dm-aggregates.git'
gem "dm-migrations", :git =>
'git://github.com/datamapper/dm-migrations.git'
gem "dm-transactions", :git =>
'git://github.com/datamapper/dm-transactions.git'
gem "dm-serializer", :git =>
'git://github.com/datamapper/dm-serializer.git'
gem "dm-timestamps", :git =>
'git://github.com/datamapper/dm-timestamps.git'
gem "dm-validations", :git =>
'git://github.com/datamapper/dm-validations.git'
gem "dm-types", :git =>
'git://github.com/datamapper/dm-types.git'
Thanks in advance for any assistance.
--
You received this message because you are subscribed to the Google Groups
"DataMapper" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/datamapper/-/PVpGsuFK8IIJ.
To post to this group, send email to datamapper@googlegroups.com.
To unsubscribe from this group, send email to
datamapper+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/datamapper?hl=en.
