JDBC scripting with JudoScript, Part 2

A real-world case study demonstrates the power and flexibility of JDBC scripting with JudoScript

In the first half of this article I introduced JudoScript and its JDBC scripting abilities. In this second half I'll present a demonstration of those abilities in a JDBC scripting case study. I summarize the differences between conventional and functional scripting languages. I use criteria like signal/noise ratio, directness, coherency, and focus to compare code written in JudoScript with languages like Java and Perl, where Java is arguably one of the best general-purpose programming languages and Perl is the conventional scripting language that no other has surpassed in terms of capability.

[ Also see: What is JDBC? Introduction to Java Database Connectivity ]

Introduction to programming in JudoScript

The world already has too many programming languages. Inventing a new syntax for programming truly demands reasons. JudoScript, being a general-purpose programming language as well, simply adopts a JavaScript-like programming model and syntax, which is not bad at all. However, JudoScript has many sophisticated datastructures, thread programming, object-oriented programming, and syntactic sugar—it is a powerful programming language. JudoScript is also a top-of-the-line Java scripting language, capable of scripting Java to the fullest allowed by JVMs, including capabilities to extend Java classes and implement Java interfaces using JudoScript classes. I start by introducing the basics of JudoScript programming.

Values in JudoScript all have types. JudoScript has primitive types of integer, double, string, and date and time; all other values are objects, which can be built-in type objects or any Java object. JudoScript is a dynamically typed language, meaning that variables are generic "containers," and the value held in a variable at any time has a definitive type. JudoScript supports object-oriented programming, so you can define your own classes of objects. Each type, whether a primitive value, built-in datastructure, or extraneous object, has numerous properties and a set of predefined methods that are accessed and invoked in the same way regardless of type. Here are some examples:

a = 12345;
println a, ', HEX: ', a.fmtHex(), ', Roman: ', a.fmtRoman();
d = Date(2004, 3, 26);
println 'Today (', d.fmtDate('yyyy-MM-dd'), '), week of the year is ',
lst = new java::ArrayList;
println "List's length: ", lst.size();

Flow controls include if-elif-else, switch-case-default, while, do-while, and the for-family statements. Unlike Java, the conditional expression for if and while does not have to be quoted in parentheses, but curly braces ({}) must always surround the bodies, even if the body contains just one statement.

Functions and user classes can be defined. I don't discuss class definition in this article. Functions can take parameters; when called, the number of parameters can be less or more than the number of declared parameters. Missing parameters are assumed undefined, and extras are stored in the predefined local array variable, $$args. Functions are always expected to return a value; if no explicit return is used, undefined returns.

Exceptions can be thrown and caught like this:

