This chapter provides a complete listing and reference for the methods in the OpenScript UtilitiesService Class of Utilitis Module Application Programming Interface (API).
The following section provides an alphabetical listing of the methods in the OpenScript UtilitiesService API.
The following table lists the UtilitiesService API methods in alphabetical order.
Table 22-1 List of UtilitiesService Methods
| Method | Description |
|---|---|
|
Gets a file service instance for this Utilities service. |
|
|
Gets a SQL service instance for this Utilities service. |
|
|
Load the given comma-separated-value file as InputStream into a |
|
|
Load a specified XML file as InputStream into an XML tree. |
|
|
Load a specified XML contents into an XML tree. |
|
|
Convenience method to create a list of parameters for SQL a query. |
|
|
Saves a table to a CSV file format. |
The following sections provide detailed reference information for each method and enum in the UtilitiesService Class of Utilitis Module Application Programming Interface.
Gets a file service instance for this Utilities service. The FileService provides methods for working with the file system in general.
The utilities.getFileService method has the following command format(s):
utilities.getFileService( );
Creates a new file and appends the text strings to the file.
utilities.getFileService().createDestinationFile("myFile.txt", false); String line1 = "This is a new line 1"; String line2 = "This is a another new line 2"; String contents = "\n" + line1 + "\n" + line2; utilities.getFileService().appendStringToFile("myFile.txt", contents);
Gets a SQL service instance for this Utilities service. SQLService provides methods for working with SQL databases.
The utilities.getSQLService method has the following command format(s):
utilities.getSQLService( );
Create a database connection, execute SQL statements and query database, then disconnect the database connecton.
//define database utilities.getSQLService().define("myOracleDB", "oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@mySystem:1521:mySystem", "username", decrypt("ZSL2IzF3WpLh8ydBZYDV3Q==")); //connect to database utilities.getSQLService().connect("myOracleDB"); //execute SQL statement String query = "Create table Employee (ID number(4) not null unique, " + "FirstName varchar2(40) not null, LastName varchar2(40) not null, " + "Country varchar2(40), HireDate date)"; info("Query: " + query); utilities.getSQLService().execute("myOracleDB", query); //execute update SQL statement query = "Insert into Employee (ID, FirstName, LastName, Country, HireDate) " + "Values (101, 'Tom', 'Smith', 'USA', '01-JAN-95')"; utilities.getSQLService().executeUpdate("myOracleDB", query); //query SQL statement query = "Select * from Employee"; Table table = utilities.getSQLService().query("myOracleDB", query); //print table for (int i=0; i<table.getRowCount(); i++) { Row row = table.getRow(i); String [] rowValue = row.getAll(); String rowContent = ""; for (int col=0; col<rowValue.length; col++) rowContent += rowValue[col] + " "; info(rowContent); } //disconnect from database utilities.getSQLService().disconnect("myOracleDB");
Load the given comma-separated-value file as InputStream into a Table object.
The utilities.loadCSV method has the following command format(s):
utilities.loadCSV(filePath);
utilities.loadCSV(filePath);
utilities.loadCSV(url);
utilities.loadCSV(fileInput);
utilities.loadCSV(repository, pathRelToRepository);
a File object specifying the file containing CSV-formatted data.
a String specifying the name of repository containing CSV-formatted data file. May contain {{ }} syntax for transforming.
String specifying the path to the CSV-formatted data file within the named repository. May contain {{ }} syntax for transforming. Must not be null. Any leading file separator on the path, such as / or \, is ignored.
a URL object specifying the path to the CSV file.
a FileInputStream.
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
on any exception while loading and reading the CSV file.
Loads a CSV file into a Table object and prints the contents to the Results view.
String filePath = "c:\\fmstocks_data.csv";
InputStream in = null;
in = new FileInputStream(new File(filePath));
Table table = utilities.loadCSV(in);
//Print the CSV file
String columns = "";
int columnNumber = table.getColumns().getColumnCount();
String [] columnNames = table.getColumns().getColumnNames();
for (int index=0; index<columnNumber; index++)
columns += columnNames[index] + " ";
info(columns);
List <Row> rows = table.getRows();
for (int index=0; index<rows.size(); index++) {
String [] rowValue = rows.get(index).getAll();
String rowContent = "";
for (int columnIndex=0; columnIndex<rowValue.length; columnIndex++)
rowContent += rowValue[columnIndex] + " ";
info(rowContent);
}
Load a specified XML file as InputStream into an XML tree.
The utilities.loadXML method has the following command format(s):
utilities.loadXML(filePath);
utilities.loadXML(filePath);
utilities.loadXML(url);
utilities.loadXML(fileInput);
utilities.loadXML(repository, pathRelToRepository);
a String specifying the file path to be loaded. May contain {{ }} syntax for transforming.
a String specifying the name of repository containing XML file to load. May contain {{ }} syntax for transforming.
String specifying the path to the XML file within the named repository. May contain {{ }} syntax for transforming. Must not be null.Any leading file separator on the path, such as / or \, is ignored.
a URL object specifying the URL.
a FileInputStream.
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
if the file is not found.
if an error occurs loading the XML.
Loads an XML file into a XML object and prints the contents to the Results view.
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
//[...]
//load XML file by InputStream
String filePath = "c:\\myxmlfile.xml";
InputStream in = null;
in = new FileInputStream(new File(filePath));
XML xml = utilities.loadXML(in);
info(xml.toString());
info("*** getChildren() ***");
XML [] children = xml.getChildren();
String lsChild = "";
for (int i=0; i<children.length; i++) {
if (i != 0)
lsChild += ", ";
lsChild += children[i].getTagName() + " ";
}
if (lsChild.equals(""))
info("No child present of xml element " + xml.getTagName());
else
info("List of all child elements of xml element " +
xml.getTagName() + ": " + lsChild);
for (int i=0; i<children.length; i++) {
XML child = children[i];
// print tag name
String tagName = child.getTagName();
info("Tag Name: " + tagName);
//print attributes
String [] allAttr = child.getAttributeNames();
if (allAttr != null) {
for (int j=0; j<allAttr.length; j++) {
info("Attribute Name: " + allAttr[j]);
info("Attribute Value: " + child.getAttribute(allAttr[j]));
}
}
else
info("No attribute");
// print values
if (child.getValue() != null) {
info("Value: " + child.getValue());
}
// print parent
info("Parent: \n" + child.getParent());
}
Load a specified XML contents into an XML tree.
The utilities.loadXMLContent method has the following command format(s):
utilities.loadXMLContent(contents);
The XML contents to be loaded.May contain {{ }} syntax for transforming.
represents an exception that may be thrown during the execution of a script where the exception should be reported to an end user through the controller.
if an error occurs loading the XML.
Loads an XML string into an XML object and prints the contents to the Results view.
//load XML file by file name string.
String contents = "<GroceryStore><StoreName>Fourth Coffee</StoreName>" +
"<Departments><Department Name=\"Breads\">" +
"<Item ID=\"B2\" Type=\"Muffin\"><Name>Blueberry Muffin</Name>" +
"<Price>3.99</Price><New/></Item>" +
"</Department></Departments></GroceryStore>";
XML xml = utilities.loadXMLContent(contents);
info(xml.toString());
Convenience method to create a list of parameters for SQL a query.
The utilities.parameters method has the following command format(s):
utilities.parameters(obj, param);
a List<Object> of parameters for SQL a query.
Optional list of parameters for the SQL query List<Object>.
Creates a database connection, execute SQL statements and query database with parameters object, then disconnect the database connecton.
//define database
utilities.getSQLService().define("myOracleDB",
"oracle.jdbc.driver.OracleDriver",
"jdbc:oracle:thin:@mySystem:1521:mySystem", "username",
decrypt("ZSL2IzF3WpLh8ydBZYDV3Q=="));
//connect to database
utilities.getSQLService().connect("myOracleDB");
//execute SQL statement
String query = "Create table Employee (ID number(4) not null unique, " +
"FirstName varchar2(40) not null, LastName varchar2(40) not null, " +
"Country varchar2(40), HireDate date)";
info("Query: " + query);
utilities.getSQLService().execute("myOracleDB", query);
//execute update SQL statement
query = "Update Employee SET LastName = 'Davis'
WHERE ID = ? and LastName = ?";
info("Query: " + query);
utilities.getSQLService().executeUpdate("myOracleDB", query,
utilities.parameters(101, "Smith"));
//query SQL statement
query = "Select * from Employee";
Table table = utilities.getSQLService().query("myOracleDB", query);
//print table
for (int i=0; i<table.getRowCount(); i++) {
Row row = table.getRow(i);
String [] rowValue = row.getAll();
String rowContent = "";
for (int col=0; col<rowValue.length; col++)
rowContent += rowValue[col] + " ";
info(rowContent);
}
//disconnect from database
utilities.getSQLService().disconnect("myOracleDB");
Saves a table to a CSV file format.
The utilities.saveCSV method has the following command format(s):
utilities.saveCSV(table, csvFilename, overwrite);
utilities.saveCSV(table, repository, pathRelToRepository, overwrite);
a Table object.
a String specifying the full path and name of a file where the Table is saved. May contain {{ }} syntax for transforming.
a Boolean that specifies whether or not to overwrite an existing file.
a String specifying the name of repository where the Table is to be saved. May contain {{ }} syntax for transforming. Must not be null.
String specifying the path to the file within the named repository.May contain {{ }} syntax for transforming. Must not be null. Any leading file separator on the path, such as / or \, is ignored.
Saves a CSV Table object to a new file in overwrite mode
String repository = "Default";
String pathRelToRepository = "Data/fmstocks_data.csv";
String newCSVFile = "new_fmstocks_data.csv";
boolean overwrite = true;
Table table = utilities.loadCSV(repository, pathRelToRepository);
utilities.saveCSV(table, repository, pathRelToRepository, overwrite);