NIPO ODIN Version 5.17
*SQLGET
System
NIPO CATI and NIPO CAWI only
Purpose
Retrieves a value or text from a database.
Syntax
*SQLGET count var[,var...] "SQL statement"
Description
This command retrieves a variable from a database. If more than 1 variable is used the first variable will get the value of the first field in the database record, the second variable gets the second field et cetera. The variable can also be an array. In that case, if the SQL statement returns more than 1 record, the first record will be copied from the first element of var, the second record from the second element of var et cetera.
To be able to use *SQLGET, the NIPO Fieldwork System must be configured to use a database.
Arguments
count
A numeric variable that will contain the number of records retrieved by the MS SQL statement. This cannot be an array variable.
var
A variable that will receive the result of the SQL statement. This may be an array variable, but should be specified without the index.
"SQL statement"
An SQL statement. Only SELECT statements in the format "SELECT ... FROM ... WHERE ..." are allowed.
Remark
- 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 advised to match the number of result 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 store subsequent records. The number of matching records will be stored in the count. - If more than one matching record is found, but
var was not defined as an array variable, the system retrieves all matching records but can store only the last found information. The variable count contains the number of records found. Note that the order in which records are found is not always the (ascending) record numbers. - If more than one matching record is found, but the array variable was defined with insufficient indexes, the system only retrieves part of the records.
- 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.
- 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
*SQLGET statement, the statement returns zero records. If information from more than one table is required, consider using more than one *SQLGET 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.
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.
When retrieving fields from Sample tables, keep in mind that Sample tables are prefixed with Sample (for example, the Sample table of survey A1234 is called SampleA1234).
If you need to retrieve information from the sample record currently in use, it is recommended to use *SAMPLEDATA to directly access fields.
Example 1
In the database table Names the ID is stored in the field UserID and the name is stored in the field Name.
Survey configuration file A1234.INI:
[Config]
RespondentDatabase=NIPOFieldworkSystem
Questionnaire
*VARS CNT,NAME,USERID
*QUESTION 1 *NUMBER L8 *SAVE USERID
What is your ID number?
*SQLGET CNT NAME "SELECT Name FROM Names WHERE ID='*?USERID'"
*PAGE
According to our database your name is *?NAME.
Example 2
Retrieve fields Brand and Type from database table Products with *SQLGET. The script assumes each brand can have up to 5 types, and a previously defined list of computer brands (not included in this script).
*TEXTVARS CNT,BRAND,TYPE[5]
*VARS PRICE[5]
*QUESTION 1 *ALPHA L50 *SAVE BRAND *LIST "Computer brands"
What is the brand of your computer?
*FORMAT 3.2
*SQLGET CNT TYPE,PRICE "SELECT Type,Price FROM ProductTable WHERE Brand='*?BRAND'"
*QUESTION 1001 *CODES L5 *MULTI *DUMMY
*REPEAT 5
*IF [ CNT >= ?R ] *INCLUDE Q1001 [ ?R ]
*ENDREP
*QUESTION 2 *CODES L1 *IF [ CNT >= 1 ] *CONTROL Q1001 W
Please select the type of your *?BRAND computer:
Type: Price
1: *?TYPE[1] *?PRICE[1]
2: *?TYPE[2] *?PRICE[2]
3: *?TYPE[3] *?PRICE[3]
4: *?TYPE[4] *?PRICE[4]
5: *?TYPE[5] *?PRICE[5]
9: Don’t know *NOCON
*QUESTION 1002 *ALPHA L50 *DUMMY
Dummy question to store the type
*QUESTION 1003 *NUMBER L3.2 *DUMMY
Dummy question to store the price
*** Store the type and price in the dummy questions
*IF [ Q2,1-5 ] *INCLUDE Q1002 "*?TYPE[Q2]"
*IF [ Q2,1-5 ] *INCLUDE Q1003 [ PRICE[Q2] ]
|