Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle XML

Posted on 2004-04-08
37
Medium Priority
?
603 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Are you looking for the options available for exporting EDB files to PST? You may be confused as they are different in different Exchange versions. Here, I will discuss some options available.
Tech spooks aren't just for those who are tech savvy, it also happens to those of us running a business. Check out the top tech spooks for business owners.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

618 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