Solved

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

Posted on 2004-09-20
117
1,317 Views
Last Modified: 2008-01-09
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
Comment
Question by:vihar123
  • 40
  • 37
  • 27
  • +1
117 Comments
 
LVL 35

Assisted Solution

by:girionis
girionis earned 150 total points
ID: 12100315
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
 

Author Comment

by:vihar123
ID: 12100336
>>>st.execute("INSERT INTO mytable " + sb.toString());

shld i create mytable manually in my database or it created by itself?
0
 
LVL 86

Accepted Solution

by:
CEHJ earned 200 total points
ID: 12100344
>>now I want to send this data into an other database.

What database, and what is the table name and column name?
0
 
LVL 37

Assisted Solution

by:zzynx
zzynx earned 150 total points
ID: 12100359
>> 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
 

Author Comment

by:vihar123
ID: 12100361
>>>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
 
LVL 37

Expert Comment

by:zzynx
ID: 12100368
>> 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
 
LVL 86

Expert Comment

by:CEHJ
ID: 12100378
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
 
LVL 35

Expert Comment

by:girionis
ID: 12100389
> 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
 

Author Comment

by:vihar123
ID: 12100400
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 12100407
Why would you be using a file at all? Your reading the result set and sending, on a new connection, the new data
0
 
LVL 37

Expert Comment

by:zzynx
ID: 12100417
>>its giving me this exception----

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

Where's the line

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

?
0
 
LVL 37

Expert Comment

by:zzynx
ID: 12100422
>> 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
 
LVL 86

Expert Comment

by:CEHJ
ID: 12100432
No. You should be probably using the OracleXMLSave class to put the data into the other table
0
 
LVL 37

Expert Comment

by:zzynx
ID: 12100443
BTW. I think the insert statement needs the keyword "values" too: http://www.w3schools.com/sql/sql_insert.asp
0
 
LVL 35

Expert Comment

by:girionis
ID: 12100444
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
 
LVL 37

Expert Comment

by:zzynx
ID: 12100447
>> You should be probably using the OracleXMLSave class to put the data into the other table
Back at the start, vihar ;°)
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12100466
>>Back at the start, vihar ;°)

Why? The OracleXMLSave class has only just been mentioned
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12100477
Something like:

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

Expert Comment

by:zzynx
ID: 12100485
>> 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
 
LVL 86

Expert Comment

by:CEHJ
ID: 12100554
>>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
 

Author Comment

by:vihar123
ID: 12100816
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
 
LVL 35

Expert Comment

by:girionis
ID: 12100851
> 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
 

Author Comment

by:vihar123
ID: 12100907
>>>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
 
LVL 86

Expert Comment

by:CEHJ
ID: 12100915
>>is there any option through which, i can insert the whole table????

You need another xml-like table probably with the same schema
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12100919
... and then you could use the code i posted above
0
 
LVL 35

Expert Comment

by:girionis
ID: 12100923
Create a table with only one field. Then insert the whole XML in there.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12100943
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
 

Author Comment

by:vihar123
ID: 12100972
>>>Create a table with only one field. Then insert the whole XML in there.

tried it but the same error is coming
0
 
LVL 35

Expert Comment

by:girionis
ID: 12100989
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
 
LVL 35

Expert Comment

by:girionis
ID: 12100993
st.execute("INSERT INTO Employee (myField) VALUES('" + sx + "')");
0
 

Author Comment

by:vihar123
ID: 12100994
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 12101009
>>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
 

Author Comment

by:vihar123
ID: 12101044
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
 

Author Comment

by:vihar123
ID: 12101061
>>>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
 
LVL 37

Expert Comment

by:zzynx
ID: 12101095
Make that

     st.execute("INSERT INTO employeetest (emp_name) VALUES('" + sx + "')");
0
 
LVL 35

Expert Comment

by:girionis
ID: 12101097
Try this:

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

Author Comment

by:vihar123
ID: 12101112
>>>st.execute("INSERT INTO employeetest (emp_name) VALUES('" + sx + "')");

