servlets and xml

[Lasse Koskela]

Originally posted by clyde melly:
for the mail servlet program in order to access the geterrormessage method do i make this mail servlet class an inner class and then proceed or any other way out.

Yes, there is another--more common--way out:

// file: MyFirstClass.java
public class MyFirstClass {
    public void doSomething() {
        MySecondClass msc = new MySecondClass();
        System.out.println(msc.saySomething());
    }
}
// file: MySecondClass.java
public class MySecondClass {
    public String saySomething() {
        return "something";
    }
}

It's about class and method visibility. You'll find it from any "introduction to Java programming" book out there.

[clyde melly]

Yeah this is another way out.The common approach.Got nothing much related to the datasource stuff.Any sample programs on that.Wud really help

[clyde melly]

Lasse the reply not related to the assignment.Was looking into SCBCD.Any good links,demo tests etc as u r certified.Let me know

[Lasse Koskela]

Our SCBCD forum is the best place to ask this kind of questions. They have a "links" page over there.

[clyde melly]

Thanks for scbcd.Regarding datasource stuff i need to get into inputstream and outputstream stuff.any examples wud really help

[Lasse Koskela]

You can try something like this:

public class InMemoryDataSource implements javax.activation.DataSource {
    
    private byte[] data;
    
    public InMemoryDataSource(byte[] data) {
        this.data = data;
    }
    
    public String getContentType() {
        return "text/plain";
    }
    public String getName() {
        return "errors.txt";
    }
    public java.io.InputStream getInputStream() {
        return new java.io.ByteArrayInputStream(this.data);
    }
    public java.io.OutputStream getOutputStream() {
        throw new RuntimeException("This method is probably not needed");
    }
}

[clyde melly]

Hi lasse,
Was just working on getting data from the database and outputting it in the form of
xml document.Now suppose in the database i have eno ,ename & phone .

so I want to put it in the form of :

<?xml version="1.0"?>
<ename eno="">
<phone>
</ename>

How do i go about.It is like fetching the values from database and then adding to a newly created documents.Then create new element
and then appending.I also want to see my xml file printed out.just let me know.

[Lasse Koskela]

First, do this.
Then, do this and this in a loop.
When you're done, do this for seeing the outcome.

[clyde melly]

Will work on and let u know about the jdbc xml stuff.Thanx

[clyde melly]

Lasse ;
DataSource source = new InMemoryDataSource();

Should the parameter passed be here for InMemoryDataSource be a String
(errormessage)?
The constructor for InMemoryDataSource(byte[] data) is basicaly giving a byte array.Do i need to convert the byte to String and then use.Then
say Byte.toString() which returns String and use it in here
DataSource source = new InMemoryDataSource();

[Lasse Koskela]

java.lang.String#getBytes()
Create the InMemoryDataSource by converting the errorMessage string into a byte array and give that to the constructor. Alternatively, you can change/overload the constructor to accept a String and make the conversion within the constructor.

[clyde melly]

Will work on datasource stuff and let u know.
I have a table in the database with 4 fields of which 3 r of type numeric.As per the code
when I try to do

Element element = doc.createElement(colName);
doc.appendChild(element);

I get an DOM006 hierarchy request error.How do I go about?

The 4 fields are namely oid,pid,qty(numeric) and status(string).

so my xml output should somewhat be :

<?xml version='1.0'?>
<order oid="">
    <pid>value</pid>
    <qty>value</qty>
    <status>value</status>
</order>

or is there a better way to express my xml output(i.e structure).or this is alright.
Code:

