Sidebar

How can I use JDBC and safely close the JDBC resources?

0 votes
404 views
asked Feb 20, 2014 by rich-c-2789 (15,250 points)
edited Oct 7, 2014 by brandon-w-8204
How can I use JDBC and safely close the JDBC resources? (nullPointererror with Cache)

1 Answer

0 votes

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;
}

answered Feb 20, 2014 by rich-c-2789 (15,250 points)
edited Dec 26, 2014 by ben-s-7515
commented Oct 7, 2014 by brandon-w-8204 (30,600 points)
This can be used for a cache database
commented Nov 13, 2015 by rich-c-2789 (15,250 points)
Yes, we used this as a work around until we added the doSelectQuery.  See this question:

https://www.qvera.com/kb/index.php/1065/how-to-query-a-cache-database
...