Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   Java (http://www.velocityreviews.com/forums/f30-java.html)
-   -   Oracle - java - get xmlType col into resultset (http://www.velocityreviews.com/forums/t634143-oracle-java-get-xmltype-col-into-resultset.html)

Mike 09-07-2008 07:18 PM

Oracle - java - get xmlType col into resultset
 
Hi
I've already posted it in Oracle group but no answer. But I think the
problem is rather programming not the db.

I think I've read everything about Oracle-Java and i still didn't get
it right.
I am desperate.

I want something as simple as that:
I inserted some documents into this table:
CREATE TABLE xmlDocuments(docId NUMBER, xmlDocum XMLType);

Now i want to retrieve whole documents in my java app and parse it.
I tried lots of combinations.
First tried something that works in SQL2008 and DB2:
qString = "SELECT XMLDOCUM FROM XMLDOCUMENTS;";
rs = st.executeQuery(qString);

and rs has rows but if i getString(1)
while (rs.next())
{ ....
saxParser.parse(new InputSource(new StringReader(rs.getString(1))),
handler); //works with DB2 and MSSQL2008
i get a null pointer exception at saxParser

I system.out the rs.getString(1) instead of parsing it and it prints:
null

So i started to read about oracle and xml an came to this:
qString = "SELECT t.xmlDocum.getStringVal() FROM xmlDocuments t;"; //
gives me java.sql.SQLException: ORA-00911: invalid character
qString = "SELECT SYS_XMLGEN(XMLDOCUM) FROM XMLDOCUMENTS;" //no
results again


And if i use these queries in oracle command line tool:
SELECT t.xmlDocum.getStringVal() FROM xmlDocuments t;
SELECT XMLDOCUM FROM XMLDOCUMENTS;

SET LONG 10000;
SELECT XMLDOCUM.getStringVal() FROM XMLDOCUMENTS;

oracle gives me my documents. Connecting sys as sysdba from both java
and cmd.
i am using: oracle.jdbc.pool.OracleDataSource driver/connection:
OracleDataSource ds;
ds = new OracleDataSource();
ds.setURL(url);
conn = ds.getConnection(username, password);

connection works and I get other results:
SELECT count(*) from XMLDOCUMENTS;
i get 4 because 4 docs are in the table right now.

Please help me.
Mike

Sigfried 09-08-2008 01:31 PM

Re: Oracle - java - get xmlType col into resultset
 
Mike a écrit :
> Hi
> I've already posted it in Oracle group but no answer. But I think the
> problem is rather programming not the db.
>
> I think I've read everything about Oracle-Java and i still didn't get
> it right.
> I am desperate.
>
> I want something as simple as that:
> I inserted some documents into this table:
> CREATE TABLE xmlDocuments(docId NUMBER, xmlDocum XMLType);
>
> Now i want to retrieve whole documents in my java app and parse it.
> I tried lots of combinations.
> First tried something that works in SQL2008 and DB2:
> qString = "SELECT XMLDOCUM FROM XMLDOCUMENTS;";
> rs = st.executeQuery(qString);
>
> and rs has rows but if i getString(1)
> while (rs.next())
> { ....
> saxParser.parse(new InputSource(new StringReader(rs.getString(1))),
> handler); //works with DB2 and MSSQL2008
> i get a null pointer exception at saxParser
>
> I system.out the rs.getString(1) instead of parsing it and it prints:
> null



Please try the getCharacterStream() method.

Sigfried 09-08-2008 01:31 PM

Re: Oracle - java - get xmlType col into resultset
 
Mike a écrit :
> Hi
> I've already posted it in Oracle group but no answer. But I think the
> problem is rather programming not the db.
>
> I think I've read everything about Oracle-Java and i still didn't get
> it right.
> I am desperate.
>
> I want something as simple as that:
> I inserted some documents into this table:
> CREATE TABLE xmlDocuments(docId NUMBER, xmlDocum XMLType);
>
> Now i want to retrieve whole documents in my java app and parse it.
> I tried lots of combinations.
> First tried something that works in SQL2008 and DB2:
> qString = "SELECT XMLDOCUM FROM XMLDOCUMENTS;";
> rs = st.executeQuery(qString);
>
> and rs has rows but if i getString(1)
> while (rs.next())
> { ....
> saxParser.parse(new InputSource(new StringReader(rs.getString(1))),
> handler); //works with DB2 and MSSQL2008
> i get a null pointer exception at saxParser
>
> I system.out the rs.getString(1) instead of parsing it and it prints:
> null



Please try the getCharacterStream() method.

Tim Slattery 09-08-2008 02:27 PM

Re: Oracle - java - get xmlType col into resultset
 
Mike <darthvadertojabuahahahaha@gmail.com> wrote:

>Hi
>I've already posted it in Oracle group but no answer. But I think the
>problem is rather programming not the db.
>
>I think I've read everything about Oracle-Java and i still didn't get
>it right.
>I am desperate.
>
>I want something as simple as that:
>I inserted some documents into this table:
>CREATE TABLE xmlDocuments(docId NUMBER, xmlDocum XMLType);
>
>Now i want to retrieve whole documents in my java app and parse it.
>I tried lots of combinations.
>First tried something that works in SQL2008 and DB2:
>qString = "SELECT XMLDOCUM FROM XMLDOCUMENTS;";
>rs = st.executeQuery(qString);
>
>and rs has rows but if i getString(1)
>while (rs.next())
>{ ....
>saxParser.parse(new InputSource(new StringReader(rs.getString(1))),
>handler); //works with DB2 and MSSQL2008
>i get a null pointer exception at saxParser


This page might help:
http://www.oracle.com/technology/sam...ype/index.html

--
Tim Slattery
Slattery_T@bls.gov
http://members.cox.net/slatteryt

Lew 09-09-2008 08:18 PM

Re: Oracle - java - get xmlType col into resultset
 
Boris wrote:
> got it!!


Please do not discussion-post.

> i [sic] use this query:
>
> queryString = "select x." + column + ".getStringVal() from " + table +
> " x";


This way of building SQL queries is subject to SQL service attacks and
mistakes. It is better to use 'PreparedStatement' and set meats.

--
Lew


- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
"The biggest political joke in America is that we have a
liberal press.

It's a joke taken seriously by a surprisingly large number
of people... The myth of the liberal press has served as a
political weapon for conservative and right-wing forces eager
to discourage critical coverage of government and corporate
power ... Americans now have the worst of both worlds:
a press that, at best, parrots the pronouncements of the
powerful and, at worst, encourages people to be stupid with
pseudo-news that illuminates nothing but the bottom line."

-- Mark Hertzgaard


Mike 09-09-2008 09:57 PM

Re: Oracle - java - get xmlType col into resultset
 
On 8 Wrz, 16:27, Tim Slattery <Slatter...@bls.gov> wrote:
> Mike <darthvadertojabuahahah...@gmail.com> wrote:
> >Hi
> >I've already posted it in Oracle group but no answer. But I think the
> >problem is rather programming not the db.

>
> >I think I've read everything about Oracle-Java and i still didn't get
> >it right.
> >I am desperate.

>
> >I want something as simple as that:
> >I inserted some documents into this table:
> >CREATE TABLE xmlDocuments(docId NUMBER, xmlDocum XMLType);

>
> >Now i want to retrieve whole documents in my java app and parse it.
> >I tried lots of combinations.
> >First tried something that works in SQL2008 and DB2:
> >qString = "SELECT XMLDOCUM FROM XMLDOCUMENTS;";
> >rs = st.executeQuery(qString);

>
> >and rs has rows but if i getString(1)
> >while (rs.next())
> >{ ....
> >saxParser.parse(new InputSource(new StringReader(rs.getString(1))),
> >handler); //works with DB2 and MSSQL2008
> >i get a null pointer exception at saxParser

>
> This page might help:http://www.oracle.com/technology/sam...codesnippet/js...
>
> --
> Tim Slattery
> Slatter...@bls.govhttp://members.cox.net/slatteryt


Hello
Thank you for your help.
I tried both solutions and both of them didn't work:

1.
I get exception when using getCharacterStream:
java.sql.SQLException: Niepoprawny typ kolumny: getCharacterStream not
implemented for class

oracle.jdbc.driver.T4CNamedTypeAccessor

2.
and there is no method:
registerOutParameter (1, OracleTypes.OPAQUE,"SYS.XMLTYPE");
in new Oracle driver (no OracleCallableStatement)

any other ideas how to solve this problem?
Mike


Mike 09-09-2008 10:21 PM

Re: Oracle - java - get xmlType col into resultset
 
got it!!
i use this query:

queryString = "select x." + column + ".getStringVal() from " + table +
" x";

and then:

saxParser.parse(new InputSource(new StringReader(rs.getString(1))),
handler);
and rs.getString(1) gives me full xml document

once again than you for all your help
Mike


On 9 Wrz, 23:57, Mike <darthvadertojabuahahah...@gmail.com> wrote:
> On 8 Wrz, 16:27, Tim Slattery <Slatter...@bls.gov> wrote:
>
>
>
> > Mike <darthvadertojabuahahah...@gmail.com> wrote:
> > >Hi
> > >I've already posted it in Oracle group but no answer. But I think the
> > >problem is rather programming not the db.

>
> > >I think I've read everything about Oracle-Java and i still didn't get
> > >it right.
> > >I am desperate.

>
> > >I want something as simple as that:
> > >I inserted some documents into this table:
> > >CREATE TABLE xmlDocuments(docId NUMBER, xmlDocum XMLType);

>
> > >Now i want to retrieve whole documents in my java app and parse it.
> > >I tried lots of combinations.
> > >First tried something that works in SQL2008 and DB2:
> > >qString = "SELECT XMLDOCUM FROM XMLDOCUMENTS;";
> > >rs = st.executeQuery(qString);

>
> > >and rs has rows but if i getString(1)
> > >while (rs.next())
> > >{ ....
> > >saxParser.parse(new InputSource(new StringReader(rs.getString(1))),
> > >handler); //works with DB2 and MSSQL2008
> > >i get a null pointer exception at saxParser

>
> > This page might help:http://www.oracle.com/technology/sam...codesnippet/js...

>
> > --
> > Tim Slattery
> > Slatter...@bls.govhttp://members.cox.net/slatteryt

>
> Hello
> Thank you for your help.
> I tried both solutions and both of them didn't work:
>
> 1.
> I get exception when using getCharacterStream:
> java.sql.SQLException: Niepoprawny typ kolumny: getCharacterStream not
> implemented for class
>
> oracle.jdbc.driver.T4CNamedTypeAccessor
>
> 2.
> and there is no method:
> registerOutParameter (1, OracleTypes.OPAQUE,"SYS.XMLTYPE");
> in new Oracle driver (no OracleCallableStatement)
>
> any other ideas how to solve this problem?
> Mike



rajashekar.katla01@gmail.com 01-25-2013 07:50 PM

Re: Oracle - java - get xmlType col into resultset
 
On Tuesday, September 9, 2008 11:21:46 PM UTC+1, Mike wrote:
> got it!!
> i use this query:
>
> queryString = "select x." + column + ".getStringVal() from " + table +
> " x";
>
> and then:
>
> saxParser.parse(new InputSource(new StringReader(rs.getString(1))),
> handler);
> and rs.getString(1) gives me full xml document
>
> once again than you for all your help
> Mike
>
>
> On 9 Wrz, 23:57, Mike <darthvadertojabuahahah...@gmail.com> wrote:
> > On 8 Wrz, 16:27, Tim Slattery <Slatter...@bls.gov> wrote:
> >
> >
> >
> > > Mike <darthvadertojabuahahah...@gmail.com> wrote:
> > > >Hi
> > > >I've already posted it in Oracle group but no answer. But I think the
> > > >problem is rather programming not the db.

> >
> > > >I think I've read everything about Oracle-Java and i still didn't get
> > > >it right.
> > > >I am desperate.

> >
> > > >I want something as simple as that:
> > > >I inserted some documents into this table:
> > > >CREATE TABLE xmlDocuments(docId NUMBER, xmlDocum XMLType);

> >
> > > >Now i want to retrieve whole documents in my java app and parse it.
> > > >I tried lots of combinations.
> > > >First tried something that works in SQL2008 and DB2:
> > > >qString = "SELECT XMLDOCUM FROM XMLDOCUMENTS;";
> > > >rs = st.executeQuery(qString);

> >
> > > >and rs has rows but if i getString(1)
> > > >while (rs.next())
> > > >{ ....
> > > >saxParser.parse(new InputSource(new StringReader(rs.getString(1))),
> > > >handler); //works with DB2 and MSSQL2008
> > > >i get a null pointer exception at saxParser

> >
> > > This page might help:http://www.oracle.com/technology/sam...codesnippet/js...

> >
> > > --
> > > Tim Slattery
> > > Slatter...@bls.govhttp://members.cox.net/slatteryt

> >
> > Hello
> > Thank you for your help.
> > I tried both solutions and both of them didn't work:
> >
> > 1.
> > I get exception when using getCharacterStream:
> > java.sql.SQLException: Niepoprawny typ kolumny: getCharacterStream not
> > implemented for class
> >
> > oracle.jdbc.driver.T4CNamedTypeAccessor
> >
> > 2.
> > and there is no method:
> > registerOutParameter (1, OracleTypes.OPAQUE,"SYS.XMLTYPE");
> > in new Oracle driver (no OracleCallableStatement)
> >
> > any other ideas how to solve this problem?
> > Mike


Thank you so much for posting the solution here, it saved my time.
Take care...
-Raj


All times are GMT. The time now is 09:37 AM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.