We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Oracle XML

fpoyavo
fpoyavo asked
on
Medium Priority
657 Views
Last Modified: 2008-01-16
Hi Experts,

I have JSP that retrieves data from table and I would save it as XML file.

Is there any way to do that ?

Thank you.
Comment
Watch Question

You can surely do that...
Have a look at
http://www.jeckle.de/freeStuff/JDBC2XML/

They have got the code there too...
That code is written in Servlet style but its should not be very hard have it in jsp..

Author

Commented:
Would you do me a favor to make it as JSP. Please. I just would learn and see difference between these two.
<%@ page import="java.sql.* "%>
<%@ page import="javax.xml.* "%>
<%@ page import="org.w3c.dom.* "%>
<%@ page import="de.jeckle.RS2DOM.RS2DOM "%>
<%
//Connect to the Oracle Database, you will have to download the Oracle JDBC driver for this.....
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
      Connection con = DriverManager.getConnection( "jdbc:oracle:thin:@test123.server.com:1111:apptest",                "abc",
                "123");

stmt = (Statement) con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM test");
            //transfer ResultSet into XML Schema stored as DOM
                  Document xsd = RS2DOM.ResultSet2XSDDOM(rs);
                  //transfer ResultSet into DOM
                  Document d = RS2DOM.ResultSet2DOM(rs);

// You have the XML loaded in Document object d now... you can do whatever you want to do with it...
// here in this example I am writing it to a file on your harddisk..
transformer = tFactory.newTransformer();
       transformer.setOutputProperty( OutputKeys.INDENT, "yes" );
       transformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "2" );
     File f=new File(c:\\test.xml");
             transformer.transform(  new StreamSource(new StringReader(d)), new StreamResult(f) );
}
catch(Exception e)
{
e.printStaceTrace();
}
%>

Here you will have to make sure that you have a Oracle JDBC driver on your webserver and you have downloaded the class from the above url that I have posted...






ooops
>>> transformer.transform(  new StreamSource(new StringReader(d)), new StreamResult(f) );

will be
 transformer.transform(  new StreamSource(d), new StreamResult(f) );

Author

Commented:
You are the best  man I should say. Let me try that.

Author

Commented:
I have gotten message:

Class de.jeckle.RS2DOM.RS2DOM not found in import.

Where do I get this class ?
http://www.jeckle.de/freeStuff/JDBC2XML/

This page have a download there...
download the java file and compile it...
it should be placed in directory structure

web-inf/classes/de/jeckle/RS2DOM/RS2DOM
 
 

Author

Commented:
Another problem:

Class org.apache.jsp.Transformer not found.

Undefined variable or class name: tFactory

tFactory.newTransformer();


Class org.apache.jsp.File not found.
                     File f=new File("c:\\test.xml");

Method printStaceTrace() not found in class java.lang.Exception.
                e.printStaceTrace();

Man please help.



I have modifed the jsp for you.... all were my tayping mistakes...:-)
If you still get the error>>Class org.apache.jsp.Transformer not found
then you do not have XML parser in your system ( though its quite unlikely)..

so give it a shot and let me know...

<%@ page import="java.sql.* "%>
<%@ page import="javax.xml.* "%>
<%@ page import="org.w3c.dom.* "%>
<%@ page import="java.io.* "%>
<%@ page import="de.jeckle.RS2DOM.RS2DOM "%>
<%
//Connect to the Oracle Database, you will have to download the Oracle JDBC driver for this.....
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
      Connection con = DriverManager.getConnection( "jdbc:oracle:thin:@test123.server.com:1111:apptest",                "abc",
                "123");

stmt = (Statement) con.createStatement();
          ResultSet rs = stmt.executeQuery("SELECT * FROM test");
          //transfer ResultSet into XML Schema stored as DOM
               Document xsd = RS2DOM.ResultSet2XSDDOM(rs);
               //transfer ResultSet into DOM
               Document d = RS2DOM.ResultSet2DOM(rs);

