Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

how to send retreived XML data from a Java application into Oracle DB

Hi,

I am retreiving data from an Oracle DB in XML format from my Java application, now I want to send this data into an other database. Can somebody tell me how to do this, all contributions will be appreciatted.

My code :-

import java.sql.*;
import java.math.*;
import oracle.xml.sql.query.*;
import oracle.jdbc.*;
import oracle.jdbc.driver.*;
import java.io.*;



public class xmlretTest {
   
    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
       
    //String tableName = "emp1";
    Connection conn;
   
    try
    {
     DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
                     
     conn = DriverManager.getConnection (".......");
                       
     OracleXMLQuery qry = new OracleXMLQuery(conn, "select * from emp1");
     
     String sx = qry.getXMLString();
     System.out.println(sx);
   
                     try
   
                     {
 
                     File temp = File.createTempFile("pattern", ".suffix");
                     BufferedWriter out = new BufferedWriter(new FileWriter(temp));

                     out.write(sx);
                     out.close();
                     Runtime.getRuntime().exec("rundll32 SHELL32.DLL,ShellExec_RunDLL " + temp.getAbsolutePath());

                     }
                     
                     catch (IOException e) {
                     System.out.println(e);
                     
                                  }
    }
   
  catch (SQLException e)
              {
                 e.printStackTrace();      
              
                     System.out.println(e);
                                          
              }
   }
}
0
vihar123
Asked:
vihar123
  • 40
  • 37
  • 27
  • +1
3 Solutions
 
girionisCommented:
Read it up as a String and then insert it using an INSERT statement. Something like:


BufferedReader in = new BufferedReader(new FileReader("<your file>"));
String line = null;
StringBuffer sb = new StringBuffer();
while ((line = in.readLine()) != null)
{
   sb.append(line);
}

Statement st = conn.createStatement();
st.execute("INSERT INTO mytable " + sb.toString());
0
 
vihar123Author Commented:
>>>st.execute("INSERT INTO mytable " + sb.toString());

shld i create mytable manually in my database or it created by itself?
0
 
CEHJCommented:
>>now I want to send this data into an other database.

What database, and what is the table name and column name?
0
Technology Partners: 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!

 
zzynxSoftware engineerCommented:
>> shld i create mytable manually in my database or it created by itself?
An insert statement certainly does NOT create a table, that's for sure.
0
 
vihar123Author Commented:
>>>What database, and what is the table name and column name?

Oracle db, if u see my code above, I am retreiving an entire table into an xml format and this xml i want to send to an other Oracle db, in other words insert this xml into a new table.
0
 
zzynxSoftware engineerCommented:
>> now I want to send this data into an other database
Then you probably can't use the current connection.
You'll need another connection for the other database
0
 
CEHJCommented:
Well  i don't know the Oracle-specific code, but you'd probably have to CREATE TABLE first with some sort of xml option
0
 
girionisCommented:
> shld i create mytable manually in my database or it created by itself?

If the other database does not already have it then yes, you will need to create it.
0
 
vihar123Author Commented:
ok i have created a table in the db with same configs, so now do i need to save this temp somewhere and then send it into db, cant i just send it after retreiving?
well what i want to achieve is - to show this retreived xml file and send it into the db when the user clicks button.

-----------------------------------------------------------------
its giving me this exception----

>>>java.io.FileNotFoundException: temp



 String line;
                                        try
                                {
                         
                               BufferedReader in = new BufferedReader(new FileReader("temp"));
                                           line = null;
                                           StringBuffer sb = new StringBuffer();
                                           while ((line = in.readLine()) != null)
                                                      {
                                                   sb.append(line);
                                                      }

                                                      Statement st = conn.createStatement();
                                                      st.execute("INSERT INTO mytable " + sb.toString());
                                        }
                     
                              catch (IOException e) {
                                        System.out.println(e);
                     
                                                                      }
    }
0
 
CEHJCommented:
Why would you be using a file at all? Your reading the result set and sending, on a new connection, the new data
0
 
zzynxSoftware engineerCommented:
>>its giving me this exception----

     >>>>>java.io.FileNotFoundException: temp

Where's the line

         File temp = File.createTempFile("pattern", ".suffix");

?
0
 
zzynxSoftware engineerCommented:
>> Why would you be using a file at all?
to show this retreived xml file and send it into the db when the user clicks button
0
 
CEHJCommented:
No. You should be probably using the OracleXMLSave class to put the data into the other table
0
 
zzynxSoftware engineerCommented:
BTW. I think the insert statement needs the keyword "values" too: http://www.w3schools.com/sql/sql_insert.asp
0
 
girionisCommented:
Vihar, if you do not save the xml data into a file then you do not need to read from a file. Do something like that:

String sx = qry.getXMLString();
Statement st = conn.createStatement();
st.execute("INSERT INTO mytable VALUES('" + sx + "')");
0
 
zzynxSoftware engineerCommented:
>> You should be probably using the OracleXMLSave class to put the data into the other table
Back at the start, vihar ;°)
0
 
CEHJCommented:
>>Back at the start, vihar ;°)

Why? The OracleXMLSave class has only just been mentioned
0
 
