Alternatives to Using Supplied Resources
See also: Summary of Supplied Resources
Direct
access to a database using JDBC
Reading
XML document with Java and E4X
Reading
XML document with XML Resource adapter then process with E4X
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.
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");
}
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>
<To>
</Flight>
<Flight>
<FlightNo>XA127</FlightNo>
<Departure>10:30</Departure>
<From>
<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);
}
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);
}