Deprecated: Function set_magic_quotes_runtime() is deprecated in /home/mwexler/public_html/tp/textpattern/lib/txplib_db.php on line 14
The Net Takeaway: Judoscript and Databases


Danny Flamberg's Blog
Danny has been marketing for a while, and his articles and work reflect great understanding of data driven marketing.

Eric Peterson the Demystifier
Eric gets metrics, analytics, interactive, and the real world. His advice is worth taking...

Geeking with Greg
Greg Linden created Amazon's recommendation system, so imagine what can write about...

Ned Batchelder's Blog
Ned just finds and writes interesting things. I don't know how he does it.

R at LoyaltyMatrix
Jim Porzak tells of his real-life use of R for marketing analysis.







Judoscript and Databases · 03/10/2005 05:57 PM,

First off, if you found this page via a web search or bookmark, you may be much happier in the JudoScript Section of this site to see the multiple articles about Judoscript, including this one, but also about Databases, Graphics, Files, etc.

Database Access
Primary docs at including good example of batch updating. Note that I am using the namespace style syntax, new to recent versions of Judoscript, which uses ::.

Lots of detail at the JDBC Book Chapter

You probably need your JDBC files in your classpath for this to work. On Windows, I added an environment variable like this:
System Properties (or My Computer Properties); Advanced tab; Environment Variables.
I added:

C:\Program Files\judoscript-0.9\judo.jar;c:\oracle\ora92\jdbc\lib\;c:\oracle\ora92\jdbc\lib\;


 const #dbUrl  = 'jdbc:oracle:thin:@localhost:1521:crescent';
 const #dbUser = 'jamesh';
 const #dbPass = secret('guess');
 db::connect conny to #dbUrl, #dbUser, #dbPass;

“conny” here is a connection object. If it is not present, the default connection object, named $$con, is used, and its methods can be invoked directly. .describe() describes a database table and returns the information in a tableData structure (a proprietary and pretty handy Judoscript feature).

  tableInfo = describe(table);
  println tableInfo.getTitles(); //prints the columns in the table

Can also use .getMetaData(). It returns an extended object of java.sql.DatabaseMetaData class, except that any of its methods that returns a result set are overridden and return a tableData instead.

        md = getMetaData();
        tables = md.getTables('PROJ_%');
        println tables;

(Note that, if your database is not in the known list that is hardcoded into Judoscript, then the JDBC driver has to be loaded explicitly like you would in Java: java.lang.Class::forName(‘your.jdbc.driver.Name’); Then continue with the connect to …. command.)


