UNICOM Intelligence JDBC Driver specification

VDATA and HDATA are supported by the UNICOM Intelligence OLE DB provider. After a query, the JDBC driver returns a standard JDBC result set (the JDBC driver converts results returned by the UNICOM Intelligence OLE DB provider into a JDBC result set). The transformation can result in multiple tables being returned. For more information, see Query from VDATA and Query from HDATA.

▪VDATA: A single, flat table that contains a column for each variable that can be presented in a flattened form.

▪HDATA: A set of hierarchical tables (the top-level table is called HDATA). Each lower-level table represents the data in a loop or grid.

For more information, see Virtual tables.

SQL syntax

The JDBC driver works in pass-through mode. For more information about SQL query statements, see “SQL queries” in the UNICOM Intelligence Developer Documentation Library. The section includes information about:

▪basic SQL queries

▪advanced SQL queries

▪hierarchical SQL queries

▪advanced hierarchical SQL queries.

Expression evaluation

The UNICOM Intelligence Evaluate component implements expression parsing and evaluation. The component is used by the Case Data Model (CDM), the Metadata Model (MDM), and the mrScript engine. The Evaluate component supports standard expression grammar. The parser is unique, however, in its support for categorical variables and its optional support for single or double-quoted string literals. For more information about expression evaluation, see Expression evaluation.

UNICOM Intelligence Function Library

The UNICOM Intelligence Function Library supplements basic expression evaluation with more advanced operations. For more information about the function library, see UNICOM Intelligence Function Library.

Aggregate functions

UNICOM Intelligence JDBC Driver specification

UNICOM Intelligence Developer Documentation Library

The UNICOM Intelligence Developer Documentation Library supplements basic expression evaluation with more advanced operations. For more information about the function library, see “UNICOM Intelligence Function Library” in the UNICOM Intelligence Developer Documentation Library.

Aggregate functions

The Case Data Model provides support for a number of aggregate functions. These functions can be used in SQL queries to summarize information from multiple rows

AVG(expr)

Returns the average of the values defined in the expression. This function can be used with numeric data only.

BASE(expr)

Returns the total number of cases included in the expression. Generally, the base includes every case for which the value is not NULL. However, if the IsInBase attribute has been set to True, the case is included in the base regardless of the value. Conversely, if the IsInBase attribute has been set to False, the case is excluded from the base regardless of the value.

COUNT(expr)

Returns a count of the cases in a column.

SUM(expr)

When used with numeric data, returns the sum of the values. When used with categorical data, it returns the union of the categorical values.

MIN(expr)

Returns the lowest value defined in the expression.

MAX(expr)

Returns the highest value defined in the expression.

STDEV(expr)

Returns the standard deviation of the values defined in the expression. This function can be used with numeric data only. The standard deviation is a measure of dispersion around the mean. In a normal distribution, 68% of cases fall within one standard deviation of the mean and 95% of cases fall within 2 standard deviations. For example, if the mean age is 45 with a standard deviation of 10, then 95% of the cases would be between 25 and 65 in a normal distribution.

Database metadata

The UNICOM Intelligence JDBC Driver supports the following metadata:

getColumns

Retrieves a description of table columns available in the specified catalog. Only column descriptions matching the catalog, schema, table and column name criteria are returned. The results are ordered by TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and ORDINAL_POSITION.

ResultSet getColumns(String catalog,
                     String schemaPattern,
                     String tableNamePattern,
                     String columnNamePattern)
                     throws SQLException

The JDBC driver ignores all parameters and returns all columns. The driver returns all fields in VDATA, HDATA, array objects, and grid objects. The field name can be used in SQL.

getSchemas

Retrieves the available database schema names. The results are ordered by TABLE_CATALOG and TABLE_SCHEM.

ResultSet getSchemas()
throws SQLException

getTables

Retrieves a description of the tables available in the given catalog. Only table descriptions matching the catalog, schema, table name, and type criteria are returned. The results ordered by TABLE_TYPE, TABLE_CAT, TABLE_SCHEM, and TABLE_NAME.