import org.w3c.dom.*;
import org.xml.sax.*;
import java.sql.*;
//xalan stuff
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.Transformer;
import javax.xml.transform.Source;
import javax.xml.transform.stream.StreamSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.transform.*;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.dom.DOMResult;
public class NewXmlDocTest {
    static String colName,colVal;
    public static Document createDomDocument() {
        try {
            DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
            Document doc = builder.newDocument();
            return doc;
        } catch (Exception e) {
        }
        return null;
    }
    public static void writeXmlFile(Document doc, String filename) {
        try {
            // Prepare the DOM document for writing
            Source source = new DOMSource(doc);
            // Prepare the output file
            File file = new File(filename);
            Result result = new StreamResult(file);
            // Write the DOM document to the file
            Transformer xformer = TransformerFactory.newInstance().newTransformer();
            xformer.transform(source, result);
        } catch (Exception e) {
        }
    }
    static public void main(String g[]) {
        int numCols;
        Connection con=null;
        Statement statement=null;
        ResultSet resultset=null;
        ResultSetMetaData resultmetadata = null;
        Document doc = createDomDocument();
        // Insert the root element node
        // jdbc connection
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            con=DriverManager.getConnection("jdbc:odbc:xjdbc");
            statement = con.createStatement();
            resultset = statement.executeQuery("select * from ord");
            resultmetadata = resultset.getMetaData();
            numCols = resultmetadata.getColumnCount();
            System.out.println("Numcols : "+numCols);
            while (resultset.next()) {
                //For each row of data
                for (int i=1; i <= numCols; i++) {
                    //For each column, retrieve the name and data
                    colName = resultmetadata.getColumnName(i);
                    colVal = resultset.getString(i);
                    //If there was no data, add "and up"
                    if (resultset.wasNull()) {
                        colVal = "clyde";
                    }
                    Element element = doc.createElement(colName);
                    doc.appendChild(element);
                }
                System.out.println(colName+" - - "+colVal);
            }
            resultset.close();
            statement.close();
            con.close();
        } catch (Exception e) {
            System.out.println("SQL Error: " + e.getMessage());
        }
        writeXmlFile(doc, "c:/xmlnew.txt");
    }
}

EDIT: I added the [CODE] tags for you again to be able to read the code.
[ October 12, 2003: Message edited by: Lasse Koskela ]

[Lasse Koskela]

Ok, the reason for that hierarchy error is that you're trying to append several elements into the root level. An XML document can only have one (1) root element; everything else has to go inside it.
You need to do something like this:

add root level element to hold all orders
while (resultset has more rows)
    create an order element based on resultset
    add order element to root level element
end while

[clyde melly]

As per ur suggestion I made the changes.But my output looks like

<?xml version="1.0" encoding="UTF-8"?>
<order><oid>1</oid><pid>250</pid><qty>1</qty><status>pen</status></order>

I want the oid to be an attribute of order rather than an element.How do I go about?

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import java.io.*;
import org.w3c.dom.*;
import org.xml.sax.*;
import java.sql.*;
//xalan stuff
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.Transformer;
import javax.xml.transform.Source;
import javax.xml.transform.stream.StreamSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.transform.*;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.dom.DOMResult;
public class NewXmlDocTest {
    static String colName, colVal;
    public static Document createDomDocument() {
        try {
            DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
            Document doc = builder.newDocument();
            return doc;
        } catch (Exception e) {
        }
        return null;
    }
    
    public static void writeXmlFile(Document doc, String filename) {
        try {
            // Prepare the DOM document for writing
            Source source = new DOMSource(doc);
    
            // Prepare the output file
            File file = new File(filename);
            Result result = new StreamResult(file);
    
            // Write the DOM document to the file
            Transformer xformer = TransformerFactory.newInstance().newTransformer();
            xformer.transform(source, result);
        } catch (Exception e) {
        }
    }
    static public void main(String g[]) {
        int numCols;
        Connection con=null;
        Statement statement=null;
        ResultSet resultset=null;
        ResultSetMetaData resultmetadata = null;
        Document doc = createDomDocument();
        Element dataroot = doc.createElement("order");
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            con=DriverManager.getConnection("jdbc:odbc:xjdbc");
            statement = con.createStatement();
            resultset = statement.executeQuery("select * from ord");
            resultmetadata = resultset.getMetaData();
            numCols = resultmetadata.getColumnCount();
            System.out.println("Numcols : "+numCols);
            while (resultset.next()) {
                //For each row of data
                for (int i=1; i <= numCols; i++) {
                    //For each column, retrieve the name and data
                    colName = resultmetadata.getColumnName(i);
                    colVal = resultset.getString(i);
                    if (resultset.wasNull()) {
                        colVal = "clyde";
                    }
                    //Create a new element with the same name as the column
                    Element dataEl = doc.createElement(colName);
                    dataEl.appendChild(doc.createTextNode(colVal));
                    dataroot.appendChild(dataEl);
                }
            }
            resultset.close();
            statement.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        // Add the root element to the document
        doc.appendChild(dataroot);
        writeXmlFile(doc, "c:/xmlnew1.txt");
    }
}

