NIPO ODIN Version 5.17
*SQLADD
System
NIPO CATI and NIPO CAWI only
Purpose
Create new database record.
Syntax
*SQLADD count var1,var2,… "SELECT field1,field2,… FROM TableName"
Description
Use the *SQLADD command in a NIPO ODIN questionnaire to create new records in a database table. This may be a custom table, the current Sample table, or a Sample table for another survey. If the *SQLADD command is executed successfully the variable count contains the number of records that have been added in the table.
See Advanced Use of *SQLADD Command for more details on working with *SQLADD.
Arguments
count
This a variable that stores the result of the command, which is the number of records that were successfully added to the SQL table. Can be used to see if the query was executed successfully. More than one record may be added if any of the passed variables was an array type variable. The smallest array size defines how many records may be added. If count is smaller than that smallest array, one or more records could not be added. Likely causes are primary key violations, incompatible data types et cetera. An error prevents the system from adding more array values from the *SQLADD statement.
var1,var2, ...
Input variables or values that are to be inserted in the new record(s). For undo purposes these must include the primary key(s) for the table, for undo purposes. The number of variables should match the number of fields in the SELECT statement. Array type variables must be specified without array indexes.
field1, field2, ...
Fields into which the variables are to be inserted. These should at least include all primary key field(s). As a consequence the table must use a primary key. The order in which the fields are specified is the order in which their values are inserted, and the number of fields should match the number of variables: var1 goes into field1, var2 into field2 and so forth.
TableName
Name of the table to create records in. The database ODBC name (DSN) is configured in the survey configuration file, usually as part of the survey configuration. As a survey can use only one database reference, this is usually the same database that contains the survey’s Sample table. The table may be the current Sample table, the sample of another survey or any other table in the same database.
Remark
- This function requires the use of a database server connected to the NIPO Fieldwork System.
- Going back into the questionnaire over an
*SQLADD statement will undo it, unless the record was locked by another process. You may specify a *NOTBACK command in the NIPO ODIN questionnaire if undoing is not appropriate. - The variables must be specified with the
*VARS, *TEXTVARS or *SAMPLEDATA command first. - The fields must exist in the table.
- The table must have a primary key.
*SQLADD may not be used on a view, since views do not have a primary key.*SQLADD may not be used on a table with an automatic primary key, since *SQLADD determines its own key.- Contrary to the
*NEXTRECORD command, you must specify a valid record number (key) for the table. - The number of variables must match the number of fields.
- Expressions are not supported. Use the
*PUT command first to evaluate expressions. - The syntax check in the NIPO ODIN Developer will not check if the contents of the variables that you specified fit in the fields. In other words, you must ensure the variables match the field types of the database. Texts that do not fit a character field are truncated.
- You may change the contents of system fields (system variables) in Sample tables. There is no validity check during the
*SQLADD command, except for constraints of the fields, so ensure you use valid system values. In particular, the InterviewNumber field should be set to a value in the range 1-99999999, the Status field to 1-8 and the ResponseCode field to 0-99. - If there is a type mismatch between the variable value and the field type, an error is reported and the record is not added.
- For reference, the field type
TinyInt may be used for values between and including 0 and 255; the type SmallInt may be used for values between and including -32768 and +32767; the field type Integer may fit values between -214783648 and -214783647 and the type Floating may contain any value of up to 15 digits, with precision used.
Example 1
*VARS Count,NewNumber
*TEXTVARS Name,Email,Telephone
*PUT NewNumber [ 9001 ]
*PUT Name "NIPO Software Helpdesk"
*PUT Email "Helpdesk@niposoftware.com"
*PUT Telephone "00 31 20 5225 980"
*SQLADD Count NewNumber,Name,Email,Telephone
"SELECT RecordNumber,Name,Email,Telephone
FROM Contacts"
A new record is created in the Contacts table with RecordNumber 9001 and the variables are saved in the Name, Email and Telephone fields for this record. All other fields from this table either remain empty or receive their initial value as set in the constraints for this table.
Example 2
*VARS NewNumber,Count
*TEXTVARS Name,Address,City,Country,TelephoneNumber
*QUESTION 1 *FORM
Type the contents for your new record
1: Name *ALPHA 69L45 *SAVE Name
2: Address *ALPHA 114L35 *SAVE Address
3: City *ALPHA 149L35 *SAVE City
4: Country *ALPHA 184L35 *SAVE Country
5: TelephoneNumber *ALPHA 219L17 *SAVE TelephoneNumber
*PUT NewNumber [TTInterviewNumber+50000000]
*SQLADD Count NewNumber,Name,Address,City,Country,TelephoneNumber \
"SELECT InterviewNumber,DisplayField1,DisplayField2, \
DisplayField3,DisplayField4,TelephoneNumber \
FROM SampleA1234"
*QUESTION 2 *IF [ Count = 1 ]
Successfully added new record.
*END
The system adds a new sample record to the table of survey SampleA1234. A value of 50000000 is added to the InterviewNumber and used as the primary key for the new record. This method may create new, non-existent primary keys without causing a primary key violation.
Example 3
*VARS NewNumber, Count, Maxnumber
*TEXTVARS Name, Address, City, Country, TelephoneNumber
*QUESTION 1 *FORM
Type the contents for your new record
1: Name *ALPHA 69L45 *SAVE Name
2: Address *ALPHA 114L35 *SAVE Address
3: City *ALPHA 149L35 *SAVE City
4: Country *ALPHA 184L35 *SAVE Country
5: TelephoneNumber *ALPHA 219L17 *SAVE TelephoneNumber
*** First retrieve the highest InterviewNumber so far
*SQLGET Count MaxNumber \
"SELECT InterviewNumber FROM SampleSqlAdd2 \
WHERE InterviewNumber = \
(Select MAX(InterviewNumber) FROM SampleSqladd2)"
*PUT NewNumber [ MaxNumber + 1 ]
*SQLADD Count NewNumber,Name,Address,City,Country,TelephoneNumber \
"SELECT InterviewNumber,DisplayField1,DisplayField2, \
DisplayField3,DisplayField4,TelephoneNumber \
FROM SampleSqlAdd2"
*QUESTION 2 *IF [Count = 1]
Successfully added new record.
*END
This checks for the highest used InterviewNumber with *SQLGET, adds one and then uses this value to create the new record.
Example 4
*VARS FamilyNumber, FamilyMemberNumber[9], FamilySize, Count
*TEXTVARS Name[9]
*QUESTION 1 *FORM
1:FamilyNumber: *NUMBER 61L6 *MIN 1 *MAX 999999 *SAVE FamilyNumber
2:FamilySize: *NUMBER 67L1 *SAVE FamilySize *MIN 1 *MAX 9
*QUESTION 1001 *CODES 68L9 *MULTI
*REPEAT 9
*IF [?R <= FamilySize] *INCLUDE Q1001 [?R] \
*PUT FamilyMemberNumber[?R] [(FamilyNumber * 100) + ?R]
*ENDREP
*QUESTION 2 *FORM *CONTROL Q1001 W
Names of family members
1: First person: *ALPHA 77L35 *SAVE Name[1]
2: Second person: *ALPHA 112L35 *SAVE Name[2]
3: Third person: *ALPHA 147L35 *SAVE Name[3]
4: Fourth person: *ALPHA 182L35 *SAVE Name[4]
5: Fifth person: *ALPHA 217L35 *SAVE Name[5]
6: Sixth person: *ALPHA 252L35 *SAVE Name[6]
7: Seventh person: *ALPHA 287L35 *SAVE Name[7]
8: Eighth person: *ALPHA 322L35 *SAVE Name[8]
9: Ninth person: *ALPHA 357L35 *SAVE Name[9]
*SQLADD Count FamilyMemberNumber,Name \
"SELECT PanelMemberNumber,Name FROM TestPanelmembers"
*QUESTION 3 *IF [Count = FamilySize]
Successfully added *? Count records out of *? FamilySize members.
The system adds at maximum 9 records with the names of panel members in the TestPanelmembers table. The system uses the 6-digit FamilyNumber and adds a 2-digit number for each family member to create a unique 8-digit PanelMemberNumber.
When fewer than 9 family members were specified, the system reports a warning message to the fieldwork manager. However this error may be ignored.
Note:
The variable name FamilyMemberNumber[9] and the database field name PanelMemberNumber do not need to carry the same name.
|