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.

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:

The following example illustrates how you can set different parameters: