First, do you really need to do direct JDBC? Can you use a database connection in QIE with qie.doQuery? qie.doQuery is the prefered way to run a query in QIE.
Below is an example of how to do direct JDBC. This example still leverages the database connections in QIE. Just pass in the name of the database connection and the query string to execute. Also, note how we handle closing the JDBC resources in the finally block of the try catch.
function executeQuery(connectionName, queryString) {
//NOTE: This does not return a header row.
//NOTE: Fields can only be accessed via index. Not by name.
//STEP 1: Obtain a connection
var csvResults = qie.parseCSVString('');
var connection = qie.getDbConnection(connectionName);
var statement;
var queryResultSet;
var csvMessage;
try {
//STEP 2: Create a statement
qie.debug("Creating statement...");
statement = connection.createStatement();
//STEP 3: Execute query
queryResultSet = statement.executeQuery(qie.evaluateTemplate(queryString));
//STEP 4: Build csv
var newMessage = '';
var columnCount = queryResultSet.getMetaData().getColumnCount();
for (var i = 0; i < columnCount; i++) {
newMessage += '"' + queryResultSet.getMetaData().getColumnLabel(i + 1) + '",';
}
if (StringUtils.endsWith(newMessage, ',')) {
newMessage = StringUtils.substring(newMessage, 0, StringUtils.lastIndexOf(newMessage, ',', 0));
}
newMessage += "\n";
while (queryResultSet.next()) {
for (var j = 0; j < columnCount; j++) {
newMessage += '"' + queryResultSet.getObject(j + 1) + '",';
}
if (StringUtils.endsWith(newMessage, ',')) {
newMessage = StringUtils.substring(newMessage, 0, StringUtils.lastIndexOf(newMessage, ',', 0));
}
newMessage += "\n";
}
if (StringUtils.endsWith(newMessage, '\n')) {
newMessage = StringUtils.substring(newMessage, 0, StringUtils.lastIndexOf(newMessage, '\n', 0));
}
//STEP 5: Parse response into message model allowing use of the csvMessage.getNode('column_name', 0);
csvMessage = new com.qvera.qie.utils.message.CSVMessageModel(newMessage.getBytes(), new com.qvera.qie.web.persistable.messageType.DbMessageType(), true);
//STEP 6: Clean-up environment
queryResultSet.close();
statement.close();
connection.close();
} catch(e) {
qie.debug(e);
} finally {
//finally - close resources
try{
if (queryResultSet !== null) {
queryResultSet.close();
}
} catch (e) {
qie.debug(e);
}
try{
if (statement !== null) {
statement.close();
}
} catch (e) {
qie.debug(e);
}
try{
if (connection !== null) {
connection.close();
}
} catch (e) {
qie.debug(e);
}
}
return csvMessage;
}
