servlets and xml

[clyde melly]

How do I go about.New to xml.Using Xerces parser.Plz help.Thanx in advance.

an XML file, called agents.xml, containing the details of at least 5 travel agents. The structure of the file is as follows:
<agents>
<agent iatanumber=”” name=””>
<tradingaddress>
<line1></line1>
<line2></line2>
<line3></line3>
<postcode></postcode>
<region></region >
<country></country>
</tradingaddress>
<legaladdress>
<line1></line1>
<line2></line2>
<line3></line3>
<postcode></postcode>
<region></region >
<country></country>
</legaladdress>
</agent>
</agents>

Write a Servlet using Java 1.3.x (but NOT Java 1.4) to read an xml file from the virtual directory /test and validate it as follows:
The Servlet should be called TestParser
the filename is passed in the parameter xmlfile using the post method
e.g xmlfile will have a value of file1.xml
the file must be validated against the XML Schema – any validation errors should be EMAILED from the server to the account [e-mail], the from address should be [e-mail], the subject should be “XML Parser Error in File: “+filename
The body should contain details of the error, including the line at which the error occurred and the XML file that failed to validate should be attached to the email.

Next question
Write a Servlet using Java 1.3.x (but NOT Java 1.4) called DBTable to do the following:
The Database name will be supplied in the parameter dbname – this will be passed using either the Get or Post methods so your servlet should cater for both
Check for the existence of the MySQL database table agents and if it exists, delete it
Create a MySQL Database table as follows:
table name: agents
field: iatano numeric
field: trline1 alphanumeric
field: trline2 alphanumeric
field: trline3 alphanumeric
field: trline4 alphanumeric
field: trpostcode alphanumeric
field: trregion alphanumeric
field: trcountry alphanumeric
field: lgline1 alphanumeric
field: lgline2 alphanumeric
field: lgline3 alphanumeric
field: lgline4 alphanumeric
field: lgpostcode alphanumeric
field: lgregion alphanumeric
field: lgcountry alphanumeric
If the Servlet encounters any errors it should email details of the errors, including a complete stack trace as follows:
EMAILED from the server to the account [e-mail], the from address should be [e-mail], the subject should be “Table Creation Failed”
The body should contain a summary of the error.
The stack trace should be attached to the email as an ASCII text file.

Next question
Write a Servlet using Java 1.3.x (but NOT Java 1.4) to parse a validated XML file from the virtual directory /test and insert it into the database created in Question 8.
The table should not allow duplicates – and should reject only the duplicate entries – continuing to insert the valid entries.
All errors including duplicate entries should be emailed as follows:
EMAILED from the server to the account [e-mail], the from address should be [e-mail], the subject should be “
Agent Insert Failed”
The body should contain the following information:
Records Loaded: xx
Records Rejected: yy
(where xx and yy are the number of errors)
An xml files should be attached to the email as follows which should be of the following format:
<rejectedrecords>
<rejected>
<recordno>xx</recordno> note xx is the record no in the original file
<agent iatanumber=”” name=””>
<tradingaddress>
<line1></line1>
<line2></line2>
<line3></line3>
<postcode></postcode>
<region></region >
<country></country>
</tradingaddress>
<legaladdress>
<line1></line1>
<line2></line2>
<line3></line3>
<postcode></postcode>
<region></region >
<country></country>
</legaladdress>
</agent>
<reason>
the reason the record was rejected, this may include any
Java exception description
</reason>
</rejected>
</rejectedrecords>

[Lasse Koskela]

This sounds very much like a homework assignment... We're generally not in favour of people solving others' homeworks for them. You should really ask more specific questions in order to get answers.

[clyde melly]

I was just asking to help.I don't want anyone to write code. I asked how do i go about as I'm new to using xml.Once i'm on track i can go my own.Its simple plain english.Lasse r u listening.

[Lasse Koskela]

First of all, welcome to the JavaRanch! (I just realized it was your first post)