CEHJCommented:
Something like:

OracleXMLSave saver = OracleXMLSave(saveConnection, saveTableName);
saver.insertXML(sx);
saver.close();
0
 
zzynxSoftware engineerCommented:
>> Why? The OracleXMLSave class has only just been mentioned
Hey CEHJ, no offense meant.
I know the history of this Q. And that was just what he tried in the beginning.
0
 
CEHJCommented:
>>And that was just what he tried in the beginning.

Well you must be referring to previous questions then. Of course, if has only just been determined in this current one that another table is needed to receive the data, it's no wonder OracleXMLSave didn't work before
0
 
vihar123Author Commented:
its giving me some error as :- java.sql.SQLException: ORA-00936: missing expression at this line

>>>st.execute("INSERT INTO Employee VALUES(," + sx + ",");

what does this error mean?

down is my table structure -----

CREATE TABLE employee (
  emp_name  VARCHAR2(20) NULL,
  emp_no    NUMBER(4,0)  NOT NULL,
  emp_desig VARCHAR2(15) NULL,
  emp_dept  VARCHAR2(15) NOT NULL,
  emp_sal   NUMBER(10,0) NOT NULL,
  emp_comm  NUMBER(10,0) NULL,
  emp_loc   CHAR(2)      DEFAULT 'fe' NULL,
  emp_id    NUMBER(4,0)  NULL
)
  PCTUSED    0
/
0
 
girionisCommented:
> what does this error mean?

It means that the final comma is not necessary. It should be:

st.execute("INSERT INTO Employee VALUES('" + sx + "'");

But even the above wouldn't work since you need to either insert the data in all the fields or specify the column to use:

st.execute("INSERT INTO Employee (myField) VALUES('" + sx + "'");

The other option you have is to break the xml fiel down and insert each tag in a separate table field.
0
 
vihar123Author Commented:
>>>The other option you have is to break the xml fiel down and insert each tag in a separate table field.

is there any option through which, i can insert the whole table????
0
 
CEHJCommented:
>>is there any option through which, i can insert the whole table????

You need another xml-like table probably with the same schema
0
 
CEHJCommented:
... and then you could use the code i posted above
0
 
girionisCommented:
Create a table with only one field. Then insert the whole XML in there.
0
 
CEHJCommented:
The following article shows that the syntax is exactly as i mentioned earlier:

http://www.xml.com/pub/a/2001/06/20/databases.html
0
 
vihar123Author Commented:
>>>Create a table with only one field. Then insert the whole XML in there.

tried it but the same error is coming
0
 
girionisCommented:
This is because you have the commas you do no tneed. Get rid of them and use the statement i posted earlier:

st.execute("INSERT INTO Employee (myField) VALUES('" + sx + "'");
0
 
girionisCommented:
st.execute("INSERT INTO Employee (myField) VALUES('" + sx + "')");
0
 
vihar123Author Commented:
hi cehj

tried this thing before, but it was giving me some clob error (java.lang.NoClassDefFoundError: oracle/jdbc2/Clob) , even after making the schema and also reinstalling xdk and classes12, so i thought better try an other way

>>>Something like:

OracleXMLSave saver = OracleXMLSave(saveConnection, saveTableName);
saver.insertXML(sx);
saver.close();

0
 
CEHJCommented:
>>tried this thing before, but it was giving me some clob error

The thing to do is to sort this problem out first, or you're just reinventing the wheel. Did you post a previous q on this? If so, please post the link to it.
0
 
vihar123Author Commented:
when im doing that its giving me this error that comma fails

>>>java.sql.SQLException: ORA-00917: missing coma

try
   
                     {
 
                     File temp = File.createTempFile("pattern", ".suffix");
                     BufferedWriter out = new BufferedWriter(new FileWriter(temp));

                     out.write(sx);
                     out.close();
                     Runtime.getRuntime().exec("rundll32 SHELL32.DLL,ShellExec_RunDLL " + temp.getAbsolutePath());
                              //String sx = qry.getXMLString();
                              Statement st = conn.createStatement();
                  st.execute("INSERT INTO employeetest emp_name VALUES('" + sx + "')");

                     }
0
 
vihar123Author Commented:
>>>The thing to do is to sort this problem out first, or you're just reinventing the wheel. Did you post a previous q on this? If so, please post the link to it.

well i did try my best to solve it, but i cant waste my time like this, getting result is more important, well the link is

http://www.experts-exchange.com/Programming/Programming_Languages/Java/Q_21130008.html
0
 
zzynxSoftware engineerCommented:
Make that

     st.execute("INSERT INTO employeetest (emp_name) VALUES('" + sx + "')");
0
 
girionisCommented:
Try this:

st.execute("INSERT INTO employeetest (emp_name) VALUES('" + sx + "')");
0
 
vihar123Author Commented:
>>>st.execute("INSERT INTO employeetest (emp_name) VALUES('" + sx + "')");

