JudoScript.COM Design principles of Judo the sport and the language
HomeJudo LanguageJuSP PlatformJamaica Language 
Judo ReferenceJuSP ReferenceWiki/WeblogTutorials/PresentationsDownloadsGoodiesFeedback  
Book: The Judo Language 0.9
 

Search

 

 By FreeFind.com











In this chapter:

Chapter 22. JDBC (SQL) Scripting

By James Jianbo Huang

printer-friendly version
Synopsis: Judo's JDBC scripting support is one of its key features and major inspirations. There are direct syntactical support to run SQL statements directly, preparedly or in batches. You can easily bind variables and process query results. Judo allows named bind variables within SQL in addition to question marks (?). You can conveniently invoke stored procedures and issue database commands. JDBC scripting, or SQL scripting, allows IT workers to easily handle data issues, produce reports and automate and integrate backend testing with enterprise applications.

 
Introduction to JDBC Scripting   to be done

Java, JDBC and RDBMS


SQL, JDBC and JDK Versions


JDBC SQL Data Types


JDBC and ODBC


SQL/JDBC Scripting in Judo

SQL statements are generally partitioned into data definition language (DDL) and data manipulation language (DML). DDL are the statements that creates and destroys the database schema objects; the statements are: CREATE and DROP. DML includes queries (the SELECT statement) and updates (the INSERT, UPDATE and DELETE statements.) When DDL statements are run, they eigher succeed or fail. In the world of Java, a failure is signified by an exception raised. DML statements may succeed or fail; in case of success, the RDBMS returns results. For updates, the results are simply counts that indicate how many rows are affected; for queries, the results can be complicated and big.

There are three main families of statements in Judo that run SQL statements: db::query, db::update and db::sql. SQL statements in JDBC can be run directly, or be prepared first before run. The db::prepare statement explicitly prepares a SQL statement; all three execute-family statements can run both prepared and unprepared SQL. The db::query statements are for SQL queries, most notably the SQL SELECT statement but occasionally some others as well. The db::query statements are for DML update statements: UPDATE, INSERT and DELETE. These statements return a single value as the count of affected rows. Lastly, the db::sql statements are for DDLs, i.e., CREATE and DROP, and for DML updates that you don't really care about the results (such as INSERT's.)

SQL is not case sensitive. In this book, we use this convention: any SQL keywords are in all-upper-case; data object and attribute names are in lower or mixed case. For instance, SELECT salary FROM emp. In this chapter, we focus on explaining all the facilities in Judo for JDBC scripting, and use Oracle as the RDBMS. Most SQL statements are generic and applicable to any RDBMSs, except for situations such as date/time functions and store procedures.



 
Connections and Drivers

Before any database operations, you must connect to the database. This is done by the connect command. This is its syntax:

Connect ::= db::connect [ [ Variable ] [ attributes ] to ] url [ , username [ , password ] ]
attributes ::= ( attribute ( , attribute )* )
attribute ::= ( driver | autoCommit | readOnly | catalog | typeMap | transactionIsolation ) = Expr
url ::= Expr
username ::= Expr
password ::= Expr

For example, the following statement connects to an Oracle database using its "thin" driver, and assigns the connection into the variable mycon.

db::connect mycon to 'jdbc:oracle:thin:@dbsvr:1521:dbname', 'user', 'pass';

The connection variable is used by subsequent database commands like this:

db::sql use mycon { ... }

If the variable is omitted, the db::connect command will store the connection object into a predefined global variable $$con. Database commands without explicit use clause all use $$con. Most programs are single-connection, so you don't have to explicitly use a connection variable name.

The connection object has a number of methods; the most important ones are disconnect(), commit() and rollback(). The disconnect() method must be called when the database operations are finished. The system functions db::disconnect(), db::commit() and db::rollback() all call the corresponding methods of the $$con object. We will discuss more database-wide operations using the connection object in the section Database Meta-Information and Utilities.

JDBC Drivers

