Solved

Oracle XML

Posted on 2004-04-08
37
589 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.
0
Comment
Question by:fpoyavo
  • 19
  • 18
37 Comments
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10785433
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..
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 10785618
Would you do me a favor to make it as JSP. Please. I just would learn and see difference between these two.
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10785821
<%@ 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...






0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10785832
ooops
>>> transformer.transform(  new StreamSource(new StringReader(d)), new StreamResult(f) );

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

Author Comment

by:fpoyavo
ID: 10785834
You are the best  man I should say. Let me try that.
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 10785930
I have gotten message:

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

Where do I get this class ?
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10785964
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
 
 
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 10786219
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.



0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10786280
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();
}
%>

0
 
LVL 1

Author Comment

by:fpoyavo
ID: 10786399
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.
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10786437
Add this import to the jsp
<%@ page import="javax.xml.transform.* "%>

and then test it out
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 10786475
Yep right there :)

The only one left is :

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

Thank you.
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10786489
I think I changed it to....



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

Author Comment

by:fpoyavo
ID: 10786528
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)
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 10786555

More details : Incompatible type for constructor.
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10786566
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) );

0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10786576
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
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 10786657

oK.....iT seems doing something but there is no error meesages nor test.xml on my c: drive
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10786684

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...
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 10787120
Nothing at all ....just blank jsp after processing no error, no file ??? :) really getting strange
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10787150
Post your jsp here, also what web server you are using???
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 10787215
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();
}
 

%>
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10787264
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..:-)
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 10788089
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 ?
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10788227
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...
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 10788647
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.
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10790940
<%@ 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..
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10790954
ooopsss..
remove that myfile.close(); its incorrect..

rest of it is good...
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 10791541
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
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 10791583
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"));}


0
 
LVL 1

Author Comment

by:fpoyavo
ID: 10791599
And this :

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

Is it usefull ?
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 10791648
Now I am getting : errorjava.sql.SQLException: Exhausted Resultset
0
 
LVL 19

Accepted Solution

by:
Kuldeepchaturvedi earned 500 total points
ID: 10792210
Okay in the class RS2DOM ( the one we downloaded and compiled...


this is the code in method resultSet2DOM...
>>while (rs.isLast() == false) {
>>rs.next();

change it to

while(rs.next()){
>>rest of the method...

keep rest of it same as our old jsp that we are testing with..
compile this class back again... it should work... ( I assume we have the method resultSet2XSD() still commented)
                  
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10792413
Don't forget to restart the tomcat server...
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 10792653
:) 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.
0
 
LVL 1

Author Comment

by:fpoyavo
ID: 10792772
Man It is ..........WWWWWOOOOOORKIIIIING THANKS A LOT.
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 10792804
Glad to hear that.....:-) and thanks for the "A"
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In this article, I show you step by step with screenshots to assist you - HOW TO: Deploy and Install the VMware vCenter Server Appliance 6.5 (VCSA 6.5), with some helpful tips along the way.
In this article, I will show you HOW TO: Perform a Physical to Virtual (P2V) Conversion the easy way from a computer backup (image).
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now