ResultSet getTables(String catalog,
                    String schemaPattern,
                    String tableNamePattern,
                    String[] types)
                    throws SQLException

The JDBC driver ignores all parameters and returns all tables.

getTypeInfo

Retrieves a description of all the supported database data types. The results are ordered by DATA_TYPE and then by how closely the data type maps to the corresponding JDBC SQL type.

If the database supports SQL distinct types, then getTypeInfo() will return a single row with a TYPE_NAME of DISTINCT and a DATA_TYPE of TYPES.DISTINCT. If the database supports SQL structured types, then getTypeInfo() returns a single row with a TYPE_NAME of STRUCT and a DATA_TYPE of Types.STRUCT. If SQL distinct or structured types are supported, then information on the individual types can be obtained from the getUDTs() method.

UNICOM Intelligence TYPE_NAME	UNICOM Intelligence data type	JDBC metadata DATA_TYPE	JDBC metadata TYPE_NAME	Java class
DOUBLE	5	8	double	java.lang.Double
LONG	3	4	integer	java.lang.Integer
DATE	7	93	timestamp	java.sql.Timestamp
BOOLEAN	11	-7	boolean	java.lang.Boolean
TEXT	130	-1	longvarchar	java.lang.String
CATEGORICAL	129	12	varchar	java.lang.String
OBJECT	128	Ignore
LEVEL	136	-2	BINARY	byte[]

UNICOM Intelligence data types

See these sections in the UNICOM Intelligence Developer Documentation Library for more information:

▪data types

▪MDM document structure

▪variable type overview.

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 may or may 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 within 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 would be:

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)

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)

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

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.

Query from HDATA

When you query data from HDATA, the UNICOM Intelligence 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

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 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.

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

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 HDATA

Query grid field in HDATA

UNICOM Intelligence JDBC Driver specification

Query array field in HDATA

In HDATA, array fields are considered level. Querying a level field is same as querying all sub-fields of the level field. For more information, see Return level field.

You can query array object sub-fields from the array table. For more information, see the example below.

Array objects contain the specialty field LevelID, which is a sequential number in the current level.

Example

Using data from the UNICOM Intelligence Developer Documentation Library household.mdd example:

SELECT

^.housetype

, ^.address

, levelid

, age

, person

FROM HDATA.person

The result data is:

^.housetype	^.address	levelid	age	person
{59}	15B Park Avenue, Harrogate, HG1 4TY	1	25	1
{61}	46 Freedom Lane, Brighton, BN2 3YT	1	45	2
{61}	46 Freedom Lane, Brighton, BN2 3YT	2	43	3
{61}	46 Freedom Lane, Brighton, BN2 3YT	3	15	4
{61}	46 Freedom Lane, Brighton, BN2 3YT	4	12	5
{60}	The Meadows, Clifton Heights, BS34 3EG	1	72	6
{60}	The Meadows, Clifton Heights, BS34 3EG	2	81	7
{62}	23 Steep Hill, Norfold, CB64 5TY	1	32	8
{62}	23 Steep Hill, Norfold, CB64 5TY	2	9	9
{62}	23 Steep Hill, Norfold, CB64 5TY	3	2	10
{61}		1	29	11
{61}		2	31	12
...	...	...	...	...

See also

Query from HDATA

Query grid field in HDATA

In HDATA, grid fields are considered level. Querying a level field is same as querying all sub-fields of the level field. For more information, see Return level field.

You can query grid object sub-fields from the array table. For more information, see the examples below.

Grid objects contain the specialty field LevelID, which is an element value of the current grid.

Single response grids and multiple responses grids

To return categorical values in JDBC, see Return category value.

Example

Using data from the UNICOM Intelligence Developer Documentation Library museum.mdd example:

SELECT

^.name

,levelid

,column

FROM HDATA.RATING

The result data is:

