Oracle - java - get xmlType col into resultset

Discussion in 'Java' started by Mike, Sep 7, 2008.

  1. Mike

    Mike Guest

    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
    Mike, Sep 7, 2008
    #1
    1. Advertising

  2. Mike

    Sigfried Guest

    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, Sep 8, 2008
    #2
    1. Advertising

  3. Mike

    Sigfried Guest

    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, Sep 8, 2008
    #3
  4. Mike

    Tim Slattery Guest

    Mike <> 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/sample_code/tech/java/codesnippet/jsp/xmltype/index.html

    --
    Tim Slattery

    http://members.cox.net/slatteryt
    Tim Slattery, Sep 8, 2008
    #4
  5. Mike

    Lew Guest

    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
    Lew, Sep 9, 2008
    #5
  6. Mike

    Mike Guest

    On 8 Wrz, 16:27, Tim Slattery <> wrote:
    > Mike <> 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/sample_code/tech/java/codesnippet/js...
    >
    > --
    > Tim Slattery
    > ://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, Sep 9, 2008
    #6
  7. Mike

    Mike Guest

    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 <> wrote:
    > On 8 Wrz, 16:27, Tim Slattery <> wrote:
    >
    >
    >
    > > Mike <> 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/sample_code/tech/java/codesnippet/js...

    >
    > > --
    > > Tim Slattery
    > > ://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, Sep 9, 2008
    #7
  8. Mike

    Guest

    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 <> wrote:
    > > On 8 Wrz, 16:27, Tim Slattery <> wrote:
    > >
    > >
    > >
    > > > Mike <> 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/sample_code/tech/java/codesnippet/js...

    > >
    > > > --
    > > > Tim Slattery
    > > > ://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
    , Jan 25, 2013
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Abhinav
    Replies:
    2
    Views:
    412
    Abhinav
    Jun 25, 2004
  2. Stryder
    Replies:
    2
    Views:
    1,630
    Stryder
    Apr 16, 2009
  3. Stryder
    Replies:
    3
    Views:
    3,310
    Stryder
    Apr 29, 2009
  4. Feyruz
    Replies:
    4
    Views:
    2,124
    Sherm Pendley
    Oct 14, 2005
  5. Replies:
    8
    Views:
    200
    Eric Sosman
    Apr 20, 2013
Loading...

Share This Page