Data Manipulation Language (DML)

The SQL Engine can support SQL Data Manipulation Language (DML) queries for INSERT, UPDATE and DELETE statements. You can choose to support some or all of these queries in your custom driver's result set implementation.

You can also use an index to optimize these queries. Since UDPATE and DELETE queries might involve a WHERE clause, using an index can improve performance.

The following sections explains how to enable support for DML queries in your custom driver.

This section explains how to change your driver from read-only to read-write, in order to support DML.

Step 1 - Specify the Connection is Read/Write

Set the DSI_CONN_DATA_SOURCE_READ_ONLY property in the constructor of your CustomerDSIIConnection object, to “N” which enables read/write access to the datasource. The preferred method for setting this is to call the DSIPropertyUtilities::SetReadOnly helper method and pass in false for the in_isReadOnly parameter.

Example: Codebase's Connection class

In this example, the Codebase sample driver’s CBConnection class contains a helper method called SetConnectionPropertyValues(), which is invoked from the class’s constructor. One of the first actions this method performs is to call the SetReadOnly method passing in false:

void CBConnection::SetConnectionPropertyValues()

{

DSIPropertyUtilities::SetStoredProcedureSupport(this, true);

DSIPropertyUtilities::SetReadOnly(this, false);

...

Step 2 - Open the Underlying Table in Read/Write Mode

In your driver’s DSIExtSqlDataEngine‑derived class, ensure that the OpenTable method can open your data source’s underlying table in read/write mode.

Example: Codebase's OpenTable Method

In the Codebase sample driver, the in_openType parameter is forwarded to the underlying CBTable class that the OpenTable method creates:

if (utilities.DoesTableExist(m_codeBaseSettings->m_dbfPath,in_catalogName,in_schemaName,in_tableName,schemaName))

{

table = new CBTable(

m_codeBaseSettings,

m_statement,

in_catalogName,

schemaName,

in_tableName,

(TABLE_OPEN_READ_ONLY == in_openType));

}

return table;

}

Step 3 - Specify how UPDATE and DELETE Queries are Handled

Determine if UPDATE and DELETE queries will be handled by a result set or by an index. Set DSIEXT_DATAENGINE_USE_DSII_INDEXES property to “N” or “Y” respectively to enable indexing. By default, the DSII indexes are disabled in DSI layer in the method DSIExtSqlDataEngine::SetDefaultPropertyValues().

Step 4 - Implement the Required Methods

Implement the following required methods:

AppendRow()

DeleteRow()

WriteData()

Optionally, you can implement the additional methods:

OnStartRowUpdate()

OnFinishRowUpdate()

OnStartDMLBatch()

OnFinishDMLBatch()

These methods are described further in the following sections, as well as in the C++ API Reference.

Supporting DML in the Java SDK is similar to supporting it in the C++ SDK, as described in this section.

Step 1 - Specify Read/Write on your Connection

Set the DSI_DATA_SOURCE_READ_ONLY property in the constructor of your CustomerDSIIConnection object, to “N” which enables read/write access to the datasource. The preferred method for setting this is to call the PropertyUtilities::SetReadOnly helper method pass in false for the isReadOnly parameter.

Step 2 - Open the Underlying Table in Read/Write Mode

In your driver’s DSIExtSqlDataEngine‑derived class, ensure that the OpenTablemethod can open your data source’s underlying table in read/write mode.

If a driver will handle INSERT statements, these must be handled in a result set regardless of whether indexes are enabled or not.

To handle row insertions, any table (that is, any DSIExtResultSet–derived class) returned by OpenTable in TABLE_OPEN_READ_WRITE mode must override and implement the AppendRow and WriteData methods, and optionally OnFinishRowUpdate. Note that OnStartRowUpdate will not be called for INSERT statements because the invocation of the AppendRow method implies that a row update will occur.

Note:

Under the Java Simba SQLEngine, result sets are typically defined by deriving from the DSIExtJResultSet class. DSIExtJResultSet provides default implementations which throw an exception, and therefore each must be implemented.

