pQuery.callStoredProcedure

Signature: pQuery.callStoredProcedure(connection, readOnlyConnection*, quoteValues*, timestampFormat*)

Returns: CSV Message[] resultSets - an array of CSV Messages representing the result sets returned by a stored procedure or query, or null when the call returns no result sets. To see what methods can be used on a CSV Message, refer to the 'Source' functions.

Execute a parameterized call to a stored procedure and return the result sets as an array of CSV messages.

Note

This method uses JDBC execute() method. The CSV Messages that are returned can be used like any Source message. To see what methods can be used on a CSV Message, refer to the 'Source' functions. This method supports either vendor specific syntax or the 'Procedure CALL Escape Sequence' to call stored procedures. To use 'Procedure CALL Escape Sequence' syntax the braces need to be converted to numeric character references. Example: {[?=]call procedure-name[([parameter][,parameter]...)]} convert to {[?=]call procedure-name[([parameter][,parameter]...)]}

Note

Each CSV Message in the array holds the column header row of its own result set, so one whose result set matched no rows has a getRowCount() of 0.

Note

A call that returns no result sets at all returns null rather than an array, so check the array itself for null.

Parameters

Type	Name	Description	Default
String	connection	the name of the database connection
Boolean	readOnlyConnection*	(optional) use a read-only database connection	false
Boolean	quoteValues*	(optional) true = individual values are surrounded with quotes	false
String	timestampFormat*	(optional) the format string to use for timestamp fields	yyyyMMddHHmmss

Example

// Call a stored procedure that returns one or more result sets
var pQuery = qie.getParameterizedQuery("call patient_summary(:onlyActive)");
pQuery.setBoolean("onlyActive", true);

// callStoredProcedure returns a CSV Message[] -- one CSV Message per result set
var resultSets = pQuery.callStoredProcedure("EMR Database");

// resultSets[0] is the first result set; read it like a Source message (a column name returns the first row)
var firstPatientName = resultSets[0].getNode("name"); // Ada Lovelace
var patientCount = resultSets[0].getRowCount();       // 2

// resultSets[1] is a second, independent result set returned by the same call
var totalBalance = resultSets[1].getNode("total_balance");   // 96.25

// a procedure that returns no result sets at all returns null rather than an array
var updateOnly = qie.getParameterizedQuery("call archive_inactive_patients()");
var noResultSets = updateOnly.callStoredProcedure("EMR Database"); // null