same error again :-(
0
 
LVL 35

Expert Comment

by:girionis
ID: 12101118
What is the output of this:

System.out.println(sx);

DOes it contain any ' characters?
0
 
LVL 35

Expert Comment

by:girionis
ID: 12101124
If yes then use a PreparedStatement.
0
 
LVL 37

Expert Comment

by:zzynx
ID: 12101137
Try

String query = "INSERT INTO employeetest (emp_name) VALUES(?)"
PreparedStatement ps = connection.prepareStatement( query ) ;
ps.setString( 1, sx) ;
0
 
LVL 35

Expert Comment

by:girionis
ID: 12101138
PreparedStatement ps = conn.prepareStatement("INSERT INTO employeetest (emp_name) VALUES(?)");
ps.setString(1, sx);
ps.executeUpdate();
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12101144
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
 

Author Comment

by:vihar123
ID: 12101161
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
 
LVL 35

Expert Comment

by:girionis
ID: 12101183
> 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
 

Author Comment

by:vihar123
ID: 12101213
new error:-

java.sql.SQLException: ORA-01401: inserted value too large for column
0
 
LVL 35

Expert Comment

by:girionis
ID: 12101260
What type is the column?
0
 
LVL 35

Expert Comment

by:girionis
ID: 12101263
Actually the type is irrelevant. What is its length?
0
 

Author Comment

by:vihar123
ID: 12101294
>>>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
 
LVL 37

Expert Comment

by:zzynx
ID: 12101336
>>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
 
LVL 35

Expert Comment

by:girionis
ID: 12101343
Ôñõ ôçéó¨

CREATE TABLE employeetest2 (
  emp_name VARCHAR2(2048)
)
0
 
LVL 35

Expert Comment

by:girionis
ID: 12101348
> Ôñõ ôçéó¨

should be

Try this

Wrong encoding.
0
 
LVL 37

Expert Comment

by:zzynx
ID: 12101353
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
 

Author Comment

by:vihar123
ID: 12101403
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
 
LVL 35

Expert Comment

by:girionis
ID: 12101424
You will have to parse the XML and extract the data you want:

http://javaalmanac.com/cgi-bin/search/find.pl?words=dom
0
 

Author Comment

by:vihar123
ID: 12101573
>>>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
 

Author Comment

by:vihar123
ID: 12101615
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
 
LVL 35

Expert Comment

by:girionis
ID: 12101621
> 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
 
LVL 35

Expert Comment

by:girionis
ID: 12101623
> 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 35

Expert Comment

by:girionis
ID: 12101656
> 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
 

Author Comment

by:vihar123
ID: 12101739
>>>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
 
LVL 35

Expert Comment

by:girionis
ID: 12101791
> 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
 

Author Comment

by:vihar123
ID: 12101874
>>>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
 
LVL 35

Expert Comment

by:girionis
ID: 12101912
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
 
LVL 35

Expert Comment

by:girionis
ID: 12101919
The other thing you can do is to use the oracel/sql/CLOB class instead, if they are the same.
0
 

Author Comment

by:vihar123
ID: 12101971
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
 
LVL 35

Expert Comment

by:girionis
ID: 12102000
> how to use them?

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

Expert Comment

by:girionis
ID: 12102008
> 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
 

Author Comment

by:vihar123
ID: 12102111

>>>>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
 
LVL 37

Expert Comment

by:zzynx
ID: 12102120
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
 

Author Comment

by:vihar123
ID: 12102229
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
 

Author Comment

by:vihar123
ID: 12102357
can somebody give me a parsing example relating to my requirement????? plzzzz urgent
0
 
LVL 35

Expert Comment

by:girionis
ID: 12109616
0
 

Author Comment

by:vihar123
ID: 12109853
>>>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
 
LVL 35

Expert Comment

by:girionis
ID: 12109918
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
 
LVL 35

Expert Comment

by:girionis
ID: 12109923
"factory" variable is a DocumetnBuilderFactory:

DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12109946
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
 

Author Comment

by:vihar123
ID: 12110021
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
 
LVL 35

Expert Comment

by:girionis
ID: 12110044
Which line?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12110065
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
 

Author Comment

by:vihar123
ID: 12110122
>>>>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
 

Author Comment

by:vihar123
ID: 12110146
>>>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
 
LVL 35

Expert Comment

by:girionis
ID: 12110149
I think there is an error in the XML file against the dtd file. Can you check it in an XML editor?
0
 
LVL 35

Expert Comment

by:girionis
ID: 12110167
>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
 
LVL 86

Expert Comment

by:CEHJ
ID: 12110207
>>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
 

Author Comment

by:vihar123
ID: 12110220
>>>>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
 
LVL 86

Expert Comment

by:CEHJ
ID: 12110248
What version of Oracle are you using?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12110286
0
 

Author Comment

by:vihar123
ID: 12110411
>>>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
 
LVL 35

Expert Comment

by:girionis
ID: 12110417
I think if you combine all the posts together you will find the answer you want.
0
 

Author Comment

by:vihar123
ID: 12110441
>>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
 
LVL 35

Expert Comment

by:girionis
ID: 12110461
>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
 
LVL 86

Expert Comment

by:CEHJ
ID: 12110478
I don't know about the versions either.
0
 

Author Comment

by:vihar123
ID: 12111847
>>>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
 
LVL 35

Expert Comment

by:girionis
ID: 12114528
Does it validate fine against a DTD?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12114565
It might be *expecting* a dtd
0
 

Author Comment

by:vihar123
ID: 12132647
is there some way like writing a procudure and calling it from the java prog
0
 
LVL 35

Expert Comment

by:girionis
ID: 12132683
What kind of procedure?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12132702
0
 

Author Comment

by:vihar123
ID: 12132703
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
 
LVL 35

Expert Comment

by:girionis
ID: 12132726
If you mean stored procedures have a look here: http://www.onjava.com/pub/a/onjava/2003/08/13/stored_procedures.html
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12132795
You need, if using that, to

sp.setString(tableName);
sp.setInt(keyFieldId);
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12132809
(where 'keyFieldId' is the id of a clob field you're saving the xml to)
0
 

Author Comment

by:vihar123
ID: 12132819
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
 
LVL 35

Expert Comment

by:girionis
ID: 12132864
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 12132866
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
 

Author Comment

by:vihar123
ID: 12132918
>>>>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
 
LVL 35

Expert Comment

by:girionis
ID: 12132929
Have a look at the java almanac examples posted.
0
 

Author Comment

by:vihar123
ID: 12133721
>>>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
 
LVL 86

Expert Comment

by:CEHJ
ID: 12135812
>>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
 

Author Comment

by:vihar123
ID: 12140811
>>>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
 
LVL 35

Expert Comment

by:girionis
ID: 12140945
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 12141087
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 12141100
iow - forget about the dtd (at least for the moment)
0
 

Author Comment

by:vihar123
ID: 12141446

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
 
LVL 86

Expert Comment

by:CEHJ
ID: 12141689
8-)
0
 
LVL 37

Expert Comment

by:zzynx
ID: 12141713
Thanks
0
 
LVL 35

Expert Comment

by:girionis
ID: 12141813
:)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

706 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

15 Experts available now in Live!

Get 1:1 Help Now