How can I read data from a CLOB field.

Question

I need to query a CLOB field from a database table.  How can I do that in QIE.

Here is an example:

Table:

CREATE TABLE books ( 
   id INT NOT NULL, 
   subject VARCHAR(50) NOT NULL, 
   text CLOB, 
 );

When query it with the following:

var queryResult = qie.doQuery(
   "books",
   "select id, subject, text from books");

qie.debug('id: ' + queryResult.getNode('id'));
qie.debug('subject: ' + queryResult.getNode('subject'));
qie.debug('text: ' + queryResult.getNode('text'));

I get these results when the CLOB field has a small amount of text:

id: 1
subject: short
text: clob26: 'some text'

And this when the CLOB contains a lot of text:

id: 1
subject: short
text: SPACE(4189 /* table: 14 id: 1 */)

How can I get the contained text using QIE?

Answer 1

First, note that most databases store CLOB data differently based on the amount of data to be stored.  A small amount of text may be stored as a VARCHAR when it is under a certain size.  See your database vendor documentation for details as this varies from one vendor to another.

You can load the CLOB data in QIE using JDBC.  Below is an example:

Create a Published Function with this code:

function loadClobFromQuery(connectionName, queryString, idPredicateValue) {
   var connection = qie.getDbConnection(connectionName);
   var preparedStatement;
   var queryResultSet;
   var myClob;
   var clobAsString;

   try {
      preparedStatement = connection.prepareStatement(queryString);
      preparedStatement.setInt(1, idPredicateValue);
      queryResultSet = preparedStatement.executeQuery();

      if (queryResultSet.next()) {
         myClob = queryResultSet.getClob(1);
         qie.debug("Length of retrieved Clob: " + myClob.length());
      }
      clobAsString = myClob.getSubString(1, (0 + myClob.length()));      
   } catch(e) {
      qie.debug(e);
   } finally {
      //finally - close resources
      if (myClob !== null) {
         myClob.free();  //free the clob to release memory to avoid out of memory errors
      }     
      try{
         if (queryResultSet !== null) {
            queryResultSet.close();
         }
      } catch (e) {
         qie.debug(e);
      }
      try{
         if (preparedStatement !== null) {
            preparedStatement.close();
         }
      } catch (e) {
         qie.debug(e);
      }
      try{
         if (connection !== null) {
            connection.close();
         }
      } catch (e) {
         qie.debug(e);
      }
   }
   return clobAsString;
}

Then call it like this:

//Exclude the clob field in this query.  We will grab it below.
var queryResult = qie.doQuery("books", "select id, subject from books");

//Only grabbing the clob from the first row of the results since you probably know how to iterate the results already
var result = loadClobFromQuery("books", 
        "select text from books where id = ? limit 1", 
        queryResult.getNode('id'));

qie.debug('id: ' + queryResult.getNode('id'));
qie.debug('subject: ' + queryResult.getNode('subject'));
qie.debug('text: ' + result);