AppendRow is invoked by the Simba SQLEngine when an INSERT statement is encountered, to signal to the driver that a new row needs to be created. This method must append an empty row to the end of the underlying data source and position the cursor at the new row. After the method exits, the Simba SQLEngine will then invoke the class’s WriteData method (described below) to write the column data to the cells in this row. Drivers can also perform other row insertion logic as required. For example a driver could cache flags to indicate that a row insertion has occurred, where the flags are used in other phases of data insertion.

Example: Codebase's CBTable::AppendRow method

The CBTable::AppendRow method from the Codebase sample driver positions the cursor at the bottom of the underlying table. It then adds an empty row and caches flags indicating that a row is in the process of being appended and that the table has been modified:

void CBTable::AppendRow()

{

ENTRANCE_LOG(m_log, "Simba::", "CBTable", "AppendRow");

SE_CHK_INVALID_OPR("AppendRow", m_isReadOnly);

// Move to the last record.

m_tableHandle.bottom();

// Append a blank row.

m_tableHandle.appendStart();

m_tableHandle.blank();

m_isAppendingRow = true;

m_hasInsertedRecords = true;

}

The m_isAppendingRow flag will be checked at a later point after the row has been updated, in order to perform the final data commit. See OnFinishRowUpdate below. The m_hasInsertedRecords flag is later checked when closing the cursor, to determine if table operations should be flushed:

void CBTable::DoCloseCursor()

{

...

if (m_hasInsertedRecords)

{

m_tableHandle.flush();

}

...

}

Implementing OnFinishRowUpdate

Optionally, the result set can also implement OnFinishRowUpdate if needed. OnFinishRowUpdate is invoked by the Simba SQLEngine after a row has been updated or inserted. A driver can use this method to perform any final steps required to complete the data update or row insertion, such as committing the data to disk.

Example: Codebase's OnFinishRowUpdate method

In this example, Codebase checks if the method is being invoked due to a row insertion operation. It then performs the final logic necessary to append a row to the underlying table. Afterwards, it increments the known row count, sets the cursor to the new row, and resets the flag it uses to determine if the row is being inserted.

void CBTable::OnFinishRowUpdate()

{

if (m_isAppendingRow)

{

// Commit the new row.

m_tableHandle.append();

// Update the row count and current row.

++m_rowCount;

assert(m_rowCount == m_tableHandle.recCount());

SetCurrentRow(m_rowCount - 1);

m_isAppendingRow = false;

}

}

Optionally, the OnStartDMLBatch and OnFinishDMLBatch methods can also be implemented to handle the start and end of an INSERT operation in the result set class. OnStartDMLBatch takes in the DMLType enum which specifies the type of operation (DML_INSERT in this case), as well as the number of rows that will be affected by the operation. This method will be invoked before AppendRow and OnFinishDMLBatch will be invoked after the INSERT operation is complete. These methods can be used by a driver to prepare for the insertion of new rows and to perform any post‑INSERT logic in the result set class.

A driver can handle UPDATE and DELETE statements on a table, for example DSIExtResultSet for C++ or DSIExtJResultSet for Java. A driver can also handle UPDATE and DELETE statements on one of its indexes, for example DSIExtIndex. This section describes the methods you use to implement the handling of these statements in a result set.

Note:

If indexes are enabled, these methods will not be invoked. The analogous methods in DSIExtIndex will be called instead.

Indexes are not supported when using the Java Simba SQLEngine.

Handling Updates

This section explains how to handle an update statement in a result set when using the C++ SDK.

Note:

Handling row updates/insertions and DML batches under the Java Simba SQLEngine is similar. except that tables derive from DSIExtJResultSet and the TableOpenType.READ_WRITE enum must be passed to OpenTable().

To handle an UPDATE statement in a result set, any table (i.e. DSIExtResultSet–derived class) returned by OpenTable in TABLE_OPEN_READ_WRITE mode must override and implement WriteData. Optionally, it can also override and implement OnStartRowUpdate, OnFinishRowUpdate, OnStartDMLBatch, and OnFinishDMLBatch.

