DBQuery Behavior

Question

I have some questions on the behavior of DBQuery.

1. Does DBQuery cache anything on the QIE server or does it just retrieve a row, process it, retrieve another row, etc?

2. What if the query returns millions of rows.  Does the QIE server just start working on a stream of data coming from the source?  Does it retrieve query results in batches?  If not, is there a way, other than to write the query specifically, to allow DBQuery to work in batches vs. the entire query result set?

Answer 1

Good question.  qie.doQuery(), allocates a connection, executes the query, loads the result set into a CSV Message Model, closes the connection, and then returns the CSV Message Model.  It handles the proper cleanup of the connections and safely catches any exceptions.  As your question implies, this simplified approach is ideal for a controlled number of rows returned from the DB.

For queries that retrieve large binary data fields (blob), or if calling a stored procedure that uses cursors, you can use the qie.getDbConnection().  We have some examples of using the qie.getDbConnection() here.

As a general rule, use the qie.doQuery() and refine your query to limit the number of rows.  You can usually call the qie.doQuery() in a while loop to achieve the same result.

Comments

Is it fair to say that the CSV Message Model is stored on the QIE server then?  So, no actual processing takes place until the CSV Message Model is populated?

---

The CSV Message Model is the returned object from the qie.doQuery() function call.  So it exists in memory within the scope of the script, but I wouldn't say it is "stored" on the QIE server.  As soon as it passes out of scope that memory can be used for other objects.

You are correct about waiting for the CSV Message Model to be populated.  Scripts are always execute synchronously (meaning a DB call, or a WS call will wait until the result is returned.)