^.name	levelid	column
MyName	{31}	{52}
MyName	{32}	{}
MyName	{33}	{}
MyName	{34}	{}
MyName	{35}	{}
MyName	{36}	{}
MyName	{37}	{}
MyName	{38}	{51}
MyName	{39}	{52}
MyName	{40}	{}
MyName	{41}	{50}
MyName	{42}	{51}
MyName	{43}	{}
MyName	{44}	{51}
MyName	{45}	{}
MyName	{18}	{}
	...	...

Numeric grids

Example

Using data from the UNICOM Intelligence Developer Documentation Library short_drinks.mdd example:

SELECT Levelid

,mond

,tuesd

FROM HDATA.numdrksz

The result data is:

LevelID	mond	tuesd
{48}	5	4
{49}	2	3
{50}	0	0
{51}	0	0
{52}	0	1
{53}	0	0
{54}	0	0
{48}	3	3
{49}	4	3
{50}	0	0
{51}	0	0
{52}	0	0
{53}	0	0
{54}	0	0
...	...

See also

Query from HDATA

Return category value

Single response category

Return category as a string, such as {100}.

Multiple response category

Return category as a string, such as {100,102}.

Return level field

In HDATA, array fields and grids field are considered level (hierarchical table).

If the query statement is SELECT * FROM HDATA, the current level is HDATA. If the query statement is SELECT * FROM HDATA.person, the current level is HDATA.person.

In the UNICOM Intelligence OLE DB Provider, when querying fields are the same as the current level (or upper, then current level), the field’s result should be considered as a normal field; otherwise querying fields are returned as a sub-table (level object).

Example: query content level fields and parent level fields

Using data from the UNICOM Intelligence Developer Documentation Library household.mdd example:

SELECT

^.housetype

, ^.address

, levelid

, age

, person

FROM HDATA.person

The result data is:

^.housetype	^.address	levelid	age	person
{59}	15B Park Avenue, Harrogate, HG1 4TY	1	25	1
{61}	46 Freedom Lane, Brighton, BN2 3YT	1	45	2
{61}	46 Freedom Lane, Brighton, BN2 3YT	2	43	3
{61}	46 Freedom Lane, Brighton, BN2 3YT	3	15	4
{61}	46 Freedom Lane, Brighton, BN2 3YT	4	12	5
{60}	The Meadows, Clifton Heights, BS34 3EG	1	72	6
{60}	The Meadows, Clifton Heights, BS34 3EG	2	81	7
{62}	23 Steep Hill, Norfold, CB64 5TY	1	32	8
{62}	23 Steep Hill, Norfold, CB64 5TY	2	9	9
{62}	23 Steep Hill, Norfold, CB64 5TY	3	2	10
{61}		1	29	11
{61}		2	31	12
...	...	...	...	...
{59}	15B Park Avenue, Harrogate, HG1 4TY	1	25	1
{61}	46 Freedom Lane, Brighton, BN2 3YT	1	45	2
		2	43	3
		3	15	4
		4	12	5
{60}	The Meadows, Clifton Heights, BS34 3EG	1	72	6
{60}	The Meadows, Clifton Heights, BS34 3EG	2	81	7
{62}	23 Steep Hill, Norfold, CB64 5TY	1	32	8
		2	9	9
		3	2	10
{61}		1	29	11
{61}		2	31	12
...	...	...	...	...

Return level object

Since the JDBC Driver only supports basic JDBC types, when the driver receives a level object from the UNICOM Intelligence OLE DB Provider, the driver transports the level object as a binary array. At the Java client site, users are able to identify column values as a level of a sub-table by using the data type. If you want to place data inside a sub-table, you must query data directly from the table (for example, SELECT * FROM HDATA.person).

Connection string

When connecting using the JDBC driver, the User ID and Password in the DataSourceIPCustomProperties in the oadm.ini file must have valid values (see Updating the JDBC Service Driver to support custom connection property prefixes).

The User and Password used in the connection string setup in your Java code can have any values: these values are not used, but they must be set. For example, to test that you can read case data, reference the PHjc.jar, and create a Java application as follows.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

