Skip to content

Home

Jump to bottom
Peter G. Horvath edited this page May 30, 2018 · 39 revisions

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/).

package sample;

import java.sql.*;

public class HelloWorldSample {

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

        String url = "jdbc:dyna4jdbc:scriptengine:JavaScript";

        try (Connection connection = DriverManager.getConnection(url)) {

            try (Statement statement = connection.createStatement()) {

                statement.executeUpdate(" var msg = 'Hello World'; ");
                ResultSet resultSet = statement.executeQuery(" print(msg); ");

                while (resultSet.next()) {
                    String string = resultSet.getString(1);

                    System.out.println(string);
                }
            }
        }
    }
}

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

Publicly available release version are published to Maven Central repository.

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

NOTE: Documentation is in progress: the manual is not yet finalized.

Configuration Parameters

See separate Wiki page: Configuration Parameters

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 specifications, where each entry matches the following pattern. Legend: [ ]: mandatory ; < >: optional

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

  1. Column Header: The human-readable name assigned to the column (see java.sql.ResultSetMetaData.getColumnLabel(int))
  2. SQL type definition(optional): the SQL type definition of the column, auto-detected if not present
  3. 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:

  1. FOO::\tBAR:: ==> Columns named as 'FOO' and 'BAR', values from the second output row appear in the result set as first row.
  2. FOO\tBAR ==> Columns are named as 1 and 2, while 'FOO' and 'BAR' appear in result set as the first entry.
  3. FOO:\tBAR: ==> Columns are named as 1 and 2, while 'FOO:' and 'BAR:' appear in result set as the first entry.
  4. FOO::\tBAR: ==> Error condition detected by the driver, error INCONSISTENT_HEADER_SPECIFICATION is emitted.
  5. FOO:\tBAR:: ==> Error condition detected by the driver, error INCONSISTENT_HEADER_SPECIFICATION is emitted.
  6. FOO\tBAR:: ==> Error condition detected by the driver, error INCONSISTENT_HEADER_SPECIFICATION is emitted.
  7. 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.

Passing Parameters to Scripts

Parameters can be passed via java.sql.PreparedStatement#setXXXX(...) methods: the values set will be visible as variables named according to the index: parameter1, parameter2 and so on.

For details and a full sample, please check the page Passing Parameters to Scripts

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

Clone this wiki locally