{ connect to 'fail it', 'any', 'any';
  println $_; // $_ is the exception object thrown.
  println 'Finally!';

Or, you can use this format:

try {
  connect to 'fail it', 'any', 'any';
} catch ex { // If ex is not specified, $_ is used.
  println ex;
} finally {
  println 'Finally!';

Next, let's dig a little deeper by looking at some of JudoScript's built-in data types.

Built-in data types

JudoScript's built-in data types include primitive values; the Array, LinkedList, Set, Object, and TableData objects; and the for..in and printTable statements.

The primitive values

JudoScript's built-in data types include primitive types and datastructures. Primitive types are integer, floating-point number, string, and date and time. All types share many methods; some methods are number-, string-, or date-time-specific. Most number-specific methods are mathematical functions, conversions (e.g., int()), and formats (e.g., fmtHex(), and fmtRoman()). The string value is the most commonly used; it has all the string operations. Moreover, it represents file paths and URLs, so methods exist for those operations, such as fileExists(), fileTime(), isDir(), and parseUrl(). The date/time value has additional methods such as formatDate(). The date/time value also has fields; some are read-only, others are read-write. The following example gets a Date object for tomorrow:

t = Date(); // Now
println t.fmtDate('yyyy-MM-dd');

The fmtDate() (or formatDate()) method takes the same formatting string as the java.text.SimpleDateFormat class. For a variable holding a date or time string, you can call parseDate():

s = '2003-9-18';
t = s.parseDate('yyyy-MM-dd');
println t.fmtDate('MMM d, yy'); // Prints: Sep 18, 03

I discuss some of the detailed usages of primitive types later. Please refer to the language reference for more details.

The Array, LinkedList, and Set objects

Array can be created in two ways, both consistent with JavaScript syntax:

a = [];
a = [ 1, 'xyz', Date() ];
a = new Array;
a = new Array( 1, 'xyz', Date() );

JudoScript arrays are all dynamic, i.e., you can call an array's add() method to add elements. Array elements can be retrieved and set with the [] operator; the index starts at 0.

LinkedLists behave the same as arrays, except the underlying implementation uses linked lists.

Another similar datastructure is the Set object. It can also be created in two ways:

a = LinkedList[];
a = LinkedList[ 1, 'xyz', Date() ];
a = new LinkedList;
a = new LinkedList( 1, 'xyz', Date() );
a = Set[];
a = Set[ 1, 'xyz', Date() ];
a = new Set;
a = Set( 1, 'xyz', Date() );

Sets do not support access by index.

The for..in statement

The for..in statement iterates many JudoScript and Java datastructures, including arrays, linked lists, sets, Java arrays, and instances of these Java classes/interfaces: java.util.Iterator, java.util.Enumeration, java.util.Collection, java.util.List, and java.util.Map. All JudoScript loops support an intrinsic function, loopIndex(), which can prove useful at times. The following example prints an array's elements as a CSV (character-separated value):

a = [ 1, 'xyz', Date() ];
for x in a {
  if loopIndex() > 0 { print '\t'; }
  print x;

Arrays (including LinkedList) have practical methods. You can sort, filter, and convert an array flexibly, with user-defined comparison, filtering, and transformation function objects.

The Object object

Object is compatible with the name-sake JavaScript object and is the parent of any user-defined class. It is a map that stores name-value pairs. An Object can be created in two ways:

a = {};
a = { city='San Jose', state='CA' };
a = new Object;
a = new Object( city='San Jose', state='CA' );

Values can be accessed via names or expressions:

a = new Object;
a.city  = 'San Jose';
a.state = 'CA';
println 'City:  ', a.city;
x = 'state';
println 'State: ', a.(x);

Object has many methods, some of which are quite powerful, such as keys(), keysSorted(), keysSortedByValues(), keysFiltered(), and keysFilteredByValues().

The TableData object and the printTable statement

A TableData object is a 2D array with a title. The title is an array of strings. A row number references each row; within a row, the value can be referenced by either the column index or the column name. You can sort or filter the rows based on a specific column or columns, and, of course, you can get or set individual cells. TableData has a special print statement, printTable.

The example of the tableDesc() function presented in Part 1 demonstrates the use of printTable well; the code is repeated here:

function tableDesc tableName, dbcon {
  if dbcon == null { dbcon = $$con; }
  executeQuery qry use dbcon:
     SELECT * FROM (* tableName *) WHERE 0 > 1
  println [[*
    Name            Type       Display  Precision   Scale Nullable Class
                    Name          Size                             Name
  printTable qry.getColumnAttributes()
         for column('name')               :<16,
             column('type')               :<10,
             column('displaySize')        :>8,
             column('precision')          :>11,
             column('scale')              :>8,
             column('nullable').fmtBool() :>9,
             ' ', column('className'), nl; // Newline
// Try it out
connect to dbUrl, dbUser, dbPassword;
tableDesc 'emp';

The qry.getColumnAttributes() call returns a TableData, which has columns like name, type, and scale. The column() expression in that statement denotes a value for this column. The :<16 is a print alignment expression (meaning left-aligned, width of 16).

As you can see, JudoScript uses the JavaScript syntax and programming model, but is much more powerful than JavaScript. If you deem algorithmic programming as a domain, then JudoScript also supplies rich domain support for programming just like JDBC scripting and other functional domain support. There are many interesting topics and details I can't afford to go into in this article; please refer to the language reference and other resources to learn more. Next I tackle another important programming topic, Java scripting.

Java scripting

JudoScript can create and use Java objects, arrays, and class objects. Java objects and arrays are created with the same new operator but in a special namespace called java. To access a Java class, use the java:: operator:

a = new java::java.awt.Dimension(20,30);
a = new java::int[4];
a = new java::int[] { 1, 2, 3, 4 };
c = java::java.sql.Types;
println c.BOOLEAN;

JudoScript supports the same Java import statement for easier resolution of Java class names. The java.lang.*, java.io.*, and java.util.* packages are implicitly imported. Thus, we could use System and Class directly in the previous example.

Java arrays and java.util.List instances share operations with JudoScript arrays:

a = new java::int[] { 1, 2, 3, 4 };
for x in a {
  println 'a[', loopIndex(), '] = ', x;
a = new java::Vector;
a[0] = 'A';
a[9] = 'J';
for x in a {
  println 'a[', loopIndex(), '] = ', x;

JudoScript also supports extending Java classes and implementing Java interfaces. The following is an example:

class MySetIterator extends java::HashSet, Iterator
  Iterator iter;
  constructor a, b, c {
    iter = null;
    if c != null { add(c); }
    if b != null { add(b); }
    if a != null { add(a); }
  // Iterator methods
  boolean hasNext() {
    if iter == null {
      iter = iterator(); // of HashSet.
    return iter.hasNext();
  Object next() {
    return (iter==null) ? null : iter.next();
o = new MySetIterator('Hi!', 9);
for x in o { println x; }

This class looks a bit odd: it is somewhat like a Java class and somewhat like a JudoScript class. In fact, this is a dynamically generated Java class with fields (e.g., iter) and methods (e.g., hasNext() and next()). Fields and methods in such classes are always public. The constructor and the method bodies are JudoScript code, and the new operator creates new instances. More rules govern such classes; please refer to Resources to learn more.

Useful mechanisms for data processing

Now let's look at some of the JudoScript mechanisms that will help you process data.

The print statements

JudoScript has println, print, and flush statements. All three can take any number of parameters and print to System.out, System.err, or text files. println automatically flushes. The following simple menu system illustrates the println and flush statements:

println <err> [[*
  0) Clobber All
  1) Build All
  2)   Generate Parser
  3)   Build Base
  4)   Build Extension
  5) Create Shipment
  6) Archive All
  x) Exit
flush <err> 'Enter your choice: '; // Don't use print!
opt = readLine();
switch opt {
case 0:  clobberAll(); break;
case 1:  buildAll();   break;
case 2:  genParser();  break;
case 3:  buildBase();  break;
case 4:  buildExt();   break;
case 5:  shipIt();     break;
case 6:  archive();    break;
default: exit 0;

JDBC scripting frequently prints reports. The print statements' formatting facility is easy and effective:

x = 100;
println x :<10;

The :<10 expression tells the print statement to print the value aligned to the left, with a width of 10. Similarly, you can write :>10 for a right-aligned value. For floating-point numbers, you can align numbers along the decimal point:

dollars = [ 0.10, 1000.981, 40.496 ];
for d in dollars {
  println d :*8.2;

The result is:


And you can repeat a string by specifying the repeating factor in {} following the item:

x = 'Underline This';
println x, nl, '-' {x.length};

nl is a literal for newline. The result is:

Underline This

Working with spreadsheets (CSVs)

Spreadsheets can be easily exported into CSVs for processing. The string type has a csv() method that parses a CSV into an array. Text files can be easily read in via the do..as lines statement. The following example has two functions; one loads a CSV into a table, the other downloads table data into a CSV:

1 2 3 Page 1
Page 1 of 3