#89 Use with Beanshell

[Binh Pham]

I wonder if you've ever considered providing hooks
to connect with beanshell (http://www.beanshell.org). I
am thinking of writing
stored procedures or scripts in beanshell.

Ben

[Anonymous]

Logged In: YES
user_id=199381

Yes. It works. And very well, I might add.

I did the initial work on this about 6 months ago. I
think I might have posted on this on either the open
discussion or developers list, but then again, maybe
onlyto Fred? Its bean (heh) a while.

Notable things to look out for (as is always with
HSQLDB), are any type of code that runs off the the
connection object injected into the beanshell
namespace and then tries to do something reentrant
under multiple threads. This can spell deadlock for
HSQLDB.

The really nice thing about BeanShell is that you can
write your own "functions" and even classes, sortof (a
bit like JavaScript, as opposed to Java), and all of this is
persisted from one call to the next, as long as you keep
the namespace container hanging around. This is not
too, too difficult to do, just by injecting a differnt
connection (the Session's intenal connection object)
into a static instance at each call.

I had it set up a bit like:

Object Library.eval(Connection conn, String s) throws
SQLException {

if the static beanshell interpreter is null, create it, else
use it.

put conn into the top namespace (replaces the previous
one, if it was there)

try {
evaluate your String, s, getting an Object result (which,
with the latest patches, gets automatically converted to
a Result if the Object is a Result or jdbcResultSet)
} catch( e ) {
wrap if requried in sql excpetion and rethrow
}

}

This works just fine. The only problem is that each
session sees the same code. I think its dangerous to let
beanshell hang on to a set of session or connection
references. So, to get this working really nicely, each
session would instead reference a beanshell instance.
However, this is heavyweight and not very general. it
would be nicer to standardize an interface for Sessions's
instantialing modules of any kind and being able to
refences/user them. Also, it might be nice to
implement this through a more general scripting
interface so that other scrupting languages are suported
as well. This will take time, as there are so many other
things to do.

Cheers,
Campbell

[Anonymous]

Logged In: YES
user_id=199381

OK. I found the mail I sent, dated 16 November, 2002:

Hi.

I was just playing around with the beanshell interpreter
for a few minutes and thought I would try to add a
method org.hsqldb.Library.eval(Connection conn, String
stmnt);

After a few dumb bugs (we weren't setting internal
connection's properties, so NPE there...I was not doing
casting properly in Parser.processCall(), so class cast
exceptions there), I got it working, and it works great!

Check this out:

Added to org.hsqldb.Library:

// SYSTEM

private static bsh.Interpreter interpreter;

static {

interpreter = new bsh.Interpreter();

bsh.BshClassManager.setClassLoader(
Library.class.getClassLoader() );
try {
interpreter.eval("import java.sql.*;");
interpreter.eval("Connection conn;");
} catch (Exception e) {}

}

public static Object eval(Connection conn, String
stmnt)
throws SQLException {
Object result;
try {
interpreter.setVariable("conn", conn);
result = interpreter.eval(stmnt);
} catch (Exception e) {
e.printStackTrace();
result = e.getMessage();
}
return result;
}

Changed in Parser.processCall:

Result processCall() throws SQLException {

Expression e = parseExpression();

e.resolve(null);

int type = e.getDataType();
Object o = e.getValue();

if (o instanceof jdbcResultSet) {
return ((jdbcResultSet) o).getResult();
}

if (o instanceof Result) {
return (Result) o;
}

Result r = new Result(1);

r.sTable[0] = "";
r.colType[0] = type;
r.sLabel[0] = "";
r.sName[0] = "";

Object row[] = new Object[1];

row[0] = o;

r.add(row);

return r;

}

Changed in jdbcConnection constructor:

jdbcConnection(Session c) throws SQLException {

Trace.doAssert(c != null, "The specified Session is
null");

iType = INTERNAL;
cSession = c;
dDatabase = c.getDatabase();

// need non-null properties for constructing
ResultSet objects
connProperties = dDatabase.getProperties();

sDatabaseName = dDatabase.getName();
}

So, here is the kind of thing one can do:

call "org.hsqldb.Library.eval"('

String query = "select table_name from system_tables
where table_name like ?";
PreparedStatement ps = conn.prepareStatement
(query);

myFunc(String s) {
ps.setString(1,s);
return ps.executeQuery();
}

return myFunc("SYSTEM_T%");

');

TABLE_NAME
----------------------
SYSTEM_TABLEPRIVILEGES
SYSTEM_TABLES
SYSTEM_TABLETYPES
SYSTEM_TRIGGERCOLUMNS
SYSTEM_TRIGGERS
SYSTEM_TYPEINFO

...............................
6 rows retreived
retrieve time: 20.5 (+/- 0.5) ms
display time : 10.5 (+/- 0.5) ms
...............................
total : 31 (+/- 1.0) ms

Now, this does not do too much good if the only way to
do this is through a simple, single bsh.Interpreter.
_But_ if set up properly (i.e. each session gets its own
beanshell name space), then things get quite exciting.
Even with the way I have things set up, the tip of the
iceberg shows:

call "org.hsqldb.Library.eval"('

String query = "select table_name from system_tables
where table_name like ?";
PreparedStatement ps = conn.prepareStatement
(query);

myFunc(String s) {
ps.setString(1,s);
return ps.executeQuery();
}

');

call "org.hsqldb.Library.eval"('myFunc("SYSTEM_T%");');

TABLE_NAME
----------------------
SYSTEM_TABLEPRIVILEGES
SYSTEM_TABLES
SYSTEM_TABLETYPES
SYSTEM_TRIGGERCOLUMNS
SYSTEM_TRIGGERS
SYSTEM_TYPEINFO

...............................
6 rows retreived
retrieve time: 0.5 (+/- 0.5) ms
display time : 10.5 (+/- 0.5) ms
...............................
total : 11 (+/- 1.0) ms

call "org.hsqldb.Library.eval"('myFunc("SYSTEM_B%");');

TABLE_NAME
------------------------
SYSTEM_BESTROWIDENTIFIER
SYSTEM_BYTECODE

...............................
2 rows retreived
retrieve time: 0.5 (+/- 0.5) ms
display time : 10.5 (+/- 0.5) ms
...............................
total : 11 (+/- 1.0) ms

So, one can define variables and functions in some
calls, and these are *remembered* in later calls.

To me, this could be really powerful for the precompiled
cursor stuff, as the worry over local v.s. remote goes
away. The big issue is the call overhead, but as shown
above, there is very little, comparitively speaking.

It would be simple to make the beanshell interpreter
instantiation reflective (so that the bsh.jar is optional)
and it would probabably be very simple to make stuff
defined in the beanshell interpreter persistent (create a
library method that inserts the successfully interpreted
java into a persistent system table or scripts it directly
to the log under the appropriate circumstances).

Also, since beanshell provides reflection on methods
defined in a beanshell namespace, it would be quite
simple to extend org.hsqldb.Function to resolve
beanshell methods (like myFunc, above), meaning that
we could allow dynamic creation of SQL
functions/procedure that would work seamlessly within
the SQL environment (i.e. without having to wrap things
in a call to "org.hsqldb.Library.eval"(...); or some such
thing).

Anyway, this was just a little diversion but I thought it
was quite cool. A really interesting twist is the
possibility of declaring SQLJ source, compiling live it to
Java source and then interpreting it via beanshell.

[Anonymous]

Logged In: YES
user_id=199381

Set low until work begins on really integrating Hiep's
embedded language patches properly.