WriteData is invoked by the Simba SQLEngine when inserting a row or updating row values. This method is responsible for writing a single column of data for a particular row to the underlying table, and will be invoked for each column which is to be inserted or updated.

Example: Codesbase's CBTable::WriteData() method

This method starts by ensuring that the operation is not being performed on a read‑only table and that the column and row specified are within range. A handle to the underlying column is then obtained which will be used to specify the cell to write to. The method then checks if the column’s default value (e.g. null) should be stored, and then delegates the writing of the data to a helper class called CBTypeUtilities which handles the details of storing the data in the underlying table.

The OnStartRowUpdate and OnFinishRowUpdate methods from DSIExtResultSet can also be overridden and implemented if needed.

OnStartRowUpdate is invoked by the Simba SQLEngine when an UPDATE operation is about to be performed, before writing data to update a row with (i.e. before WriteData is invoked). This method does not take in any parameters but can be optionally used by a driver to cache information that a row update is about to take place (e.g. to store a flag that WriteData can use to determine if the write is part of an insert or update operation). Note that this method is not called after AppendRow (i.e. during an INSERT operation) because it is implied that data will be written.

OnFinishRowUpdate is invoked by the Simba SQLEngine after data has been updated or a row inserted. This method can be used by a driver to perform any final steps required to complete the data update or row insertion, such as committing the data to disk. Note that the sample driver only uses this method to complete the insertion of a new row and not for row updates.

Optionally, the OnStartDMLBatch and OnFinishDMLBatch methods can also be implemented to handle the start and end of the UPDATE operation in the result set class. OnStartDMLBatch takes in the DMLType enum which specifies the type of operation (DML_UPDATE in this case), as well as the number of rows that will be affected by the operation. This method will be invoked before OnStartRowUpdate and OnFinishRowUpdate. The OnFinishDMLBatch method will be invoked after the UPDATE operation is complete. These methods can be used by a driver to prepare for the updates of rows and to perform any post‑UPDATE logic in the result set class.

Handling Deletes

To handle DELETE statements in a result set, the DeleteRow method must be overridden and implemented.

DeleteRow is invoked by the SQL Engine or Java Simba SQLEngine when a DELETE statement is encountered. This method is responsible for performing the deletion logic for the current row on the underlying table.

Example: Codebase's CBTable::DeleteRow method

This example shows the type of operations that are typically performed by a driver to delete a row.

This method starts by ensuring that the DELETE operation is not being performed on a read‑only table and that the cursor is on a valid row. Note that the Simba SQLEngine first invokes the class’s methods to properly position the cursor before DeleteRow is called, so this check should always be successful.

Finally, the method delegates the deletion to its underlying table class, reduces the cached row count, repositions the cursor to the row preceding the deleted row, and sets a flag indicating that a deletion has occurred. The class will check this flag later on when closing the cursor to perform proper cleanup.

void CBTable::DeleteRow()

{

ENTRANCE_LOG(m_log, "Simba::", "CBTable", "DeleteRow");

SE_CHK_INVALID_OPR("DeleteRow", m_isReadOnly);

// Ensure the cursor is positioned on a valid row.

if ((GetCurrentRow() >= m_rowCount) ||

(GetCurrentRow() < 0))

{

CBTHROW2(

DIAG_ROW_VAL_OUT_OF_RANGE,

L"CBInvalidRowNum",

NumberConverter::ConvertToWString(GetCurrentRow() + 1),

m_tableName);

}

// Mark the current record for deletion.

assert(m_rowCount > 0);

m_tableHandle.deleteRec();

// Update the row count.

--m_rowCount;

SetCurrentRow(GetCurrentRow() - 1);

m_hasDeletedRecords = true;

}

Optionally, the OnStartDMLBatch and OnFinishDMLBatch methods can also be implemented to handle the start and end of the DELETE operation in the result set class. OnStartDMLBatch takes in the DMLType enum which specifies the type of operation (DML_DELETE in this case), as well as the number of rows that will be affected by the operation. This method will be invoked before DeleteRow. The OnFinishDMLBatch method will be invoked after the DELETE operation is complete. These methods can be used by a driver to prepare for the deletion of new rows and to perform any post‑DELETE logic in the result set class.