// You have the XML loaded in Document object d now... you can do whatever you want to do with it...
// here in this example I am writing it to a file on your harddisk..
TransformerFactory tFactory = TransformerFactory.newInstance();
Transformer transformer;
transformer = tFactory.newTransformer();
       transformer.setOutputProperty( OutputKeys.INDENT, "yes" );
       transformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "2" );
     File f=new File(c:\\test.xml");
            transformer.transform(  new StreamSource(new StringReader(d)), new StreamResult(f) );
}
catch(Exception e)
{
e.printStackTrace();
}
%>

Author

Commented:
I get this now :

Class org.apache.jsp.TransformerFactory not found.
                TransformerFactory tFactory = TransformerFactory.newInstance();

We have XML installed on this server.  I ran another JSP having XML processing and it went fine.

Thank you.
Add this import to the jsp
<%@ page import="javax.xml.transform.* "%>

and then test it out

Author

Commented:
Yep right there :)

The only one left is :

Method printStaceTrace() not found in class java.lang.Exception.
                e.printStaceTrace();

Thank you.
I think I changed it to....



catch(Exception e)
{
e.printStackTrace();
}
%>

Author

Commented:
Man we are close :)

Can't convert org.w3c.dom.Document to java.lang.String.
                       transformer.transform(  new StreamSource(new StringReader(d)), new StreamResult(f) );

pointing to (d)

Author

Commented:

More details : Incompatible type for constructor.
try changing this line to

 transformer.transform(  new StreamSource(d), new StreamResult(f) );
if this does not work try

 transformer.transform(  new DOMSource(d), new StreamResult(f) );

if both does not work then try

Can't convert org.w3c.dom.Document to java.lang.String.
                       transformer.transform(  new StreamSource(new StringReader(org.apache.soap.util.xml.DOMWriter.nodeToString(d))), new StreamResult(f) );


This one is bound to work

Author

Commented:

oK.....iT seems doing something but there is no error meesages nor test.xml on my c: drive

do
 System.out.println(org.apache.soap.util.xml.DOMWriter.nodeToString(d));

just before the transform and see if it prints anything on the tomcat console...

Author

Commented:
Nothing at all ....just blank jsp after processing no error, no file ??? :) really getting strange
Post your jsp here, also what web server you are using???

Author

Commented:
It is RED HAT 8, TOMCAT

<%@ page import="java.sql.* "%>
<%@ page import="java.io.* "%>
<%@ page import="javax.xml.transform.*"%>
<%@ page import="javax.xml.transform.stream.*"%>
<%@ page import="org.w3c.dom.* "%>
<%@ page import="java.text.*" %>
<%@ page import="java.util.*" %>
<%@ page import="de.jeckle.RS2DOM.RS2DOM"%>
<%
 

 
Connection con = null;

try
{
   Class.forName("oracle.jdbc.driver.OracleDriver");

      con = DriverManager.getConnection(
                "jdbc:oracle:thin:@192.101.201.20:1521:ORATEST",
                "TEST",
                "TEST");

        Statement stmt = con.createStatement();

           
        ResultSet rs = stmt.executeQuery("select * from test;");

           
               Document xsd = RS2DOM.ResultSet2XSDDOM(rs);
           
               Document docout = RS2DOM.ResultSet2DOM(rs);

 
       TransformerFactory tfactory = TransformerFactory.newInstance();
       Transformer transformer = null;

       transformer = tfactory.newTransformer();
       transformer.setOutputProperty( OutputKeys.INDENT, "yes" );
       transformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "2" );
       

       File myfile = new File("c:\\temp\\test.xml");
     

       System.out.println(org.apache.soap.util.xml.DOMWriter.nodeToString(docout));
       transformer.transform(  new StreamSource(new StringReader(org.apache.soap.util.xml.DOMWriter.nodeToString(docout))), new StreamResult(myfile) );

       


 
}
catch(Exception e)
{
    e.printStackTrace();
}
 

%>
1. Do you have a table with Name "test" in your database?
2. does it have data in it?
3. On red HAT C:\\temp\\Test.xml not gonna work.... we will have to change it /root/Test.xml
4. try commening the Document xsd = RS2DOM.ResultSet2XSDDOM(rs); line.. we are not doing anything with it anyway...
5. If it still runs silently and does not report any errors then I will be dammned..:-)

