Write-back
The Simba MongoDB ODBC Connector supports Data Manipulation Language (DML) statements such as INSERT, UPDATE, and DELETE. The connector 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 connector samples the data and generates the schema definition, the connector 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 connector supports DML statements on mixed data types. When you execute a write operation, the connector 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 connector handles each DML statement as described below.
Important:
The CREATE, ALTER, and DROP statements are not supported for tables.
INSERT
Note:
When inserting a value into an array, do not specify the bottom-level index value in your INSERT statement. The bottom-level index value is no longer required as of connector version 2.0.0.
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 connector and can be auto-generated when issuing INSERT statements through the connector. 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 connector return an error for the remaining rows. UPDATE is not executed atomically.
DELETE
The DELETE statement is not supported for virtual tables.
The connector considers a table valid as long as the table contains some data. If a table is completely empty, then the connector 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.
- Features
- Writeback Driver Configuration Options on page 1
- Configuring the Driver on page 1