Write-back

The Simba MongoDB ODBC Driver supports Data Manipulation Language (DML) statements such as INSERT, UPDATE, and DELETE. The driver does not support SQL subqueries or ODBC transactions.

Important:

Writing data to the MongoDB database may change the typing of the data. If the existing data is of a type that is different from the column data type specified in the schema definition, then the existing data will be replaced by new data that is of the column data type.

When the driver samples the data and generates the schema definition, the driver defines a SQL data type and a MongoDB data type for each column in the base tables and virtual tables, but does not change the data types of the individual values in the database. As a result, columns may contain mixed data types. The driver supports DML statements on mixed data types. When you execute a write operation, the driver will attempt to complete the operation using the column data type specified in the schema definition.

The data provided in DML statements must match the column data types. For example, a String value cannot be inserted in a column that is defined as an Integer column in the schema definition.

The driver handles each DML statement as described below.

Important:

The CREATE, ALTER, and DROP statements are not supported for tables.

INSERT

Each row in MongoDB needs to have a unique ID represented by the _id column. If not provided during insertion, MongoDB auto-generates a unique ID for each row. The _id field is exposed as a valid column in the driver and can be auto-generated when issuing INSERT statements through the driver. For example, consider the following table.

_id

sample_column

"517024D6CC79814E3FEBD352"

1

"5170ED77E49CC93A918DE316"

2

To insert a document with an auto-generated value for _id (data type: jstOID), issue the following command:

INSERT INTO sample_table_1(sample_column) VALUES(3)

The following table shows the table after the insertion.

_id

sample_column

"517024D6CC79814E3FEBD352"

1

"5170ED77E49CC93A918DE316"

2

"51710FFCE49CC93A918DE322"

3

The value for the _id column can also be inserted using the INSERT statements, as in the following examples:

INSERT INTO sample_table_2(_id, sample_column) VALUES(1,1)

INSERT INTO sample_table_2 VALUES(1,1)

UPDATE

When updating rows, special care needs to be taken to avoid duplicate values for the _id column. As mentioned before, _id needs to be unique across all rows. When an UPDATE statement tries to set a value for the _id column and matches multiple rows, only one of the rows is updated with the new values, and the driver return an error for the remaining rows. UPDATE is not executed atomically.

DELETE

The DELETE statement is not supported for virtual tables.

The driver considers a table valid as long as the table contains some data. If a table is completely empty, then the driver is not able to access the table. Consider the following example:

DELETE FROM sample_table_3

The command removes all data from sample_table_3. Therefore, sample_table_3 is invalid. Any users attempting to access the table receive an error.