How do I go about.New to xml.Using Xerces parser.Plz help.Thanx in advance.

In short, read some tutorials on parsing XML with Java. Here are some I found with a little Googling:
Xerces Java: Quick Start
Simplify XML programming with JDOM
Easy Java/XML integration with JDOM
SAX Tutorials
IBM DeveloperWorks' XML tutorials
When you're wondering how to do something specific, like "how can I convert a Document object to a String?", turn to the JavaRanch or the Java Developer's Almanac.

Write a Servlet using Java 1.3.x (but NOT Java 1.4) to read an xml file from the virtual directory /test and validate it as follows:
- The Servlet should be called TestParser
- the filename is passed in the parameter xmlfile using the post method
e.g xmlfile will have a value of file1.xml
- the file must be validated against the XML Schema –

You need to implement the servlet's doPost(...) method for handling a HTTP POST request, locate the file based on the filename returned by HttpServletRequest#getParameter("xmlfile"), and parse the file using either the SAX or DOM API. IBM DeveloperWorks has a tutorial for validating against a schema using Xerces (see the link above).

...any validation errors should be EMAILED from the server to the account [e-mail], the from address should be [e-mail], the subject should be “XML Parser Error in File: “+filename
- The body should contain details of the error, including the line at which the error occurred and the XML file that failed to validate should be attached to the email.

For this part, you need to use the JavaMail API. A good tutorial can be found here. You'll get the line of the parsing error from SAXParseException#getLineNumber() and the error message from SAXParseException#getMessage().

Write a Servlet using Java 1.3.x (but NOT Java 1.4) called DBTable to do the following: ...

This part is really about JDBC so you should post it separately to our JDBC forum.
...and the last question you should post only after you've gotten the first two working. At that point, you probably won't have that many questions to ask in the first place.

[Pauline McNamara]

Actually, his last question went like this:

Its simple plain english.Lasse r u listening.

Yes, Lasse is definitely listening, and answering too. Nice.
[ September 06, 2003: Message edited by: Pauline McNamara ]

[clyde melly]

thanx.Now i can start.Wud really require ur help.I certainly hope that u won't disappoint.

[clyde melly]

Hi lasse,
This is the first part.
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.io.IOException;
import org.xml.sax.XMLReader;
import org.xml.sax.SAXException;

public class SaxServXml extends HttpServlet {
static String str,f;
public void doPost(HttpServletRequest req, HttpServletResponse res)
throws IOException
{

str=req.getParameter("tt");

res.setContentType("text/html");

PrintWriter out = res.getWriter();


out.println("mm" +str);


ret(str);


}

public void ret(String s)
{
f=s;

if(f==null){
System.out.println("Usuage : not Working"+ f);
System.exit(0);
}
XMLReader reader=new org.apache.xerces.parsers.SAXParser();
System.out.println("Start parsing : " + f);
try
{
reader.parse(f);
System.out.println("End parsing : ");
}
catch(IOException e){
System.out.println("error reading" + e.getMessage());
}
catch(SAXException e){
System.out.println("error reading" + e.getMessage());
}
}


}

This is the second part :
I'm working from home.As I don't have an email server so just want to know this is the way of going about.Will work on the sax exceptions once done with this stuff.
import javax.mail.*;
import javax.mail.internet.*;
import java.util.*;
public class Mail
{
public void postMail( String recipients[ ], String subject, String message , String from) throws MessagingException
{
boolean debug = false;
//Set the host smtp address
Properties props = new Properties();
props.put("mail.smtp.host", "ABC");
// create some properties and get the default Session
Session session = Session.getDefaultInstance(props, null);
session.setDebug(debug);
// create a message
Message msg = new MimeMessage(session);
// set the from and to address
InternetAddress addressFrom = new InternetAddress(from);
msg.setFrom(addressFrom);
InternetAddress[] addressTo = new InternetAddress[recipients.length];
for (int i = 0; i < recipients.length; i++)
{
addressTo[i] = new InternetAddress(recipients[i]);
}
msg.setRecipients(Message.RecipientType.TO, addressTo);

// Optional : You can also set your custom headers in the Email if you Want
msg.addHeader("MyHeaderName", "myHeaderValue");
// Setting the Subject and Content Type
msg.setSubject(subject);
msg.setContent(message, "text/plain");
Transport.send(msg);
}
static public void main(String g[])
{
Mail m=new Mail();
try
{
m.postMail("[e-mail]","Hi","Ok","[e-mail]",ServerName);
}catch(Exception e){}
}
}
The html file
<html>
<body bgcolor="#ccffff">
<form action="http://localhost:8080/servlet/SaxServXml" method="post"/>
<input type="text" name="tt"/>
<br><br/>
<input type="submit" value="OK"/>
</body>
</html>
Kindly let me know if i'm going right.thanx