How does Judo load the JDBC driver? Judo maintains a list of known JDBC driver class names and their corresponding URL prefixes in the properties file com/judoscript/db/jdbcdrivers.properties. The following is an excerption:

cloudscape      = COM.cloudscape.core.JDBCDriver
db2             = COM.ibm.db2.jdbc.net.DB2Driver
easysoft        = easysoft.sql.jobDriver
firebird        = org.firebirdsql.jdbc.FBDriver
frontbase       = jdbc.FrontBase.FBJDriver
hsqldb          = org.hsqldb.jdbcDriver
idb             = org.enhydra.instantdb.jdbc.idbDriver
informix-sqli   = com.informix.jdbc.IfxDriver
interbase       = interbase.interclient.Driver
jdbcprogress    = com.progress.sql.jdbc.JdbcProgressDriver
jtds            = net.sourceforge.jtds.jdbc.Driver
mckoi           = com.mckoi.JDBCDriver
microsoft       = com.microsoft.jdbc.sqlserver.SQLServerDriver
msql            = com.imaginary.sql.msql.MsqlDriver
mysql           = com.mysql.jdbc.Driver,org.gjt.mm.mysql.Driver
nilostep        = com.nilostep.xlsql.jdbc.xlDriver
odbc            = sun.jdbc.odbc.JdbcOdbcDriver
openbase        = com.openbase.jdbc.ObDriver
oracle          = oracle.jdbc.driver.OracleDriver
pointbase       = com.pointbase.jdbc.jdbcUniversalDriver
postgres95      = postgres95.PGDriver
postgresql      = org.postgresql.Driver,postgresql.Driver
sapdb           = com.sap.dbtech.jdbc.DriverSapDB
solid           = solid.jdbc.SolidDriver
sqlbase         = centura.java.sqlbase.SqlbaseDriver
sybase          = com.sybase.jdbc2.jdbc.SybDriver
sybase:tds      = com.sybase.jdbc.SybDriver
timesten:client = com.timesten.jdbc.TimesTenClientDriver
timesten:direct = com.timesten.jdbc.TimesTenDriver

The first two prefixes following jdbc: are checked first for a know driver class; if not found, Judo then try the first prefix following jdbc:. For instance, when Judo sees "oracle" in jdbc:oracle:..., it loads the Oracle JDBC driver class oracle.jdbc.driver.JdbcDriver. You may have noticed some of the prefixes have multiple driver classes like mysql; this is because over time some JDBC drivers evolved and there can be multiple classes for different users for the same RDBMS. In fact, the jdbcdrivers.properties file also lists known dialects for Hibernate scripting (see Set-Up and Initialization of Hibernate.)

If a driver is not in the list, it can be specified as an driver attribute, or be loaded via the old-fashion Java style:

// Java style
(java::Class).forName('my.db.jdbc.driver');

// Judo style
db::connect ( driver='my.db.jdbc.driver' ) to 'jdbc:mydb:...', '', '';

Connection Attributes

Other than the driver attribute mentioned earlier, the db::connect command allows a few more attributes.

The autoCommit and readOnly attributes take boolean values; they do what the names suggest.

The catalog attribute takes a string that is the name of a catalog, which is used to select a subspace of the database in which to work. If the driver does not support catalogs, it is ignored.

The typeMap attribte takes a java.util.Map instance as the type map for this connection. The type map will be used for the custom mapping of SQL structured types and distinct types.

The transactionIsolation attribute can take one of these values: "transaction_none", "transaction_read_uncommitted", "transaction_read_committed" and "transaction_serializable".



 
Direct and Batch Execution of SQL

In this section, we will explain how to directly run DDL and DML udpates statements, either individually, in groups or in batches.

Run SQL Directly

SQL statements such as CREATE, DROP and INSERT either work or fail; in the context of Java and JDBC, a failure means an exception is raised. In Judo, these statements are run with the db::sql statement, which has the following syntax:

ExecuteSQL ::= ExecuteSQL_group | ExecuteSQL_individual | ExecuteSQL_prepraed
ExecuteSQL_group ::= db::sql [ use variable ] { ( sql_statement ; )+ }
ExecuteSQL_individual ::= db::sql [ use variable ] : sql_statement ; [ BindVariableList ; ]
ExecuteSQL_prepared ::= db::sql variable [ BindVariableList ] ;

The ExecuteSQL_individual and ExecuteSQL_prepared can also take a BindVariableList, which means the sql_statement can be parameterized. We will discuss parameterized SQL runs later.

The db::sql group statement is often used to run a number of SQL statements to create database objects and initialize tables. Let's create a simple table and insert some rows therein; this table is used in this chapter to explain Judo's JDBC scripting support. It includes a file, db::connect.judi, that simply connects to the database; this file is included in all the examples. It is listed first.

Listing 22.1 connect.judi
db::connect to 'jdbc:oracle:thin:@dbsvr:1521:dbname', 'user', 'pass';

Listing 22.2 init.judo
!include 'connect.judi'
if tableExists('EMP') {
  db::sql:
    DROP TABLE emp /* 'emp' is case-insensitive */ ;
}
db::sql {
  CREATE TABLE emp(emp_no      INTEGER PRIMARY KEY,
                   first_name  VARCHAR(100),   // First Name
                   last_name   VARCHAR(100),   // Last Name
                   birth_date  DATE,           -- Birth Date
                   salary      NUMBER);        -- Salary

  CREATE INDEX emp_fname  ON emp(first_name);
  CREATE INDEX emp_lname  ON emp(last_name);
  CREATE INDEX emp_bday   ON emp(birth_date);
  CREATE INDEX emp_salary ON emp(salary);

  INSERT INTO emp(emp_no,first_name,last_name,birth_date,salary)
  VALUES( 100, 'Jim', 'Malone', to_date('1-3-1954','dd-mm-yyyy'), 86500.0);

  INSERT INTO emp(emp_no,first_name,last_name,birth_date,salary)
  VALUES( 101, 'Linda', 'Jordan', to_date('24-7-1980','dd-mm-yyyy'), 45250.0);

  INSERT INTO emp(emp_no,first_name,last_name,birth_date,salary)
  VALUES( 102, 'Kathy', 'Yao', to_date('8-4-1970','dd-mm-yyyy'), 78500.0);
}

println 'db::sql result: ', $_;

disconnect();

This script first connects to the database, in the included connect.judi file, and then drops the table if the table already exists. The tableExists() system function, like disconnect(), is another shortcut for the name-sake method of the global variable, $$con. Once this is done, the big db::sql statement creates the database table and its indices, and inserts a few rows.

In this script, we have used db::sql to run a single SQL statement and a group of them. SQL statements in group are separated by semi-colons (;) and are run one by one. The db::sql statement may fail, in which case an exception will be raised, typically from the JDBC driver. If the execution is successful, it returns the execute results in an array, as was printed out close to the end in the script like this:

db::sql result: [-1,-1,-1,-1,-1,1,1,1]

In Judo, SQL statements are free form. All forms of Judo comment can be used within SQL statements, and there is an additional form of line comment that starts with --. This kind of line comment is commonly supported by many RDBMSs, and Judo honors this fact. These comments can appear in both situations of single-SQL-statemenet and multiple-SQL-statement commands.

Like the [[* *]] syntax, (* *) can be used to embed expressions within any SQL statements, either individually or in groups. The following example demonstrates its usage:

// Construct an IN list for the query
last_names = [ 'Olajuwon', 'Yao' ]; // an array
in_expr = last_names.toCsv(',', function(x){ return "'"+x+"'"; });

// Now run the query
db::query qry:
  SELECT * FROM emp WHERE last_name IN ( (* in_expr *) )
;

In the code above, we used Array's toCsv() method and supplied an anonymous function to quote the values. The resultant SQL statement is: SELECT * FROM emp WHERE last_name IN ( 'Olajuwon','Yao' ). The toCsv() can also return a subset of the array, which can be very useful. Suppose we are going to update a database table for a number of records; the record IDs are stored in a text file and may be tens of thousands. The SQL statement is something like this:

