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.
Follow this with a list of question names. Each question name can be followed, optionally, by an equals sign and the name of the corresponding field in the database table; if the names match, there is no need for this parameter.

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:
SQLSelect when=before,username=USER_NAME,region,gift

Use this SQL statement in the Enter Script field:
SELECT USER_NAME,REGION,GIFT FROM USER_PROFILES WHERE USER_ID='[/authenticateduserid]'

Notes:

More parameters:

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.

Next: SQLUpdate