AppLib EXECUTE SCRIPT

Declaration

create or replace procedure applib.execute_script(
    engine_name varchar(255), 
    script varchar(65535))
language java
parameter style java
modifies sql data
external name 'applib.applibJar:org.eigenbase.applib.script.ExecuteScriptUdr.executeUdp';

Syntax

CALL APPLIB.EXECUTE_SCRIPT(engineNameString, scriptStringOrFileName);

Purpose

This procedure provides a general-purpose way of executing scripts that can modify the catalog or do anything else. The variable APPLIB_JAR will be made available to the script before evaluating, which contains the absolute path to the applib jar in case the script wishes to import classes from there. In the case of Jython, simply having each JAR on your personal CLASSPATH, then launching the Jython interpreter, should be enough to have the necessary jar files processed and ready to use.

Parameters

• ENGINE_NAME: script engine to use, e.g. 'python' for Jython, 'JavaScript' for Rhino JS (bundled with Java 6), 'Clojure' for Clojure, 'jruby' for Ruby, and others.
• SCRIPT: either a string to evaluate or a filename to load and evaluate.

Examples

# create.py saved in luciddb/plugin/ directory
import sys
if APPLIB_JAR not in sys.path: # used to tell Jython where this is, if desired
  sys.path.append(APPLIB_JAR)
import site # You MUST have this if you want to use modules you installed for Jython

from java.sql import *
# Potentially useful resources:
'''from org.eigenbase.applib.util import *
from org.eigenbase.applib.resource import *
from org.eigenbase.sql import *
from org.eigenbase.sql.util import *
'''

conn = DriverManager.getConnection("jdbc:default:connection")
ps = conn.prepareStatement("create or replace schema boohoo")
ps.execute()
conn.close()

0: jdbc:luciddb:http://localhost> CALL applib.execute_script('python', '${FARRAGO_HOME}/plugin/create.py');

For an intriguing experience, running this code somewhere in your script:

import code
code.interact(local=globals())

brings up the Jython interactive interpreter on the shell window where you launched LucidDB (if and only if you launched LucidDB via sqllineEngine)! Every other command is sent to the listening server, though, but you can escape to the full Jython mode by pressing ctrl-D and the server can still be shut down with ctrl-C. By specifying the readfunc parameter of the interact() function, one could even build a sqlline-like client that evaluates SQL...

The first example can also be rewritten (almost identically) in JavaScript:

importPackage(java.sql) /* There is also an importClass() function. */
var conn = DriverManager.getConnection("jdbc:default:connection")
var ps = conn.prepareStatement('create or replace schema boohoo')
ps.execute()
conn.close()

Exceptions

• Script Engine not found -- make sure the JAR file of the engine (such as jython.jar) is on LucidDB/Farrago's classpath.
• Script Evaluation Error -- your script contained errors, see the trace logs for more info.
• File IO Error -- file exists but could not be read.