JDBC scripting with JudoScript, Part 1

Introducing JudoScript, a functional scripting language that works well with JDBC

1 2 Page 2
Page 2 of 2
function printResultsetAsCSV outfile, rs, sep, closeOnExit {
  if outfile == null {
    outfile = getSysOut();
    closeOnExit = false;
  }
  rsmd = rs.getResultSetMetaData();
  cnt = rsmd.getColumnCount();
  // Print headers
  for i from 1 to cnt {
    if i>1 { print <outfile> sep; }
    print <outfile> rsmd.getColumnName(i);
  }
  println <outfile>;
  // Print results
  while rs.next() {
    for i from 1 to cnt {
      if i>1 { print <outfile> SEP; }
      print <outfile> rs[i];
    }
    println <outfile>;
  }
  if closeOnExit { outfile.close(); }
}
// Try it out
connect to dbUrl, dbUser, dbPass;
executeQuery qry: SELECT * FROM emp;
printResultsetAsCSV openTextFile('result.csv', 'w'), qry, ',', true;

You have now seen what you can do with query results. The code is intuitive enough even though we have not covered programming yet. I present a case study in Part 2, and you can adapt the code samples for your practical uses.

Calling the result set's methods

As we know, in JDBC, column values are accessed via the result set object's various getXXX() methods. JudoScript allows you to access columns as properties via their names or indices, but you can still call any method. For instance, suppose a table has a LONG column in Oracle:

CREATE TABLE error_log(
  log_id   INTEGER PRIMARY KEY,
  note     LONG,
  encoding VARCHAR(30)
);

You can call getBytes() to get the bytes and convert them to text:

executeQuery qry:
  SELECT * FROM error_log;
;
while qry.next() {
  bytes = qry.getBytes('note');
  println '========== ', qry.log_id, ' ==========', nl,
          encode(bytes, neverEmpty(qry.encoding, 'UTF8'));
}

If we accessed the column with qry.note, the result will differ depending on the RDBMS. By calling qry.getBytes('note'), we know precisely what we are doing. Also, calling the result set's methods is the only way to access nonstandard JDBC-driver-specific features.

Now that we have learned how to handle query results, let's move on to running SQL update statements.

Directly execute SQL updates

SQL updates are simpler than queries, because the only return value is an update count. SQL update statements include UPDATE, INSERT, and DELETE. The following example shows how to execute an UPDATE:

executeUpdate upd:
  UPDATE SET salary = 55000 WHERE salary < 50000
;
println unit(upd.getResult(), 'person has', 'people have'), ' got raise.';

In the code, unit() is a utility function. If the first parameter is 1, it returns the second parameter; otherwise it returns the plural form, which is the third parameter if specified, or the second parameter plus an s.

So far, we have run SQL statements as-is. Next, we will see how to run parametized SQL statements.

Prepare and execute SQL

The JDBC specification supports running parametized SQL statements, that is, SQL statements with question marks as placeholders for parameters. Parametized SQL statements are prepared first and can be run multiple times by binding values to the parameters. Another reason to prepare SQL statements before running is that, if the SQL statement is expected to run repeatedly even without parameters, preparing it first allows the JDBC driver and/or database server to pre-parse and optimize it. In JudoScript, the prepare statement is used for both SQL queries and updates; prepared queries are executed via executeQuery and updates via executeUpdate, respectively, like this:

prepare qry: SELECT emp_no, salary FROM emp WHERE salary<?;
prepare upd: UPDATE SET salary=? WHERE emp_no=?;
// Give a 10% raise for those earning less than 50,000
executeQuery qry with @1:number = 50000;
while qry.next() {
  executeUpdate upd with @1:number = qry.salary * 1.10,
                         @2:int    = qry.emp_no;
}
commit(); // Assuming auto-commit turned off.

The syntax to bind a parameter is the with clause and @n:type, where n is the bind parameter index starting at 1, and type is one of the following: boolean, byte, date, double, float, int, long, number, short, String, varchar, struct, array, bytes, bigDecimal, blob, clob, time, timestamp, numeric, ref, bit, longvarchar, other, java_object, oracle_rowid, oracle_cursor and oracle_bfile. By default, if :type is not specified, it is assumed to be String. In fact, many JDBC drivers, such as the Oracle JDBC driver, can take a string bind value for most types.

Execute database scripts as-is

We saw earlier that executeSQL { ... } can execute multiple SQL statements. SQL statements in the block are delimited by a semicolon (;). Sometimes semicolons are legitimate text to be sent to the database. In such situations, use the executeAny statement; all text is sent to the database server as-is. The following example creates an Oracle stored procedure that we will use in the next section:

executeAny [[*
  CREATE PROCEDURE test_proc(
    param_io IN OUT NUMBER,
    param_i  IN     VARCHAR,
    param_o  OUT    VARCHAR)
  AS BEGIN
    param_o := param_i;
    IF param_io IS NOT NULL THEN
      param_io := param_io + 1;
    ELSE
      param_io := -1000;
    END IF;
  END;
*]];

The [[* *]] syntax is used to quote a chunk of text that may include new lines. The leading spaces in each line are stripped, so you can indent them in the source, making your code look nice. You can also embed expressions with (* *) syntax.

Invoke stored procedures

Major RDBMSs support stored procedures. JDBC defines a standard syntax for calling stored procedure like this:

{ ? = call foo(?,?,?) }

The parameters, unlike regular SQL, can be IN, OUT, and IN OUT.

Let's call the procedure created in the previous executeAny example. It returns a value in parameter param_io and passes the value of param_i to param_o. This is the code:

prepareCall: { call test_proc(?,?,?) };
x = null;
y = 'abcd';
executeSQL with @1:int <=> x,
                @2:varchar =  y,
                @3:varchar => z; // z will be the same as y
println 'x = ', x;  // Prints: x = -1000
println 'z = ', z;  // Prints: z = abcd

Conclusion

In this first half of this tutorial, you have learned that the idea of using Java for practical uses by the general public—not just Java developers—has led to the inception of the JudoScript language. I have detailed JudoScript's JDBC scripting support, which helps to illustrate the fact that good domain-specific support within a general-purpose language greatly helps users focus on the problems, resulting in code that is more intuitive, productive, and elegant. The topic of JDBC scripting also demonstrates the usefulness and resourcefulness of the Java platform for the general public, and shows that a good tool like JudoScript can unlock Java's huge potentials and possibly extend Java's territory beyond Java programmers.

In Part 2 I I will present a case study that uses JDBC scripting, Java scripting, XML scripting, and other goodies to address a real-world problem in a J2EE environment. Lastly, I discuss a few general programming language aspects and compare JudoScript to other languages.

James Jianbo Huang is the author of the JudoScript language. Huang holds an MS in electronics, and favors creating software and solutions. He enjoys music, teaching, and sports but does not practice judo.

Learn more about this topic

This story, "JDBC scripting with JudoScript, Part 1" was originally published by JavaWorld.

Copyright © 2004 IDG Communications, Inc.

1 2 Page 2
Page 2 of 2