RE: Using XML to access data from AS400

Hi Amit

May be this could help you
http://www-106.ibm.com/developerworks/edu/x-dw-xextract-i.html

The sequel for this is 
http://www-106.ibm.com/developerworks/edu/x-dw-xinsert-i.html

For all tutorials

http://www-106.ibm.com/developerworks/views/xml/tutorials.jsp

Best Regards
Rizwan

-----Original Message-----
From: Alex Pinsky [mailto:apinsky@xxxxxxxxxxxxxxx] 
Sent: Friday, April 18, 2003 8:25 PM
To: Java Programming on and around the iSeries / AS400
Subject: RE: Using XML to access data from AS400


Hi Amit,
Here the code example:

import java.io.*;
import java.sql.*;
import java.text.*;
import java.util.*;
import org.jdom.*;
import org.jdom.output.*;

public class WebStylesCreateXML {

  private ResultSet rs = null;
  String rootElmnt = "yourrootname";
  protected XMLOutputter xmlOut = new XMLOutputter(" ", true);

  protected Document doc = null;
  protected Element elRoot = null;;
  protected Element elItem = null;
  protected Element elCls = null;
  protected Element elVen = null;
  protected Element elSty = null;
  protected Element elClr = null;
  protected Element elSku = null;
  protected Element elDes = null;

  protected PrintWriter pw = null;
  protected SimpleDateFormat mmddyyyy = new SimpleDateFormat("MM/dd/yyyy
hh:mm aaa");


  public WebStylesCreateXML() {
    // create as/400 connection and get ipstore records
    Connection connection   = null;
    // get yearAgo date value
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    Calendar c = Calendar.getInstance();
    c.roll(Calendar.YEAR, -1);
    java.util.Date d = c.getTime();
    String yearAgo = sdf.format(d);
    System.out.println("yearAgo value is:" +yearAgo);
    try {
      // Load the IBM Toolbox for Java JDBC driver.
      DriverManager.registerDriver(new
com.ibm.as400.access.AS400JDBCDriver());

      // Get a connection to the database.
      connection = DriverManager.getConnection
("jdbc:as400://youras400name;date format=iso", "as400userid",
"as400password");
      System.out.println("Start WebStylesCreateXML processing.");
      System.out.println("got connection.");
      DatabaseMetaData dmd = connection.getMetaData ();

      // Execute the query.
      Statement stmt = connection.createStatement ();
      String sqlString = "SELECT icls, iven, isty, iclr, isku, ides FROM
"+
                         "iptsfil.ipithdr where idiv >= 40 and ilai >
'"+yearAgo+"' order by icls, isty, iclr";
      rs = stmt.executeQuery (sqlString);
      System.out.println("got query result. Start XML processing");

      createXMLFile();

      stmt.close();
      connection.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  public void createXMLFile() {

    if (rs == null) {
      System.out.println("Unable to get ResultSet, stop processing");
      return;
    }
    java.util.Date today = new java.util.Date();
    String sToday = mmddyyyy.format(today);
    elRoot = new Element(rootElmnt);
    elRoot.setAttribute("builddate", sToday);
    doc = new Document(elRoot);
    int cntr=0;
    try {
      pw = new PrintWriter(new BufferedWriter(new
FileWriter("/QIBM/ProdData/dir1/dir2/webstyles.xml")));

      while (rs.next()) {
        String cls = rs.getString(1).trim();
        String ven = rs.getString(2).trim();
        String sty = rs.getString(3).trim();
        String clr = rs.getString(4).trim();
        String sku = rs.getString(5).trim();
        String des = rs.getString(6).trim();
        
        elItem = new Element("Item");

        elCls = new Element("Class");
        elCls.addContent(cls);
        elVen = new Element("Vendor");
        elVen.addContent(ven);
        elSty = new Element("Style");
        elSty.addContent(sty);
        elClr = new Element("Color");
        elClr.addContent(clr);
        elSku = new Element("Sku");
        elSku.addContent(sku);
        elDes = new Element("Description");
        elDes.addContent(des);

        // add content to element store
        elItem.addContent(elCls);
        elItem.addContent(elVen);
        elItem.addContent(elSty);
        elItem.addContent(elClr);
        elItem.addContent(elSku);
        elItem.addContent(elDes);

        // add content to root element
        elRoot.addContent(elItem);

        cntr++;

      }  // end for loop of ResultSet
      elRoot.setAttribute("stores", String.valueOf(cntr));
      System.out.println("Processed " +cntr+ " records");
      xmlOut.output(doc, pw);
      pw.flush();
      pw.close();

    } catch(Exception ioE) {
      ioE.printStackTrace();
    }
  }

  public static void main(String[] arg) {
    WebStylesCreateXML process = new WebStylesCreateXML();
  }
}

Hope this help,
Alex

-----Original Message-----
From: Amit Shukla [mailto:AShukla@xxxxxxxxxxxxxxxxxxx]
Sent: Friday, April 18, 2003 9:27 AM
To: 'Java Programming on and around the iSeries / AS400'
Subject: RE: Using XML to access data from AS400


Hi Aaron,

I am using Java(JT400.jar/JDBC-ODBC) to extract the info from DB2
database, once the databse info is available in the ResultSet it should
be able to write an in-memory DOM document Object then this information
should be written to an XML file. So thats the idea.

Any suggestions/questions??

Amit.

-----Original Message-----
From: Bartell, Aaron L. (TC) [mailto:ALBartell@xxxxxxxxxxxxxx]
Sent: Thursday, April 17, 2003 2:54 PM
To: 'Java Programming on and around the iSeries / AS400'
Subject: RE: Using XML to access data from AS400


What languages are you familiar with?  Or rather, what language do you
want it in?

How do you want to make the XML available? Put it into the IFS, via CGI
over HTTP, or something else?

There are many ways to skin this cat.  Maybe if you could give us some
more information as to what you are trying to accomplish. . .


Aaron Bartell

-----Original Message-----
From: Amit Shukla [mailto:AShukla@xxxxxxxxxxxxxxxxxxx]
Sent: Thursday, April 17, 2003 10:55 AM
To: 'JAVA400-L@xxxxxxxxxxxx'
Subject: Using XML to access data from AS400


Hi All,

I need help with accessing data from AS400 database DB2. We have some
database files in AS400 (DB2) which has to be retrieved/accessed and
that data should be available in an XML file. Please send me some sample
code of XML to do this, if available.

Thanks a lot for your help,

Amit. 
_______________________________________________
This is the Java Programming on and around the iSeries / AS400
(JAVA400-L) mailing list To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo.cgi/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.
_______________________________________________
This is the Java Programming on and around the iSeries / AS400
(JAVA400-L) mailing list To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo.cgi/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.
_______________________________________________
This is the Java Programming on and around the iSeries / AS400
(JAVA400-L) mailing list To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo.cgi/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.


_______________________________________________
This is the Java Programming on and around the iSeries / AS400
(JAVA400-L) mailing list To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo.cgi/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.