Alternatives to Using Supplied Resources

Documentation home

See also: Summary of Supplied Resources

Direct access to a database using JDBC. 1

Reading XML document with Java and E4X. 2

Reading XML document with XML Resource adapter then process with E4X. 3

This document discusses techniques for accessing databases and XML documents without using the supplied Verj.io resources - Database Resources and XML Resources – and without using field mappings. In general, this results in more scripting code but has the advantage that it doesn’t use any mappings. Whether to use Database Resources and XML Resources or the techniques described here is largely a matter of personal choice.

The examples below show reading data – both from databases and XML documents – but the same techniques can equally be used to write data. Also, the XML examples show reading data from file, but could also be used to read documents from any source.

Direct access to a database using JDBC

The Java JDBC API can be used from a Javascript script to execute and process SQL statements. Note that it is not necessary to issue commits – database connections obtained using system.getDatabaseConnection() are automatically committed by the system, and any updates are automatically rolled back if there is a failure.

In the following example, we are loading the table CustomerTable from the database table customer. The table is defined with no resource and four columns:

Here is the script to load the table from the database using JDBC:

importPackage(com.ebasetech.xi.api);

importPackage(com.ebasetech.xi.services);

var LOAD_CUSTOMER_SQL = "select * from customer order by name";

// Get a database connection

var con = system.getDatabaseConnection("CUSTOMERDATA");

var stmt;

var rs;

try {

// execute the SQL statement

stmt = con.prepareStatement(LOAD_CUSTOMER_SQL);

rs = stmt.executeQuery();

// loop through the results - add a new row to the Customer table for each database record

while (rs.next()) {

populateCustomerTable(rs);

}

}

// This finally clause is very important - all result sets, statements and connections must be closed

// Failure to do this can result in the system running out of database connections.

finally {

if (rs) rs.close();

if (stmt) stmt.close();

if (con) con.close();

}

function populateCustomerTable(rs)

{

var customerTable = tables.CustomerTable;

// insert a row in the customer table and make this the current row

customerTable.insertRow();

// extract database data

customerTable.id.value = rs.getString("customer_id");

customerTable.name.value = rs.getString("name");

customerTable.city.value = rs.getString("city");

customerTable.credit_limit.value = rs.getString("credit_limit");

}

Reading XML document with Java and E4X

This example shows reading an XML document from file and loading the content into a table. The XML document looks like this:

<Schedule>

<Flights>

<Flight>

<FlightNo>XA123</FlightNo>

<Departure>08:00</Departure>

<From>London</From>

<To>Paris</To>

</Flight>

<Flight>

<FlightNo>XA127</FlightNo>

<Departure>10:30</Departure>

<From>Washington</From>

<To>New York</To>

</Flight>

</Flights>

</Schedule>

And this is read into the Flights table:

Here is the script to read the XML file and extract the content into the Flights table. This uses Java to load and parse the XML document (see function parseXml) and the Javascript E4X API to navigate through the document:

importPackage(com.ebasetech.xi.api);

importPackage(com.ebasetech.xi.services);

importPackage(javax.xml.parsers);

var xmlRoot = parseXml("../../samples/xml/flights.xml");

// extract the flights data from the XML document

var flightsRoot = xmlRoot.Flights;

for each (var fl in flightsRoot.Flight)

{

var flightsTable = tables.Flights;

flightsTable.insertRow();

flightsTable.FlightNo.value = fl.FlightNo.toString();

flightsTable.Departure.value = fl.Departure.toString();

flightsTable.From.value = fl.From.toString();

flightsTable.To.value = fl.To.toString();

}

function parseXml(filePath)

{

// load and parse the XML document

var factory = DocumentBuilderFactory.newInstance();

factory.setNamespaceAware(true);

var builder = factory.newDocumentBuilder();

var document = builder.parse(filePath);

// return an E4X XML object

return new XML(document);

}

Reading XML document with XML Resource adapter then process with E4X

This example shows reading the same XML document as the previous example, but this time we will use the file adapter of an XML Resource to load and parse the document, then extract the contents using E4X as per the previous example.

The XML document and the Flights table are the same as the previous example. The XML resource named FlightsXml contains an empty XML document flightsDoc and a file adapter as follows:

The script code is very similar to the previous example – the difference is in the parseXml function:

importPackage(com.ebasetech.xi.api);

importPackage(com.ebasetech.xi.services);

var xmlRoot = parseXml("flightsDoc");

// extract the flights data from the XML document

var flightsRoot = xmlRoot.Flights;

for each (var fl in flightsRoot.Flight)

{

var flightsTable = tables.Flights;

flightsTable.insertRow();

flightsTable.FlightNo.value = fl.FlightNo.toString();

flightsTable.Departure.value = fl.Departure.toString();

flightsTable.From.value = fl.From.toString();

flightsTable.To.value = fl.To.toString();

}

function parseXml(docName)

{

// read the XML document using the XML Resource’s file adapter

resources.FlightsXml.read();

// now extract the document from the resource

var doc = resources.FlightsXml.getDocument(docName);

// return an E4X XML object

return new XML(doc);

}