Custom Actions: SQLDisplay |
This custom action allows retrieving multiple rows of data from a database table using a SQL query and displaying the data in tabular form in a questionnaire.
| SQLDisplay pattern_question_name |
The SQLDisplay action is always exectured before the page is loaded. pattern_question_name is the name of a question that contains an HTML pattern, such as a table. The pattern is filled with the data retreived by from a database query. The pattern_question_name question is of type Comment, not of type HTML. The Enter Script field contains the SQL statement. The statement often uses question piping. Security Note: If using User Security, the administrator must grant the questionnaire designer the right to use this custom action. Other SQLDisplay parameters can be added in front of when=before or when=after. See more parameters below. |
Example: using SQLDisplay to list patient data
A database table PRESCRIPTIONS contains patient prescription data including patient ID, drug name, date prescribed, and physician. A patient assessment questionnaire looks up the prescriptions for the patient and displays them in tabular form.
A SQLDisplay custom action is included in the questionnaire:
SQLDisplay prescriptiontable
Use this SQL statement in the Enter Script field:
SELECT DRUG, PRESCRIBED, PHYSICIAN FROM PRESCRIPTIONS WHERE PATIENT_ID=[/patient_id]
The pattern_question_name question, of type Comment, contains the following HTML in the Comment field:
[/ifanydisplayrecords]<p>Records for patient [/patient_id]</p>
<table border="1" cellspacing="0" cellpadding="3">
<tr>
<th >Drug</th>
<th >Prescribed</th>
<th >Physician</th>
</tr>
[/alldisplayrecords]
<tr>
<td>[/DRUG]</td>
<td>[/PRESCRIBED]</td>
<td>[/PHYSICIAN]</td>
</tr>
[/endalldisplayrecords]
</table>
[/endifanydisplayrecords]
[/ifanydisplayrecords,not]No records for patient [/patient_id][/endifanydisplayrecords]
This produces a display like the following:
| Drug | Prescribed | Physician |
|---|---|---|
| Lipitor | 8/31/2010 | Johnson |
| Lunesta | 7/1/2009 | Johnson |
| Lupron | 5/15/1998 | Jones |
Notes:
Add datasource=JNDIDataSourcename to execute the SQL statement using a different data source. By default, the tables are located in the same database that all of ViewsFlash uses. But sometimes it is useful to use tables in a different schema or database altogether. In this case, create an additional JNDI Data Source and put its name in the datasource parameter. The additional database does NOT need to be the same database brand (eg., Oracle / DB2 ). More detail here.
Add errorprocess=XXXXX to specify what to do when an exception occurs. The format of this parameter can be NNNI and NNNE, where NNN is a number. NNNI will log the error to the questionnaire log and the ViewsFlash log. NNNE will also send an email to the ViewsFlash administrator.
This parameter may contain the letter C or Q in front of it. If both letters
are missing, and there's something wrong, the Action will be considered
Invalid, questionnaire processing will stop, and the action specified in
the Invalid Data section of the Security page will be performed. If this
parameter begins with a C, as in C123E, processing will continue and the
error message will be shown on the next page of the questionnaire at the
top. This setting can be useful during early testing. If this parameter beings
with a Q, as in Q123E, the error will be logged and processing will continue.
The default value of this parameter is 123E, which stops processing,
writes the cause to the log, and notifies the ViewsFlash administrator of
the cause.