Write-back
The Simba Couchbase ODBC Driver supports Data Manipulation Language (DML) statements such as INSERT, UPDATE, and DELETE. ODBC batch operations are only supported through the use of arrays of parameters and parameterized SQL statements. The connector does not support SQL subqueries or ODBC transactions.
Important:
Writing data to the Couchbase database might 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 Couchbase 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 might contain mixed data types. The Simba Couchbase ODBC Driver 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 way that the connector handles each DML statement is described in more detail below.
Important:
The CREATE, ALTER, and DROP statements are not supported for tables.
INSERT
Nested Arrays
When inserting an element into a one-level array, you do not need to specify the index; by default, the connector appends the element to the end of the array. However, when inserting an element into an array that is nested within one or more other arrays, you must specify the index of each higher-level array.
For example, consider the following document named TeamList1, which contains the following attributes:
{
"Type": "Roster",
"Team":"Wildcats",
"Players":
[["a","b","c"], 1, 2, 3,
{Name :
{LastName : "Smith"}
}
]
}
The first element in the Players array is a nested array. The following table shows the virtual table that the connector would generate to represent the nested array.
| PK | Players_idx | Players_dim2_idx | Players |
|---|---|---|---|
|
"TeamList1" |
0 |
0 |
a |
|
"TeamList1" |
0 |
1 |
b |
|
"TeamList1" |
0 |
2 |
c |
To insert the value "d" in the nested array, you would execute the following statement:
INSERT INTO "TeamList1_Players_dim2" ("PK", "Players_idx", "Players") values ('TeamList1',0,'d')
Mixed Data Types in Arrays
When you have an array of differently-typed elements, the virtual table representing the array stores the value of each differently-typed element on a separate row. If you use a single INSERT statement to insert multiple differently-typed elements, the connector inserts each value as a new row in the virtual table.
For example, consider the following document named TeamList2, which contains the following attributes:
{
"Type": "Roster",
"Team":"Wildcats",
"Players":
[1, 2, 3,
{Name :
{LastName : "Smith"}
}
]
}
The Players array contains three elements of type INTEGER (1, 2, and 3) and one element of type OBJECT (Name).
The following table shows the virtual table that the connector would generate to represent the Players array.
| PK | Players_idx | Players | Name_LastName |
|---|---|---|---|
|
"TeamList2" |
0 |
1 |
Null |
|
"TeamList2" |
1 |
2 |
Null |
|
"TeamList2" |
2 |
3 |
Null |
|
"TeamList2" |
3 |
Null |
"Smith" |
The values for the INTEGER elements and the OBJECT elements cannot be contained in the same row.
To insert the number 4 and the last name "Jones" in the Players array, you would execute the following statement:
INSERT INTO "TeamList2_Players" ("PK", "Players", "Name_LastName") values ('TeamList2', 4, 'Jones')
The following table shows the virtual table after the INSERT statement.
| PK | Players_idx | Players | Name_LastName |
|---|---|---|---|
|
"TeamList2" |
0 |
1 |
Null |
|
"TeamList2" |
1 |
2 |
Null |
|
"TeamList2" |
2 |
3 |
Null |
|
"TeamList2" |
3 |
Null |
"Smith" |
|
"TeamList2" |
4 |
4 |
Null |
|
"TeamList2" |
5 |
Null |
"Jones" |
After the INSERT statement, the TeamList2 document would look like the following in Couchbase:
{
"Type": "Roster",
"Team":"Wildcats",
"Players":
[1, 2, 3,
{Name :
{LastName : "Smith"}
},
4,
{Name :
{LastName : "Jones"}
}
]
}
UPDATE
You cannot update foreign key relations (primary key values and index values).
DELETE
When you delete a document from the Couchbase database or a row from the base table, the connector also deletes the related rows in the virtual tables.