Share:
Developers » Scripting Code Samples » SQL on External Databases » Database Queries

PREVIOUS  |  NEXT

Database Queries

Quick Links


Once you create a valid database object, you can use it in a script to execute operations against the database, such as:

  • Updating table queries
  • Select queries
  • Stored procedures
  • Executing other queries

See the following sections for methods and code samples for these operations. For information on creating a database object, see Datasource.

Update Table Queries

Update Methods

[Back to top]


An SQL update operation such as an insert, update, or delete statement returns the number of rows affected. Workflow supports the following methods:

  • int update(String)
  • int update(String, Object [])
  • int [] batchUpdate(String [])

See the following code samples for more information on syntax and usage of these methods.


Update Code Sample

[Back to top]

The following code sample demonstrates the use of the update method:

def sql = "update customers set last_name='Smith' where first_name='John'";

db.update(sql);


The following code sample demonstrates an SQL update operation that binds the given arguments and returns the number of rows affected:

def sql = "update customers set last_name=? where first_name=?";

db.update(sql, "Smith", "John");

def results = db.queryForList("select * from testcustomers");
for (def r : results) {

logger.debug('Customer name', r.first_name + ' ' +r.last_name= + '\n');
}​


Batch Update Code Sample

[Back to top]

You can apply multiple SQL updates on a single JDBC statement using batching. Updating multiple data sets by batch can save you time and is preferred over the update method, which restricts you to updating only one row at a time. 

Use the following syntax for batch updates:

// batch insert
def sqlList = new ArrayList();

for (r in results){
String insertSQL = """
insert into YOUR_EXTERNAL_TABLE (COLUMN1, COLUMN1, COLUMN1)
values (‘${r.firstData}‘,’${r.secondData}‘, ‘${r.thirdData}’)
"""
sqlList.add(insertSQL);
}

def queriesArray = sqlList.toArray(new String[sqls.size()]);
db.batchUpdate(queriesArray);


// batch update

sqlList = new ArrayList();

for (r in results){
String updateQuery = """
update YOUR_EXTERNAL_TABLE set COLUMN1='${r.firstData}' where COLUMN2=${r.firstData}
"""
sqlList.add(updateQuery);
}

queriesArray = sqlList.toArray(new String[sqls.size()]);
db.batchUpdate(queriesArray);

Select Queries

Select Methods

[Back to top]

Workflow supports the following methods:

  • List queryForList(String)
  • List queryForList(String, Object[])
  • List queryForList(String, Integer Integer)
  • Map queryForMap(String)
  • Map queryForMap(String, Object[])


Selecting Data

[Back to top]

A query is made to SQL to create a prepared statement and list of arguments to bind to the query, expecting a result list. The results will be mapped to a list of maps.

def results = db.queryForList ("select * from testcustomers where first_name = ?",  "John");


Execute a query for a result map, given a static SQL. The query is expected to be a single row query and the result row will be mapped to a map.

def row = db.queryForMap("select * from customers where first_name='Joe'");

logger.debug('Workflow Online Help Example', row.first_name);

Stored Procedures

[Back to top]

Stored procedures enable you to combine a set of SQL statements that you can later use in a single call to your datasource.  This reduces the number of times that you will need to query the datasource which can significantly improve the performance of your scripts. Using stored procedures also offers other benefits such as increased security and the preservation of data integrity.1 

In this section, we describe how you can connect to an external datasource and call a stored procedure. We first create custom parameters in Settings that define the datasource, then we connect to the datasource and execute a stored procedure.

Stored Procedure Methods

[Back to top]

Workflow supports the following methods:
  • StoreProcedure createStoreProcedure(String)
  • Object execute(StoreProcedure, Map)


Define Datasource

[Back to top]

Go to Setup > System Settings > Organization Settings and open the Custom Parameters tab to define your datasource: 

Parameter Name Example
datasource.ACME.username john_smith
datasource.ACME.password password
datasource.ACME.jdbcInterceptors org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer
datasource.ACME.driverClassName oracle.jdbc.OracleDriver
datasource.ACME.url jdbc:oracle:thin:@192.168.150.37:1520:testDatabase


Connect to Datasource and Call Stored Procedure

[Back to top]

Use the following syntax to connect to the datasource, and then create and call a stored procedure:

//Connect to the datasource
def db = resp.dbConnect('yourDatasource');

//Create stored procedure and add params
def sp = db.createStoreProcedure("test_procedure_2");
sp.addInParameter("input1", 'numeric');
sp.addInParameter("input2", 'varchar');

//Add params to new map
Map <String, String> inParams = new HashMap();
inParams.put("input1", 5);
inParams.put("input2", 'abcd');

//Execute stored procedure
def result = db.execute(sp, inParams);

Execute Other Queries

Execute Method

[Back to top]

Workflow supports the following method:

  • void execute(String)

See the example below for usage of this method.

//Connect to the datasource
def db = resp.dbConnect('yourDatasource');

//Insert data
def query = "insert into yourTable(column1, column2) values (1,'some text')"
def result = db.execute(query);



Related Articles



1  Margaret Rouse,  Stored Procedure, TechTarget, February 2017 (retrieved on May 2, 2017)