[Lasse Koskela]

Looks alright. You're going into the right direction, I guess.
By the way, you really should use the [CODE] tags for pretty-printing the code snippets...
[ September 18, 2003: Message edited by: Lasse Koskela ]

[Donald R. Cossitt]

if(f==null){
System.out.println("Usuage : not Working"+ f);
System.exit(0);
}

Is it appropriate to use "System.exit( 0 ) ; "?
Do you run the risk of shutting the server down?
(Question asked out of newbie ignorance) :roll:

[Lasse Koskela]

Is it appropriate to use "System.exit( 0 ) ; "?
Do you run the risk of shutting the server down?

Oh, I missed that one. I agree. It's not something you'd want to do!

[clyde melly]

Java program to read and parse the agents.xml file and then display the agent details in either IATA number or name order. The file and ordering should be passed into the program as command-line parameters.
import org.apache.xerces.parsers.*;
import org.w3c.dom.*;
import java.util.*;
import org.xml.sax.SAXException;
import org.xml.sax.SAXNotRecognizedException;
import org.xml.sax.SAXNotSupportedException;
import java.io.IOException;

public class domsort
{
DOMParser parser = new DOMParser();
static int j,k,a;
static int arr[] = new int[10];

private static void step (Node n)
{
System.out.println(n.getNodeName()+" :: "+n.getNodeValue());


String val=n.getNodeValue();


try
{
if(val!=null)
{
arr[j]=Integer.parseInt(val);

System.out.println("ival " +arr[j]);

j++;
}


}catch(Exception e){}

if (n.getNodeType() == n.ELEMENT_NODE)
{
System.out.println("Node type is " +n.getNodeType()+" @@ "+n.ELEMENT_NODE);

}
for (Node child = n.getFirstChild();
child != null;
child = child.getNextSibling())
{
step(child);
}
}

public domsort(String fname)
{
try
{
DOMParser parser=new DOMParser();
parser.parse(fname);
Document doc=parser.getDocument();
Element elm=doc.getDocumentElement();

step(elm);
}
catch(Exception e)
{
System.out.println("Error msg"+e.toString());
}
}
/*Sorting the values */
public void sort()
{
Arrays.sort(arr);
for(a=0;a<arr.length;a++)
{

System.out.println("val : " + arr[a]);

}
}
public static void main(String g[])
{


if(g.length>0)
{
String str=g[0];
System.out.println("Start parse : " + g[0]);
//String str1=g[1];
domsort dome=new domsort(str);
dome.sort();


}
}
}
How do I pass the order using the argument??what will g[1] refer to??
when i run the program
java filename xmlfile ?
----------------------------------------------------------------------------
Next question
The schema file should be:
agents is the root element
file can contain one or more agent elements
iatanumber must have a value and must be numeric
name must have a value
trading address must exist
line 1, postcode, region and country must all have values
the trading address elements must all exist, even if they are empty and must exist in the order listed.
legaladdress must exist
line 1, postcode, region and country must all have values
the trading address elements must all exist, even if they are empty and must exist in the order listed.