same error again :-(
0
 
girionisCommented:
What is the output of this:

System.out.println(sx);

DOes it contain any ' characters?
0
 
girionisCommented:
If yes then use a PreparedStatement.
0
 
zzynxSoftware engineerCommented:
Try

String query = "INSERT INTO employeetest (emp_name) VALUES(?)"
PreparedStatement ps = connection.prepareStatement( query ) ;
ps.setString( 1, sx) ;
0
 
girionisCommented:
PreparedStatement ps = conn.prepareStatement("INSERT INTO employeetest (emp_name) VALUES(?)");
ps.setString(1, sx);
ps.executeUpdate();
0
 
CEHJCommented:
As far as your other problem is concerned, you just need to make sure your Oracle environment is up to date and all dependencies are resolvable. Quite apart from the current problem, if this is not the case, then you're in any case dealing with a non-optimal environment
0
 
vihar123Author Commented:
What is the output of this:
>>>System.out.println(sx);

<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <EMP_NAME>Zdena</EMP_NAME>
      <EMP_NO>1015</EMP_NO>
      <EMP_DESIG>Project Manager</EMP_DESIG>
      <EMP_DEPT>EDPT</EMP_DEPT>
      <EMP_SAL>10000</EMP_SAL>
      <EMP_COMM>70</EMP_COMM>
      <EMP_LOC>fe</EMP_LOC>
   </ROW>
   <ROW num="2">
      <EMP_NAME>John</EMP_NAME>
      <EMP_NO>1001</EMP_NO>
      <EMP_DESIG>Manager</EMP_DESIG>
      <EMP_DEPT>Finance 1</EMP_DEPT>
      <EMP_SAL>5000</EMP_SAL>
      <EMP_COMM>50</EMP_COMM>
      <EMP_LOC>fe</EMP_LOC>
   </ROW>
   <ROW num="3">
      <EMP_NAME>Christian</EMP_NAME>
      <EMP_NO>1002</EMP_NO>
      <EMP_DESIG>Manager</EMP_DESIG>
      <EMP_DEPT>Personal 1</EMP_DEPT>
      <EMP_SAL>7000</EMP_SAL>
      <EMP_COMM>20</EMP_COMM>
      <EMP_LOC>fe</EMP_LOC>
   </ROW>
   <ROW num="4">
      <EMP_NAME>Markus</EMP_NAME>
      <EMP_NO>1003</EMP_NO>
      <EMP_DESIG>DBA</EMP_DESIG>
      <EMP_DEPT>Sales 1</EMP_DEPT>
      <EMP_SAL>4000</EMP_SAL>
      <EMP_COMM>34</EMP_COMM>
      <EMP_LOC>fe</EMP_LOC>
   </ROW>
   <ROW num="5">
      <EMP_NAME>Kajin</EMP_NAME>
      <EMP_NO>1004</EMP_NO>
      <EMP_DESIG>Distributor</EMP_DESIG>
      <EMP_DEPT>Dist 1</EMP_DEPT>
      <EMP_SAL>5000</EMP_SAL>
      <EMP_COMM>25</EMP_COMM>
      <EMP_LOC>fe</EMP_LOC>
   </ROW>
   <ROW num="6">
      <EMP_NAME>Stephen</EMP_NAME>
      <EMP_NO>1005</EMP_NO>
      <EMP_DESIG>HW Manager</EMP_DESIG>
      <EMP_DEPT>Hardware 1</EMP_DEPT>
      <EMP_SAL>8000</EMP_SAL>
      <EMP_COMM>25</EMP_COMM>
      <EMP_LOC>fe</EMP_LOC>
   </ROW>
   <ROW num="7">
      <EMP_NAME>Muller</EMP_NAME>
      <EMP_NO>1006</EMP_NO>
      <EMP_DESIG>HW Specilaist</EMP_DESIG>
      <EMP_DEPT>Hardware 2</EMP_DEPT>
      <EMP_SAL>2500</EMP_SAL>
      <EMP_COMM>25</EMP_COMM>
      <EMP_LOC>fe</EMP_LOC>
   </ROW>
   <ROW num="8">
      <EMP_NAME>Arun</EMP_NAME>
      <EMP_NO>1007</EMP_NO>
      <EMP_DESIG>Programmer</EMP_DESIG>
      <EMP_DEPT>Software 1</EMP_DEPT>
      <EMP_SAL>4000</EMP_SAL>
      <EMP_COMM>24</EMP_COMM>
      <EMP_LOC>fe</EMP_LOC>
   </ROW>
   <ROW num="9">
      <EMP_NAME>Meng</EMP_NAME>
      <EMP_NO>1017</EMP_NO>
      <EMP_DESIG>Trainee</EMP_DESIG>
      <EMP_DEPT>Const</EMP_DEPT>
      <EMP_SAL>4000</EMP_SAL>
      <EMP_COMM>50</EMP_COMM>
      <EMP_LOC>fe</EMP_LOC>
   </ROW>
   <ROW num="10">
      <EMP_NAME>Jia uei</EMP_NAME>
      <EMP_NO>1011</EMP_NO>
      <EMP_DESIG>Trainee#</EMP_DESIG>
      <EMP_DEPT>Construction</EMP_DEPT>
      <EMP_SAL>4000</EMP_SAL>
      <EMP_COMM>50</EMP_COMM>
      <EMP_LOC>se</EMP_LOC>
   </ROW>
   <ROW num="11">
      <EMP_NAME>Effah</EMP_NAME>
      <EMP_NO>1012</EMP_NO>
      <EMP_DESIG>Researcher</EMP_DESIG>
      <EMP_DEPT>Research</EMP_DEPT>
      <EMP_SAL>5000</EMP_SAL>
      <EMP_COMM>20</EMP_COMM>
      <EMP_LOC>AF</EMP_LOC>
   </ROW>
   <ROW num="12">
      <EMP_NAME>Johannes</EMP_NAME>
      <EMP_NO>1014</EMP_NO>
      <EMP_DESIG>Technician</EMP_DESIG>
      <EMP_DEPT>TE</EMP_DEPT>
      <EMP_SAL>4500</EMP_SAL>
      <EMP_LOC>LA</EMP_LOC>
   </ROW>
</ROWSET>
 
java.sql.SQLException: ORA-00917: Missing Coma
0
 
girionisCommented:
> What is the output of this:
> >>>System.out.println(sx);

> <?xml version = '1.0'?>


It is the ' in thw above lien that probably causes the error. use the PreparedStatement example I posted.
0
 
vihar123Author Commented:
new error:-

java.sql.SQLException: ORA-01401: inserted value too large for column
0
 
girionisCommented:
What type is the column?
0
 
girionisCommented:
Actually the type is irrelevant. What is its length?
0
 
vihar123Author Commented:
>>>What type is the column

CREATE TABLE employeetest2 (
  emp_name VARCHAR2(20)
)

i tried something like
CREATE TABLE employeetest1 (
  emp_name XMLTYPE
)

its not giving any error, but when im executing table its showing some error like this
ORA-24374: definition not sucfessful
0
 
zzynxSoftware engineerCommented:
>>CREATE TABLE employeetest2 (
>>  emp_name VARCHAR2(20)
>>)

No surprise it gives
>> java.sql.SQLException: ORA-01401: inserted value too large for column
is it?

If you're sure the maximum length < 4000 you could try:

CREATE TABLE employeetest2 (
    emp_name VARCHAR2(4000)
)
0
 
girionisCommented:
Ôñõ ôçéó¨

CREATE TABLE employeetest2 (
  emp_name VARCHAR2(2048)
)
0
 
girionisCommented:
> Ôñõ ôçéó¨

should be

Try this

Wrong encoding.
0
 
zzynxSoftware engineerCommented:
But if you don't know anything about the maximum length of the XML file,
I would propose to use CLOB which can store up to 4GB of data
0
 
vihar123Author Commented:
its working but its just inserting the whole xml docu into the field, i would like to have only the values from this xml docu to be inserted into the table, for eg:- emp_name - then 'zdena' etc.....actually i want to achive something like the whole table is being filled from the whole xml documents, can anybody help me in doing this please... thanks
0
 
girionisCommented:
You will have to parse the XML and extract the data you want:

http://javaalmanac.com/cgi-bin/search/find.pl?words=dom
0
 
vihar123Author Commented:
>>>You will have to parse the XML and extract the data you want:

in this case, i think i first need to save this xml file and then parse it into dom and then send it into db?

if so, i dont want to save this file, actually i just want to show this file as temp and then at the click of the button, this retreived xml is transferred, how to do this, can you tell me in detail?
thanks
0
 
vihar123Author Commented:
hello cehj,

>>>As far as your other problem is concerned, you just need to make sure your Oracle environment is up to date and all dependencies are resolvable. Quite apart from the current problem, if this is not the case, then you're in any case dealing with a non-optimal environment

>>>up to date and all dependencies are resolvable.
>>>dealing with a non-optimal environment

I didnt understand the above things, how to go about it, can u explain me in detail
what all i need to set to make my environment acceptable....
0
 
girionisCommented:
> in this case, i think i first need to save this xml file and then parse
> it into dom and then send it into db?

No you do not need to save it, you can do it by having it in memory.

0
 
girionisCommented:
> if so, i dont want to save this file, actually i just want to show this
> file as temp and then at the click of the button, this retreived xml
> is transferred, how to do this, can you tell me in detail?

Take a look at the link I posted, they have examples of how you can do it.
0
 
girionisCommented:
> I didnt understand the above things, how to go about it, can u explain
> me in detail
> what all i need to set to make my environment acceptable....

From my understanding all you have to do is to have the relevant jar file in your classpath.
0
 
vihar123Author Commented:
>>>From my understanding all you have to do is to have the relevant jar file in your classpath.

im having jar file, classes12.jar, ojdbc14.jar etc, i set path but still the same error
java.lang.NoClassDefFoundError: oracle/jdbc2/Clob


0
 
girionisCommented:
> java.lang.NoClassDefFoundError: oracle/jdbc2/Clob

Where is theis file? In which jar file? Maybe it's better to continue this question to the other link you posted.
0
 
vihar123Author Commented:
>>>Where is theis file? In which jar file? Maybe it's better to continue this question to the other link you posted.

its in the classes12.jar

C:\j2sdk1.4.2_03\jre\lib\classes12\oracle\sql\CLOB.class

C:\j2sdk1.4.2_03\jre\lib\classes12\oracle\sql\OracleJdbc2SQLInput.class

0
 
girionisCommented:
No, I mean the oracle.jdbc2.Clob class since the error you are getting referes to:

(java.lang.NoClassDefFoundError: oracle/jdbc2/Clob)

which is different than the oracle/sql/CLOB class.
0
 
girionisCommented:
The other thing you can do is to use the oracel/sql/CLOB class instead, if they are the same.
0
 
vihar123Author Commented:
well i cant fine them


>>>The other thing you can do is to use the oracel/sql/CLOB class instead, if they are the same.

how to use them?
0
 
girionisCommented:
> how to use them?

Make sure they are the same first. Then post the code where you use the Clob class.
0
 
girionisCommented:
> well i cant fine them

How do you compile your class? If you are able to compile it then you should be able to run it, if you do not laod the class dynamically.
0
 
vihar123Author Commented:

>>>>Make sure they are the same first. Then post the code where you use the Clob class.

how to find out if they are same, i read in some docu that ---the Oracle javadoc seems to 'randomly' refer to oracle.jdbc2.Clob and oracle.sql.CLOB. I've looked through the driver classes and cannot find an oracle.jdbc2.Clob. I'm assuming that it is an old reference which hasn't been fixed in the javadoc. The class you want is oracle.sql.CLOB. Note that it is 'CLOB' and not 'Clob' -- all uppercase.



import java.sql.*;
import java.math.*;
import oracle.xml.sql.query.*;
import oracle.jdbc.*;
import oracle.jdbc.driver.*;
import oracle.xml.sql.dml.*;
import oracle.xml.sql.*;
import java.net.*;
import oracle.xml.parser.v2.*;
import java.io.*;
import oracle.xml.sql.dml.OracleXMLSave;

public class testxml2
{
    public static void main(String args[]) throws SQLException
    {
       
        Connection conn;
       
         
       
                         try
                                {
                              DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
                     
                        conn = DriverManager.getConnection ("");
                       
                              OracleXMLQuery qry = new OracleXMLQuery(conn, "select * from emp1");
                              System.out.println( "Java version =" + System.getProperty("java.version") );
                                               System.out.println( "Class path = " + System.getProperty("java.class.path") );

                              System.out.println("Hi I'm here!!!");
                              String sx = qry.getXMLString();
                              System.out.println("hi");
                              
                                
                                
                              String tableName = "employee";
                              String fileName = "emps.xml";
                              try {
                                  OracleXMLSave save = new OracleXMLSave(conn, tableName);
                                  InputStream is = new FileInputStream(fileName);
                         int rowCount = save.insertXML(is);
                         is.close();
                                                       
                                          
                                                      }
                           catch (IOException e) {System.out.println(e);
                                      }

                              
                                }
                             catch (SQLException e)
                                         {
                                          e.printStackTrace();      
                                      
                                          System.out.println(e);
                                          
                                                  }
       
    }
}      
                  
0
 
zzynxSoftware engineerCommented:
According to http://zuse.esnig.cifom.ch/database/doc_oracle/Oracle901_Linux/java.901/a90210/03_pub.htm
oracle.jdbc2.Clob is the JDK 1.1.x version of oracle.sql.CLOB

The point is that vihar doesn't use it directly.
It's the use of OracleXMLQuery & OracleXMLSave that apparently seems to bring in the use of the Clob class.
0
 
vihar123Author Commented:
well this was the problem, using these objects brings in the necessity of  oracle/jdbc2/Clob, so i thought i shld do it other way, but even this parsing seems to be hard.

can somebody give me an example of parsing my retreived xml and sending into database, in links im unable to understand. i want to know the steps, how to go about it.
0
 
vihar123Author Commented:
can somebody give me a parsing example relating to my requirement????? plzzzz urgent
0
 
girionisCommented:
0
 
vihar123Author Commented:
>>>Have a look here: http://javaalmanac.com/egs/org.w3c.dom/WalkDom.html

in that example, we need to give file name, but im not saving the file anywhere, so what shld i give here

Document doc = parseXmlFile("infilename.xml", true);
0
 
girionisCommented:
You can use a DocumentBuilder:

DocumentBuilder builder = factory.newDocumentBuilder();
Document document = builder.parse(new InputSource(new StringReader(sx)));

You will also need to import javax.xml.parsers.*;
0
 
girionisCommented:
"factory" variable is a DocumetnBuilderFactory:

DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
0
 
CEHJCommented:
Check your Oracle Java docs for what should be installed in terms of libraries in order to use the Oracle-specific classes you need.
0
 
vihar123Author Commented:
well its giving me an exception (oracle.xml.parser.v2.XMLParseException: Start of root element expected.)

where am i going wrong in my code?????
---------------------------------------------------------------------
import java.io.*;
import javax.xml.parsers.*;
import org.w3c.dom.*;
import org.xml.sax.*;
import java.sql.*;
import java.math.*;
import oracle.xml.sql.query.*;
import oracle.jdbc.*;
import oracle.jdbc.driver.*;
import java.io.*;
import javax.xml.parsers.*;
   
    public class xmlretTestdom {
        public static void main(String[] args) {
            Document doc = parseXmlFile("", false);
            Connection conn;
            String sx = null;
   
    try
    {
     DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
                     
     conn = DriverManager.getConnection ("");
                       
     OracleXMLQuery qry = new OracleXMLQuery(conn, "select * from emp1");
     
     sx = qry.getXMLString();
    }
          catch (SQLException e)
              {
                 e.printStackTrace();      
              
                     System.out.println(e);
                                          
              }
              
                        try
   
                     {
 
                     File temp = File.createTempFile("pattern", ".suffix");
                     BufferedWriter out = new BufferedWriter(new FileWriter(temp));

                     out.write(sx);
                     out.close();
                     }
                
                     catch (IOException e) {
                     System.out.println(e);
                     
                                  }
   
   
 
   }
        public static void visit(Node node, int level) {
        NodeList list = node.getChildNodes();
        for (int i=0; i<list.getLength(); i++) {
       
            Node childNode = list.item(i);
       
            visit(childNode, level+1);
        }
   
            }
       
        public static Document parseXmlFile(String sx, boolean validating) {

            try {
                DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
                factory.setValidating(validating);
   
          DocumentBuilder builder = factory.newDocumentBuilder();
      Document doc = builder.parse(new InputSource(new StringReader(sx)));
                        
      visit(doc, 0);
               System.out.println(doc);
                return doc;
               
            } catch (SAXException e) {
                System.out.println(e);

            } catch (ParserConfigurationException e) {
                System.out.println(e);

            } catch (IOException e) {
                System.out.println(e);

            }
           
           
           
           
            return null;
           
           
        }
    }
   
       
   
0
 
girionisCommented:
Which line?
0
 
CEHJCommented:
btw, in trying to 'evade' the problem, you are actually increasing complexity, which will potentially cause you even more trouble. Even if you manage eventually to put all those data in one column, in order to do anything with it later, you'll be forced to perform xml library routines
0
 
vihar123Author Commented:
>>>>Which line?

its not showing any line number, but its this below one that is catching this exception

catch (SAXException e) {
              System.out.println(e);
0
 
vihar123Author Commented:
>>>btw, in trying to 'evade' the problem, you are actually increasing complexity, which will potentially cause you even more trouble. Even if you manage eventually to put all those data in one column, in order to do anything with it later, you'll be forced to perform xml library routines

i didnt get you, could you please explain me in detail, im sure experience counts so ur advise will be real helpful :)

 well actually im able to put all data into one column but i want to achieve something else, ie putting values in their respectable columns.
with xdk package, the problem is internally its calling for oracle/java2/clob class which, is presetly changed to sql class, but the package is not yet changed i think.
so right now im trying to insert my retreived xml into a table in their respective colums
0
 
girionisCommented:
I think there is an error in the XML file against the dtd file. Can you check it in an XML editor?
0
 
girionisCommented:
>with xdk package, the problem is internally its calling for oracle/java2/clob class which, is presetly changed to sql class, but
>the package is not yet changed i think.

The easiest way would be to look at the Oracle docs and see what classes you should be using.
0
 
CEHJCommented:
>>i didnt get you

I assume you mean the last point. If you save the xml as a String in one column, every time you want to do something with it, you're going to have to create a dom, which is obviously not a good idea.


Oracle are not going to release a set of classes that don't work. You'll just have to find out what libraries you need from the docs. Get the simplest possible example using the classes you need working
0
 
vihar123Author Commented:
>>>>The easiest way would be to look at the Oracle docs and see what classes you should be using

well i have searched for but couldnt fine what i wanted, so as i said i shld go on trying another way....but please if you find some docs please send me....
0
 
CEHJCommented:
What version of Oracle are you using?
0
 
vihar123Author Commented:
>>>What version of Oracle are you using?

Oracle 6.0 - developer version
Oralce 9i R2 - Client version

Well ill try to explain what exactly i want to achieve :-

I am developing a Java application, I have put JTree on left and fields on right, JTree retreives data from particular table colums and displays them in nodes. For eg:- name node, salary node, commission node.
So here when the user clicks a particular node, lets say salary node, and then clicks transfer button, then I want to retreive some data in an XML format and send it to an other table. So I will make a same schema as that of the retreived table and want to insert this xml data into the new table, in their respective columns.

So this is what I have been trying to achieve, hope it give a good idea. :-)
0
 
