|
| 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:
- Prepare a parameterized SQL
- Bind values to the parameters in the SQL
- Execute the SQL
- 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 c 2001-2021 JudoScript.COM. All Rights Reserved.