The $SQL mask permits the exchange of data between a project and an external database. The instructions are inserted into the mask field of a chosen question in the questionnaire.
$SQL PROVIDER="" CMD="APPEND"|"UPDATE"|"APPENDUPDATE"|"READ" [SQL=""]|[KEYS="" TABLE="" FIELDS=""] QUESTIONS=""
|
COMMAND |
Description |
|
PROVIDER= |
Specifies the ADO connection string required for access to the database. This is normally supplied by the database administrator. |
|
CMD= |
APPEND: adds a record to the database. |
|
UPDATE: updates the database record. |
|
|
APPENDUPDATE: updates the database record or creates it. |
|
|
READ: retrieves a database record. |
|
|
SQL= |
The text of the SQL request. |
|
KEYS= |
Specifies the key that uniquely identifies the record whose data is to be exchanged between the interview file and the database record. This key must be presented in the form: NAME=VALUE where NAME stands for the field in the database, and VALUE stands for the value of this field. For a multiple key, the elements must be separated by commas, and each element must be within quotes. Example: KEYS="FNAME=<SURNAME>" |
|
TABLE= |
Specifies the tables to which the command applies. For multiple tables, the elements must be separated by commas. Each element must be within quotes. |
|
FIELDS= |
APPEND|UPDATE: specifies the names of the fields in the database that will be receiving the values. |
|
READ: specifies the names of the fields in the database from which to retrieve data. |
|
|
Note: To reference several tables, fields should be reference by their table name and field name separated by a period. Example: Table1.Field 1 |
|
|
QUESTIONS= |
APPEND|UPDATE: specifies the questions whose values are to be passed to the database. |
|
READ: specifies the name of the questions that receive the values retrieved from the database. |
|
|
Notes:
|
Note: Separate several items with commas (,) and each item should be in single (') quotation marks.
There are two modes that can be used: simple and advanced.
In this mode, simply furnish the information necessary for an SQL request. Once this information is supplied, the command is executed and will add, modify or read the register.
In this mode, the user must furnish the SQL request. As in the case of the READ command, the user must employ the QUESTIONS= parameter to ensure that the correct values are returned by the request in the questionnaire field. For the commands APPEND|UPDATE, the QUESTION= parameter is ignored since the request for the SQL= is sufficient.
Example: $SQL CMD=READ PROVIDER= "Provider=SQLOLEDB.1;Initial Catalog=database1;Data Source="SERVER1" SQL="Select Field1, Field2 from Table1 where RECNO=$Q" QUESTIONS= "Q1", "Q2"
Note: System variables and recalled text of other questions may both be used as arguments in $SQL masks.