EDIT: I again added the code tags and formatted your code into something readable
[ October 14, 2003: Message edited by: Lasse Koskela ]

[Lasse Koskela]

I want the oid to be an attribute of order rather than an element.How do I go about?

org.w3c.dom.Element#setAttribute(java.lang.String name, java.lang.String value)

[clyde melly]

Worked with setAttribute and also the datasource stuff.Everything is working off fine.Thanx to ur immense help and guidance.The assignment wouldn't have been completed without your inputs. Looking into xml and oracle.How should i go about.Any links,tutorials etc.

[Lasse Koskela]

Glad to hear that it's working!
For XML and Oracle, you should hang around the JDBC and Oracle forums. Also, the Oracle Technology Network is your friend.

[clyde melly]

Lasse looking into using JDOM.How do i start about.examples,tutorials would help.Working in java,I guess JDOM is far better approach.what u say??

[Lasse Koskela]

I haven't used JDOM myself so I really can't help. I have had to deal with a crappy piece of software, though, that used JDOM but the crappiness wasn't JDOM's fault.
Besides, you should start a new thread for discussing JDOM as it doesn't have anything to do with the topic of this thread.

[clyde melly]

Lasse
As per the question

" An xml files should be attached to the email "


code for that xml file if the record was rejected.

Document doc = createDomDocument();

Element dataroot = doc.createElement("record");

dataroot.appendChild(doc.createTextNode(Integer.toString(rec_rej)));

doc.appendChild(dataroot);

writeXmlFile(doc, "c:/inrej.xml");


the inrej.txt file contains the format

<?xml version='1.0'?>
<record>
3 the no of rejected records(3 is used just as an example)
</record>


Now for this purpose I will have to use this :
DataSource source = new FileDataSource(filename);
messageBodyPart.setDataHandler(new DataHandler(source));
messageBodyPart.setFileName(filename);
where filename will be inrej.xml.Am I right or wrong?

[Lasse Koskela]

The filename should be "C:/inrej.xml", i.e. the path to the file. Just "inrej.xml" would work if the current directory is C: but it's not safe to rely on that being true.

[clyde melly]

Ok so i need to use the filedatasource.and the path along with it.thanx

[clyde melly]

Lasse
Code:

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import java.io.*;
import org.w3c.dom.*;
import org.xml.sax.*;
import java.sql.*;
//xalan stuff
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.Transformer;
import javax.xml.transform.Source;
import javax.xml.transform.stream.StreamSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.transform.*;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.dom.DOMResult;

public class JdbcToXml
{
static String colName,colVal,colLabel;

public static Document createDomDocument() {

try {
DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder();

Document doc = builder.newDocument();
return doc;
} catch (Exception e) {
}
return null;
}

public static void writeXmlFile(Document doc, String filename) {
try {
// Prepare the DOM document for writing
Source source = new DOMSource(doc);

// Prepare the output file
File file = new File(filename);
Result result = new StreamResult(file);

// Write the DOM document to the file
Transformer xformer = TransformerFactory.newInstance().newTransformer();
xformer.transform(source, result);
} catch (Exception e) {

} }

static public void main(String g[])
{
int numCols;

Connection con=null;

Statement statement=null;

ResultSet resultset=null;

ResultSetMetaData resultmetadata = null;


Document doc = createDomDocument();


// Created new document order

Element dataroot = doc.createElement("order");



// jdbc connection

try {

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc dbc:xjdbc");

statement = con.createStatement();

resultset = statement.executeQuery("select * from ord");

resultmetadata = resultset.getMetaData();

numCols = resultmetadata.getColumnCount();

System.out.println("Numcols : "+numCols);



/* if (resultset.next()==false) {

for (int i=1;i<=numCols;i++) {

colName = resultmetadata.getColumnName(i);

System.out.println (colName);

}

}*/

while (resultset.next()) {

//For each row of data



for (int i=1; i <= numCols ; i++) {
//For each column, retrieve the name and data

colName = resultmetadata.getColumnName(i);

colVal = resultset.getString(i);

if (resultset.wasNull()) {
colVal = "clyde";
}


if(colName.equals("oid"))
{

dataroot.setAttribute(colName,colVal);

}

else
{


Element dataEl = doc.createElement(colName);

dataEl.appendChild(doc.createTextNode(colVal));

dataroot.appendChild(dataEl);

}

}
}

resultset.close();statement.close();con.close();

} catch (Exception e) {
//System.out.println("SQL Error: "+e.getMessage());
e.printStackTrace();
}


doc.appendChild(dataroot);

writeXmlFile(doc, "c:/xmlnew1.xml");

}
}
This is the output
<?xml version="1.0" encoding="UTF-8"?>
<order oid="12"><pid>43</pid><qty>2</qty><status>f</status></order>

