Dynamically adding a custom JDBC driver

The latest OpenAF versions enable the dynamic loading of custom JDBC drivers.

Let's take, for example, the CSV JDBC driver. After downloading the jar file to an empty folder let's also create a sample CSV file (test.csv):

"key";"value"
1; "Item 1"
2; "Item 2"
3; "Item 3"

Then, on an OpenAF script or console execute:

> loadExternalJars(".")
> var db = new DB("org.relique.jdbc.csv.CsvDriver", "jdbc:relique:csv:.?separator=;&fileExtension=.csv", "sa", "sa");
> sql db select * from test
key|  value
---+---------
1  | "Item 1"
2  | "Item 2"
3  | "Item 3"
[#3 rows]

So, what happened:

• The loadExternalJars function tries to dynamically load all .jar files on the path provider (using the OpenAF's custom class loader).
• When creating the DB object instance, OpenAf will try to find the provided JDBC class driver and will load it through an internal "proxy", if needed.
• The returned DB object instance will work as the H2, PostgreSQL, etc... drivers (within the limits of the JDBC driver).

Getting a DB table's columns/fields

Ever wonder how the OpenAF-console can do commands like "dsql" to list all the columns of a table (or a database object) and quickly? It's all on the JDBC metadata for any query over all columns. No need to go to the specific database column catalog.

Getting the metadata

For each of the tables identified on the catalog of the corresponding database:

1. Performe a simple query to access the table's metadata BUT getting the JDBC ResultSet object:

var db = new DB(jdbcURL, jdbcUsername, jdbcPassword);
var rs = db.qsRS("select * from \"" + schemaName + "\".\"" + tableName + "\"");

Note: If you want to use a specific JDBC driver class just replace by "new DB(jdbcDriver, jdbcURL, jdbcUsername, jdbcPassword)" otherwise it will try to guess from the jdbcURL for known drivers.

2. Get the number of columns from the JDBC ResultSet object:

var numberOfColumns = rs.getMetadata().getColumnCount();

3. Get the columns' metadata:

var columns = []; 
for(let ci = 1; ci <= numberOfColumns; ci++) { 
    columns.push({ 
        name : rs.getMetaData().getColumnName(ci), 
        type:  rs.getMetaData().getColumnTypeName(ci).toUpperCase(), 
        size : rs.getMetaData().getColumnDisplaySize(ci),
        scale: rs.getMetaData().getScale(ci) 
    })
};

4. (please) Close the result set object and any possible transaction (e.g. because PostgreSQL):

rs.close();
db.rollback();

And there you have it, a columns array where each map entry has the necessary column information of the specific table:

> table columns
     name      |  type   |size|scale
---------------+---------+----+-----
ID             |NUMERIC  |11  |0
SOME           |VARCHAR  |35  |0
TKEY           |VARCHAR  |512 |0
VALUE          |NUMERIC  |25  |6
CREATED_BY     |VARCHAR  |64  |0
CREATED_DATE   |TIMESTAMP|22  |0
MODIFIED_BY    |VARCHAR  |64  |0
MODIFIED_DATE  |TIMESTAMP|22  |0
[#8 rows]

What about the list of tables?

Ok, for the list of tables you might really need to go the database's catalog. Here are some examples:

Oracle

To get all tables in a specific Oracle schema:

var tables = mapArray(db.qs("select owner || '.' || table_name from all_tables where owner = ?", ['mySchema'], true).results, [ "table_name" ]);

PostgreSQL

To get all tables in a specific PostgreSQL schema:

var tables = mapArray(db.qs("select table_schema || '.' || table_name from information_schema.tables where table_schema = ?", ['mySchema'], true).results, [ "table_name" ]);

H2

To get all tables in a specific H2 schema:

var tables = mapArray(db.qs("select table_schema || '.' || table_name from information_schema.tables where table_schema = ?", ['mySchema'], true).results, [ "table_name" ]);

Note: Yes, it's equal to PostgreSQL

How to copy CLOBs between two databases

Specially in Oracle is not very easy to get the CLOB field value from a source database and insert/update it on another CLOB field on a target database.

In OpenAF the DB.q and DB.u functions are "CLOB/BLOB" aware and will try to convert them to strings to make it seamless. But there is the DB.Lob functions to handle them in particular.

The next example shows how to retrieve CLOB values from one database and inserting them on a temporary table on a target database:

log("Connecting...");

var db1 = new DB("jdbc:oracle:thin:@//1.2.3.1:1521/SOURCE", "loginSOURCE", "passwordSOURCE");
var db2 = new DB("jdbc:oracle:thin:@//1.2.3.2:1521/TARGET", "loginTARGET", "passwordTARGET");

log("Retrieving data...")

var res = db1.q("select obj_uuid, obj_definition from objects_table");

log("#" + res.results.length + " records retrieved");

log("Copying data...");
db2.u("truncate table TEMP_TABLE"); // Assuming you have a TEMP_TABLE already created on db2

var c = 0;
for(i in res.results) {
   var line = res.results[i];
   c += db2.uLobs("insert into TEMP_TABLE (OBJ_UUID, OBJ_DEFINITION) values (:1, :2)", [ line.OBJ_UUID, line.OBJ_DEFINITION ]);
}

log("#" + c + " records copied.");

db2.commit();
db2.close();
db1.close();

log("Done");

The result will be similar to:

Thu Apr 15 2015 12:32:25 GMT-0400 (EDT) | INFO | Connecting...
Thu Apr 15 2015 12:32:25 GMT-0400 (EDT) | INFO | Retrieving data...
Thu Apr 15 2015 12:32:26 GMT-0400 (EDT) | INFO | #3497 records retrieved
Thu Apr 15 2015 12:32:26 GMT-0400 (EDT) | INFO | Copying data...
Thu Apr 15 2015 12:32:30 GMT-0400 (EDT) | INFO | #3497 records copied.
Thu Apr 15 2015 12:32:30 GMT-0400 (EDT) | INFO | Done

Using an in-memory database

OpenAF comes with the H2 database embeeded. The main objective is, of course, to interact with H2 databases. But it also provides other perks (e.g. like the MVStore). One of them is an "in-memory" H2 database.

Why would you want or need an "in-memory" database? There are a lot of uses that we won't elaborate now but you can read them in wikipedia. But since H2 is also a relational database engine it can be helpful even if it just for testing.

Creating

Creating the H2 in-memory database it's easy. Just provide a name and execute:

> var db = createDBInMem("testDB");

Why a name? Because you can have several databases providing you have the memory capacity for them. The OpenAF's createDBInMem function returns an already instatiated db object that you can now use.

Using it

> db.u("CREATE TABLE test (id NUMBER(10), desc VARCHAR2(255))");
0
> db.q("SELECT * FROM test");
{
  "results": []
}

Let's insert some data:

> db.us("INSERT INTO test (id, desc) VALUES (?, ?)", [ 0, "Result 0" ]);
1
> db.q("SELECT * FROM test")
{
  "results": [
    {
      "ID": 0,
      "DESC": "Result 0"
    }
  ]
}

Let's make it a hundred dummy data records:

> var ar = []; for(var ii = 1; ii < 99; ii++) { ar.push([ ii, String("Result " + ii)]); }
98
> db.usArray("INSERT INTO test (id, desc) VALUES (?, ?)", ar)
98
> db.commit();
> 
> db.q("SELECT COUNT(1) c FROM test")
{
  "results": [
    {
      "C": "99"
    }
  ]
}

Persisting

If you exit OpenAF the in-memory database will, of course, lose all it's data. But what if you want to persist it? There are some helper functions for that: persistDBInMem and loadDBInMem:

> persistDBInMem(db, "test.sql");

Then on another OpenAF execution:

> var db = createDBInMem("testDB");
> loadDBInMem(db, "test.sql")
5
> db.q("select count(1) C from test")
{
  "results": [
    {
      "C": "99"
    }
  ]
}

Note: the generated files are SQL files. There isn't any intention of supporting large volumes of data databases.

Setting DB auto commit

By default OpenAF opens all database connections in "autocommit=false" mode. But in some special cases you might need to turn it on. Let's see one of those cases:

Creating a PostgreSQL tablespace example

If you open a PostgreSQL connection and try to create a tablespace "TEMP" as you would usually do, this will happen:

> var db = new DB("jdbc:postgresql://some.host:5432/postgres", "adminUser", "adminPass");
> db.u("create tablespace temp location '/data'");
-- JavaException: org.postgresql.util.PSQLException: ERROR: CREATE TABLESPACE cannot run inside a transaction block

That's because PostgreSQL considers anything with "autocommit=false" to be a transaction block.

How to set auto commit to on?

To bypass this situation, on a dedicated DB connection, execute the following:

> var db = new DB("jdbc:postgresql://some.host:5432/postgres", "adminUser", "adminPass");
> db.getConnect().setAutoCommit(true);
> db.u("create tablespace temp location '/data'");
1

The getConnect method will return you the JDBC connection instance object which, among others, allows you to execute the setAutoCommit method. That's enough to then execute the "create tablespace" statement without being in a transaction block.

Casting values in PostgreSQL and H2 for OpenAF

When performing specific queries in PostgreSQL and H2 although some results are clearly numeric that are returned as strings. This might affect scripts that previously accessed Oracle and now, accessing using the same query in PostgreSQL and H2, have a different behavior.

Example with PostgreSQL

Let's start with a quick PostgreSQL example:

> var db = new DB("jdbc:postgresql://127.0.0.1/postgres", "postgres", "admin")
> db.q("select count(1) from (select 1 a) as a")
{
  "results": [
    {
      "count": "1"
    }
  ]
}

The "count" value is returned as a string despite that count is always numeric. To solve this you can cast the specific column on the SQL select:

> db.q("select cast(count(1) as integer) from (select 1 a) as a")
{
  "results": [
    {
      "count": 1
    }
  ]
}
>

Another option is to cast in javascript:

> var res = db.q("select count(1) from (select 1 a) as a")
> var c = Number(res.results[0].count);
1

Example with H2

The same example but with H2:

> var db = createDBInMem();
> db.q("select count(1) abc from ( select 1 abc, 'cenas' xpto from dual )")
{
  "results": [
    {
      "ABC": "1"
    }
  ]
}

The count field 'abc' is returned as string. But we can cast in the same way it has handled in PostgreSQL:

> db.q("select count(1) abc from ( select 1 abc, 'cenas' xpto from dual )")
{
  "results": [
    {
      "ABC": 1
    }
  ]
}

Another options is always to cast it in javascript:

> var db = createDBInMem();
> var res = db.q("select count(1) abc from ( select 1 abc, 'cenas' xpto from dual )")
> var c = Number(res.results[0].ABC);
1

Note: For date types ensure that you have executed db.convertDates(true) to ensure the native conversion in Java.