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.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>",
Table = "<table_value>",
[MinAnswers = <minimum_answers>]
[MaxAnswers = <maximum_answers>]
[SQLFilter = <sql_filter>]
[CacheConnection = <cache_connection>]
[CacheTimeout = <cache_timeout>]
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]
[CacheConnection = cache_connection_value]
[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. For the other parameters that are not in the db section, see the corresponding topic. For the single and multiple response database questions, see
Categorical. For the database array questions, see
Categorical loop.
ConnectionString
The ConnectionString for the database question. Use the appropriate database provider for your database question input. The preferred type for database questions is SQL tables, because they are inherently multi-user.
Table
The table or view name available through the specified connection string. This table or view is used to retrieve the ids, labels, and so on for the question.
MinAnswers
The minimum answer count for the database question. For multiple response database questions, this is the lower bound.
MaxAnswers
The maximum answer count for the current database question. For multiple response questions, this is the upper limit.
When MaxAnswers = 1 is specified, the question is a single response database question. When MaxAnswers = 1 is not specified, the question is a multiple response database question.
SQLFilter
The SQL filter is applied when querying the specified table in the database. This filter is applied for all interviews. To specify an interview‑specific filter, use the IQuestion.DBFilter IOM property.
CacheConnection
Indicates whether the database connection should be cached between calls to update the cached DB elements. For OLE DB providers that lock access to the underlying data store, specify False.
The default value is True.
CacheTimeout
The timeout (in seconds) for the cached connection. If the value is 0 or less than 0, the connection is not cached, and the elements are retrieved from the database every time a question is asked.
For performance reasons, use a value of 10 or more.
The default value is 600 (that is, 10 minutes).
IteratorIDType
The data type of the DBID variable for the database Array question type.
Columns
Defines how the table columns map to the properties of the elements. The ID and Label columns must be defined and must be mapped to the correct database columns.
|
Column
|
Description
|
|
ID
|
The name of the ID column in the database table or view.
The value in this column is used as the Element Name. If the value is not a valid name, MakeMDMName() is called to create a valid name.
|
|
Label
|
The name of the Label column in the database table or view.
The value in this column is used as the Element Label. If the value in the database is empty, the Name id used as the Label.
|
|
KeyCode
|
The name of the KeyCode column in the database table or view.
The value in this column is used as the Element KeyCode. The KeyCode is used only if Routing.UseKeyCodes is true.
|
|
File
|
The name of File column in the database table or view.
The value in this column is set into Element.Style.Image.Name as an image link to the Element.
|
|
AnalysisValue
|
The name of AnalysisValue column in the database table or view.
The value in this column is saved into the Element Properties in the Analysis context.
|
|
Fixed
|
The name of Fixed column in the database table or view.
The value in this column indicates if the Element has a fixed position in the Elements collection.
|
|
Exclusive
|
The name of Exclusive column in the database table or view.
The value in this column indicates if the Element is an exclusive element.
|
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=MSOLEDBSQL.1;Integrated Security=SSPI;Initial Catalog=<database_name>;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, type the name of the highlighted range; that is, the Table Name that is specified in the Database Settings dialog. The OLE DB driver interprets the spreadsheet as a data table with column headings.
Click OK.
For more information, see ‘Working with database questions from Excel spreadsheets’ on page 135.
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.
▪Standard translation methods do not work for database questions. See ‘Configuring translations for database questions’ on page 137.
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 Access database):
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,
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 Access database):
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,
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 Access database):
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,
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 example uses a Microsoft Excel file.
MakeQ "Which car make do you prefer?" text
db(
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
Data Source= C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Code\
General\Excel\Cars.xls;Extended Properties='Excel
12.0;HDR=YES;IMEX=1';Persist Security Info=0",
Dbq=C:\Program Files\IBM\SPSS\DataCollection\7\
DDL\Code\General\Excel\Cars.xls",
Table = "Make$",
MinAnswers = 1,
MaxAnswers = 1,
CacheConnection = False,
Columns(
ID = "MakeID",
Label = "MakeLabel"
)
) codes(
{
- "No answers" NA,
- "Don't know" DK,
- "Refuse answer" REF
} );
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