JDBC adapter

The JDBC adapter allows TrustBuilder to send SQL-statements to a database.

Prerequisites

JNDI The database identification relies on data sources retrieved by means of the Java Naming and Directory Interface (JNDI). Data sources may be configured in an application server, and can include information such as database reference, user name and password. For standalone use, including unit tests, data sources may be created programmatically and bound to the proper JNDI names before running the application. In addition to these basic properties, other, non-functional properties may typically be configured on an application server, like the connection pool size and the size of the SQL statement cache.

Configuration

AdapterUniqueID

Unique name assigned to this adapter; the name is used to reference the adapter in the workflow. The ID has following requirements:

START with a letter or _ (underscore)
FOLLOWED by a combination of following characters: Letter, Number, '.' (dot), '-' (dash), '_' (underscore)

Unsupported FeaturesNot every database driver supports all the features provided by the JDBC interface. The UnsupportedFeatures parameter actually accepts a comma-separated list of options to be ignored. Specifying an unknown feature will result in a configuration error.

QueryTimeout: In particular, setting the query timeout in requests (either explicitly or by default) may fail. Hence this option may be disabled globally as follows. If this option is omitted, query timeouts will be set by default (either limited or unlimited as described in the query specification).

DataSourceThe data source field must represent a valid JNDI name identifying a data source. Failure to do so will lead to a fatal error during engine configuration.

DataSource URL The data source can also be a JDNI url.

CODE

jdbc:postgresql://localhost/dbname

Setting the username and password will encrypt the user password in the config file. This way you can avoid exposing the password in your application server context files.

Workflow Settings

A request for the adapter is prepared by specifying the following properties/scripts in the adapter activity:

Input Property: the variable containing the instructions the adapter have to execute
Output Property: the variable the adapter will store the response in after execution
Before Adapter Script: script that will be executed before calling the adapter
After Adapter Script: script that will be executed after the adapter fulfilled its task

Request - API

jdbcSelectRequest Creates a JDBC SELECT-request:

CODE

jdbcSelectRequest(SQL SELECT statement, SQL arguments)

Where:

SQL SELECT statement: Non-null, non-empty string, can contain questionmarks that will be replaced by the SQL arguments
SQL arguments: Allow the use of variables in the SQL Select Statement

Example:

CODE

jdbcSelectRequest("SELECT * FROM Emp WHERE employee_id = ?", employeeId)

jdbcUpdateRequest (aliases jdbcInsertRequest and jdbcDeleteRequest) Creates a JDBC request with zero or more updates:

CODE

jdbcUpdateRequest(SQL UPDATE statement, SQL arguments)

Where:

SQL SELECT statement: Non-null, non-empty string, can contain question marks that will be replaced by the SQL arguments
SQL arguments:Allow the use of bind variables in the SQL Select Statement

Example:

CODE

jdbcUpdateRequest("DELETE * FROM Emp WHERE employee_id = ?",employeeId)

jdbcQuery Create a JDBC query if multiple queries are required in batch / transaction (see jdbcMultiUpdateRequest)

CODE

jdbcQuery(SQL statement, SQL arguments)

Where:

SQL SELECT statement: Non-null, non-empty string, can contain questionmarks that will be replaced by the SQL arguments
SQL arguments:Allow the use of bind variables in the SQL Select Statement

Example:

CODE

var query1 = jdbcQuery("INSERT INTO Dept VALUES(?)",name);
var query2 = jdbcQuery("INSERT INTO Emp VALUES(?, ?, ?)",name, email ,employeeId);

jdbcMultiUpdateRequest

Create a JDBC request with one or more jdbcQuery objects.

CODE

jdbcMultiUpdateRequest([query1,query2])

Where:

queries to execute: queries to be executed in the same transaction, seperated with a comma

Example:

CODE

var q1 = tb.jdbcQuery("UPDATE PERSON set name = ? WHERE NAME = ?",'SMITH','SMITH2');
// Do the same query but with different parameters
q1.addParametersToBatch('John','John2');

var q2 = tb.jdbcQuery("DELETE * FROM PERSON WHERE NAME = ?",'SMITH');

var update = tb.jdbcMultiUpdateRequest([q1,q2]);
update.setAutoCommit(true)
update.setQueryTimeout(5);

Response - API

Common Properties The response API can be applied to the variable specified in the "output property" (see "Workflow Settings"): to verify whether the action performed by the adapter was successful, to query for the data returned by the adapter.

All responses have four properties in common:

status Status flag indicating whether the response is ok (0) or not (1).
substatus Response specific number indicating what the problem was, eg. http status code
message Response specific message in case there was a problem (can be null)
rc Return Code, a human readable code based on the substatus

The status flag indicates whether a request was valid yes or no; consequently, the message or return code (rc) can be used to give the end-user a reasonable explanation or send the information to the underlying logging system.

Adapter Specific Properties

size	Number of rows being returned
getRows()	Returns 2 dimensional array with the results
getRow(rowno)	Returns array with the result of row number (rowno)
getItemAsDate(rowitem)	Returns item from database as javascript Date