Author

Commented:
Hi, :)

1 yes I use it for test
2 yes over 10000 rows but I was trying to get more specific data by using where as well and it works fine in another jsp without xml ofcourse
3 I did that too
4 commented out
5 believe it or not .... nothing again.

May be it has to do with

transformer.setOutputProperty( OutputKeys.INDENT, "yes" );
       transformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "2" );
       
What these parameters do ?
those will come in to picture only after it executes the transformer.transform() method..
and I am doing a System.out.println() just before that...
it is bound to put a log or a console msg on the tomcat......

Are you sure your tomcat is showing nothing on the console????????
Or may be putting it in a log file some where????

I am done for the day so try looking for the server logs or server out put of your tomcat... we shd have some thing....
and we will take it from there tomorrow...

Author

Commented:
In Tomcat logs I have found

004-04-09 01:13:24 jsp: init
2004-04-09 01:15:05 jsp: init
2004-04-09 01:16:11 jsp: init
2004-04-09 01:18:01 jsp: init
2004-04-09 01:21:46 jsp: init

Nothing else.
<%@ page import="java.sql.* "%>
<%@ page import="java.io.* "%>
<%@ page import="javax.xml.transform.*"%>
<%@ page import="javax.xml.transform.stream.*"%>
<%@ page import="org.w3c.dom.* "%>
<%@ page import="java.text.*" %>
<%@ page import="java.util.*" %>
<%@ page import="de.jeckle.RS2DOM.RS2DOM"%>
<%
 

 
Connection con = null;

try
{
   Class.forName("oracle.jdbc.driver.OracleDriver");

      con = DriverManager.getConnection(
                "jdbc:oracle:thin:@192.101.201.20:1521:ORATEST",
                "TEST",
                "TEST");

        Statement stmt = con.createStatement();

           
        ResultSet rs = stmt.executeQuery("select * from test;");

           
               Document xsd = RS2DOM.ResultSet2XSDDOM(rs);
           
               Document docout = RS2DOM.ResultSet2DOM(rs);

 
       TransformerFactory tfactory = TransformerFactory.newInstance();
       Transformer transformer = null;

       transformer = tfactory.newTransformer();
       transformer.setOutputProperty( OutputKeys.INDENT, "yes" );
       transformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "2" );
       

       File myfile = new File("c:\\temp\\test.xml");
      myfile.close();

       out.write(org.apache.soap.util.xml.DOMWriter.nodeToString(docout));
       transformer.transform(  new StreamSource(new StringReader(org.apache.soap.util.xml.DOMWriter.nodeToString(docout))), new StreamResult(myfile) );

       


 
}
catch(Exception e)
{
    e.printStackTrace();
   out.write(e);
}
 

%>

try this one..
ooopsss..
remove that myfile.close(); its incorrect..

rest of it is good...

Author

Commented:
I did some of yours and my modification and here is the error message :


errorjava.sql.SQLException: Invalid operation for forward only resultset : isLast

:) but we are close

Author

Commented:
I have found it on Net ...may be this is it


Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);java.sql.ResultSet rs = stmt.executeQuery("SELECT TIME,TT FROM TEST");// it will be scrollable, will not show changes made by others,// and will be updatableint count = 0;while (rs.next()){++count;System.out.println ("TestJavaResultSet: Record #"+ count +" " + rs.getString("TIME")); }count = 0;rs.beforeFirst();while (rs.next()){++count;System.out.println ("TestJavaResultSet: Record #" + count + " " + rs.getString("TT"));}


Author

Commented:
And this :

stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );

Is it usefull ?

Author

Commented:
Now I am getting : errorjava.sql.SQLException: Exhausted Resultset
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Don't forget to restart the tomcat server...

Author

Commented:
:) I have good news ..it started writing file but inside not that good.

This is what I have got inside ..just a couple of lines and no data

<?xml version="1.0" ?>
  <result xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />


That's it.

Author

Commented:
Man It is ..........WWWWWOOOOOORKIIIIING THANKS A LOT.
Glad to hear that.....:-) and thanks for the "A"
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.