SQLite JDBC driver not working on Aria Automation 8.18.1

[jernejj]

Hi,

I have a problem with SQLite JDBC driver in Aria Automation Orchestrator 8.18.1.24266232

I register driver by uploading jar file to appliance and set system property (as specified here).

The problem is - I cannot call getColumnCount() or getColumnName() on ResultSetMetadata object.

It works fine with Aria Automation Orchestrator 8.14.0.22610007.

I am using SQLite JDBC driver verison 3.47.1.0 from https://github.com/xerial/sqlite-jdbc/releases 

 

This is the source code for POC

function getData(query, cn) {
    var stmt = cn.prepareStatement(query);
    var result = stmt.executeQuery();
    return result;
}

function execData(query, cn) {
    var stmt = cn.prepareStatement(query);
    stmt.executeUpdate();
}

var url = "jdbc:sqlite::memory:";
var jdbc = new JDBCConnection("org.sqlite.JDBC");
var conn = jdbc.getConnection(url);

execData("create table test123 ([aaa] varchar(3))", conn);
execData("insert into test123 ([aaa]) values ('aa')", conn);
try {
    var result1 = getData("select sqlite_version() as ver", conn);
    while (result1.next()) {
        var value = result1.getString("ver");
        System.log("SQLITE version: "+value);
    }
    result1.close();
} catch (e) {
    System.log("SQLITE version: NOT SQLITE");
}

var result2 = getData("select * from test123", conn);
var md = result2.getMetaData();

System.log("Metadata object: "+md);
System.log("ObjectClassName: "+System.getObjectClassName(md));
try {
    System.log("Num of columns: "+md.getColumnCount());
} catch (e) {
    System.log("Num of columns: ERROR - "+e);
}
try {
    System.log("Column name: "+md.getColumnName(1));
} catch (e) {
    System.log("Column name: ERROR - "+e);
}
System.log("Column methods on getMetaData instance (via class.getMethods()):");
var methods = md.class.getMethods();
for (var i = 0; i < methods.length; i++) {
    var m = methods[i].getName();
    if (m.toLowerCase().indexOf("column") !== -1) System.log(" - "+m+" ["+methods[i]+"]");
}

result2.close();

Running this code on vRO  8.14.0 returns correct results:

Running this code on vRO  8.18.1 does not work (lines 5 & 6).

[jernejj]

Down the rabbit hole -- but what a way to spend festive time :)

Not really sure what changed, but we can get around (for now) with some hacking - using "free" Rhino engine and java reflection :)

1. Get access to whole Java context inside JS - follow Stefan Schnell's post about how to do that - https://community.broadcom.com/vmware-cloud-foundation/discussion/tip-use-java-classes-without-shutter-file
   1. >vracli vro properties set -k com.vmware.scripting.javascript.allow-native-object -v true
2. Use java reflection
   1. Loop over .class.getMethods() and filter by getName() .. to get references to getColumnCount, getColumnName (instance of java.lang.reflect.Method) - that actually works without step 1
   2. Use invoke() method to get result
      1. There is a problem with getColumnName since its parameter is integer but JS deals with number (and is considered double in java)
      2. We need full access to Java to use "java.lang.Integer(5)" here 

Source code snippet:

// var md = result.getMetaData();
 
function getColumns(md) {
    if (typeof md.getColumnCount === "undefined") { // vRO 8.18+ & SQLite
        return getColumnsReflection(md);
    }
    var retVal = [];
    var numColumns = md.getColumnCount();
    // System.log("Normal way");
    for (var i = 1; i <= numColumns; i++) {
        var cn = md.getColumnName(i);
        retVal.push(cn);
    }
    return retVal;
}
 
function getColumnsReflection(md) {
    // System.log("Reflection way");
    var cntMethod;
    var nameMethod;
    var methods = md.class.getMethods();
    for (var i = 0; i < methods.length; i++) {
        var mName = methods[i].getName();
        if (mName === "getColumnCount") cntMethod = methods[i];
        if (mName === "getColumnName") nameMethod = methods[i];
    }
    var retVal = [];
    var numColumns = cntMethod.invoke(md);
    for (var i = 1; i <= numColumns; i++) {
        var intParam = java.lang.Integer(i); // this is line that requires unlocked Rhino egine Java access
        var cn = nameMethod.invoke(md, intParam);
        retVal.push(cn);
    }
    return retVal;
}

[WhiteForEver]

Thank you very much for that important insight.