Database questions
The database questions types are used when generating responses by connecting to an external database. Database questions enhance the current Text/Long/Double/Date/Loop/Grid to define the questions responses.
Database questions consist of the following types:
▪single response database question
▪multiple response database question
▪database array question.
For more information, see DBQuestionsTypesConstants in the
MDM Object Model Reference.
Syntax for single and multiple response database questions
For clarity, each item is shown on a separate line, and optional items are indented, See also
Syntax conventions.
field_name
[ "field_label" ]
[ [ <properties> ] ]
[ <styles and templates> ]
[text | double | long | date]
db(
ConnectionString = "connection_string_value",
Table = "table_value",
[
MinAnswers = min_answers_value]
[
MaxAnswers = max_answers_value]
[
SQLFilter = sqlfilter_string]
[
CacheTimeout = cache_timeout_value]
Columns (
ID = "id_column_name",
Label = "label_column_name"
[
KeyCode = "keycode_column_name"]
[
File = "file_column_name"]
[
AnalysisValue = "analysis_value_column_name"]
[
Fixed = "fixed_column_name"]
[
Exclusive = "exclusive_column_name"]
)
)
[ [ range_expression ] ]
[ <codes> ]
[
expression ("expression_text") ]
[ (
initialanswer |
defaultanswer ) (date_value) ]
[ <axis> ]
[ <usage-type> ]
[ <helperfields> ]
[
nocasedata ]
[
unversioned ]
Syntax for database array questions
Each item is shown on a separate line; optional items are indented:
field_name
[ "field_label" ]
[ [ <properties> ] ]
[ <styles and templates> ]
loop
[ range_expression ]
db(
ConnectionString = "connection_string_value",
Table = "table_value",
[
SQLFilter = sqlfilter_string]
[
CacheTimeout = cache_timeout_value]
[
IteratorIDType = [text | double | long | date]]
Columns (
ID = "id_column_name",
Label = "label_column_name"
[
KeyCode = "keycode_column_name"]
[
File = "file_column_name"]
[
AnalysisValue = "analysis_value_column_name"]
[
Fixed = "fixed_column_name"]
[
Exclusive = "exclusive_column_name"]
)
)
fields ( <field> (; <field> )* [;] )
[
row |
column ]
[ noexpand ]Parameters
For
<axis>,
<usage-type>,
<helperfields>,
nocasedata, and
unversioned, see
Common parameters.
Note When MaxAnswers = 1 is specified in the script, the question is a single response database question. If not specified; when MaxAnswers = 1 is not specified, the question is a multiple response database question.
DBIB helper field
The DBID helper field exists for all database questions types. For single and multiple database questions, the DBID property is the database ID value for the selected database response (for example, SingleResponseDBQ.DBID, MultiResponseDBQ [1].DBID). For database array questions, the DBID property is the iteration name of each defined database array question loop. The DBID value is stored when writing to case data.
The DBID property can be used whenever the database ID value is needed or when the database ID value can be applied. For example, when one of MultiResponseDBQ’s responses is assigned to a SingleResponseDBQ question: SingleResponseDBQ = MultiResponseDBQ[0].DBID.
ConnectionString considerations
The source of a database questions can be anything that can be accessed by an OLE DB connection string: for example, SQL Server tables, Microsoft Excel files, and Microsoft Access files. When choosing the source type, consider reliability in a multiuser environment, as well as ease of creation and activation.
For more information, see:
Using SQL Server tables
SQL Server tables are one of the best source to use, because they are reliable in a multiuser environment. To use them, you need access to SQL Server to create the table.
SQL Server tables are useful for database questions that are used in multiple projects (for example, country/state/city information or brand/model information): you have to change the table only once for the update to affect all the projects that use it.
However, if you have a multiple cluster quality strategy, setting up the connection strings to the table can be difficult. For example, if you have a development, staging, and production environment, you might also have development, staging, and production SQL Servers and a setup where the environments cannot access the SQL Servers in the other environments. In this case, the SQL table must be created in all environments, and the database question connection string must be set based on the environment where the project is running. This can be done by using the %PROJECT_DIRECTORY% macro.
The %PROJECT_ DIRECTORY% macro is typically used in file‑based connection strings; however, it can be used to substitute the SQL Server name into the connection string.
For example:
Set the ConnectionString for the database question as follows using your data base name and security method. Set the Data Source to %PROJECT_DIRECTORY%:
ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=<DatabaseName>;Data Source=%PROJECT_DIRECTORY%;"
Then in OnInterviewStart, set your ProjectDirectory:
Sub OnInterviewStart(IOM)
IOM.MDM.ProjectDirectory = GetEnvironmentDB()
End Sub
The GetEnvironmentDB function can use a registry setting, a project property that is copied to an interview property by the sample management script, a sample management field, a URL parameter, or it can be based on the IsTest interview property.
This example uses the IsTest interview property:
Function GetDBEnvironment()
If (IOM.Info.IsDebug) Then
' Debugging in Professional
GetDBEnvironment = "LocalServer"
ElseIf (IOM.Info.IsTest) Then
' Staging environment
' Relies on the staging environment always using Test=1 on the URL or
' projects always being in Test status
GetDBEnvironment = "StagingServer"
Else
' Production environment
' Relies on never running test interviews on the production environment
GetDBEnvironment = "ProductionServer"
End If
End Function
All of the running interviews for a specific project in a specific environment must use the same database. The metadata is loaded into the in‑memory project objects cache when the first interview for the project is run, and then it is shared amongst all interviews for that project on that server. If one interview changes the ProjectDirectory value, it also changes for all running interviews.
The OnInterviewStart method is not run when the project is accessed by Data Export, Reporter, or other analysis tools; therefore, the ProjectDirectory must be set for those environments as well. You can set defaults for each context as HDATA properties: these defaults are used if the property has not been set in the script.
HDATA -
[
DBQProjectDirectory = "ProductionServer"
]
ANALYSIS:
[
DBQProjectDirectory = "AnalysisServer"
]
These are the evaluation rules for the ProjectDirectory property:
1 Use the MDM.ProjectDirectory that is set in the script. It can be set in an interview script (created in Author or Professional), or in a data management script.
2 If the MDM.ProjectDirectory is not set in the script, use the DBQProjectDirectory HDATA property in the current context.
3 If the DBQProjectDirectory HDATA property is not available, use the folder location of the MDD file.
Using Microsoft Excel and Microsoft Access files
Microsoft Excel files are not always reliable in a multiuser environment. However, the UNICOM Intelligence Data Model supports Microsoft Excel and Microsoft Access files for database questions that are accessed by the Microsoft Office Access Database Engine OLE DB Provider (Microsoft.ACE.OLEDB.12.0); use this in preference to the Microsoft OLEDB Provider for ODBC (MSDASQL).
File‑based database questions are useful when the database question is specific to one project or when the SQL Server is not accessible (for example, when running offline interviews on Interviewer. The database file (.xls or .mdb) can be stored on the server, or in the project folder or subfolder. If it is stored in a project folder or subfolder, it can be activated as part of the normal activation process. The %PROJECT_DIRECTORY% macro is used in the connection string when the database file is activated. The default value for %PROJECT_DIRECTORY% is the current project folder.
Example connection string for Excel:
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=%PROJECT_DIRECTORY%/makes_data.xls;
Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"";"
Example connection string for Access:
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%PROJECT_DIRECTORY%\Cars.accdb;Persist Security Info=False",
When using a Microsoft Excel spreadsheet or a Microsoft Access database as the data source, you must set the CacheConnection property to False (the default value is True).
Additional considerations for Excel based database questions
▪For the database question’s table name, you can use either named ranges or sheet names.
▪To use a Microsoft Excel named range as a data source for database questions, define the range as follows:
Highlight all the data rows and columns in the spreadsheet.
Click Formulas > Defined Name > Define Name.
In the Define Name dialog box, type the name of the highlighted range; that is, the Table Name that is specified in the Database Settings dialog box. The OLE DB driver interprets the spreadsheet as a data table with column headings.
Click OK.
Considerations for all source types
▪For improved reliability in a server environment, you can transfer data that is stored in Microsoft Excel, Microsoft Access, and other file‑base data sources to a UNICOM Intelligence Data File (DDF). You can transfer the data by using a data management script (DMS). When the data is in a DDF file, you need to include only that DDF file in the database question connection string; you do not need an MDD file is not required. For example:
ConnectionString = "Provider=mrOleDB.Provider.2;Data Source=mrDataFileDsc;Location=%PROJECT_DIRECTORY%\Vehicles.ddf"
▪Make sure that the database drivers that are used to create questions on a client are also available on the server.
▪If you work on multiple networks, make sure that the database that is used to create a database question is available on the same network as the Interviewer Server.
▪Make sure that the DSNs which are used to setup the connection strings on the local workstations point to the same database or file on the Interviewer Server.
▪The default error handler for database connections monitors whether the connection string returns any categories.
If the question does not have to be answered, the error handler logs the error, and then the respondent is advanced to the next question.
If the question must be answered, the error handler displays an error to the respondent, and does not allow them to go to the next question. You should write scripts to handle these errors. Check all connections before you start the interview, and then respondents are asked to return when the survey is ready.
More information
For more information about database question configurations, see IDBQuestionOptions in the
MDM Object Model Reference.
Examples
Single response database question
The following example defines a single response database question from responses that are generated from the external database (in this case, a Microsoft Excel file):
MakeQ "Which car make do you prefer?" text
db(
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%PROJECT_DIRECTORY%\Cars.accdb;Persist Security Info=False",
Table = "Make",
MinAnswers = 1,
MaxAnswers = 1,
CacheConnection = False
CacheTimeout = 500,
Columns(
ID = "MakeID",
Label = "MakeLabel"
)
);
Multiple response database question
The following example defines a multiple response database question from responses that are generated from the external database (in this case, a Microsoft Excel file):
ModelQ "Select the top three models you prefer in the brand{Q1}" text [1..3]
db(
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%PROJECT_DIRECTORY%\Cars.accdb;Persist Security Info=False",
Table = "Model",
MinAnswers = 1,
MaxAnswers = 3,
CacheConnection = False
CacheTimeout = 500,
Columns(
ID = "ModelID",
Label = "ModelLabel"
)
) expand;
Database array question
The following example defines a database array from responses that are generated from the external database (in this case, a Microsoft Excel file):
PartsQ "Please answer the following question for each brand." loop [..]
db(
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%PROJECT_DIRECTORY%\Cars.accdb;Persist Security Info=False",
Table = "Parts",
CacheConnection = False
CacheTimeout = 500,
IteratorIDType = "Text",
Columns(
ID = "PartID",
Label = "PartLabel"
)
) fields -
(
Engine "Rate the engine for the model."
categorical[1..1]
{
Perfect "Perfect",
Good "Good",
Normal "Normal",
Bad "Bad"
};
Looks "Rate the looks for the model."
categorical[1..1]
{
Perfect "Perfect",
Good "Good",
Normal "Normal",
Bad "Bad"
};
Comfort "Is the model comfortable?"
boolean;
);
The following example defines a single response database question that includes special categories. This is defined using the codes keyword:
MakeQ "Which car make do you prefer?" text
db(
ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;
Dbq=C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Code\General\Excel\Cars.xls",
Table = "Make$",
MinAnswers = 1,
MaxAnswers = 1,
CacheConnection = False
CacheTimeout = 500,
Columns(
ID = "MakeID",
Label = "MakeLabel"
)
) codes(
{
- "No answers" NA,
- "Don't know" DK,
- "Refuse answer" REF
} );
Note Other specify responses are not completely supported in database question Codes. The responses can be selected, but the input edit box cannot be rendered by HTMLPlayer and their input text values are not stored.
See also