Note: since all items are rendered in javascript as strings, blob are rendered in their hex notation, date/time objects are rendered as "yyyy-MM-dd HH:mm:ss.S" where either date or time can be missing depending of the database type.

Response Codes

Status	Substatus	Description
0	0	OK
>0	1	SQL Error
	>1	Unknown error

Additional Notes

Load Balancing The adapter does not support load balancing.

Request - API

The following parameters can be given along:

setAutoCommit: indicates whether transactions will be committed implicitly (yes) or in non-autocommit mode (no). Default behavior: autocommit.
setQueryTimeout: timeout in seconds
setMaxRows: maximum results that will be returned

Working with dates

Converting a database timestamp can be done with the following javascript function

CODE

//date = timestamp received from database e.g: 2013-09-13 12:49:42.837
function parseDatabaseDate(date){
    var parts = date.split(' '),
        dt = parts[0].split('-'),
        ts = parts[1].split(':'),
        sms = ts[2].split('.');
    return new Date(dt[0],dt[1],dt[2],ts[0],ts[1],sms[0],sms[1]);
}

//converting to epoch
//select datefield from table where id='id'
var dbDate = workItem.databaseOutput.getRow(0)[0];
var myDate = parseDatabaseDate(dbDate);
var epochDate = myDate.getTime();

Example

Example 1 - jdbcSelectRequest

Request

CODE

function JDBCSelectEmployee(workItem){
   var employeeId = workItem.employeeId;
   var sql="SELECT * FROM Emp WHERE employee_id = ?";
   // We need to prepare the input of the adapter, this is stored in the workItem
   workItem.jdbcInput = tb.jdbcSelectRequest(sql,employeeId); 

   // Maximum returned results
   workItem.jdbcInput.setMaxRows(10);
   // Timeout in seconds
   workItem.jdbcInput.setQueryTimeout(5);
}

Response

CODE

var getUsersQueryResponse = workItem.jdbcGetUsersQueryResponse;
//setting errormessage and code in the workItem
workItem.code = getUsersQueryResponse.rc;
workItem.message = getUsersQueryResponse.message;

if(getUsersQueryResponse.status == 0){      
    for(var i = 0;i<getUsersQueryResponse.size;i++){
         if(getUsersQueryResponse.getRow(i) != null){
            var rowOut = getUsersQueryResponse.getRow(i);
            tb.log('Row '+i+' = '+rowOut);  
         }
     }
 }

Example 2 - jdbcUpdateRequest

Request

CODE

function JDBCDeleteEmployee(workItem){
  var employeeId = workIem.employeeId;
  workItem.jdbcInput = tb.jdbcUpdateRequest("DELETE * FROM Emp WHERE employee_id = ?",employeeId); //Remove an employee with prepared statements
  workItem.jdbcInput.setAutoCommit(true); // We want this done directly, not in an transaction
  workItem.jdbcInput.setQueryTimeout(5); // Set the timeout of the query 
}

Response

CODE

if(jdbcOutput.status == 0){     
    tb.log('Delete successful');
}

Example 3 - jdbcMultiUpdateRequest

Request

CODE

function JDBCMultiUpdateEmployee(workItem){
    var employeeId = workIem.employeeId;
    var name = workItem.name;
    var email = workItem.email;
    var q1 = tb.jdbcQuery("INSERT INTO Emp VALUES(?, ?, ? )",name, email ,employeeId );
    q1.addParametersToBatch('smith','jsmit@acme.org','44344'); // Add a new query with different parameters
    var q2 = tb.jdbcQuery("DELETE * FROM Emp WHERE NAME = ?",name); // Remove the added employee

    var update = tb.jdbcMultiUpdateRequest([q1,q2]); // This will run the query
    update.setAutoCommit(true)
    update.setQueryTimeout(5);
}

Response

CODE

if(jdbcOutput.status == 0){     
    tb.log('insert / Delete successful');
}

Tomcat datasource examples

To use the JDBC adapter in your tomcat installation you must set the resources. Adjust your Trustbuilder context xml file for the resource and your environment.

Postgres

CODE

tb.xml
<Resource name="jdbc/auth" auth="Container" type="javax.sql.DataSource"
       username="authuserr"
       password="*******"
       driverClassName="org.postgresql.Driver"
       url="jdbc:postgresql://localhost:5432/auth"
       maxActive="20"
       maxIdle="10"
       validationQuery="select 1" />

Microsoft SQL

CODE

tb.xml
<Resource name="jdbc/auth" auth="Container" type="javax.sql.DataSource"
     username="Trustbuilderuser"
     password="password"
     driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
     url="jdbc:sqlserver://SQLTWA\VLB;DatabaseName=Trustbuilder" />

Add the jdbc adapter in your config.xml with the correct datasource

CODE

<stb:Adapter stb:id="auth" xsi:type="stb:JdbcAdapter">
    <stb:DataSource>java:comp/env/jdbc/auth</stb:DataSource>
</stb:Adapter>