The schema :
<?xml version="1.0"?>
<xs:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xs:element name="agents" type="agType"/>
<xs:complexType name="agType">
<xs:sequence>
//for agent node
<xs:element name="agent"
minOccurs="1"
maxOccurs="unbounded"/>
<xs:complexType>
<xs:sequence>

//for tradingaddress node
<xs:element name="tradingaddress"
minOccurs="1"
maxOccurs="unbounded"/>
<xs:complexType>
<xs:sequence>

<xs:element name="line1" use="required" type="xsd:string"/>
<xs:element name="line2" type="xsd:string"/>
<xs:element name="postcode" use="required" type="xsd:string"/>
<xs:element name="region" use="required" type="xsd:string"/>
<xs:element name="country" use="required" type="xsd:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
//tradingaddress closed
<xs:attribute name="iatanumber" use="required" type="xs:integer"/>
<xs:attribute name="name" use="required" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
//agent closed
</xs:sequence>
</xs:complexType>
</xs:element>
//agents closed
</xs:schema>
Is the above schema correct.Now for legaladdress node I have to again do the same way as I did for tradingaddress.But then the schema will grow in size.Is there any other way out.

[Lasse Koskela]

How do I pass the order using the argument??what will g[1] refer to??
when i run the program
java filename xmlfile ?

g[0] will contain "xmlfile", g[1] will throw an ArrayIndexOutOfBounds exception because the array is only 1 cell long.

[clyde melly]

Yes lasse I Know.But as per the question the second argument here i refer to g[1].how do i pass that.so the iatanumber is displayed in order.Also what regarding the schema stuff?

[Lasse Koskela]

Are you asking how to pass the ordering method as g[1]?
java filename xmlfile method
For example, if the user wants to sort by name, she types
    java MyApplication foobar.xml name
and if she wants to sort by IATA number, she types
    java MyApplication foobar.xml iata
Then you get either "iata" or "name" as g[1] and figure out which sorting method to use.
Am I (again) answering to the wrong question?

[clyde melly]

No Lasse the code which i have written thru that how do i pass the second argument.like g[0] was the filename,same case g[1] wud be either iata or name.just go thru the question once again.let me know whether i'm going right or i need to go the other way.and plz let me know regarding the schema stuff.thanx

[clyde melly]

Hi lasse,i'm just losing track.Require your help.Just let me know where i'm going wrong.

[Lasse Koskela]

Here's a working sample for parsing your XML file into a list of Agent objects, sorting the list according to user input, and printing the sorted list into System.out.

package xml;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.Iterator;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import org.w3c.dom.*;
import org.xml.sax.SAXException;

public class ClydeMelly
{
    public static void main(String[] args)
    {
        try
        {
            String filename = args[0];
            String sorting = args[1];
        
            ClydeMelly clydeMelly = new ClydeMelly();
            clydeMelly.displayResults(filename, sorting);
        }
        catch (Throwable t)
        {
            t.printStackTrace();
        }
    }
    
    private void displayResults(String filename, String sortingOrder) 
        throws ParserConfigurationException, IOException, SAXException
    {
        // parse the XML into a DOM tree
        Document doc = parseXml(filename);
        
        // convert the DOM tree into domain objects
        ArrayList agentObjects = convertDocumentIntoDomainObjects(doc);
        
        // sort the domain objects according to user preference
        java.util.Collections.sort(agentObjects, new AgentComparator(sortingOrder));
        
        // print out the domain objects in order
        Iterator iterator = agentObjects.iterator();
        while (iterator.hasNext())
        {
            Agent agent = (Agent) iterator.next();
            System.out.println(agent.toString());
        }
    }
    
    private Document parseXml(String filename)
        throws ParserConfigurationException, IOException, SAXException
    {
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();
        Document doc = builder.parse(new File(filename));
        return doc;
    }
    
