SQLcl as a library in existing programs

I got a question over the weekend if SQLcl could be leveraged as library from inside a Jython program. This may seem like it's the same thing as adding jython to SQLcl to use instead of Javascript but it's a significant difference. This method allows for the use of SQLcl in any existing program. That means when the program needs to interact with the database, all the code in SQLcl that is tried and true can be leveraged.

This is a simple example to prove it's possible.  First the jar files need to be added to Jython's command line.

# all files in sqlcl/lib/*
# adjust to your install ( unzip ) of sqlcl
LIB=/Users/klrice/workspace/raptor_common/sqlcl/built/lib/
CP=
for  f in $(ls $LIB/*.jar); do
 echo $f
 CP=$CP:$f
done
echo --- $CP ---

#Start up jython 
jython -J-cp $CP 

Now that the needed libraries are in the path. It's quite easy to start using the same methods and classes that have been shown in various javascript examples.  This example gets a connection on it's own then instantiates SQLcl for programmatic use.

#import various things
from java.sql import DriverManager
from oracle.dbtools.db import DBUtil
from oracle.dbtools.raptor.newscriptrunner import *

#plain ol jdbc connection
conn  = DriverManager.getConnection('jdbc:oracle:thin:@//localhost:1521/orcl','klrice','klrice');

#get a DBUtil but won't actually use it in this example
util  = DBUtil.getInstance(conn);

#create sqlcl
sqlcl = ScriptExecutor(conn);

#setup the context
ctx = ScriptRunnerContext()

#set the context
sqlcl.setScriptRunnerContext(ctx)
ctx.setBaseConnection(conn);

#change the format
sqlcl.setStmt('set sqlformat json');
sqlcl.run();

#run the sql
sqlcl.setStmt('select * from emp');
sqlcl.run();

The output of this is simply the json output.