Sidebar

How to use the result from doQuery()?

+2 votes
2.0K views
asked Jul 3, 2013 by mike-r-7535 (13,830 points)

How should I use the result from a doQuery() call?

2 Answers

+1 vote
 
Best answer

DB Query Results are formatted as CSV messages.  A DB query result message will always contain a header row populated with the column alias names returned by the database query.  

Examples of using the query results

Querying the table

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

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 will return the column header value.

Looping through the query results

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

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

}

In the above example we did a math operation to add 1 to the “i” counter so that we do not return the column header value in our loop.

answered Oct 14, 2013 by gary-t-8719 (14,860 points)
selected Dec 25, 2014 by sam-s-1510
+2 votes
The call to doQuery() returns a CSVMessageModel object.  Here are some ways to use a CSVMessageModel:

Let's say we have a table called 'SuperSickPatient', which has the following fields: 'FirstName', 'LastName', and 'ExtraPHI'.

 

Q. How do I query the table?
A. var result = qie.doQuery( dbName, 'SELECT FirstName, LastName, ExtraPHI FROM SuperSickPatient');

Q. What does the qie.doQuery() return?
A. qie.doQuery() returns a CSV Message Object (Code Wizard still notes it as a 'Table' object)
       
Q. How do I know if any records where returned?
A. result.getRows().size() > 0       ---> result.getRowCount() > 0
       
Q. How many records were returned?
A. result.getRows().size()           ---> result.getRowCount()

Q. How do I access the ExtraPHI field within the first record?
A. result.getNode('3');
       OR
       result.getNode('ExtraPHI');
       OR
       result.getNode('3[1]');           ---> result.getNode('3', 1);
       OR
       result.getNode('ExtraPHI[1]');    ---> result.getNode('ExtraPHI', 1);

Q. How do I loop through the results and print the LastName to the qie log?
A.
   for (var i = 1; i <= result.getRows().size(); i++) {
      qie.info("LastName for record #" + i + " is: " + result.getNode('LastName[' + i + ']'));
      // OR
      // qie.info("LastName for record #" + i + " is: " + result.getNode('LastName', i));
   }
 
Q. How do I access the HEADER for the FirstName field?
A. result.getNode('1[0]');           ---> result.getNode('1', 0);
   OR
   result.getNode('FirstName[0]');   ---> result.getNode('FirstName', 0);

 

Notes:
 - If you use getNode() to retrieve the fields from the record, you should always qualify which record you want in your nodePath unless you are only looking for the first record (ie. 'LastName' or 'LastName[1]' to get the LastName from the 1st record and 'LastName[2]' to get the LastName from the 2nd record).
answered Jul 3, 2013 by mike-r-7535 (13,830 points)
...