NIPO ODIN Version 5.17
Scenario 1: Store New Record in Table (Not the Sample Table)
This scenario applies the following directives:
- The NIPO Fieldwork System database contains a custom table
TestSqladd1, created using the Microsoft SQL Server Management Studio. - This table has a primary key called
RecordNumber. - This table contains some custom fields that are going to be provided with values from the script:
Example table for *SQLADD: scenario 1
| Fieldname
|
Fieldtype
|
Constraints
|
Field1
|
varchar(50)
|
|
Field2
|
varchar 10
|
|
Integer1
|
int
|
|
Integer2
|
smallint
|
-32768 to 32767
|
Integer3
|
tinyint
|
0 to 255
|
Floating
|
floating
|
|
Example NIPO ODIN questionnaire for *SQLADD: scenario 1
*VARS RecordNumber, Integer1, Integer2, Integer3, Floating, Count
*TEXTVARS Field1, Field2
*QUESTION 1 *FORM
Type the contents for your new record
1: RecordNumber *NUMBER 61L8 *SAVE RecordNumber
2: Field1 (max 50 pos) *ALPHA 69L50 *SAVE Field1
3: Field2 (max 10 pos) *ALPHA 119L10 *SAVE Field2
4: Integer1 (int) *NUMBER 129L11 *SAVE Integer1
5: Integer2 (smallint) *NUMBER 140L6 *SAVE Integer2
6: Integer3 (tinyint) *NUMBER 146L4 *SAVE Integer3
7: Floating (use decimals) *NUMBER 150L10.5 *SAVE Floating
*SQLADD Count \
RecordNumber,Field1,Field2,Integer1,Integer2,Integer3,Floating \
"SELECT \
RecordNumber,Field1,Field2,Integer1,Integer2,Integer3,Floating \
FROM TestSqlAdd1"
*QUESTION 2 *IF [Count >= 1]
Writing a new record was successful
*QUESTION 3 *IF [Count = 0]
Writing a new record was NOT successful
*END
This will create new records in the table TestSqladd1. The table is not accessible through NIPO Software applications other than through *SQLGET calls. Any attempt to create record number that already exists in the table results in *SQLADD to fail. An error is reported to the Fieldwork Manager. On any error, the questionnaire is resumed at question 3.
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 script does not check for valid input values.
For *SQLADD to work, a Sample table is not required. However, to be able to use the *SQLADD command a database reference must be made in the survey configuration file with setting RespondentDatabase=<DSN> signifying a valid DSN on the NIPO CATI / Web Master. The database user name and password of the NIPO Fieldwork System are used to attempt to access this database.
When you use a Sample table on the survey, the new table must be in the same database as you can not specify another DSN. The owner of the database table is not relevant. However the database user (the NIPO Fieldwork System) must have sufficient rights to create new records in the table.
|
