Menu
Simba Technologies
Simba Technologies

Simba MongoDB ODBC Driver with SQL Connector 2.2.3
Installation and Configuration Guide

Data Types

The Simba MongoDB ODBC Driver supports many MongoDB data types, and converts data between MongoDB and SQL data types as needed. The mapping between each data type is determined by the schema definition, which you can create by using the Schema Editor application that is installed with the driver.

Note:

For information about how to use the Schema Editor, see the Schema Editor User Guide located in the installation directory of the driver.

  • On Windows 7 or earlier, the guide is available from the Simba MongoDB ODBC Driver program group in the Start menu.
  • On Windows 8 or later, you can search for the guide on the Start screen.

The following table lists the supported data type mappings.

To support complex data types such as objects and arrays, the driver renormalizes the data into virtual tables. For more information, see Virtual Tables.

MongoDB Type SQL Type

Binary

SQL_BIGINT

SQL_BIT

SQL_DOUBLE

SQL_INTEGER

SQL_VARCHAR

Boolean

Date

SQL_TIMESTAMP

DBPointer

SQL_VARCHAR

Decimal SQL_DECIMAL

JavaScript

SQL_VARCHAR

JavaScript (with scope)

SQL_VARCHAR

Important:

MongoDB cannot represent JavaScript (with scope) data as a string. The driver converts the data to type SQL_VARCHAR, but returns the following string as the value: Unsupported JavaScript with Scope.

MaxKey

SQL_VARCHAR

MinKey

SQL_VARCHAR

NumberDouble

SQL_BIGINT

SQL_DOUBLE

SQL_INTEGER

SQL_VARCHAR

NumberInt

NumberLong

ObjectID

SQL_VARCHAR

Regular Expression

SQL_VARCHAR

String

SQL_VARCHAR

Symbol

SQL_VARCHAR

Timestamp

SQL_VARCHAR

Undefined

SQL_VARCHAR

UUID

SQL_BINARY

Array

N/A

The data is renormalized into a virtual table.

Object

N/A

The data is renormalized into a virtual table.

Different rows in the sampled data might have the same field assigned to different data types. The driver resolves this mixed data typing by specifying a single type for the field in the schema definition. The specified type is the first data type from this list that appears in the sampled data.

  1. Array

    Fields that have Object as one of the data types are also returned as Array data.

  2. Binary
  3. String

    Fields that have any of the following types as one of the data types, but not Array or Binary, are also returned as String data:

    • Date
    • Timestamp
    • DBPointer
    • JavaScript
    • JavaScript (with scope)
    • Symbol
    • Regular Expression
    • MaxKey
    • MinKey
    • Undefined
    • OID
  4. NumberDouble
  5. NumberLong
  6. NumberInt

For example, the driver treats the following field named f as MongoDB data of type Array:

{f : {g1 : 1}}

{f : [1, 2, 3]}

Data types that do not have a direct mapping from MongoDB to ODBC are represented as type VARCHAR in ODBC. The detected MongoDB type is used during INSERT and UPDATE operations.

Embedded Documents

The driver renormalizes embedded documents into columns. For example, consider the following JSON document:

{"contact": {"address": {"street": "1-123 Broadway", "city": "Vancouver"}}, "phone": "+12345678"}

When generating the schema definition, the driver identifies the following columns in the document, all of type String:

contact_address_street contact_address_city phone

1-123 Broadway

Vancouver

+12345678

The driver is able to work with these columns as if they were standard table columns.

Arrays

The ODBC interface does not natively support collection-based data types, so the Simba MongoDB ODBC Driver implements two options for accessing and interacting with collection-based data. Depending on preference, arrays in MongoDB can be renormalized into virtual tables or columns.

By default, arrays are renormalized into virtual tables. To view array elements as columns instead, use the Move to Parent option in the Schema Editor. You can create the column in a parent virtual table or in the base table.

Note:

For information about how to use the Schema Editor, see the Schema Editor User Guide located in the installation directory of the driver.

  • On Windows 7 or earlier, the guide is available from the Simba MongoDB ODBC Driver program group in the Start menu.
  • On Windows 8 or later, you can search for the guide on the Start screen.

Arrays as Virtual Tables

The driver can renormalize MongoDB arrays into virtual tables. For more information, see Virtual Tables.

Arrays as Columns

The driver can also renormalize MongoDB arrays into columns. Consider the following JSON document:

{"values": ["hello", 1, {"v1": {"v2": "this is an embedded document"}}]}

The driver can represent the array elements using the following columns, where values_1 is of type Double and the other two are of type String:

values_0 values_1 values_2_v1_v2

Hello

1.0

this is an embedded document

The driver works with these columns as if they were standard table columns.

The column names include the index of the array element that the column represents, starting with an index of 0. In other words, the first element of the array uses a suffix of _0, the second element uses _1, and so on.