Home

dyna4jdbc

Introduction

dyna4jdbc is a JDBC driver implementation written in the Java programming language. It enables users to execute dynamic JVM languages or console-oriented external programs through the JDBC API, captures the output generated and parses it to a standard JDBC ResultSet, which the caller application can process further.

This approach combines the power of dynamic languages with the rich ecosystem of reporting and data visualisation tools, that support the JDBC standard. You can for example write a complex Groovy/Scala/JavaScript/Jython etc. JVM script or call any console application (Shell script, Python, R language, etc.) and visualize or process the results further from your favourite JDBC-compatible tool.

For a short feature tour, please visit the [official project home page] (http://dyna4jdbc.org/).

How does it work?

dyna4jdbc implements (a subset of the) JDBC API, so that reporting tools can interact with it like any other JDBC driver, which actually connect to a database. Internally, Java ScriptEngine API is used to invoke dynamic JVM languages (Scala, JavaScript, Groovy, Jython etc.) or a new process is started to run external console programs. The output is captured and is assumed to be a TAB (\t) delimited tabular result, which is parsed by the driver:

tabulators ( \t ) separate cells (columns)
newline ( \n ) starts a new row in the result set

Which languages does it work with?

dyna4jdbc is confirmed (via integration tests executed during every build) to work with the following langages:

Any other language, which properly implements ScriptEngine API (JSR-223: Scripting for the JavaTM Platform) should work out of the box.

As of now, the console application support is experimental / implemented partially.

Tool support

dyna4jdbc should work with any JDBC-compatible tool. It is tested and known to properly function with the following software products:

Status and availability

The first publicly available pre-release version has been published in the the project release download section.

Please note that currently, this library is under development and should be considered as "beta", with some limitations.

Dependencies

The driver is built into a self-contained JAR file. Any additional dependencies required to run a specific JVM language (e.g. Groovy/Scala etc language library JAR files) have to be installed separately by the user.

System Requirement

This driver was developed against Oracle Java Development Kit, version 8 using Java 8 language features. Java 8 is required for both building and running. It should be compatible with any operating system, where Java 8 is officially available.

User Manual

Documentation is in progress: this section contains only the essential basics required to get started.

Adding data to the result set

Simply echo (print) your results to the standard output using the host language. Each line emitted represents a row in the result set. By default, TAB (\t) separates cells (this can be customized in the driver configuration). The driver captures the values and makes reasonable efforts to heuristically guess the correct SQL type corresponding to the value.

Defining the JDBC headers

Column headers can either be automatically generated from the index of the column or defined by the first output line of the scipt. In the latter case, the script must emit a special formatting header, wich contains three fields separated by a colon (':') character.

Automatic detection

If the script does not emit a special first row to format columns, then the engine will automatically detect the data type and assign numeric columns names starting from 1.

For example, assume, an application connects to the JavaScript interpreter using the JDBC URL jdbc:dyna4jdbc:scriptengine:JavaScript and executes the following script:

print ("Foo\t123");
print ("and\t456");
print ("Bar\t789");

This will yield the following result set (notice the automatically assigned column headers 1 and 2):

1	2
Foo	123
and	456
Bar	789

User-defined column specification

If the script wants to specify the columns headers and data types, it should emit a special first row containing a "formatting header", which defines the column name and its data type.

When columns are defined by the user script, then the first output row must emit a TAB (\t) separated list of column specification, where each one matches the following pattern. Legend: [ ]: mandatory ; < >: optional

[Column Header] : <SQL type definition> : <additional flags>

Column Header: The human-readable name assigned to the column (see java.sql.ResultSetMetaData.getColumnLabel(int))
SQL type definition(optional): the SQL type definition of the column, auto-detected if not present
Additional flags(optional): additional formatting flags for the column

Important: The formatting header MUST ALWAYS CONTAIN TWO COLONS, even if a field is not used!

For example, assume, an application connects to the JavaScript interpreter using the JDBC URL jdbc:dyna4jdbc:scriptengine:JavaScript and executes the following script:

print ("Some Text:VARCHAR(10):\tNumbers:INTEGER(5):"); 
print ("Foo\t123");
print ("and\t456");
print ("Bar\t789");

This will yield the following result set (notice that the first row does NOT appear in the result set, but instead, used to configure the column!):

Some Text	Numbers
Foo	123
and	456
Bar	789

Please note that specifying the column metadata is optional: the engine can still auto-detect the correct type even if only the column name is specified. (Actually, the script supplied column configuration overrides the column specification established by the engine; the analysis is still performed always).

Caution:

a first line, which does not match the expected layout of a "formatting header", will be interpreted as part of the result set and the column headers will be generated automatically.
automatic and user-defined column specification cannot be mixed.

Examples for handling of "formatting header" (= the first line) output:

FOO::\tBAR:: ==> Columns named as 'FOO' and 'BAR', values from the second output row appear in the result set as first row.
FOO\tBAR ==> Columns are named as 1 and 2, while 'FOO' and 'BAR' appear in result set as the first entry.
FOO:\tBAR: ==> Columns are named as 1 and 2, while 'FOO:' and 'BAR:' appear in result set as the first entry.
FOO::\tBAR: ==> Error condition detected by the driver, error INCONSISTENT_HEADER_SPECIFICATION is emitted.
FOO:\tBAR:: ==> Error condition detected by the driver, error INCONSISTENT_HEADER_SPECIFICATION is emitted.
FOO\tBAR:: ==> Error condition detected by the driver, error INCONSISTENT_HEADER_SPECIFICATION is emitted.
FOO::\tBAR: ==> Error condition detected by the driver, error INCONSISTENT_HEADER_SPECIFICATION is emitted.

Check the samples on details regarding how to generate output properly.

Adding dynamic language JARs to the class path

If the host application does not embed the classes necessary to run the Script Engine, the location of the external JAR file must be supplied in the connection URL or as driver properties.

Example: specifying Groovy script engine to be used and adding the JAR file c:\DEV\groovy-all-2.4.6.jar to the classpath can be achieved by using the following JDBC connection URL:

jdbc:dyna4jdbc:scriptengine:groovy:classpath=c:/DEV/groovy-all-2.4.6.jar

Samples

Groovy

NOTE: In addition to the driver JAR itself, the full Groovy language package has to be available on the classpath.

The following sample demonstrates fetching JSON data from Google Finance Internet service and transforming it into a tabular format. (NOTE: This is just a technical sample to demonstrate the capabilities of the tool: please consult Google Use of Service Conditions before you actually use any data from their API!)

Use the following URL to establish the connection: jdbc:dyna4jdbc:scriptengine:groovy:classpath=<groovy JAR path>

Important: in the URL above, replace <groovy JAR path> with the actual path to your groovy-all-X.X.X.jar file. Otherwise the driver cannot locate Groovy runtime and will not be able to establish a Groovy script session.

For this sample, we execute the Groovy script below. You can see how straightforward it is: it simply fetches data and outputs the desired results to the standard output via println method:

import groovy.json.JsonSlurper

def printRow(String... values) { println values.join("\t") }

def jsonData = new URL("http://www.google.com/finance/info?infotype=infoquoteall&q=NASDAQ:AAPL,IBM,MSFT,GOOG").text.replaceFirst("//", "")
def data = new JsonSlurper().parseText(jsonData)

printRow "Ticker::", "Name::", "Open::", "Close::", "Change::" // first line with the formatting header
data.each { printRow it["t"], it["name"], it["op"], it["l_cur"], it["c"] }

Full Java source code (Remember: both driver JAR and Groovy JARs have to be on the classpath):

package samples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
public class GroovyDemo {

    private static final String GROOVY_SCRIPT = ""
            + "	import groovy.json.JsonSlurper														\n"
            + "																						\n"
            + " def printRow(String... values) { println values.join(\"\t\") }						\n"
            + " def jsonData = new URL('http://www.google.com/finance/info?"
                + "infotype=infoquoteall&q=NASDAQ:AAPL,IBM,MSFT,GOOG').text.replaceFirst('//', '')	\n"
            + " def data = new JsonSlurper().parseText(jsonData)									\n"
            + " printRow 'Ticker::', 'Name::', 'Open::', 'Close::', 'Change::'						\n"
            + " data.each { printRow it['t'], it['name'], it['op'], it['l_cur'], it['c'] } 			\n";

    public static void main(String[] args) throws SQLException {

        try (Connection connection = DriverManager.getConnection("jdbc:dyna4jdbc:scriptengine:groovy")) {

            try (Statement statement = connection.createStatement()) {
                boolean results = statement.execute(GROOVY_SCRIPT);
                while (results) {
                    try (ResultSet rs = statement.getResultSet()) {

                        printResultSetWithHeaders(rs);
                    }

                    results = statement.getMoreResults();
                }
            }
        }
    }

    private static void printResultSetWithHeaders(ResultSet rs) throws SQLException {
        ResultSetMetaData metaData = rs.getMetaData();
        final int columnCount = metaData.getColumnCount();

        for (int i = 1; i <= columnCount; i++) {
            String columnLabel = metaData.getColumnLabel(i);
            int columnDisplaySize = metaData.getColumnDisplaySize(i);
            String formatStr = "%" + columnDisplaySize + "s | ";
            System.out.format(formatStr, columnLabel);
        }
        System.out.println();

        for (int i = 1; i <= columnCount; i++) {
            int columnDisplaySize = metaData.getColumnDisplaySize(i);
            String formatStr = "%" + columnDisplaySize + "s | ";
            System.out.format(String.format(formatStr, "").replace(' ', '-'));
        }
        System.out.println();

        while (rs.next()) {

            for (int i = 1; i <= columnCount; i++) {
                int columnDisplaySize = metaData.getColumnDisplaySize(i);
                String formatStr = "%" + columnDisplaySize + "s | ";
                System.out.format(formatStr, rs.getString(i));
            }
            System.out.println();
        }
    }

}

Provide feedback

Saved searches

Use saved searches to filter your results more quickly