[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 605
  • Last Modified:

Oracle XML

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
fpoyavo
Asked:
fpoyavo
  • 19
  • 18
1 Solution
 
KuldeepchaturvediCommented:
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
 
fpoyavoAuthor Commented:
Would you do me a favor to make it as JSP. Please. I just would learn and see difference between these two.
0
 
KuldeepchaturvediCommented:
<%@ 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

will be
 transformer.transform(  new StreamSource(d), new StreamResult(f) );
0
 
fpoyavoAuthor Commented:
You are the best  man I should say. Let me try that.
0
 
fpoyavoAuthor Commented:
I have gotten message:

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

Where do I get this class ?
0
 
KuldeepchaturvediCommented:
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
 
fpoyavoAuthor 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.



0
 
KuldeepchaturvediCommented:
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
 
fpoyavoAuthor 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.
0
 
KuldeepchaturvediCommented:
Add this import to the jsp
<%@ page import="javax.xml.transform.* "%>

and then test it out
0
 
fpoyavoAuthor Commented:
Yep right there :)

The only one left is :

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

Thank you.
0
 
KuldeepchaturvediCommented:
I think I changed it to....



catch(Exception e)
{
e.printStackTrace();
}
%>
0
 
fpoyavoAuthor 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)
0
 
fpoyavoAuthor Commented:

More details : Incompatible type for constructor.
0
 
KuldeepchaturvediCommented:
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
 
KuldeepchaturvediCommented:
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
 
fpoyavoAuthor Commented:

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

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
 
fpoyavoAuthor Commented:
Nothing at all ....just blank jsp after processing no error, no file ??? :) really getting strange
0
 
KuldeepchaturvediCommented:
Post your jsp here, also what web server you are using???
0
 
fpoyavoAuthor 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();
}
 

%>
0
 
KuldeepchaturvediCommented:
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
 
fpoyavoAuthor 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 ?
0
 
KuldeepchaturvediCommented:
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
 
fpoyavoAuthor 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.
0
 
KuldeepchaturvediCommented:
<%@ 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
 
KuldeepchaturvediCommented:
ooopsss..
remove that myfile.close(); its incorrect..

rest of it is good...
0
 
fpoyavoAuthor 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
0
 
fpoyavoAuthor 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"));}


0
 
fpoyavoAuthor Commented:
And this :

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

Is it usefull ?
0
 
fpoyavoAuthor Commented:
Now I am getting : errorjava.sql.SQLException: Exhausted Resultset
0
 
KuldeepchaturvediCommented:
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
 
KuldeepchaturvediCommented:
Don't forget to restart the tomcat server...
0
 
fpoyavoAuthor 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.
0
 
fpoyavoAuthor Commented:
Man It is ..........WWWWWOOOOOORKIIIIING THANKS A LOT.
0
 
KuldeepchaturvediCommented:
Glad to hear that.....:-) and thanks for the "A"
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 19
  • 18
Tackle projects and never again get stuck behind a technical roadblock.
Join Now