girionisCommented:
I think if you combine all the posts together you will find the answer you want.
0
 
vihar123Author Commented:
>>I think if you combine all the posts together you will find the answer you want

Ok i think im quite near to my goal, but i have one question, Im having Oracle 9i r2 as only client version, i cannot develop anything with it, so is it a problem or still i can achieve what i want to????
0
 
girionisCommented:
>Ok i think im quite near to my goal, but i have one question, Im having Oracle 9i r2 as only client version, i cannot develop
>anything with it, so is it a problem or still i can achieve what i want to????

I am not sure how the license works but if you cannot use the oracle classes for developing you will have to use the default java classes, as mentioned in the above examples.
0
 
CEHJCommented:
I don't know about the versions either.
0
 
vihar123Author Commented:
>>>I think there is an error in the XML file against the dtd file. Can you check it in an XML editor?

i dont find any error with this xml, what does it mean with this exception
oracle.xml.parser.v2.XMLParseException: Start of root element expected.


<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <EMP_NAME>Zdena</EMP_NAME>
      <EMP_NO>1015</EMP_NO>
      <EMP_DESIG>Project Manager</EMP_DESIG>
      <EMP_DEPT>EDPT</EMP_DEPT>
      <EMP_SAL>10000</EMP_SAL>
      <EMP_COMM>70</EMP_COMM>
      <EMP_LOC>fe</EMP_LOC>
   </ROW>
   <ROW num="2">
      <EMP_NAME>John</EMP_NAME>
      <EMP_NO>1001</EMP_NO>
      <EMP_DESIG>Manager</EMP_DESIG>
      <EMP_DEPT>Finance 1</EMP_DEPT>
      <EMP_SAL>5000</EMP_SAL>
      <EMP_COMM>50</EMP_COMM>
      <EMP_LOC>fe</EMP_LOC>
   </ROW>
   <ROW num="3">
      <EMP_NAME>Christian</EMP_NAME>
      <EMP_NO>1002</EMP_NO>
      <EMP_DESIG>Manager</EMP_DESIG>
      <EMP_DEPT>Personal 1</EMP_DEPT>
      <EMP_SAL>7000</EMP_SAL>
      <EMP_COMM>20</EMP_COMM>
      <EMP_LOC>fe</EMP_LOC>
   </ROW>
   <ROW num="4">
      <EMP_NAME>Markus</EMP_NAME>
      <EMP_NO>1003</EMP_NO>
      <EMP_DESIG>DBA</EMP_DESIG>
      <EMP_DEPT>Sales 1</EMP_DEPT>
      <EMP_SAL>4000</EMP_SAL>
      <EMP_COMM>34</EMP_COMM>
      <EMP_LOC>fe</EMP_LOC>
   </ROW>
   <ROW num="5">
      <EMP_NAME>Kajin</EMP_NAME>
      <EMP_NO>1004</EMP_NO>
      <EMP_DESIG>Distributor</EMP_DESIG>
      <EMP_DEPT>Dist 1</EMP_DEPT>
      <EMP_SAL>5000</EMP_SAL>
      <EMP_COMM>25</EMP_COMM>
      <EMP_LOC>fe</EMP_LOC>
   </ROW>
   <ROW num="6">
      <EMP_NAME>Stephen</EMP_NAME>
      <EMP_NO>1005</EMP_NO>
      <EMP_DESIG>HW Manager</EMP_DESIG>
      <EMP_DEPT>Hardware 1</EMP_DEPT>
      <EMP_SAL>8000</EMP_SAL>
      <EMP_COMM>25</EMP_COMM>
      <EMP_LOC>fe</EMP_LOC>
   </ROW>
   <ROW num="7">
      <EMP_NAME>Muller</EMP_NAME>
      <EMP_NO>1006</EMP_NO>
      <EMP_DESIG>HW Specilaist</EMP_DESIG>
      <EMP_DEPT>Hardware 2</EMP_DEPT>
      <EMP_SAL>2500</EMP_SAL>
      <EMP_COMM>25</EMP_COMM>
      <EMP_LOC>fe</EMP_LOC>
   </ROW>
   <ROW num="8">
      <EMP_NAME>Arun</EMP_NAME>
      <EMP_NO>1007</EMP_NO>
      <EMP_DESIG>Programmer</EMP_DESIG>
      <EMP_DEPT>Software 1</EMP_DEPT>
      <EMP_SAL>4000</EMP_SAL>
      <EMP_COMM>24</EMP_COMM>
      <EMP_LOC>fe</EMP_LOC>
   </ROW>
   <ROW num="9">
      <EMP_NAME>Meng</EMP_NAME>
      <EMP_NO>1017</EMP_NO>
      <EMP_DESIG>Trainee</EMP_DESIG>
      <EMP_DEPT>Const</EMP_DEPT>
      <EMP_SAL>4000</EMP_SAL>
      <EMP_COMM>50</EMP_COMM>
      <EMP_LOC>fe</EMP_LOC>
   </ROW>
   <ROW num="10">
      <EMP_NAME>Jia uei</EMP_NAME>
      <EMP_NO>1011</EMP_NO>
      <EMP_DESIG>Trainee#</EMP_DESIG>
      <EMP_DEPT>Construction</EMP_DEPT>
      <EMP_SAL>4000</EMP_SAL>
      <EMP_COMM>50</EMP_COMM>
      <EMP_LOC>se</EMP_LOC>
   </ROW>
   <ROW num="11">
      <EMP_NAME>Effah</EMP_NAME>
      <EMP_NO>1012</EMP_NO>
      <EMP_DESIG>Researcher</EMP_DESIG>
      <EMP_DEPT>Research</EMP_DEPT>
      <EMP_SAL>5000</EMP_SAL>
      <EMP_COMM>20</EMP_COMM>
      <EMP_LOC>AF</EMP_LOC>
   </ROW>
   <ROW num="12">
      <EMP_NAME>Johannes</EMP_NAME>
      <EMP_NO>1014</EMP_NO>
      <EMP_DESIG>Technician</EMP_DESIG>
      <EMP_DEPT>TE</EMP_DEPT>
      <EMP_SAL>4500</EMP_SAL>
      <EMP_LOC>LA</EMP_LOC>
   </ROW>
