Custom Actions: SQLUpdate |
This custom action allows inserting or updating data in a database
table.
| SQLUpdate when=before or when=after |
|
Use when=before to perform this action before the page is loaded. Use when=after to perform this action when the respondent moves to the next page. If neither of these is used, when=after is the default. The Enter Script field contains the SQL statement. The statement usually uses question piping. Security Note: If using User Security, the administrator must grant the questionnaire designer the right to use this custom action. Other SQLUpdate parameters can be added in front of when=before or when=after. See more parameters below. |
Example: using SQLUpdate for activity logging and audit trails
A table UPDATES with fields ID and DATE_LASTUPDATED
is used to log when a questionnaire is entered or updated.
A SQLUpdate action is included on the last page of the questionnaire:
SQLUpdate when=after
Use this SQL statement in the Enter Script field:
INSERT INTO UPDATES (DATE_LASTUPDATED, ID) VALUES ( SYSDATE, [/authenticateduserid])
Add none=1 when a SQLUpdate could result in not updating any any records. Without this parameter, an exception is reported.
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.