Howdy folks, Has anyone had any luck getting Oracle''s XMLType to play nicely with Rails? According to the docs, it''s just a wrapper around CLOB. However the ruby-oci8 driver throws an exception about user defined types if you try to query a table with an XMLType column. Any pointers would be appreciated. Thanks, -wilig
I haven''t played with it but it would be cool to get it back as a REXML document. Tony On 3/6/06, William Groppe <wilig.ruby@gmail.com> wrote:> > Howdy folks, > > Has anyone had any luck getting Oracle''s XMLType to play nicely with > Rails? According to the docs, it''s just a wrapper around CLOB. > However the ruby-oci8 driver throws an exception about user defined > types if you try to query a table with an XMLType column. > > Any pointers would be appreciated. > > Thanks, > > -wilig > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060306/2054b0ea/attachment-0001.html
Tony Collen wrote:> I haven''t played with it but it would be cool to get it back as a REXML > document.Yes, that would be cool. :)> Tony > > On 3/6/06, *William Groppe* < wilig.ruby@gmail.com > <mailto:wilig.ruby@gmail.com>> wrote: > > Howdy folks, > > Has anyone had any luck getting Oracle''s XMLType to play nicely with > Rails? According to the docs, it''s just a wrapper around CLOB. > However the ruby-oci8 driver throws an exception about user defined > types if you try to query a table with an XMLType column.I suspect it''s because the XMLType datatype was added in Oracle 9i. I''m not sure if Kubo would be willing to support this or if we need to wait for ruby-oci9. :) I recommend posting a feature request on the ruby-oci8 project page. Regards, Dan
Hi,
XMLTYPE can be retrieved as CLOB as following:
--------------------------------------------------------------
require ''oci8''
def select_as_csv(conn, sqltext)
conn.exec(sqltext) do |row|
row.collect! do |col|
case col
when OCI8::LOB
col.read()
else
col
end
end
puts(row.join('',''))
end
end
conn = OCI8.new(''ruby'', ''oci8'')
#conn.exec("CREATE TABLE po_tab OF XMLTYPE")
#conn.exec("CREATE TABLE po_xml_tab(poid NUMBER(10), poDoc XMLTYPE)")
xml1 =
''<root><key>John</key><val>Smith</val></root>''
xml2 =
''<root><key>bar</key><val></val></root>''
conn.exec(''insert into po_tab values(:1)'', xml1)
conn.exec(''insert into po_tab values(:1)'', xml2)
select_as_csv(conn, ''select XMLTYPE.getClobVal(value(x)) from po_tab
x'')
conn.exec(''insert into po_xml_tab values(:1, :2)'', 1, xml1);
conn.exec(''insert into po_xml_tab values(:1, :2)'', 2, xml2);
select_as_csv(conn, ''select poid, XMLTYPE.getClobVal(podoc) from
po_xml_tab'')
conn.rollback
--------------------------------------------------------------
To insert a XML value, bind as a String.
To select a XML value, use XMLType.getClobVal(CLOBColumn).
I don''t know how to use it with Rails. :-<
I had not successed to use Rails with Oracle until last week, when
"Agile Web Develepment with Rails" Japanese edition was published.
> > I haven''t played with it but it would be cool to get it back
as a REXML
> > document.
>
> Yes, that would be cool. :)
Me too.
> I suspect it''s because the XMLType datatype was added in Oracle
9i. I''m not
> sure if Kubo would be willing to support this or if we need to wait for
> ruby-oci9. :)
User defined types, which are added in Oracle 8, have not been
supported yet. :-p
I have a will to support XML Type. But ruby-oci8 is progressing in a
very slow tempo. It won''t be supported before summer.
--
KUBO Takehiro
email: kubo@jiubao.org
web: http://www.jiubao.org
GnuPG fingerprint = 5F7B C8EF CA16 57D0 FDE1 9F47 C001 1F93 AC08 2262
Kubo, Thanks so much for your guidance. Works great. I also found the if you TRIM the column during the select the clob value will be returned as a string. I''m not sure if this will fail if the string is over 4096 characters though. Looking thru the 9i docs they claim to have removed this limit. Well either way, I now have a working solution. I will try my hand at writing a plugin for Rails that allows you to retrieve the value as a REXML document. I''m thinking of something along these lines class Product < ActiveRecord::Base as_xml :description end Need it for a project I''m working on, and it will be a good way to get to know ActiveRecord. -wilig On 3/7/06, KUBO Takehiro <kubo@jiubao.org> wrote:> Hi, > > XMLTYPE can be retrieved as CLOB as following: > -------------------------------------------------------------- > require ''oci8'' > > def select_as_csv(conn, sqltext) > conn.exec(sqltext) do |row| > row.collect! do |col| > case col > when OCI8::LOB > col.read() > else > col > end > end > puts(row.join('','')) > end > end > > conn = OCI8.new(''ruby'', ''oci8'') > > #conn.exec("CREATE TABLE po_tab OF XMLTYPE") > #conn.exec("CREATE TABLE po_xml_tab(poid NUMBER(10), poDoc XMLTYPE)") > > xml1 = ''<root><key>John</key><val>Smith</val></root>'' > xml2 = ''<root><key>bar</key><val></val></root>'' > > conn.exec(''insert into po_tab values(:1)'', xml1) > conn.exec(''insert into po_tab values(:1)'', xml2) > select_as_csv(conn, ''select XMLTYPE.getClobVal(value(x)) from po_tab x'') > > conn.exec(''insert into po_xml_tab values(:1, :2)'', 1, xml1); > conn.exec(''insert into po_xml_tab values(:1, :2)'', 2, xml2); > select_as_csv(conn, ''select poid, XMLTYPE.getClobVal(podoc) from po_xml_tab'') > > conn.rollback > -------------------------------------------------------------- > > To insert a XML value, bind as a String. > To select a XML value, use XMLType.getClobVal(CLOBColumn). > > I don''t know how to use it with Rails. :-< > I had not successed to use Rails with Oracle until last week, when > "Agile Web Develepment with Rails" Japanese edition was published. > > > > I haven''t played with it but it would be cool to get it back as a REXML > > > document. > > > > Yes, that would be cool. :) > > Me too. > > > I suspect it''s because the XMLType datatype was added in Oracle 9i. I''m not > > sure if Kubo would be willing to support this or if we need to wait for > > ruby-oci9. :) > > User defined types, which are added in Oracle 8, have not been > supported yet. :-p > > I have a will to support XML Type. But ruby-oci8 is progressing in a > very slow tempo. It won''t be supported before summer. > > -- > KUBO Takehiro > email: kubo@jiubao.org > web: http://www.jiubao.org > GnuPG fingerprint = 5F7B C8EF CA16 57D0 FDE1 9F47 C001 1F93 AC08 2262 > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >