I ran the do_postgres specs on my local Postgres 9.1 database, but didn't get any errors (as expected). I'm not testing against the same database that is causing me trouble, so it might be something to do with the custom type ImageT column type or the slightly older Postgres version - at this point I'm not sure.
I also tried some bytea escaping code<https://github.com/jruby/activerecord-jdbc-adapter/blob/master/lib/arjdbc/postgresql/adapter.rb#L598> I found in the ActiveRecord driver, but doesn't really work well from within DM. It successfully inserts, but Postgres seems to be actually storing the escaped string itself as raw data rather than interpreting it as binary before storing - when I query it back out the size is greatly inflated and I have to decode it before I can use it. If I go that route I might as well just use a VARCHAR and base64 encode/decode. I'm assuming that DM must do some extra escaping (quoting) of the prepared statement before sending the raw SQL to Postgres. So my updated question is, is there a way to get to the raw database connection (i.e. the JDBC connection context) from within DM that would let me do plain unquoted SQL queries? On Tuesday, September 11, 2012 9:13:51 AM UTC-5, Abe Voelker wrote: > > 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/-/pbr5DH2XEioJ. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/datamapper?hl=en.