    private ArrayList convertDocumentIntoDomainObjects(Document doc)
    {
        ArrayList domainObjects = new ArrayList();
        
        Element root = doc.getDocumentElement();  // root: "<agents>"
        NodeList agentElements = root.getChildNodes();
        for (int i=0; i < agentElements.getLength(); i++)
        {
            Node agentElement = agentElements.item(i);
            if (agentElement.getNodeType() != Node.ELEMENT_NODE)
            {
                // ignoring element due to wrong node type
                continue;
            }
            
            String name = agentElement.getNodeName();
            if (name != null && name.equals("agent"))
            {
                // ok, the node we have here is <agent> so lets convert it
                // into the corresponding domain object (of type Agent)
                domainObjects.add(convertAgentElementIntoObject((Element)agentElement));
            }
        }
        
        return domainObjects;
    }
    
    private Agent convertAgentElementIntoObject(Element agentElement)
    {
        Agent agent = new Agent();
        agent.setName(agentElement.getAttribute("name"));
        agent.setIataNumber(agentElement.getAttribute("iatanumber"));
        return agent;
    }
}
class Agent
{
    private String iataNumber;
    private String name;
    
    // other properties omitted for clarity
    
    public String toString()
    {
        return getName() + " (" + getIataNumber() + ")";
    }
    
    public String getIataNumber()
    {
        return iataNumber;
    }
    public String getName()
    {
        return name;
    }
    public void setIataNumber(String string)
    {
        iataNumber = string;
    }
    public void setName(String string)
    {
        name = string;
    }
}
class AgentComparator implements Comparator
{
    private static final int IATA = 1;
    private static final int NAME = 2;
    private static final int DEFAULT = NAME;
    private int sortingOrder;
    
    public AgentComparator(String sortingOrder)
    {
        if (sortingOrder.equals("iata"))
        {
            this.sortingOrder = IATA;
        }
        else if (sortingOrder.equals("name"))
        {
            this.sortingOrder = NAME;
        }
        else
        {
            this.sortingOrder = DEFAULT;
        }
    }
    
    public int compare(Object obj1, Object obj2)
    {
        if (obj1 instanceof Agent && obj2 instanceof Agent)
        {
            Agent agent1 = (Agent) obj1;
            Agent agent2 = (Agent) obj2;
            if (this.sortingOrder == IATA)
            {
                return agent1.getIataNumber().compareTo(agent2.getIataNumber());
            }
            else
            {
                return agent1.getName().compareTo(agent2.getName());
            }
        }
        return 0;
    }
}

[Lasse Koskela]

Oh yeah, here are the commands to run that thing.

// sorted by name
java -classpath <...> xml.ClydeMelly agents.xml name
// sorted by iata number
java -classpath <...> xml.ClydeMelly agents.xml iata

[clyde melly]

Thanx Lasse.But I didn't want the code.I wanted to work on it myself.Ok what regarding the schema stuff.Lasse wud be greatful if u can suggest some good sites on xml and servlets and books on them.I have to finish this assignment in a day or two for a job opening.Just taking time as do not have much exposure to using java with xml.Hope u understand

[Lasse Koskela]

Could you post what you have so far regarding the schema?
(please use the UBB CODE tag and indent the XML/XSD appropriately...)
I'm not that good with schemas but I could crunch a working .xsd file with brute force (by experimenting). It's really not that difficult when you have access to the w3schools.org XSD Tutorial and an online XSD validator.

Now for legaladdress node I have to again do the same way as I did for tradingaddress.But then the schema will grow in size.Is there any other way out.

For this part, you can define the complexType for an address separately and just refer to it like this:

<xs:element name="tradingaddress" type="addressType"/>
<xs:element name="legaladdress" type="addressType"/>
<xs:complexType name="addressType">
    <-- specify the address element's contents here -->
</xs:complexType>

[ September 24, 2003: Message edited by: Lasse Koskela ]

[clyde melly]

How to populate a Microsoft Access database with the agents details and to read the data from the database in the correct order for displaying.
Will it involve first insert and then selecting.Is there another way around
for inserting my xml file into the database.

[Lasse Koskela]

That question should be asked in the JDBC Forum.

[clyde melly]

