empty_clob() problem

[lydianblues]

With Rails (2.3.4), ruby-oci8 (2.0.3),
activerecord-oracle_enhanced-adapter (1.2.2),
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
(full install on Mac OS X, 10.6.1), client and server on the same
machine using TNS listener.

I find that "clob" functionality doesn't work:

Using this migration:
create_table :product_families, :force => true do |t|
t.text :description
end

Then in the console do:
pf = ProductFamily.new
pf.description = "XXXXXXXXX"
pf.save!

-- this works with no error, but the database statement that is
generated
-- inserts empty_clob() for the value of the pf.description field. It
looks like
-- function call, not a string in the INSERT statement because it's
not quoted.

This is pretty basic to be broken...

[Raimonds Simanovskis]

ActiveRecord generates SQL statements without bind variables therefore
you can insert strings just up to 4000 characters (limitation of
literal size in Oracle). Therefore oracle_enhanced adapter saves CLOB/
BLOB data (which could be larger than 4000 characters) using separate
callback. As a result two SQL statements will be generated - initial
INSERT which will insert empty_clob() and then additional UPDATE which
will set CLOB/BLOB value.

So if you in console after pf.save! will do

pf.reload # requery record from database
puts pf.description

Then you should see that CLOB data were saved.

Raimonds

[Michael Schmitz]

Ok thanks!

On another note, isn't it a major problem that ActiveRecord doesn't use bind variables?
I suppose since BV's are specific to Oracle, nothing will ever happen to fix this.  Unless
ActiveRecord factors out its SQL-generation layer so it can become database specific.
This makes sense for a lot  of reasons... 

Michael

[Raimonds Simanovskis]

Currently ActiveRecord does not support bind variables and generates
SQL statements with bind variables included as constants.
See http://groups.google.com/group/oracle-enhanced/browse_thread/thread/90d1bfbbc02397b5
about how oracle_enhanced adapter uses cursor_sharing session option
to improve performance of such SQL statements on Oracle database.

Currently ActiveRecord SQL generation is being significantly
refactored using Arel (http://github.com/rails/arel) library and it
will be used in Rails 3.0.
At the moment this has broken Oracle compatibility but now it will be
easier to do alternative SQL generation implementations and I hope
that it will be possible to use bind variables separately from SQL
statements in Rails 3.0. So hopefully sometime next year it will be
better :)

Raimonds