</ROWSET>
0
 
girionisCommented:
Does it validate fine against a DTD?
0
 
CEHJCommented:
It might be *expecting* a dtd
0
 
vihar123Author Commented:
is there some way like writing a procudure and calling it from the java prog
0
 
girionisCommented:
What kind of procedure?
0
 
vihar123Author Commented:
something like this that inserts xml into table......but i dont know how it works and how i shld call it from my prg
------------

reate or replace function insert_xml_emps(
  p_tablename  in varchar2,
  p_in_tmpclob in number)
  return number
is
  l_ctx    dbms_xmlsave.ctxType;
  l_rows   number;
begin
  for c1 in (select theclob
               from tmp_clob
              where id = p_in_tmpclob) loop

    l_ctx := dbms_xmlsave.newContext(p_tableName);
    l_rows := dbms_xmlsave.insertxml(l_ctx,c1.theclob);
    dbms_xmlsave.closeContext(l_ctx);

    delete from tmp_clob
     where id = p_in_tmpclob;
  end loop;
  return l_rows;
end insert_xml_emps;
/
0
 
girionisCommented:
If you mean stored procedures have a look here: http://www.onjava.com/pub/a/onjava/2003/08/13/stored_procedures.html
0
 
CEHJCommented:
You need, if using that, to

