Thursday, November 29, 2001

JudoScript - a Dynamic Form of Java

UPDATE (2007-11-21) JudoScript is still around and still as easy to use as ever. Groovy has a lot of mindshare but JudoScript is simpler to use, I think. I've found an interesting meta-langauge for Java called JudoScript. Here is the first paragraph of that page:
JudoScript is a full-fledged, modern scripting language, built on and for the Java Platform. It seamlessly interacts with Java objects, does most operating system shell tasks with many bells and whistles, supports easy and versatile JDBC scripting, XML scripting, built-in scheduler and many other niceties.

Wednesday, November 28, 2001

How to Parse XML Using Oracle And PLSQL

After a bit of research, I've figured out how to parse xml using Oracle9i. The process is a bit more involved than Oracle8i but there is more functionality. The following bit of PL/SQL code will parse the following XML:

<r session="sess01" updator="medined" object="1005769145473">
 <f n="PMA_APPROVAL_MEANS" v="Identicality Per FAR 21.303(c)"/>
</r>

You'll need to place the following code into context that makes sense for your projects, but here is the gist:

-- declare attributes
p          xmlparser.Parser;
doc        xmldom.DOMDocument;
element    xmldom.DOMElement;
facts      xmldom.DOMNodeList;
node       xmldom.DOMNode;
attributes xmldom.DOMNamedNodeMap;

-- parse the xml packet
p := xmlparser.newParser;
xmlparser.setValidationMode(p, FALSE);
xmlparser.parseBuffer(p, v_xml_packet);
doc  := xmlparser.getDocument(p);

BEGIN
  element := xmldom.getDocumentElement(doc);
EXCEPTION
  RAISE;
END;

v_session := xmldom.getAttribute(element, 'session');
v_updator := xmldom.getAttribute(element, 'updator');
v_object  := xmldom.getAttribute(element, 'object');

facts := xmldom.getChildrenByTagName( element, 'f');
FOR j IN 1..xmldom.getLength(facts) LOOP

        node := xmldom.item(facts, j-1);
        attributes := xmldom.getAttributes(node); 

        if (xmldom.isNull(attributes) = FALSE) then
          num_attributes := xmldom.getLength(attributes);

          -- loop through attributes
          for i in 0..num_attributes-1 loop
             node := xmldom.item(attributes, i);
             attribute_name := xmldom.getNodeName(node);
             if attribute_name = 'n' then
                 v_fact_name := xmldom.getNodeValue(node);
             end if;
             if attribute_name = 'v' then
                 v_fact_value := xmldom.getNodeValue(node);
             end if;
          end loop;

          ---- do some processing here.

       end if;
END LOOP;

Monday, November 19, 2001

UNSPSC codes are four sets of two characters. Each pair of character helps to define a category in a hierarchical manner. So the left pairs (like 10 00 00 00) define a very broad category while the right pairs (like 11 23 44 02) defines a very narrow category. Giving any category code, you can find its parent category code via the following java code:
// Move the unspsc code into a stringbuffer so it can be manipulated
StringBuffer uc = new StringBuffer(unspsc_code);

// replace digits 7 & 8 with zeros.
// if code has changed then that's the parent category
// The setCharAt function uses a zero-based index.So the
// range is 0 to 7 instead of 1 to 8.
uc.setCharAt(7, '0');
uc.setCharAt(6, '0');

if (! unspsc_code.equals(uc.toString())) {
  System.out.println(uc + " is the parent category");
}
else {
  // replace digits 5 & 6 with zeros.
  uc.setCharAt(5, '0');
  uc.setCharAt(4, '0');
  if (! unspsc_code.equals(uc.toString())) {
    System.out.println(uc + " is the parent category");
  }
  else {
    // replace digits 3 & 4 with zeros.
    uc.setCharAt(3, '0');
    uc.setCharAt(2, '0');
    if (! unspsc_code.equals(uc.toString())) {
      System.out.println(uc + " is the parent category");
    }
    else {
      System.out.println(uc + " is a top-level category.");
    }
  }
}			

Sunday, November 18, 2001

I need to perform timing studies with the various JDBC drivers. For example, inet software has a set of drivers.

Friday, November 16, 2001

Java JDBC Metadata Holds Query Column Names

  private Connection mAccess;
  private Statement mStatement;
  private ResultSet mResultSet;
  private ResultSetMetaData mResultSetMetaData;

  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  mAccess = DriverManager.getConnection("jdbc:odbc:pma_boeing");

  mStatement = mAccess.createStatement();
  ResultSet mResultSet = mStatement.executeQuery("Select top 10 * from boeing");

  mResultSetMetaData = mResultSet.getMetaData();
  int TotalColumn = mResultSetMetaData.getColumnCount();

  for (int j = 1; j <= TotalColumn; j++) {
    System.out.println("[" + j + "] " + mResultSetMetaData.getColumnName(j));
  }

Thursday, November 15, 2001

More time studies today using JDBC to move records from MS Access to Oracle. With an Oracle Insert taking 25 msecs and the XML creation taking 1 msec, each object starts out taking 26 msecs. When the whole program (reading records, starting threads, database connections, etc.) was run, each object took 149 msecs to process. After I adding a database connection pool, each object took 109 msecs. Which is still too long. My goal is 36 msecs to process 100,000 objects per hour.

Wednesday, November 14, 2001

Java Oracle Stored Procedure Processing XML

After I reinstalled the plsql xdk on Oracle 8i, my jdbc stored procedure started working again. Now I can perform some additional time studies. My technique is flexible since I'm passing an xml packet to the procedure. The procedure loops over all of the attributes in the packet and inserts each one into the data repository. My xml looks like this:
<r session="sess01" updator="medined" object="1005769145473">
 <f n="PMA_APPROVAL_MEANS" v="Identicality Per FAR 21.303(c)"/>
 <f n="PMA_MANUFACTURER" v="Boeing"/>
</r>
Each f tag defines one fact, or attribute about an object. The object attribute of the r tag links all of the attributes together. The session attribute lets me know which objects were loaded together.
SQL to find the number of potential open cursors in Oracle: select value,name from v$parameter where name like 'open_cursors'

Tuesday, November 13, 2001

Spent two hours at the Northern Virginia Java User's Group listening to a talk on Enterprise Java Beans (EJBs). While I can see the value, I worry that the EJB servers are essentially trying to replicate the basics of a database in middleware. For example, there is a EJB Query language that is very much like SQL Lite. And the Deployment XML file holds the relationships between Entity objects (also known as database tables). While EJB 2.0 allows for complex object-to-relational mappings, the technology is very new. How much of Oracle or SQL Server needs to be replicated inside the middleware server? Why should it be necessary to pay for developing the same functionality in the middleware and in the database? On another note, I've decided that the Nested Tree Model (NTM) package that I'm developing for Oracle needs to be more independant. I currently have the module tightly coupled with a Data Repository package for table creation, auditing, and action validation. Tomorrow, I'll decouple.
My first entry. I've gotten Oracle to transform xml from its own ROWSET/ROW format into ColdFusion's query object WDDX. This means that I can encapsulate all SQL inside Stored Procedures and Functions which may or may not be a good thing.