NIPO ODIN Version 5.17
*SQLPUT
System
NIPO CATI / NIPO CAWI only.
Purpose
Stores one or more values or texts in a database table record.
Syntax
*SQLPUT count var[,var...] "SQL statement"
Description
Stores variable in an existing database table record. If more than 1 variable is used first variable will be stored in the first field, the second in the second field et cetera.
Arguments
count
A numeric variable that will contain the number of records updated by the SQL statement. This should not be an array variable.
var
One or more variables containing the data to be stored in the table record(s). Variables may be arrays - if the SQL statement retrieves more than one record, the indexed values of these arrays are used to fill the records.
"SQL statement"
An SQL statement. Only SELECT statements in the format "SELECT ... FROM ... WHERE ..." are allowed. It is not possible to use an UPDATE query.
Note:
Requires a reference to the database name (DSN name on the NIPO CATI / Web Master) in the survey configuration file. This reference is by default included when a sample table is created.
Do not attempt to update records within the Sample table of a running survey, including the questionnaire's own Sample table. Records of such tables may be locked, causing the update to fail.
If you need to update information in the sample record currently in use, it is recommended to use *SAMPLEDATA to directly access fields.
Remark
- This command does not create new records - it only updates existing records.
- NIPO ODIN will not syntax-check the SQL statement. As far as NIPO ODIN is concerned it is just a text string which means that you can substitute variables with the
*? command. If there is an error while executing the SQL statement this is reported to the fieldwork manager. The interview continues with count set to zero. - Variable names in the NIPO ODIN questionnaire do not need to be equal to the field names in the database table.
- It is required to match the number of input variables to the number of fields returned by the SQL statement. The order in which fields are listed in the
SELECT statement is the same order for storage used by the defined variables. - If no record is found matching the
WHERE statement or one or more database fields in the SQL statement are missing, the count variable will be zero. - If more than one record is found matching the
WHERE statement and the var is defined as an array, the array indexes of the variables are used to fill subsequent records. The number of matching records will be stored in the count. If the arrays are too small to fit the selection, only part of the selection is filled; if the arrays are too large to fit the number of found records, only part of the arrays are stored. Note that the order in which records are found is not always the (ascending) record numbers. - After the
WHERE statement any valid SQL syntax expression is allowed. Use brackets to set priority rules. - SQL statements may only refer to tables within the a single database. When referring to other databases on the same server, the SQL statement returns zero records and the update fails.
- SQL statements may refer to only one table at the time, so joined queries are not allowed. When referring to several tables in a single
*SQLPUT statement, the statement returns zero records. If information must be stored in more than one table, consider using more than one *SQLPUT statement. - Field names within the table may not contain spaces or other characters that are not in the ranges A-Z, a-z and 0-9.
- The
WHERE statement does a case sensitive search by default. This may only be disabled in the database configuration.
Example
The database table Names contains a primary key that matches the ID used in the Sample table. The respondent's name must be stored in the field Name. It is assumed that the table Names already contains the matching respondent IDs.
*VARS CNT
*TEXTVARS Name
*QUESTION 1 *ALPHA L45 *SAVE Name
What is your name?
*SQLPUT CNT Name "SELECT Name FROM Names WHERE ID='*?ID'"
*PAGE
Your name (*?Name) has been updated in our database.
|