A driver can handle UPDATE and DELETE statements in an index to provide better performance in locating a row to update or delete, than when handling the statement via a result set. This section describes the methods to implement the handling of these statements in an index. Note that DSII indexes must be enabled in order for the Simba SQLEngine to invoke these methods.

Note:

Indexes are not supported by the Java Simba SQLEngine.

Handling Updates

To handle an UPDATE statement in an index, a DSIExtIndex–derived class must override and implement WriteData, and optionally OnStartRowUpdate and OnFinishRowUpdate.

WriteData is invoked by the Simba SQLEngine when inserting a row or updating row values. This method is responsible for writing a single column of data for a particular row to the underlying table, and will be invoked for each column which is to be inserted or updated.

Example:

This method delegates the writing of cell data to its underlying index table and then instructs that object to set a flag indicating that the table has been modified.

The OnStartRowUpdate, OnFinishRowUpdate, OnStartDMLBatch, and OnFinishDMLBatch methods from DSIExtIndex can optionally be overridden and implemented.

OnStartRowUpdate is invoked by the Simba SQLEngine when an UPDATE operation is about to be performed, before writing data to update a row with (i.e. before WriteData is invoked). This method does not take in any parameters but can be optionally used by a driver to cache information that a row update is about to take place.

OnFinishRowUpdate is invoked by the Simba SQLEngine after data has been updated or a row inserted. This method can be used by a driver to perform any final steps required to complete the data update or row insertion, such as committing the data to disk.

The OnStartDMLBatch and OnFinishDMLBatch methods can be implemented to handle the start and end of the UPDATE operation in the index class. OnStartDMLBatch takes in the DMLType enum which specifies the type of operation (DML_UPDATE in this case), as well as the number of rows that will be affected by the operation. This method will be invoked before OnStartRowUpdate. The OnFinishDMLBatch method will be invoked after the UPDATE operation is complete. These methods can be used by a driver to prepare for the updates to rows and to perform any post‑UPDATE logic in the index class.

Handling Deletes

To handle DELETE statements in an index, the DeleteRow method must be overridden and implemented.

DeleteRow is invoked by the SQL Engine when a DELETE statement is encountered. This method is responsible for performing the deletion logic for the current row on the underlying table.

Example:

This example shows the type of operations that are typically performed by a driver to delete a row.

void XMIndex::DeleteRow()

{

assert(!m_beforeFirstRow);

assert(m_rowPos != m_rows.end());

std::vector<RowIdentifier>::iterator currRow = m_rowPos;

// Delete the row from the table.

m_tableData.DeleteDataRow(*currRow);

if (currRow == m_rows.begin())

{

// Remove the row from the list of rows in the index

m_rows.erase(currRow);

// Set the row position to before the first row.

m_beforeFirstRow = true;

}

else

{

// Set the position to the previous row.

--m_rowPos;

// Remove the row from the list of rows in the index.

m_rows.erase(currRow);

}

m_table.SetTableModified();

}

This method obtains a pointer to the current row and then determines whether that row is the first row in the index’s table. If it is the first row, the code removes the row and then sets a flag (m_beforeFirstRow) indicating that the cursor no longer points to a row. If the row is not the first in the table, the cursor is decremented to the row preceding the row to be deleted, and the current row is removed. Finally, a flag is set on the underlying table to indicate that the table has been modified.

Optionally, the OnStartDMLBatch and OnFinishDMLBatch methods can also be implemented to handle the start and end of the DELETE operation in the index class. OnStartDMLBatch takes in the DMLType enum which specifies the type of operation (DML_DELETE in this case), as well as the number of rows that will be affected by the operation. This method will be invoked before DeleteRow. The OnFinishDMLBatch method will be invoked after the DELETE operation is complete. These methods can be used by a driver to prepare for the deletion of new rows and to perform any post‑DELETE logic in the index class.