Wednesday 18 September 2013

Using IKVM on a Apache Derby Database

After creating the .Net version of the Derby dlls using:

var dllsFolder = "jars".tempDir(false);
    
var ikvm = new API_IKVM();
var derby = @"E:\_Code_Tests\Java\db-derby-10.10.1.1-bin\lib\derbyclient.jar";
var derbyClient = @"E:\_Code_Tests\Java\db-derby-10.10.1.1-bin\lib\derbyclient.jar";
    
ikvm.convertJarFileIntoDotNetAssembly(derby, dllsFolder);
ikvm.convertJarFileIntoDotNetAssembly(derbyClient, dllsFolder);
dllsFolder.startProcess();
    
//O2File:API_IKVM.cs


I was able to create and consume an in-memory instance of Derby using a variation of the sample script provided in the IVMK’s blog post: Running Derby on IKVM (http://www.weblog.ikvm.net/PermaLink.aspx?guid=c129dd0d-bfec-4465-9574-5275ab785238)

var ikvmc = new API_IKVMC();  // this will setup the correct dlls
    
java.lang.Class.forName(typeof(org.apache.derby.jdbc.EmbeddedDriver).AssemblyQualifiedName); //loads driver in memory
    
var con = DriverManager.getConnection("jdbc:derby:testdb2;create=true");
    
Statement st = con.createStatement();
st.execute("create table foo (id integer)");
    
PreparedStatement pst = con.prepareStatement("insert into foo (id) values (?)");
for(int i = 0; i < 1000; i++)
{
    pst.setInt(1, i);
    pst.execute();
}
    
pst = con.prepareStatement("select count(*) from foo where id = ?");
int total = 0;
for(int i = 0; i < 1000; i++)
{
    pst.setInt(1, i);
    ResultSet rs = pst.executeQuery();
    rs.next();
    total += rs.getInt(1);
    rs.close();
}
return total;
//using java.sql;

//O2Ref:C:\Users\o2\AppData\Roaming\OWASP_O2_Platform_5.3\9_15_2013\jars\derby.dll

//O2File:API_IKVMC.cs
//using O2.XRules.Database.APIs.IKVM
//O2Ref:IKVM\ikvm-7.2.4630.5\bin\IKVM.OpenJDK.Core.dll
//O2Ref:IKVM\ikvm-7.2.4630.5\bin\IKVM.OpenJDK.Jdbc.dll


Here is a more advanced script that connects to a live derby database and shows the contents of its tables in a table list:

var connectionString = "jdbc:derby://localhost:8108/dbAddress;user=admin;password=******;";
    
var ikvmc = new API_IKVMC();  // this will setup the correct dlls
    
java.lang.Class.forName(typeof(org.apache.derby.jdbc.ClientDriver).AssemblyQualifiedName); //loads driver in memory
connectionString.info();
var con = DriverManager.getConnection(connectionString);
    
var topPanel = panel.clear().add_Panel();
var tableList = topPanel.add_TableList();
var tables = topPanel.insert_Left(250).add_TreeView();
var SEPARATOR = "--------";
    
Action<string,Action<java.sql.ResultSet, List<string>>> executeQuery =
    (tableName,rowDataCallback)=>{
                                    try
                                    {
                                        if(tableName == SEPARATOR)
                                            return;
                                        var st = con.createStatement();
                                        var resultSet = st.executeQuery("select * from {0}".format(tableName));
                                        tableList.clearTable();
                                        var metadata = resultSet.getMetaData();
                                        var columns = new List<string>();
                                        for(int i=1; i <= metadata.getColumnCount();i++)
                                            columns.add(metadata.getColumnName(i));
                                        
                                        tableList.add_Columns(columns);
                                        tableList.columnsWidthToMatchControlSize();
                                        while(resultSet.next())
                                            rowDataCallback(resultSet,columns);
                                    }
                                    catch(Exception ex)
                                    {
                                        ex.log();
                                    }
                                };
    
Action<string> showTable = 
    (tableName)=>{    
                    "showing Table: {0}".info(tableName);
                    executeQuery(tableName,
                                 (resultSet,columns)=>{
                                                         var results = new List<string>();
                                                         foreach(var name in columns)
                                                             try
                                                            {
                                                                 //results.add(resultSet.getObject(name).str());
                                                                 results.add(resultSet.getString(name).str());
                                                             }
                                                             catch(Exception ex)
                                                             {
                                                                 ex.log();
                                                             }
                                                         tableList.add_Row(results);                    
                                                      });                    
        };
    
Action loadTableNames = 
    ()=>{
            var tableNames = new List<string>();
            executeQuery("SYS.SYSTABLES",
                         (resultSet,columns)=>{
                                                     tableNames.add(resultSet.getString("TABLENAME"));                                                     
                                               });
            tables.add_Nodes(tableNames.sort());
        };
            
tables.afterSelect<string>(showTable);
tables.add_Node("SYS.SYSTABLES","SYS.SYSTABLES");
tables.add_Node("PROJECT","PROJECT");
tables.add_Node("RULEVIEW","RULEVIEW");
tables.add_Node(SEPARATOR);
loadTableNames();
     
 
return "done";    
//return resultSet.getSQLXML(0);

//using java.sql;
    
//O2Ref:C:\Users\o2\AppData\Roaming\OWASP_O2_Platform_5.3\9_15_2013\jars\derby.dll
//O2Ref:C:\Users\o2\AppData\Roaming\OWASP_O2_Platform_5.3\9_15_2013\jars\derbyclient.dll
    
//O2File:API_IKVMC.cs
//using O2.XRules.Database.APIs.IKVM
//O2Ref:IKVM\ikvm-7.2.4630.5\bin\IKVM.OpenJDK.Core.dll
//O2Ref:IKVM\ikvm-7.2.4630.5\bin\IKVM.OpenJDK.Jdbc.dll


The script shown above looks like this when executed:

image