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 exampleDBInterface 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 an
UPDATE
query: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 |
---|---|---|
|
| SQL_INTEGER |
|
| SQL_FLOAT |
|
| SQL_REAL |
|
| SQL_VARCHAR |
|
| SQL_TIMESTAMP |
The following example illustrates how you can set different parameters:
Comments
Log in or register to comment.