The Database Interface method

The Database Interface method is used to access relational databases efficiently and in a robust manner. In order to use Database Interface, a Database Driver Module must be installed on the specific database that you want to query.

The ETL Development API provides an additional DBInterface class that is responsible for efficient usage of Database Interface.

This class contains various built-in methods that can be utilized to perform various database activities and operations. The following code examples illustrate some of these methods:

  • Connect to, and disconnect from, a database:

    Java example
    DBInterface dbif = new DBInterface();
    dbif.connect(<driver>, <dburl>, <dbusr>, <dbpwd>);
    ...the query goes here...
    dbif.disconnect();

    where <driver> is a jdbc driver and <dburl> is the jdbc connection url 

  • Prepare and execute the query statement once a connection to the database has been established:

    dbif.prepare("SELECT * FROM example");
    //the result is fetched into a Dataset object
    DataSet res = dbif.executeQuery();
  • Execute anUPDATEquery:

    dbif.prepare("UPDATE table WHERE ts=?");
    dbif.setTSParam(1,"2004-01-01 10:00:00");
    dbif.executeUpdate();
  • Provide transaction support, that lets you rollback a transaction if a query encounters an error:

    dbif.beginTransaction();
    dbif.prepare("UPDATE table WHERE ts=?");
    dbif.setTSParam(1,"2018-01-01 10:00:00");
    dbif.executeUpdate();
    dbif.prepare("UPDATE table2 WHERE ts=?");
    dbif.setTSParam(1,"2018-01-01 10:00:00");
    dbif.executeUpdate();
    dbif.endTransaction();
  • Call stored procedures:

    dbif.beginTransaction();
    dbif.prepare("call myprocedure(?,?)");
    dbif.setStringParam(1,"test");
    int ires = dbif.executeUpdateIntReturn(2);
    dbif.endTransaction();

    If the stored procedure returns an integer value, the executeUpdateIntReturn method must be used to read the output value. This method requires the index of the return parameter as input.

Note

The DBInterface class logs activities and errors using the Log class.

Setting parameters

Parameters can be set using setParam methods. They work positionally with a dynamic parameter in an SQL query – the question mark (?).
A dynamic parameter (for which '?' works as a placeholder) is a parameter to an SQL statement whose value is not specified during statement creation.

The following table provides a list of methods that set parameters:

Method name

Parameter type

Expected DBI type

setIntParam

integer

SQL_INTEGER

setFloatParam

float

SQL_FLOAT

setRealParam

real

SQL_REAL

setStringParam

string

SQL_VARCHAR

setTSParam

timestamp

SQL_TIMESTAMP
(Format: YYYY-MM-DD HH:MI:SS)

The following example illustrates how you can set different parameters:

Example

dbif.prepare("SELECT * FROM example WHERE a=? AND b=?");
dbif.setStringParam(1,"TEST");
dbif.setIntParam(2,3);
DataSet res = dbif.executeQuery();
Was this page helpful? Yes No Submitting... Thank you

Comments