I want add a new element record to order.When i try to add i get a dom hierarchy error.
How do i go about.
Secondly if my table in the database doesn't contain any records and I want the output as
<?xml version="1.0" encoding="UTF-8"?>
<order/>
i.e should i use resultset.wasNull or resultset.next()==false,but the second one will be a problem when i actually have records
in the table.How do i go about.thanx

[Lasse Koskela]

I want add a new element record to order.When i try to add i get a dom hierarchy error.

What error? How are you trying to add an element?

Secondly if my table in the database doesn't contain any records and I want the output as
<?xml version="1.0" encoding="UTF-8"?>
<order/>
i.e should i use resultset.wasNull or resultset.next()==false,but the second one will be a problem when i actually have records
in the table.

if (!resultset.next()) {
    // do something if the resultset was empty
} else {
    do {
        // do something with the resultset's contents
    } while (resultset.next());
}

[Lasse Koskela]

By the way, this article reference by Balaji in another thread might help you with adding elements to the DOM tree...

[clyde melly]

Create an element "record" and then trying to appending it to my order element.This is the way i'm going and end up with the error.But let me check the stuff on Balaji and i'll get back.

[clyde melly]

Lasse,
Just went thru the tutorial on DOM and no error.Now I add record element(for rejected records).My output is i.e
<order><record>other elements.....(rec value)</record></order>.That is also happening.But the value of the rejected record is being displayed after the other elements i.e just before record closes(see that rec value).I want it to be as
<order><record>(rec value)</record></order>
Lasse if u go thru the code then u'll see that i can use the rec_rej variable at the end.I can't use it before.How do i go about.If this is not clear don't break ur head over it.I'll try to put that specific code and a detailed explaination in my next reply ASAP.And lasse that resultset stuff was not clear.Can u give me some links on that resultset tutorials.Thanx

[Lasse Koskela]

I'll try to explain "the ResultSet stuff" with an example scenario:

/**
 * This method is invoked below with different sizes of a ResultSet.
 */
public void handle(ResultSet resultset) {
    // this first call to next() moves the resultset cursor to the first record
    if (!resultset.next()) {
        System.out.println("ResultSet was empty");
        // you might want to print out a hardcoded "<?xml version="1.0"?>\n<order/>" within this branch
    } else {
        int counter = 1;
        do {
            // do something with the resultset's contents
            System.out.println("row #" + (i++));
        } while (resultset.next());
    }
}

And here's some scenarios:

handle(emptyResultSet) prints out "ResultSet was empty"

handle(resultSetWithSizeOfOne) prints out "row #1"

handle(resultSetWithSizeOfFive) prints out "row #1 \n row #2 \n ... \n row #5"

In other words, the condition expression of a do-while is evaluated after each loop (versus evaluating it before each loop as in for and while loops).

[clyde melly]

Lasse have u just gone thru the code for the rec_rej value i asked in the previous reply.Just look into it or else let me know if u want a detailed stuff regarding it.Thanx

[Lasse Koskela]

But there is no variable named "rec_rej"?

[clyde melly]

