Quick Links

Example of Using SQL on External Database

[Back to top]

The following script example connects to the database, manipulates the table and its values, and then finishes by executing a query to ensure the table has been populated as expected:

//Connect to database
def db = resp.dbConnect('datasource.my_database');
logger.debug('SQL examples', 'connected');

db.execute("insert into abc(a, b, c) values('a', 'b', 234)");
logger.debug('SQL examples', 'inser one row');

def results = db.queryForList ("select * from abc");
logger.debug('SQL examples', 'results='+results);

db.update("update abc set a='updated a' where a='a'")
results = db.queryForList ("select * from abc");
logger.debug('SQL examples', 'results='+results);

logger.debug('end', 'end');    

The following screenshot displays how this query will execute in Debug Tool:

See Connect to Datasource and Call Stored Procedure for another datasource connection example.

Unit Test for the Connection

[Back to top]

To create a unit test to test your connection, go to Setup > Development > Scripts and create a Unit Test script type. The following code sample illustrates the syntax you could use to test the connection:

//Connection test
def db = resp.dbConnect('datasource.mydb');
def query = "select count(*) as count from cs_participant";
def results = db.queryForList(query);

logger.debug('Test connection', 'size: ' + results.size);

if (results!=null) {

for(def r : results) {
def count = r.COUNT;
logger.debug('Test connection', 'COUNT = ' + count + '\n');

Important: Change the argument value of resp.dbConnect (string) in line 2 above, with the same name of your datasource setup.

See the following Knowledge Base article for a more comprehensive connection and unit testing example: 
How to Setup/Connect Commission TrueComp Database Inside of Workflow.