Before querying, you can actually check to see if your table is present:

  if tableExists('EMP') {
      DROP TABLE emp /* 'emp' is case-insensitive */ ;

A query looks like this:

        db::query qry:
          select * from table1
          where seq < 5;
        while {

          println '--------';
          println qry[1];            // column 1
          println qry."Record Type"; // column by that name

Remember that result sets are 1 based, but TableData results are arrays and so are 0 based.

Another way to get columns, besides the above is, assuming you have query a:


  //  The below prints each column header 
  for i from 1 to colcount {
    println i,'=',aMetaData.getColumnLabel(i);

Now, there are different kinds of SQL statements. Queries which return rows are db::query things. But CREATE, DROP and INSERTs either work or they don’t, and you don’t walk their results. These are db::sql things.

These can be either db::sql: single statment; or db::sql{ statement; statement; statement; }

Can also have prepared statements:

        prepare a:
          select * from people order by alias
        executeQuery a;


        preparedExecuteQuery a:
          select * from people order by alias

The “query” object changes depending on where you are in the process. When a query is executed or prepared, this query object represents the query itself. After execution, it also represents the result set.

The result set has a number of methods, and one of the most important ones is .next(), which allows JudoScript 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 qry1 to reference the column named “emp_no”, and other columns are referenced by their names. (so, how do get get the count of columns? How do we get the column names?)

James uses examples like this. Note that he is hand formatting the column types, b/c he hard coded them in the query:

 !include 'setup/connect.judi'
 executeQuery qry:
   SELECT emp_no, first_name, last_name, birth_date, salary
   FROM emp
 println 'ID   First-Name  Last-Name    Salary    Birth-Date', nl
         '---  ----------  -----------  --------  ----------';
 while {
   println qry[1].int()   :<5,
           qry.first_name :<12,
           qry.last_name  :<12,
           qry.salary     :*7.1, '  ',


Because these are basically all cursors, they can have the usual JDBC options:

I’ve found, however, that Judoscript doesn’t have a simple “print the query output” routine, so I’ve been using code like this:

 //  for info on building connect string 

 const #dbUrl='';
 const #dbUser='AUser';
 const #dbPass='APassword';

 connect to #dbUrl, #dbUser, #dbPass;

 executeQuery a:
    select * from tablespace.t_url 
    where client_id=developer.clientid('clientname')
        and rownum < 100 and url_Id>30000
 // How many columns did we get?

 // Print a header
 for i from 1 to colcount {
 	println i,'=',aMetaData.getColumnLabel(i);

 // Print the output
 while {
   for i from 1 to colcount { 
     print a[i];
     if i != colcount {print '\t';} 
 // We're Done.

Advanced Queries
You can actually pass code in as a bundle, say, to make stored procedures and such. SQL statements in the block are delimited by a semicolon (;). However, sometimes you need to send a semicolon to the db: so, use the executeAny statement. In executeAny, all text is sent to the database server as-is. Note the use of the [[**]] “here-doc” styling:

executeAny [[*
    param_io IN OUT NUMBER,
    param_i  IN     VARCHAR,
    param_o  OUT    VARCHAR)
    param_o := param_i;
    IF param_io IS NOT NULL THEN

      param_io := param_io + 1;
      param_io := -1000;
    END IF;

You can use Parameterized SQL through the db::prepare statement.

Stored Procedures can be tricky. Here is an example from James’ site:

include 'setup/connect.judi'

// create the stored procedure
executeAny [[*
  create package types
    type cursortype is ref cursor;

executeAny [[*
  create function ListPeople return types.cursortype
    l_cursor types.cursorType;
    open l_cursor for
      select emp_no, first_name, last_name, birth_date, salary
      from emp;
    return l_cursor;

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

executeSQL with @ret:oracle_cursor => res;

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


As he says, “The Oracle function, ListPeople, returns a “cursortype” which is a ref cursor defined in a package named types. The JudoScript executeSQL statement returns this as a oracle_cursor into variable res, which is used in the subsequent loop to print out some results.”. More details here

Using HSQL
From a user on Yahoo Groups:

1. Make sure that you have hsqldb.jar in your classpath.
2. Create a directory anywhere, such as d:mydata, and create a batch file such as startdb.bat like this:

 cd mydata
 java org.hsqldb.Server -database mydb

You don’t have to do anything to create the database. The first time it runs and does not see a database, it creates one for you (with user name as “sa” and no password).

3. Connect to it via JDBC.

 connect to 'jdbc:hsqldb:hsql://localhost', 'sa', '';

 CREATE TABLE my_table (

        name VARCHAR NOT NULL,
        password VARCHAR,
        email VARCHAR NOT NULL,
        country_id INTEGER,
        active BIT,
        create_time DATE NOT NULL,
        UNIQUE (email),
        FOREIGN KEY (country_id) REFERENCES country(id)


And from now on, business as usual…

4. In the data directory, 2 files seem to be important: and mydb.script. But seems always of 0 bytes, where mydb.script keeps all the SQL statements! Kinda like Oracle REDO log, which allows you to reproduce the whole database.
Near the top, it has a line:


I changed the admin user/password by editing this file! Don’t
forget to also change the following statement, otherwise it won’t


Original Source = about HSQLDB and org.hsqldb.util.DatabaseManager.

Database Metadata

How is aFields = a.getColumnAttributes(); different from a “desc()” function?
describe() describes a database table, appears to return the following:

Column Type Precision Scale Nullable’;

As in:

 connect to url, username, password;
 tableInfo = describe(table);

 println 'Table: ', table, nl;
 println 'Column         Type            Precision   Scale Nullable';
 println '-------------- ------------ ------------ ------- ----------';
 printTable tableInfo for
   column('name')      :<15!,
   column('type')      :<13!,
   column('precision') :>12,
   column('scale')     :>8, ' ',

Is it possible that a.getColumnAttributes(); returns the same thing?
column(0) appears to be same as column 1, col 7 (0 counting) is Null, and then false and blank?

.transpose method?

* * *


  Textile Help
Please note that your email will be obfuscated via entities, so its ok to put a real one if you feel like it...

powered by Textpattern 4.0.4 (r1956)