publicclassJDBCTest{
  publicstaticvoidmain (String args[]) throws SQLException, ClassNotFoundException {
    Class.forName("com.spss.datacollection.jdbc.openaccess.OpenAccessDriver");
    String conStr = "jdbc:paswdatacollection://localhost:19999;CustomProperties=(Data Source=;Location=;Initial Catalog='C:\\CATITEST1.mdd';);User=JY-UI\\Administrator;Password=xxxxx;";
    Connection conn = DriverManager.getConnection(conStr);
    Statement sta = conn.createStatement();
    String Sql = "select * from vdata";
    ResultSet rs = sta.executeQuery(Sql);
    while(rs.next()) {
      System.out.println(rs.getString("Respondent.Serial"));
    }
    System.out.println("Finish!");
  }
}

The connection string in the code can also be set to include both the User (not used) and the User Id (used) to override the oadm.ini values. For example:

String conStr = "jdbc:paswdatacollection://localhost:19999;CustomProperties=(Data Source=;Location=;Initial Catalog='C:\\CATITEST1.mdd';User ID=JY-UI\\Administrator;Password=Pass1234);User=sa;Password=abc;";

Troubleshooting

After installing the JDBC Service Driver, how can I verify that it is functioning properly? Is there a log that can help debug potential problems?

There is a Windows service named DataCollectionJDBCDriverService that runs even when the Data Model is not installed on your machine. When problems occur, you can check the DataCollectionJDBCDriverService service log in the Windows Event Viewer. You can access the Event Viewer from the Windows Control Panel:

Start > Control Panel > Administrative Tools > Event Viewer

What permissions are required to use the JDBC Client Driver?

The account referenced in the connection string should be a Windows user, and must have permission to log onto the JDBC Service Driver machine. Blank passwords are not accepted.

How can I convert category values, such as {25,26}, to readable strings via the JDBC Client Driver?

By default, the JDBC Client Driver returns category values for the type CATEGORICAL. You can use the method FormatValue() to convert the values to a label. For example, if you want to convert a column named location in an HDATA table, you can write a query such as:

SELECT FormatValue(location, location, ’b’) from HDATA

Note that the first location in the above query is the variable name defined in the metadata and the second location is the column name in HDATA. See “FormatValue” in the UNICOM Intelligence Developer Documentation Library for more information.

Obtaining the category element label

Use the metadata mechanisms and, if required, use the DefinedCategories function. Metadata should be supplied via the standard JDBC metadata mechanism, not new SQL syntax. SQL syntax should be used as a pass-through to mrOleDB.

Example for multi-response categoricals

// Categorical string (unique values): SELECT Museums FROM VDATA

// Categorical string (category names): SELECT Museums.Format(’a’) FROM VDATA

// Categorical string (category labels): SELECT Museums.Format(’b’) FROM VDATA

// Category set (unique values)

SELECT Museums.GetAnswer(0), Museums.GetAnswer(1), Museums.GetAnswer(2), Museums.GetAnswer(3), Museums.GetAnswer(4) FROM VDATA

// Category set (category names)

SELECT Museums.GetAnswer(0).Format(’a’), Museums.GetAnswer(1).Format(’a’), Museums.GetAnswer(2).Format(’a’), Museums.GetAnswer

(3).Format(’a’), Museums.GetAnswer(4).Format(’a’) FROM VDATA

// Dichotomy set

SELECT Museums.HasAnswer({national_museum_of_science}) As national_museum_of_science, Museums.HasAnswer({museum_of_design}) As museum_of_design, Museums.HasAnswer({institute_of_textiles_and_fashion}) As institute_of_textiles_and_fashion, Museums.HasAnswer({archeological_museum}) As archeological_museum, Museums.HasAnswer({national_art_gallery}) As national_art_gallery, Museums.HasAnswer({northern_gallery}) As northern_gallery,

Museums.HasAnswer({other}) As other, Museums.HasAnswer({not_answered }) As not_answered FROM VDATA

The labels for all elements can be returned from mrOleDB. For example: SELECT Museums.DefinedCategories().Format(’b’) FROM VDATA