Custom Actions: SQLSelect
This custom action allows retrieving data from a database table and inserting the values into a questionnaire.
|SQLSelect when=before or when=after,question=field,question=field|
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 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 SQLSelect parameters can be added in front of when=before or when=after. See more parameters below.
Example: using SQLSelect for personalization
A database table USER_PROFILES contains customer profiles,
including logged in user id's, full name, region, and courtesy gift preference in fields named USER_ID, USER_NAME, REGION and GIFT. After creating
a questionnaire with fields username, region, and gift, a SQLSelect action
looks up the values in the table and inserts them into similar questions
in the questionnaire.
A custom action is included in the questionnaire's first page:
Use this SQL statement in the Enter Script field:
SELECT USER_NAME,REGION,GIFT FROM USER_PROFILES WHERE USER_ID='[/authenticateduserid]'
Add selectonlyifquestionempty=true in front of when=before or when=after to prevent the SQL query from executing if the first question already has a value. This is useful for filling in fields only once when a questionnaire is set up for revisions, or when it is possible to execute the SQLSelect statement more than once by navigating to its containing page again using the Previous or Save buttons.
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.