Skip to content

DB Result Node Path Syntax

DB Query Results are formatted as CSV messages. A DB query result message always contains a header row populated with the column alias names returned by the database query. Refer to CSV - Node Path Syntax for more information on the CSV message model format.

Node paths in this section can be used with getNode, getAllNodes, and other QIE functions that accept a nodePath parameter. See How Node Paths Resolve for behavior details and the Node Path Lookup Dialog to build and validate them interactively against a sample message.

Query timeout

All eight Code Wizard query calls (qie.doQuery, qie.doConditionQuery, qie.doSelectQuery, qie.doUpdateQuery, and the parameterized counterparts on the object returned by qie.getParameterizedQuery(...)) accept an optional trailing timeoutSeconds parameter. It is the number of seconds the JDBC driver waits for the query to complete before aborting it; a value of 0 (the default) disables the timeout. When the timeout fires, the call surfaces the driver's timeout error through the standard NoResultsException path, the same way any other query error is reported.

Examples of using the query results

Querying the table

var queryResult = qie.doQuery(dbName, 'SELECT firstName, lastName, patientId FROM person');

What these functions return when nothing matches

  • qie.doQuery (and the parameterized pQuery.doQuery). A SELECT that matches no rows still returns a CSV result holding the column header row, so getRowCount() is 0. Test getRowCount(), not != null.
  • qie.doQuery and pQuery.doQuery also accept statements that produce no result set at all, such as an UPDATE. Those return null, because there are no columns to build a header row from.
  • qie.doSelectQuery (and pQuery.doSelectQuery). Return null when the query matches no rows. Check the result for null before reading it.
  • qie.doConditionQuery (and pQuery.doConditionQuery). Return null when no row matches. Use a != null check for existence.
  • pQuery.callStoredProcedure. Returns one CSV result per result set the call produced, each holding its own header row. A call that produces no result sets returns null.
  • qie.doUpdateQuery (and pQuery.doUpdateQuery). Return the count of rows affected.
  • A SQL or driver error throws an exception. Wrap the call in try/catch only when you intend to handle the failure yourself rather than letting the message go to the error queue.

Prefer parameterized queries when interpolating message values

The example above uses qie.doQuery for brevity, which interpolates values directly into the SQL string. For any query that takes values from the message (patient IDs, MRNs, dates, etc.) use the parameterized form instead. It binds each value separately, which avoids broken statements when a value contains a quote and closes off SQL injection. See Parameterized Database Queries for the full pattern (qie.getParameterizedQuery, the set* binders, and the execute methods).

Verifying if any results were returned

If (queryResult.getRowCount() > 0) {

//additional code to perform

};

Determining how many records were returned

var count = queryResult.getRowCount();

Referencing a column value or the column header from the query

queryResult.getNode('columnName', instance)

The column name is the database table column name used in the select statement. Also note that the column name should be quoted as shown in the example.

In the case where the query returns multiple results the instance tells QIE which result record to reference. The instance can be any number greater than 1 up to the number of results returned by the query to reference the data values. An instance number of 0 returns the column header value.

Looping through the query results

for (var i=0 ; i < queryResult.getRowCount() ; i++) {

var value = queryResult.getNode('columnName', i+1);

}

The above example does a math operation to add 1 to the "i" counter so that the loop does not return the column header value.