Installation Guides > Installing Data Model > Supported upgrade paths > UNICOM Intelligence JDBC Driver guide > UNICOM Intelligence JDBC Driver specification > Query from VDATA
 
Query from VDATA
When querying data from VDATA, the JDBC driver converts data from the UNICOM Intelligence OLE DB provider to the JAVA result set using the following rules:
Double
A 64-bit floating-point number with at least 15 digits of precision.
JAVA result set: A single column: Double
Long
A 32-bit signed integer.
JAVA result set: A single column: Integer
Text
A character string. The maximum length of a text variable is dependent on the DSC. The string might or might not support Unicode, depending on how the CDSC has implemented its storage. The Provider implements all strings using Unicode.
JAVA result set: A single column: String
Categorical
A single or multiple categorical response type. The maximum number of responses available varies between DSCs. A categorical response is implemented in the Provider as a string. The responses are formatted in { } curly braces, with each response delimited by a comma. For example, {2,3,8} represents the responses 2, 3, and 8. Depending on how the data was collected, the order of responses in the string can be the order in which they were chosen in response to the question. This can be important for analysis.
JAVA result set: Column.Type is Categorical 129. The type is returned as a getTypeInfo method of DatabaseMetadata. The return value is determined by the connection properties. For more information, see Return category value.
Date
A 64-bit OLE date. The OLE date type contains both date and time and it is represented using the DATE type.
JAVA result set: A single column: DATE
Object
Not implemented.
JAVA result set: N/A
Boolean
A Boolean value of either True or False. This is implemented internally using VARIANT_BOOL. However, when a Boolean is converted to a Long or a Double, it will be converted to 1 for True and 0 for False.
JAVA result set: A single column: Boolean
Level
A hierarchical value. A level contains a child recordset.
JAVA result set: N/A for VDATA; a single BINARY column for HDATA
Array
Also called a Loop. These represent loops, grids, and levels. The Array object defines a question or set of questions that are to be asked more than once.
JAVA result set: Multiple columns. For more information, see Query array field in VDATA or Query array field in HDATA.
Class
These represent a grid question. A Grid object can be considered a special case of the Array object.
JAVA result set: Multiple columns. For more information, see Query class field in VDATA or “Query Class field in HDATA”.
Compound
These group categorical and grid questions that share a category list, typically for presentation in a paper questionnaire.
JAVA result set: Multiple columns. For more information, see Query compound field in VDATA or “Query Compound field in HDATA”.
Grid
These are used to group questions.
JAVA result set: Multiple columns. For more information, see Query grid field in VDATA or Query grid field in HDATA.
See also
Query array field in VDATA
UNICOM Intelligence JDBC Driver specification
Query array field in VDATA
If an array is unbound, it is not possible to query data from VDATA, but is it is possible to query flattened data from HDATA. You cannot query an array field such as:
SELECT person FROM VDATA
You can however query sub-fields in an array field because although array fields are not columns in VDATA, sub-fields are treated as columns. If the array field is a bound array, the result set is converted into multiple columns, such as:
ArrayField[M].subfield, ArrayField[M+1].subfield, ..., ArrayField[N].subfield
M is the lower-bound value and N is upper-bound value.
Example
In the following example (based on data from the UNICOM Intelligence Developer Documentation Library household.mdd example), the person field is an array that includes the age sub-field. The person field is bound from 1 to 6. To query the age field data, the query statement is:
SELECT person[1].age
, person[2].age
, person[3].age
, person[4].age
, person[5].age
, person[6].age
FROM VDATA
The result data is:
person[1].age
person[2].age
person[3].age
person[4].age
person[5].age
person[6].age
25
(null)
(null)
(null)
(null)
(null)
45
43
15
12
(null)
(null)
72
81
(null)
(null)
(null)
(null)
32
9
2
(null)
(null)
(null)
29
31
(null)
(null)
(null)
(null)
63
(null)
(null)
(null)
(null)
(null)
38
39
8
7
4
(null)
25
23
26
(null)
(null)
(null)
42
7
(null)
(null)
(null)
(null)
See also
UNICOM Intelligence JDBC Driver specification
Query class field in VDATA
A class field is a block of sub-fields. You cannot query a class field such as SELECT Respondent FROM VDATA. You can however query sub-fields in a class field because although class fields are not columns in VDATA, sub-fields are treated as columns.
Example
In the following example (based on data from the UNICOM Intelligence Developer Documentation Library museum.mdd example), the Respondent field is a class that includes the Serial and Origin sub-fields. The person field is bound from 1 to 6. To query the Serial and Origin field data, the query statement would be:
SELECT Respondent.Serial, Respondent.Origin FROM VDATA
The result data is:
Respondent.Serial
Respondent.Origin
1
(null)
2
(null)
3
(null)
See also
UNICOM Intelligence JDBC Driver specification
Query grid field in VDATA
You cannot query a grid field such as SE ATA. You can however query sub-fields in a grid field because although grid fields are not columns in VDATA, sub-fields are treated as columns. There are typically three kinds of grids: single response grids, multiple responses grids, and numeric grids.
Single response grids and multiple responses grids
You can use gridName[{elementName}].gridSubField to obtain a sub-field’s value. For more information, about returning categorical values in JDBC, see Return category value.
Example
In the following example (based on data from the UNICOM Intelligence Developer Documentation Library museum.mdd example), rating is a multiple response grid:
SELECT rating[{Dinosaurs}].Column, rating[{Conservation}].Column, rating[{Fish_and_reptiles}].Column FROM VDATA
The result data is:
rating[{Dinosaurs}].Column
rating[{Conservation}].Column
rating[{Fish_and_reptiles}].Column
{52}
{51}
{52}
{51}
{}
{50}
{48}
{}
{51}
{51}
{51}
{51}
Numeric grids
You can use gridName [{elementName}].numericName to obtain a sub-field’s value.
Example
Using data from the UNICOM Intelligence Developer Documentation Library short_drinks.mdd example:
SELECT numdrksz[{TEA}].mond,numdrksz[{COFFEE}].mond,numdrksz[{TEA}].tuesd, numdrksz[{COFFEE}].tuesd FROM VDATA
The result data is:
numdrksz[{TEA}].mond
numdrksz[{COFFEE}].mond
numdrksz[{TEA}].tuesd
numdrksz[{COFFEE}].tuesd
5
2
4
3
3
4
3
3
6
0
6
0
0
3
0
2
See also
UNICOM Intelligence JDBC Driver specification
Query compound field in VDATA
A compound field is a class that includes multiple grid fields. All grids share a same set of categorical elements that are stored in the compound field. You cannot query a compound field such as SELECT plans FROM VDATA. Compound fields are not columns in VDATA.
You can use compoundName.gridName[{elementName}].gridSubFieldName to obtain a sub field’s value.
Summary
VDATA is a flat view; all columns can be obtained via the getColumns method. The negative aspects of using VDATA are:
▪Bound array fields and grid fields are presented in expanded mode.
▪You cannot query unbound array data.
See also
UNICOM Intelligence JDBC Driver specification