agents is the root element
file can contain one or more agent elements
iatanumber must have a value and must be numeric
name must have a value
trading address must exist
line 1, postcode, region and country must all have values
the trading address elements must all exist, even if they are empty and must exist in the order listed.
legaladdress must exist
line 1, postcode, region and country must all have values
the trading address elements must all exist, even if they are empty and must exist in the order listed.
the schema :

<?xml version="1.0"?>
<xs:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xs:element name="agents" type="agType"/>
<xs:complexType name="agType">
<xs:sequence>
//for agent node
<xs:element name="agent"
minOccurs="1"
maxOccurs="unbounded"/>
<xs:complexType>
<xs:sequence>
//for Address node
<xs:element name="Address" type="addressType"
minOccurs="1"
maxOccurs="unbounded"/>
<xs:complexType name="addressType">
<xs:sequence>
<xs:element name="line1" use="required" type="xsd:string"/>
<xs:element name="line2" type="xsd:string"/>
<xs:element name="postcode" use="required" type="xsd:string"/>
<xs:element name="region" use="required" type="xsd:string"/>
<xs:element name="country" use="required" type="xsd:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
//Address closed
<xs:element name="tradingaddress" type="addressType"/>
<xs:element name="legaladdress" type="addressType"/>
<xs:attribute name="iatanumber" use="required" type="xs:integer"/>
<xs:attribute name="name" use="required" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
//agent closed
</xs:sequence>
</xs:complexType>
</xs:element>
//agents closed
</xs:schema>
Is this the correct way for a schema.

[Lasse Koskela]

No. That isn't even valid XML, let alone a valid schema document. For example, the starting and closing tags don't match and you have used "// ..." for comments, which does not work in the XML world.
Btw, you can also use the STG XML Validation Form for checking your schema document's conformance to XML. The STG validator might sometimes give you better error messages than the XSD Schema Validator.

[Lasse Koskela]

Some tips:

Indent your XML elements properly and you'll notice the element encapsulation errors immediately

Use "xs:" or "xsd:", not both, and make sure it's the same prefix as you have defined in the root element

The attribute "use" can only be applied to attributes, not elements

[clyde melly]

I know that // comments don't work up.Just used for clarity purpose.I am using xerces parser.Can i use it for schema stuf or i have to use the one which u mentioned.Regarding the jdbc stuff,I have put up the code on both servlet and jdbc forum.But no responses.Have to finish the assignment quickly or else will lose a good job opportunity

[Lasse Koskela]

Xerces supports XML Schema just fine.
I still suggest you should use the CODE tags and indent your code snippets. They are very difficult to read without no formatting whatsoever...

[clyde melly]

Lasse checked out the inser stuff.But i went the other way around.Just have to work on that no duplicate records are added into the database.No replies on jdbc,servlet stuff.Really appreciate ur kind help.Others r hardly bothered.A at on the back from my side. .Have to finish this assignment today or else will miss out on a good opportunity

[clyde melly]

Lasse sending u the code along with the questions on your email account coz i don't want to make the code public.Hope u understand.Some r in working condition,and in rest the main thing is regarding the java mail stuff and schema stuff.Before I submit the assignment I just want to check it with u coz u have been guiding me thru this whole assignment and I would say that without your help and inputs it wudn't be possible.Just help me out with some of the questions where after the code i have mentioned the problem.These progs have to be in running condition and I have to submit it today.Wud really be greatful to u in ur sincere efforts.Thanc

[Lasse Koskela]

