Oracle XML

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

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) );

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

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
 
 

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();
}
%>

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

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();
}
%>

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)

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

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

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

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..:-)

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

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

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

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"));}

And this :

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

Is it usefull ?

Now I am getting : errorjava.sql.SQLException: Exhausted Resultset

Don't forget to restart the tomcat server...

:) 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.

Man It is ..........WWWWWOOOOOORKIIIIING THANKS A LOT.

Glad to hear that.....:-) and thanks for the "A"