UPDATE record_tbl SET status = 1
WHERE record_id in ( .... ) AND status = 0

The problem is, there may be tens of thousands of records to update; constructing such a long "in" list will probably make JDBC driver fail. The following is a solution:

arr = [];
arr.loadAsLines(#args[0]);

db::connect (autoCommit=false) to dbUrl, dbUser, dbPass;

cnt = 0;
len = 100;
for start=0; start < arr.length; start += len {
  in_expr = arr.toCsv(',', function(x){ return "'"+x+"'"; }, start, len);

  db::update upd:
    UPDATE record_tbl SET status = 1
    WHERE record_id in ( (* in_expr *) ) AND status = 0
  ;

  cnt += upd.getResult();
}

commit();
disconnect();
println 'Processed ', unit(cnt, 'record');

catch:
  rollback();

The idea is to turn auto-commit off, update the table for up to 100 IDs at a time, and commit at the end. In this example, we used Array's loadAsLines() method to read in all the IDs from the file specified from the command line.


Run Raw SQL and Database Language Scripts

In db::sql{}, semi-colons (;) are used to separate SQL statements. This assumes that semi-colons are never part of the SQL text. This may not be always true. For instance, Oracle PL/SQL code use semi-colons as statement delimitors; the code need be passed as a whole through JDBC to the server and execute. Judo's db::any statement does just this. The syntax is:

ExecuteAny ::= db::any [ use variable ] Expr

The Expr is evaluated to be a text string, and is sent to the database. The following example creates an Oracle stored procedure:

db::any [[*
  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;
*]];

Run SQL in Batches

JDBC defines a mode of batch run of SQL statements. In Judo, you can run a number of SQL statements in a static batch, or you can programmatically construct a dynamic batch job and run.

Static batch
You can specify multiple SQL statements in a executeBatch much like db::sql and run the batch. The syntax is:

ExecuteBatch ::= executeBatch [ use variable ] { ( sql_statement ; )+ }

Again, you have the choice of a connection object; if nothing specified, the global $$con variable is assumed. The batch run returns in an array of update counts for each SQL statement in the batch; the result is stored in the local variable $_.

Listing 22.3 batch_insert.judo
!include 'setup/connect.judi'

executeBatch {
  INSERT INTO emp(emp_no,first_name,last_name,birth_date,salary)
  VALUES ( 103, 'Scott', 'Williamson', to_date('5-3-1956','dd-mm-yyyy'), 92500.0 );

  INSERT INTO emp(emp_no,first_name,last_name,birth_date,salary)
  VALUES ( 104, 'Louis', 'Tan', to_date('24-10-1983','dd-mm-yyyy'), 55250.0 );

  INSERT INTO emp(emp_no,first_name,last_name,birth_date,salary)
  VALUES ( 105, 'Shiva', 'Patel', to_date('8-4-1979','dd-mm-yyyy'), 48000.0 );
}
println 'executeBatch result = ', $_;

disconnect();

The above example prints the result like this:

executeBatch result = [1,1,1]

Dynamic batch
In Judo, you can create a batch object, add SQL statements and execute. Batch objects are created by the database connection's createBatch() method. The batch object's add() is used to add SQL statements; the size() method returns the number of SQL statements currently held in the batch object, and the execute() method runs and returns the result in an array of update counts. There is a addBatch command that does the same as the batch object's add() method but is easier to write SQL; its syntax is:

AddBatch ::= addBatch [ to ] IDENTIFIER : sql_statement

Here is an example:

Listing 22.4 dyn_batch_insert.judo
!include 'setup/connect.judi'

rows = [
  [ 'Chrinstine', 'Lin',     '24-5-1985', 50250.0 ],
  [ 'John',       'Johnson', '13-7-1978', 45200.0 ],
];
id = 106; // start value.

batch = createBatch(); // use $$con
for row in rows {
  addBatch to batch:
    INSERT INTO emp(emp_no,first_name,last_name,birth_date,salary)
    VALUES ( (* id++ *), '(* row[0] *)', '(* row[1] *)',
           to_date('(* row[2] *)','dd-mm-yyyy'), (* row[3] *) )
  ;
}
println 'batch size before run: ', batch.size;
res = batch.execute();
println 'batch size after run:  ', batch.size, nl,
        'executeBatch results = ', res;

disconnect();

This program keeps the row data in an array, add them all to the batch object with the addBatch command and runs them. The execution result is:

batch size before run: 2
batch size after run:  0
executeBatch results = [1,1]

Batch object can be re-used for multiple batches, as demonstrated below:

Listing 22.5 dyn_batch_insert1.judo
!include 'setup/connect.judi'

rows = [
  [ 'Richard', 'Brown', '23-5-1985', 60150.0 ],
  [ 'Smith',   'Green', '9-7-1977',  45600.0 ],
];
id = 108; // start value.

batch = createBatch(); // use $$con
for row in rows {
  sql = [[*
    INSERT INTO emp(emp_no,first_name,last_name,birth_date,salary)
    VALUES ( (* id++ *), '(* row[0] *)', '(* row[1] *)',
           to_date('(* row[2] *)','dd-mm-yyyy'), (* row[3] *) )
  *]];
  batch.add(sql);

  println 'batch size before run: ', batch.size;
  res = batch.execute();
  println 'batch size after run:  ', batch.size, nl,
          'executeBatch results = ', res;
}

disconnect();

In this example, we used the add() method of the batch object. Each iteration adds just one SQL and runs, only to demonstrate the re-use of the batch object. The result is:

batch size before run: 1
batch size after run:  0
executeBatch results = [1]
batch size before run: 1
batch size after run:  0
executeBatch results = [1]

We have covered executing DDL and DM update SQL statements in Judo. There are cases where SQL statements are kept in SQL script files; they are generally RDBMS-specific. Judo's db::sql {} and db::any commands take many popular SQL file formats and can run those files directly. This will be covered in the next section.



 
Run SQL Script Files   to be done


 
Parameterized SQL

JDBC supports running not only static SQL statements but also parameterized SQL. Parameters in the SQL statements are denoted as question marks (?) in the SQL, and are referenced by their positions starting from 1. In JDBC, the process is:

  1. Prepare a parameterized SQL
  2. Bind values to the parameters in the SQL
  3. Execute the SQL
  4. If there are more runs, go to 2; otherwise, quit

Note that any SQL statement, parameterized or not, can be prepared and run. Why would you prepare a non-parameterized SQL? Preparing a SQL gives the JDBC driver software and even the RDBMS software to "understand" the SQL to be run, and possibly do some intelligent optimization. If you have a SQL that is known to be run repeatedly, preparing first is always preferred.

A parameterized SQL can be a query, an update or a stored procedure call. In Judo, it is prepared by the db::prepare and db::prepareCall commands. The syntax is:

Prepare ::= ( prepare | prepareCall ) [ IDENTIFIER ] [ use IDENTIFIER ] : sql_statement ;

The prepared SQL statement is stored in the variable identified by the first IDENTIFIER. The connection used for this preparation process is specified by the use clause; if the use clause is not specified, it uses the connection stored in the global variable $$con. A prepared SQL can be run by db::sql, db::query, db::queryCall, db::update and db::updateCall. In the grammar of ExecuteSQL, we saw the BindVariableList, which is defined as follows:

BindVariableList ::= with BindVariable ( , BindVariable )*
BindVariable ::= @ ( INTEGER_LITERAL | IDENTIFIER ) [ : SQLType ] ( = | => | <=> ) Expr
| @ INTEGER_LITERAL . IDENTIFIER ( Arguments )
SQLType ::= SQLType_basic | SQLType_advanced
SQLType_basic ::= BIGDECIMAL | BIT | BOOLEAN | BYTE | DATE | DOUBLE | FLOAT | INT | INTEGER | LONG | NUMBER | NUMERIC | SHORT | STRING | TIME | TIMESTAMP | VARCHAR
SQLType_advanced ::= ARRAY | BLOB | BYTES | CLOB | LONGVARCHAR | REF | STRUCT | JAVA_OBJECT | OTHER | ORACLE_BFILE | ORACLE_CURSOR | ORACLE_ROWID

Note SQLType names are case-insensitive. It is easier to look at an example before explaining all these. The following examples uses db::sql to run a prepared, parameterized SQL:

Listing 22.6 prepared_execute1.judo
!include 'setup/connect.judi'

rows = [
  [ 'Richard', 'Brown', Date(1985,5,23), 60150.0 ],
  [ 'Smith',   'Green', Date(1977,9,7),  45600.0 ],
];
id = 108; // start value.

db::prepare ins:
  INSERT INTO emp(emp_no,first_name,last_name,birth_date,salary)
  VALUES (?,?,?,?,?)
;

for row in rows {
  db::sql ins with @1:Integer = id++,
                      @2         = row[0],
                      @3         = row[1],
                      @4:Date    = row[2],
                      @5:Number  = row[3];
}

disconnect();

In this example, the db::prepare command prepares the paramiterized SQL and stores in the variable ins. In the subsequent db::sql statement, the prepared SQL (stored in ins) is run with values assigned to the parameters. For details, we will discuss shortly in the appropriate sections.

This example shows that values are bound to parameters in the SQL via the @n:type expressions in the with clause, where n starts from 1 for the first parameter. The type can be one of the SQLType_basic name. If no type name is specified, it is assumed VARCHAR. The value on the righthand-side of = must be of a compatible type and is converted to the corresponding SQL type.

Now look at the SQL statement above. It has five question marks (?). This is in compliance with the current JDBC specification, but it has obvious potential problems. If we add a column value into this INSERT statement, say, before column first_name, then the bind variable indices are affected. If you have a long list of bind variables, this can easily cause problems. To remedy this, Judo supports named bind variables within prepared SQL statements, demonstrated below:

Listing 22.7 prepared_execute.judo
// all other pieces are omitted

db::prepare ins:
  INSERT INTO    emp( emp_no, first_name, last_name, birth_date, salary  )
              VALUES( :empNo, :firstName, :lastName, :birthDate, :salary )
;

for row in rows {
  db::sql ins with @empNo    :Integer = id++,
                      @firstName         = row[0],
                      @lastName          = row[1],
                      @birthDate:Date    = row[2],
                      @salary   :Number  = row[3];
}

The names must be identifiers. Since variable names are position-invariant, this is simply less error-prone, and is certainly is much more convenient to change the bind variable list.

The bind values can be bound with other operators, namely, => and <=>; they are used to designate in- and in-out parameters in stored procedure calls, which will be discussed later.

Another way to bind values, especially non-basic type values, is to call a method for the bind parameter. Suppose the table has a column of CLOB, and you are to upload the context of a text file to it; you can do it like this:

file = openTextFile('foo.txt');
db::sql ins with @1.setCharacterStream(file, 1000);
file.close();

Depending on the RDBMS, there may be more operations to deal with situations involving advanced SQL types, and this is only to illustrate calling methods as a way to bind parameters. We will discuss this more.


 
Run SQL Update Statements

SQL update statements can be run by db::sql, as we saw earlier, or by db::update command. Generally, db::sql is meant for DDL and groups of SQL update statements; the major difference between db::sql and db::update is, db::update returns an update count, whereas db::sql doesn't return anything. The syntax for db::update is:

ExecuteUpdate ::= ExecuteUpdate_1 | ExecuteUpdate_2
ExecuteUpdate_1 ::= db::update [ IDENTIFIER ] [ use IDENTIFIER ] : sql_statement ; [ BindVariableList ; ]
ExecuteUpdate_2 ::= db::update [ IDENTIFIER ] [ BindVariableList ] ;

The db::update can directly run a SQL, parameterized or not; it can also run a prepared SQL. The result is slightly different.

Run prepared SQL updates
When running a prepared SQL update statement, the prepared SQL object has only one method, getResult(), that returns the update count of the last run. The following example demonstrates this.

Listing 22.8 prepared_insert.judo
!include 'setup/connect.judi'

rows = [
  [ 'Richard', 'Brown', Date(1985,5,23), 60150.0 ],
  [ 'Smith',   'Green', Date(1977,9,7),  45600.0 ],
];
id = 108; // start value.

db::prepare ins:
  INSERT INTO    emp( emp_no, first_name, last_name, birth_date, salary )
              VALUES( :empNo, :firstName, :lastName, :birthDate, :salary )
;

cnt = 0;
for row in rows {
  db::update ins with @empNo    :Integer = id++,
                         @firstName         = row[0],
                         @lastName          = row[1],
                         @birthDate:Date    = row[2],
                         @salary   :Number  = row[3];
  cnt += ins.getResult();
}
println unit(cnt, 'Row has', 'Rows have'), ' been inserted.';

db::disconnect();

In the above example, both db::prepare and db::update may not take any variable names (the ins in the code); if this case, the prepared SQL object is stored in $_. The unit() system function is a convenience for displaying the singular and plural English names based on the count.

Directly run SQL updates
When directly running a SQL, it returns an update count, that is, the number of rows affected by the SQL update, as demonstrated by the following example.

Listing 22.9 update.judo
!include 'setup/connect.judi'

db::update upd:
  UPDATE emp SET salary=48500.0 WHERE emp_no=105
;

println 'db::update result = ',  upd;

db::disconnect();

In this example, if the variable upd is not specified, then $_ is used. In any case, the result is an integer. You can also run parameterized SQL update statement, like this:

Listing 22.10 update1.judo
!include 'setup/connect.judi'

db::update:
  UPDATE emp SET salary=:salary WHERE emp_no=105
; with @salary:Number = 48500.5;

println 'db::update result = ', $_;

db::disconnect();

Some may wonder, since it is a one-time deal, why bother to use parameterized SQL rather than simply put the value in? This is true with this example, but in a function, parameterized SQL may be the only choice:

function setSalary empID, newSalary, conn {
  if conn == null { conn = $$con; }
  db::update upd use conn:
    UPDATE emp SET salary=:salary WHERE emp_no=:empNo
  ; with @salary:Number = newSalary, @empNo:Integer = empID;

  return upd;
}

This function may return 1 or 0, as the empID may or may not be valid.

Next, we are going to discuss query and query result processing.


 
Queries and Query Results

Queries are probably the most frequently used operations against RDBMSs in applications and reporting. Unlike DDLs an DML update SQL statements, which may or may not return simple update counts, queries return a collection of results referred to as result sets, in JDBC parlance. Most likely, a query is a SQL SELECT statement, but sometimes specific RDBMS may support commands that also return result sets. In Judo, the db::query statement executes queries; its syntax is:

ExecuteQuery ::= ExecuteQuery_1 | ExecuteQuery_2
ExecuteQuery_1 ::= db::query [ IDENTIFIER ] [ QueryOptions ] [ use IDENTIFIER ] : sql_statement ; [ BindVariableList ; ]
QueryOptions ::= ( ( (type|fetch|concur) = Expr ),+ )
ExecuteQuery_2 ::= db::query IDENTIFIER [ BindVariableList ] ;

Syntactically and semantically, db::query is very similar to db::update, except for the outcome and consequently the QueryOption. The fun starts with the query result set.

Listing 22.11 simple_query.judo
!include 'setup/connect.judi'

db::query qry:
  SELECT emp_no, first_name, last_name, birth_date, salary
  FROM emp
;

println 'ID   First-Name  Last-Name    Salary    Birth-Date', nl
        '---  ----------  -----------  --------  ----------';
while  qry.next() {
  println qry[1].int()   :<5,
          qry.first_name :<12,
          qry.last_name  :<12,
          qry.salary     :*7.1, '  ',
          qry.birth_date.fmtDate('yyyy-MM-dd');
}

db::disconnect();

The query object stored in the variable qry is an entity of many faces. When a query is executed or prepared, this query object represents the query itself. After execution, it also represents the result set after run. The result set has a number of methods, and one of the most important ones is next(), which allows Judo code to traverse the results. For each iteration, the query object represents the current row, too! Each column in the row can be accessed either via an index (the first column has index 1) or via the column name. In this example, we use qry[1] to reference the column named "emp_no", and other columns are referenced by their names.

The column values are converted to Judo data types. For instance, the SQL date, time and timestamp types are all converted to Judo Date. In the example, the qry.birth_date returns a Date value, therefore we call its fmtDate() method.

It is common to format the query result like we do in the example. The result is:

ID   First-Name  Last-Name    Salary    Birth-Date
---  ----------  -----------  --------  ----------
100  Mary        Malone        86500    1954-03-01
101  Linda       Jordan        45250    1980-07-24
102  Kathy       Yao           78500    1970-04-08
103  Scott       Williamson    92500    1956-03-05
104  Louis       Tan           55250    1983-10-24
105  Shiva       Patel         48500.5  1979-04-08
106  Chrinstine  Lin           50250    1985-05-24
107  John        Johnson       45200    1978-07-13
109  Smith       Green         45600    1977-09-07
108  Richard     Brown         60150    1985-05-23

Parameterized queries can be run by binding values to all the parameters. The result is the same.

Query options
Query options include type, fetch and concur. They take textural names as their values.

The type option can be "scroll_insensitive", "scroll_sensitive" or "forward_only". Default is "forward_only".

The fetch option can be "forward" or "reverse". Default is "forward".

The concur option can be "read_only" or "updatable". Default is "read_only".


 
Execute Database Scripts As-Is

We saw earlier that db::sql{} 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 db::any 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:

Listing 22.12 test_proc.judo
!include 'setup/connect.judi'

db::any [[*
  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;
*]];

db::disconnect();

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.


 
Call 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. They can certainly be named as well like in any prepared statements.

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

db::prepareCall: { call test_proc( :paramIO, :paramIn, :paramOut ) };

x = null;
y = 'abcd';

db::sql with @paramIO :int     <=> x,
             @paramIn :varchar =  y,
             @paramOut:varchar => z; // z will be the same as y

println 'x = ', x;  // Prints: x = -1000
println 'z = ', z;  // Prints: z = abcd

RDBMSs may have their own extensions. Oracle, for instance, can return result sets from stored procedures. Because of the prevalence of Oracle, Judo supports a few Oracle-specific SQL data types, as we saw earlier in the SQLType grammar. The following example shows how to use result set returned from a procedure:

Listing 22.13 test_proc_rs.judo
!include 'setup/connect.judi'

//
// create the stored procedure
//
db::any [[*
  create package types
  as
    type cursortype is ref cursor;
  end;
*]];

db::any [[*
  create function ListPeople return types.cursortype
  as
    l_cursor types.cursorType;
  begin
    open l_cursor for
      select emp_no, first_name, last_name, birth_date, salary
      from emp;
    return l_cursor;
  end;
*]];

//
// test it
//
db::prepareCall: { :ret = call ListPeople };

db::sql with @ret:oracle_cursor => res;

while res.next() {
  println res[3] :<12, res[4].fmtDate('yyyy-MM-dd');
}

db::disconnect();

The Oracle function, ListPeople, returns a "cursortype" which is a ref cursor defined in a package named types. The Judo db::sql statement returns this as a oracle_cursor into variable res, which is used in the subsequent loop to print out some results.

 
Use Advanced SQL Types   to be done


 
Database Meta-Information and Utilities   to be done

The connection object is fundamentally a java.sql.Connection object, (or more precisely, a wrapper object of a java.sql.Connection object, so you can access all public methods of that Java class.) Sometimes you can obtain a connection, say, via a Java method call; it can be used just like one obtained through the db::connect statement.


 
back to top
 



Copyright © 2001-2005 JudoScript.COM. All Rights Reserved.