sp.setString(tableName);
sp.setInt(keyFieldId);
0
 
CEHJCommented:
(where 'keyFieldId' is the id of a clob field you're saving the xml to)
0
 
vihar123Author Commented:
thanks for the link, but is it a better way of doing, i mean then prog will become dependent on the procedure and if the procedure doestnt run properly, then the whole prg blocks, well cant i just send this xml into table using just java?
0
 
girionisCommented:
As it has already been said, you will need to parse the XML, extract the data you want and insert it seperately into the db.
0
 
CEHJCommented:
Since it looks like that proc is just saving to a clob field, you may as well do it yourself. You'll get a slightly worse performance probably, but you will increase portability and decrease dependencies
0
 
vihar123Author Commented:
>>>>As it has already been said, you will need to parse the XML, extract the data you want and insert it seperately into the db

can you please give me a small example that just parses one row and retreives and inserts, etc.

im getting really confused reading a lot of docs
0
 
girionisCommented:
Have a look at the java almanac examples posted.
0
 
vihar123Author Commented:
>>>Have a look at the java almanac examples posted.

where shld i write dtd, im retreiving it and cannot change it................

import java.io.*;
    import javax.xml.parsers.*;
    import org.w3c.dom.*;
    import org.xml.sax.*;
   
    public class BasicDom {
        public static void main(String[] args) {
            Document doc = parseXmlFile("infilename.xml", false);
        }
   
        // Parses an XML file and returns a DOM document.
        // If validating is true, the contents is validated against the DTD
        // specified in the file.
        public static Document parseXmlFile(String filename, boolean validating) {
            try {
                // Create a builder factory
                DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
                factory.setValidating(validating);
   
                // Create the builder and parse the file
                Document doc = factory.newDocumentBuilder().parse(new File(filename));
                return doc;
            } catch (SAXException e) {
                // A parsing error occurred; the xml input is not valid
            } catch (ParserConfigurationException e) {
            } catch (IOException e) {
            }
            return null;
        }
    }

 Related Examples

0
 
CEHJCommented:
>>im retreiving it and cannot change it................

What do you mean - i thought you wanted to insert it into a table, so why do you need to 'change it'?
0
 
vihar123Author Commented:
>>>What do you mean - i thought you wanted to insert it into a table, so why do you need to 'change it'?

through docs what i can get is that i have to make a dtd and then parse using sax and then send it using something, so how to make a dtd, i mean i dont want to use much of xml in my prg, i just want it to hold my data and take into an other table

well i think the question is clear, so can anyone please give me a solution that suits my prg, i have retreived data in xml format and dont want to save it anywhere and this xml, i want to send it to an other table, thats all, when im reading examples in javaalmanac, they all talk accessing some dtd and a saved xml file......
0
 
girionisCommented:
The dtd hasn't got to do with your Java code. The dtd is part of the xml and it is used to validaet the structure of the xml file against the dtd. Have a look here: http://www.w3schools.com/dtd/default.asp
0
 
CEHJCommented:
And you don't need to create a DOM. You're only sending the whole dom to one column so just save file as a clob
0
 
CEHJCommented:
iow - forget about the dtd (at least for the moment)
0
 
vihar123Author Commented:

it took quite long but thank God i could solve it, i have put in all the file that i could get from the
oracle/jdbc/lib files into the java..../ext folder and its working now......

thanks for áll the responses
0
 
CEHJCommented:
8-)
0
 
zzynxSoftware engineerCommented:
Thanks
0
 
girionisCommented:
:)
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 40
  • 37
  • 27
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now