import java.sql.*;
public class DuplicateInsert
{
    static int results,cnt,rec_load,rec_rej;
    DuplicateInsert()
    {
        try
        {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection con=DriverManager.getConnection("jdbc:odbc:xjdbc");
            Statement stmt=con.createStatement();
            results=stmt.executeUpdate("insert into ord(oid) values(97)");
            
            Document doc = createDomDocument();
            Element dataroot = doc.createElement("record"); 
            dataroot.appendChild(doc.createTextNode(Integer.toString(rec_rej)));
            doc.appendChild(dataroot);
            if(results>0)
            {
                rec_load++;
                System.out.println("No of records inserted : "+rec_load);
            }
            else
            {
                rec_rej++;
            }
            System.out.println("Total No of records inserted : "+cnt);
            stmt.close();
            con.close();
        }
        catch(Exception e)
        {
            ++rec_rej; 
            System.out.println("No of records rejected : "+rec_rej);
        }
    }

This is not the complete code.For some specific reasons I want that the element record should be having the value for rec_rej on execution.i.e even before the event happens,i want to capture the value.If u r still not getting it.Just hang on and I will send the complete code with a detailed explaination.
[ October 27, 2003: Message edited by: Lasse Koskela ]

[Lasse Koskela]

Nope. Still not getting it.
PS. Maybe you should start a new thread for this particular problem -- it might draw more attention than this monolithic three-page thread boasting over a hundred messages...

[clyde melly]

Lasse this was part of the assignment.My output through this code comes as
<order>
<record>
<other elements which i have fetched from the database.....?
(Count of rejected records)
</record>
</order>
I want the output to be
<order>
<record>(Count of rejected records)
</record>
<other elements which i have fetched from the database.....?
</order>
Don't go through the whole code.I have commented out the areas which gives me this output(Commented as /** lasse)

import org.w3c.dom.*;
import java.io.*;
import java.util.*;
import java.text.*;
import javax.mail.*;
import javax.servlet.*;
import javax.activation.*;
import javax.servlet.http.*;
import javax.mail.internet.*;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;
import org.xml.sax.SAXException;
import org.xml.sax.SAXParseException;
import org.xml.sax.ContentHandler;
import org.xml.sax.ErrorHandler;
import java.io.IOException;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import java.sql.*;

//xalan stuff
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.Transformer;
import javax.xml.transform.Source;
import javax.xml.transform.stream.StreamSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.transform.*;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.dom.DOMResult;
class SaxErrors implements ErrorHandler
{

public StringBuffer errors = new StringBuffer();

public void warning(SAXParseException ex)
{


}

public void error(SAXParseException ex)
{

}


public void fatalError(SAXParseException ex) throws SAXException

{
errors.append("Error ---\n");
errors.append("Line number : " + ex.getLineNumber() + "\n");
errors.append("Column number : " + ex.getColumnNumber() + "\n");
errors.append("Message : " + ex.getMessage() + "\n");
}


public String ErrorMessages()
{
if (errors.length() == 0)

{
return null;
}

else
{

return errors.toString();
}


}


}
public class JdbcInsertServlet extends HttpServlet {

static String colName;

static int results,rec_load,rec_rej,numCols;

Document agentsDoc = null;

Connection con = null;

Statement stmt = null;

ResultSet resultset = null;

ResultSetMetaData resultmetadata = null;

int agid;

String str1;

//New document
public static Document createDomDocument() {

try {
DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder();

Document doc = builder.newDocument();



return doc;
} catch (Exception e) {
}
return null;
}



public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {


response.setContentType("text/html");
PrintWriter out = response.getWriter();
str1=request.getParameter("thefile");
System.out.println("str1 : "+str1);


//for parsing the xml file

ErrorHandler demo=new SaxErrors();

SaxErrors s = new SaxErrors();

try
{
//Instantiate the parser and parse the file
DocumentBuilderFactory docbuilderfactory = DocumentBuilderFactory.newInstance();

DocumentBuilder docbuilder = docbuilderfactory.newDocumentBuilder();

agentsDoc = docbuilder.parse(str1);


XMLReader reader=XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
reader.setErrorHandler(demo);
reader.setFeature("http://xml.org/sax/features/validation",true);
}
catch(SAXException e){
System.out.println("error reading" + e.getMessage());
}
catch(Exception ex)
{
System.out.println("Error : " + ex.getMessage());
}
if (s.ErrorMessages() != null)
{
sendEmail(str1);
}

/**lasse

Document doc = createDomDocument();

Element dataroot=doc.createElement("order);

Element dataroot1 = doc.createElement("record");

dataroot.appendChild(dataroot1);

order element i.e the root and record being appended to it.
*/
try

{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc dbc:xjdbc");
stmt=con.createStatement();

resultset = stmt.executeQuery("select * from agents");

resultmetadata = resultset.getMetaData();

numCols = resultmetadata.getColumnCount();

/**Lasse

for (int i=1;i<=numCols;i++) {

colName = resultmetadata.getColumnName(i);


Element dataEl = doc.createElement(colName);

dataEl.appendChild(doc.createTextNode(colVal));

dataroot.appendChild(dataEl);


}
elements are added fetched from the database
*/
//Get the root element and all agents elements
Element agentsRoot = agentsDoc.getDocumentElement();
NodeList agents = agentsRoot.getElementsByTagName("agent");
for (int i = 0; i < agents.getLength(); i++) {
//For each agents, get the agent element
Element thisAgent = (Element)agents.item(i);


//Get iatano information
String thisAgentid = thisAgent.getAttribute("iatanumber");
System.out.println("Ino: " + thisAgentid);

agid = Integer.parseInt(thisAgentid);

out.println("Iata No is : " +agid);

//Get name information

String thisAgentname = thisAgent.getAttribute("name");
System.out.println("Name: "+thisAgentname);


//Loop through each trading address
NodeList traddress = thisAgent.getElementsByTagName("tradingaddress");
for (int j=0; j < traddress.getLength(); j++) {
Element thisAddress = (Element)traddress.item(j);

System.out.println("j" + j+ " "+"i "+ i);

//Get trading adress information from child
//elements

String l1 = thisAddress.getElementsByTagName("line1")
.item(0)
.getFirstChild().getNodeValue();

String l2 = thisAddress.getElementsByTagName("line2")
.item(0)
.getFirstChild().getNodeValue();



String pcode = thisAddress.getElementsByTagName("postcode")
.item(0)
.getFirstChild().getNodeValue();


String reg =thisAddress.getElementsByTagName("region")
.item(0)
.getFirstChild().getNodeValue();


String cty =thisAddress.getElementsByTagName("country")
.item(0)
.getFirstChild().getNodeValue();




//Loop through each legal address for the name and iatano
NodeList lgaddress = thisAgent.getElementsByTagName("legaladdress");
for (int k=0; k < lgaddress.getLength(); k++) {
Element thislgAddress = (Element)lgaddress.item(k);

System.out.println("k" + k+ " "+"i "+ i);

//Get trading adress information from child
//elements

String ll1 = thislgAddress.getElementsByTagName("line1")
.item(0)
.getFirstChild().getNodeValue();

String ll2 = thislgAddress.getElementsByTagName("line2")
.item(0)
.getFirstChild().getNodeValue();


String lpcode = thislgAddress.getElementsByTagName("postcode")
.item(0)
.getFirstChild().getNodeValue();

String lreg =thislgAddress.getElementsByTagName("region")
.item(0)
.getFirstChild().getNodeValue();

String lcty =thislgAddress.getElementsByTagName("country")
.item(0)
.getFirstChild().getNodeValue();



try
{
results = stmt.executeUpdate("insert into agents(iatano,trpostcode,lgregion) values("+agid+",'"+pcode+"','"+lreg+"')");


if(results>0)
{
++rec_load;
out.println("insert");
}
else
{
// ++rec_rej;
}
}catch(Exception e){++rec_rej;out.println("Reject");}



}
}

System.out.println("agid is : "+agid);

}
System.out.println("Total No of records inserted : "+rec_load);
System.out.println("Total No of records rejected : "+rec_rej);


stmt.close();
con.close();

System.out.println("Over");



} catch (Exception e) {

System.out.println("Error " + e.toString());

}
//For printing the new xml file in case of error

/**lasse
if(rec_rej > 0)

{

dataroot.appendChild(doc.createTextNode(Integer.toString(rec_rej)));

doc.appendChild(dataroot);

writeXmlFile(doc, "c:/inrej.xml");
}
here i get the value of rec_rej.
*/

}//doPost() closing

public void sendEmail( String filename){

boolean debug = false;

try {
Properties prop = new Properties();
prop.put("mail.host", "your_stmp_goes_here"); //SMTP goes here

Session mailConnection = Session.getDefaultInstance(prop, null);
mailConnection.setDebug(debug);
Message msg = new MimeMessage(mailConnection);

InternetAddress myaddress = new InternetAddress("[e-mail]", "YourName");
InternetAddress sendingTo = new InternetAddress("[e-mail]", "OtherPersonsName");

msg.setContent(msg, "text/plain");
msg.setFrom(myaddress);
msg.setRecipient(Message.RecipientType.TO, sendingTo);
msg.setSubject("Error details: ");


// Create the msg part
BodyPart msgBodyPart = new MimeBodyPart();
// Fill the msg
msgBodyPart.setText("XML Error File");
Multipart multipart = new MimeMultipart();
multipart.addBodyPart(msgBodyPart);
// Conversion of String errorMesage to byte arrray

// attachment
msgBodyPart = new MimeBodyPart();
DataSource source = new FileDataSource("c:/inrej.xml");
msgBodyPart.setDataHandler(new DataHandler(source));
msgBodyPart.setFileName("Error Messages");
multipart.addBodyPart(msgBodyPart);
// Put parts in msg
msg.setContent(multipart);

// Send the msg
Transport.send(msg);
}
catch (Exception e) {
e.printStackTrace();
}


}
//For getting values from database incase of reject and writing it to a file
public static void writeXmlFile(Document doc, String filename) {
try {
// Prepare the DOM document for writing
Source source = new DOMSource(doc);

// Prepare the output file
File file = new File(filename);
Result result = new StreamResult(file);

// Write the DOM document to the file
Transformer xformer = TransformerFactory.newInstance().newTransformer();
xformer.transform(source, result);
} catch (Exception e) {

} }
}

[clyde melly]

I don't get any positive responses in other forums so asking you about it.Still will post it in the java forum.Since I am sure you will
certainly answering as u have been answering my other questions and doubts.
Lasse this is the table html2long with the fields.
BP_IDENT
------------------------------------------
BP_DATA
----------
<html><body bgcolor="#cccfff"><input type="text" name="tt"/><br/></body></html>
106

My code :
import java.sql.*;
import java.io.*;
class Long2Html
{
public static void main (String args [])
throws SQLException, ClassNotFoundException, IOException
{
// load the Oracle driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn=DriverManager.getConnection("jdbc dbc:calvin","scott","tiger");
Statement stmt=conn.createStatement();
// query to get the row with bpdata 106
ResultSet rs =
stmt.executeQuery ("SELECT bp_ident FROM html2long WHERE bp_data=106");

// process ResultSet data
if(rs.next())
{

InputStream web_text = rs.getAsciiStream (1);

// open the export (HTML) file
FileOutputStream ostr = new FileOutputStream ("c:/longtohtml.html");

// fetch-write loop: fetch HTML column data, write to file
int i;
while ((i = web_text.read ()) != -1)

ostr.write (i);

// close the file
ostr.close ();
}

// close result set, connection, statement
if (rs != null)
rs.close();

if (stmt != null)
stmt.close();

if (conn != null)
conn.close();
}
}

The problem is that I get the acsii output for my longtohtml.html file.How do I Get
the text i.e the html file(<html><body bgcolor="#cccfff"><input type="text" name="tt"/><br/></body></html> ?

[Lasse Koskela]

The problem is that I get the acsii output for my longtohtml.html file.How do I Get the text i.e the html file(<html><body bgcolor="#cccfff"><input type="text" name="tt"/><br/></body></html> ?

You can just as well say rs.getString() instead of rs.getAsciiStream() if you want UTF-8 instead of ASCII, if that's what you mean?

[clyde melly]

Lasse i'll try it out.Just look for my previous reply.the one which was of
records rejected.It is just above this jdbc stuff.

[Lasse Koskela]

I really think you should a) divide your code into smaller methods, and b) rename your variables (for example, "dataroot1" isn't too descriptive while "recordElement" would be much better).
For instance, the following structure would be much easier to understand than a single, monolithic doPost() method:

class JdbcInsertServlet {
    public void doPost(...) {
        Document doc = parseXmlFile(request.getParameter("filename"));
        List agents = createListOfAgents(doc);
        int rejected = insertAgentsToDatabase(agents);
        // at this point, you have both the number of rejected inserts
        // and the agent objects in a nice list where they're easy to
        // print out into an XML file in any order and/or send via email
    }
    private Document parseXmlFile(String xmlFilename);
    private List createListOfAgents(Document doc);
    private Agent convertElementToAgent(Element agentElement);
    private Element convertAgentToElement(Agent agentObject);
    private Address convertElementToAddress(Element addressElement);
    private Element convertAddressToElement(Address addressObject);
    private int insertAgentsToDatabase(List agents);
    private boolean insertAgentToDatabase(Agent agent);
}