NIPO ODIN Version 5.17
Scenario 2: Store New Record in Current Sample Table
This scenario applies the following directives:
- We have a survey called
Sqladd2, using a Sample table SampleSqladd2. - In this survey we ask respondents if they know anyone else who might be willing to co-operate in this survey.
- The survey uses the
*SQLADD command to add a new record in the Sample table. - The newly created records may be distributed directly to any interviewer.
Example NIPO ODIN questionnaire for *SQLADD: scenario 2
*VARS NewNumber, Count, MaxNumber
*TEXTVARS Name, Address, City, Country, DisplayFields, TelephoneNumber
*VARS NewStatus, NewResponseCode
*QUESTION 1 *CODES 61L1
Can you give us the name and address of someone else who might be willing to co-operate in this survey?
1: Yes, knows someone else
2: No, doesn't know anyone who would co-operate.
*QUESTION 2 *FORM
Type name and address for this person
1: Name *ALPHA 62L45 *SAVE Name
2: Address *ALPHA 107L35 *SAVE Address
3: City *ALPHA 142L35 *SAVE City
4: Country *ALPHA 177L35 *SAVE Country
5: TelephoneNumber *ALPHA 212L17 *SAVE TelephoneNumber
*** Retrieve the highest interview number so far
*SQLGET Count MaxNumber \
"SELECT InterviewNumber FROM SampleSqlAdd2
WHERE InterviewNumber =
(Select MAX(InterviewNumber) FROM SampleSqladd2)"
*** Add 1 to the highest number and use this as InterviewNumber
*PUT NewNumber [MaxNumber + 1]
*** Initialize the Status and ResponseCode for the new record
*PUT NewStatus [0]
*PUT NewResponseCode [0]
*PUT DisplayFields [4]
*** Now write a new record in the Sample table
*SQLADD Count NewNumber,Name,Address,City,Country,TelephoneNumber,\
DisplayFields,NewStatus,NewResponseCode \
"SELECT InterviewNumber,DisplayField1,DisplayField2,\
DisplayField3,DisplayField4,TelephoneNumber,\
ShowDisplayFields,Status,ResponseCode \
FROM SampleSqlAdd2"
*QUESTION 3 *IF [Count >= 1]
Writing a new record was successful
*QUESTION 4 *IF [Count = 0]
Writing a new record failed
*QUESTION 9999
That's all
*END
You may even make appointments for your new records. Note that the format for the AppointmentTime field is "YYYYMMDD HHmm", regardless of the date / time format configured for use at the database server.
Example NIPO ODIN questionnaire for *SQLADD: scenario 2b
*PUT NewAppointmentTime "*?Year*?Month*?Day *?Hour*?Minutes"
*PUT NewStatus [ 4 ]
*PUT NewResponseCode [ 6 ]
*SQLADD Count NewNumber,Name,Address,City,Country,DisplayFields,\
TelephoneNumber,NewStatus,NewResponseCode,\
NewAppointmentTime\
"SELECT InterviewNumber,DisplayField1,DisplayField2,\
DisplayField3,DisplayField4,ShowDisplayFields,\
TelephoneNumber,Status,ResponseCode,\
AppointmentTime \
FROM SampleSqlAdd2"
Do not forget to initialize the fields Status and ResponseCode in the new survey. Records are only be distributed to interviewers when they have a valid and non-definitive Status and ResponseCode.
Make sure that you retrieve the highest InterviewNumber so far just before attempting to create a new record. If you retrieve the number earlier in this questionnaire, another record might have been added during another interview in the meantime.
Do not use the system variables TTDisplayField1, TTDisplayField2, TTDisplayField3, TTDisplayField4 and TTTelephoneNumber to store the name and address for the new respondents. These would be saved in the current Sample record as well.
SQL has no range limit on primary keys, therefore it is perfectly possible so create record numbers with negative values or values over 99999999, which are outside of the InterviewNumber range. The same applies to the Status field (0 or 4) and the ResponseCode field, which be set with an indefinite response code. The script does not check for valid input values. Alternatively, configure constraints on the Sample table but be advised that the *SQLADD command will then simply fail on an attempt to add a record with out-of-bounds values with the message "Database error in <database>(<owner>.<tablename>) : INSERT statement conflicted with COLUMN CHECK constraint '<field>'. The conflict occurred in database <database>', table '<tablename>', column '<field>'. The statement has been terminated.".
|