First of all, this kind of emails are often ignored completely. The reason being that questions answered in private only benefit one person and that effort could've been used to help several 'ranchers if done in public...
For this once, I did take a look at what you sent and I'm commenting it here.
Regarding the schema definition:
(many of these I've already mentioned earlier)

Use xsd or xs, not both

Make sure that elements are correctly encapsulated (indent your document and you'll see what I mean)

An <element> cannot have a type attribute if it has a body defining a complexType

A <complexType> cannot have a name attribute if it is encapsulated within an element definition

minOccurs and maxOccurs attributes can only be used when defining a sequence

The use attribute can not be used when declaring the attribute, only when referring to it

You can't refer to a type named "addressType" if you haven't defined one. You'll need to define a top-level complexType element with name"addressType" before referring to it.

Creating the error message:

/**
 * This method creates an error message based on the exception thrown 
 * by SAXParser.parse()
 */
public static String createParsingErrorMessage(SAXException e) {
    StringBuffer sb = new StringBuffer();
    if (e instanceof SAXParseException) {
        // it was a parsing exception so we'll include both the description and the linenumber on which the error occurred
        SAXParseException pe = (SAXParseException) e;
        sb.append("Parsing failed on line ").append(pe.getLineNumber());
        sb.append(": ").append(pe.getMessage());
    } else {
        // if the exception is not related to parsing we can't access the line number
        sb.append("Parsing failed: ").append(e.getMessage());
    }
    return sb.toString();
}

Regarding the attachment:
I already referred you to the JavaMail tutorial's "Working with attachments" section. They even have a working sample. The only change you need to make is to implement your own DataSource object which works with an in-memory string instead of a file.
Regarding the database stuff:

Your INSERT statement is missing quotes around the first parameter

Your UPDATE statement is also missing quotes around parameters

You can get a list of all table names with connection.getMetaData().getTables(), if that's what you meant

If you can get all of these fixed on time, come back and I'll see if I can spend more of my time on this.

[clyde melly]

lasse u misunderstood me.U were the person who helped me throughout.Others hardly even bothered,forget into even looking into it.See my query in the servlets and jdbc stuff.Lasse i just wanted u to go through the code and let me know if things are alright.The main thing is that I am doing it at my home and barring u i haven't got any help from anyone around.I am having serious problems with
JavaMail basically coz I dont have access to an smtp server.And working on this for the first time.
Schemas.Just check and let me know the errors.
Insert parts where duplicates are not allowed
Lasse please help me out.I am a novice with xml stuff and it is a learning experience for me.If possible just see those servlet and java mail question where the schema validation errors have to be sent as attachment.Alos on this duplicate stuff.Wud really appreciate and it u wud play a very important part if I do get this job.

[Lasse Koskela]

I dont have access to an smtp server.

You can download Apache James for free. Download, unpack, double-click run.bat...

Schemas.Just check and let me know the errors.

I just gave you a list of the mistakes in the schema document you emailed me. Once you've fixed those, the schema should be fine.

Insert parts where duplicates are not allowed

The easiest way to do this would be to make the "iatanumber" field a primary key. Then, trying to insert a record with an existing iatanumber will throw an exception, which you can catch and append into the list of errors (which you will later add into the attachment).
Here's some code to explain what I mean:

/**
 * This method tries to insert the given list of records into the 
 * database and returns an error message if any of the insertions failed.
 * 
 * @param recordList The list of records to insert.
 * @return The error message including all failed records, or null if
 *         everything was inserted ok.
 */
public String insertRecords(List recordList) {
    StringBuffer errors = new StringBuffer();
    Iterator records = recordList.iterator();
    while (records.hasNext()) {
        // assuming that the Record object represents whatever you're trying 
        // to INSERT into the database
        Record record = (Record) records.next();
        try {
            // this method call inserts the given record into the database
            insertRecordToDatabase(record);
        } catch (SQLException e) {
            errors.append("Inserting record failed").append("\n");
            errors.append(stackTraceToString(e)).append("\n");
            errors.append("Record causing the error:").append("\n");
            errors.append(record.toString()).append("\n\n");
        }
    }
    // return either 'null' or the complete error message
    if (errors.getLength() == 0) {
        return null;
    } else {
        return errors.toString();
    }
}

Good luck!

[clyde melly]

Lasse
Time is running out and as u mentioned about the mail server,don't have the time for it.
The main problem is that this is my first experience and haven't worked much on java mail,schemas stuff.
For the servlet part I have to fetch the table name.
I.e when i pass the parameter(database name, in this case agentpro.mdb) then how do i retrieve the table name.
i.e
<input tpye="text" value="tt"/>
Now i put the path in the text box,c:\clyde\agentpro.mdb
Now when i do a String str=request.getParameter("tt") i will get the path.Now from this path i need to retrieve the table name.

For the servlet again the schema validation and java mail part.I just can't get to put all together.That is why if there are some sample working programs,wud help me to put all together.Schema and the mail part r the most important and once I get to see a program in running and the complete execution and flow,then will i get grip of it.Hope u'll help me out.Thanx

[Lasse Koskela]

Now when i do a String str=request.getParameter("tt") i will get the path.Now from this path i need to retrieve the table name.

I don't get it. What do you mean by "retrieving the table name"? Shouldn't you know the table name already? If this is something specific to MS Access then I'm afraid I can't help with that.

[clyde melly]

As per the question have to pass the database name.Its easy if i pass the table name.What regarding the sample programs Lasse.Plz help.Servlets,javamail and schema question.Some working sample code.

[Lasse Koskela]

I'm afraid I don't have time to write working sample code myself but you could try to scavenge the samples/demo/whatever directory of the JavaMail download. There must be some examples for sending email with attachments if the online example I gave earlier isn't clear. Also you might find examples by Googling for sample code.

[clyde melly]

Lasse if you have any links or sample examples related to them do let me know.The problem is putting things together as i had earlier mentioned.Once I have worked on a running example with all these(servlets,schema and mail then) wud I be able to work on the other questions.I hope i'm making myself clear.And now the client has asked me to test and run it on the smtp server
which u mentioned(JAMES).Plz help

[clyde melly]

Write a Servlet to do the following:
As per the question
The Database name will be supplied in the parameter dbname - this will be passed using either the Get or Post methods so your servlet should cater for both?
Query : Lasse this is what I was asking for ie "database name will be passed".
-----------------------------------------------------------------------
Next Question:
Check for the existence of the MySQL database table agents and if it exists, delete it

Query : I do execute the query rs.executeQuery("drop table agents") but get an exception that No resultset was produced.
-----------------------------------------------------------------------
Once this is done I have to create a new Database table
Query : Now I will have to keep a flag boolean value coz the next time I execute it should not drop this agents table.This is the correct way or any other way out.
-----------------------------------------------------------------------
Next Question:
The table should not allow duplicates - and should reject only the duplicate entries - continuing to insert the valid entries.
Statement stmt=con.createStatement();
int results=stmt.executeUpdate("insert into ord(oid) values(97)");
Query : results will give 1 for insertion."oid" is primary key in the database,so when I will try to insert this value again I will get an error and my no.of record rejected is 1.But after re - running the program how will I get the total number of accepted and rejected records?
-----------------------------------------------------------------------
Still have to work on the schema and mail attachment part.All the programs have to be in working condition or else it does not make any use.Thanx

[Lasse Koskela]

I do execute the query rs.executeQuery("drop table agents") but get an exception that No resultset was produced.

a DROP statement is not a "read" operation but a "write" operation so you need to use executeUpdate("DROP TABLE AGENTS") instead.

Now I will have to keep a flag boolean value coz the next time I execute it should not drop this agents table.This is the correct way or any other way out.

If you can't see a better way during implementation, this will do just fine.

But after re - running the program how will I get the total number of accepted and rejected records?

Obviously you need to keep a counter somewhere. If you're making the INSERT in a while/for loop, you need to do something like

...
int successfulInserts = 0;
int failedInserts = 0;
while (...) {
    try {
        int inserted = doInsert(...);
        if (inserted == 1) {
            successfulInserts++;
        } else {
            failedInserts++;
        }
    } catch (SQLException e) {
        failedInserts++;
    }
}
...

[clyde melly]

Lasse what regarding the database name the first question.Any sample stuff on schema and attachment,something similiar to my question.Will be helpful