Changing Data Using Rowset Objects

A consumer can change data through rowset objects. OLE DB Provider for Teradata generates the DELETE, INSERT or UPDATE statement that is used to modify the data of the underlying table. The WHERE clause (for DELETE and UPDATE) identifies the rows.

After a consumer generates a rowset, the consumer changes the data through either the IRowsetChange (immediate mode) or the IRowsetUpdate (deferred mode) interface.

Setting Rowset Properties

To update through a rowset under OLE DB Provider for Teradata, the consumer must set the rowset properties as follows:

Table 9: Setting Rowset Properties
Property	Value
DBPROP_UPDATABILITY	DBPROPVAL_UP_CHANGE DBPROPVAL_UP_DELETE DBPROPVAL_UP_INSERT
DBPROP_IRowsetChange	TRUE
DBPROP_IRowsetUpdate	TRUE for deferred update mode FALSE for immediate update mode

Unsearchable Columns

In order to update using a rowset object, searchable columns must exist in the rowset. Rowsets that only contain columns that are unsearchable cannot be updated. Columns that are of the following data types are unsearchable:

DOUBLE/FLOAT/REAL

BYTE

VARBYTE

If a rowset only contains columns that are unsearchable E_FAIL is returned when IRowset::GetNextRows is called and the following error message is generated:

Query cannot be updated because it contains no searchable columns to use as a key.

Rowset Restrictions

The rowset must have at least one searchable column.

The rowset must have been generated from a call to either:

IOpenRowset::OpenRowset

ICommand::Execute

When ICommand::Execute is used, the specified SELECT statement must only contain one table or view in the FROM clause. The SELECT statement can also contain a WHERE clause.

A semicolon should not be used to terminate a SELECT statement that does not contain a WHERE clause.

Example of proper format:

SELECT column1 from table1

If a semicolon terminates the SELECT statement, the call to IRowset::GetNextRows returns the E_UNEXPECTED response with this error message:

[OLE DB Provider for Teradata]Query cannot be updated because FROM clause is not a single simple table name.

Columns should not have alias names specified in the SELECT list. OLE DB Provider for Teradata uses the alias as the column name when it generates the DML to update the database. This causes an E_FAIL to be returned from a call to a method in the IRowsetChange interface, and the following error message is generated:

[Teradata Database]Column/Parameter <column alias> does not exist.

For example, OLE DB Provider for Teradata is not able to update the rowset generated from the following SELECT statement:

SELECT ItemNo, Description AS ItemDesc, PackageType AS PkgType FROM ItemTable

FLOAT, REAL, DOUBLE Restriction

OLE DB Provider for Teradata does not use columns of type FLOAT, REAL, and DOUBLE in the WHERE clause. This restriction might have unintended consequences.

When the desired rowset contains a column of type FLOAT, REAL or DOUBLE, include the primary key of the underlying table in the SELECT statement. If you do not include the key, several other rows can be affected unintentionally.

Example

In the Item table, the Price column is type FLOAT.

Table 10: Price Column
Item No (INTEGER) PK	Description (VARCHAR)	Package Type (VARCHAR)	Price (FLOAT)
1	Peas	1 can	1.21
2	Carrots	1 can	1.30
3	Soda	6 pack	3.00
4	Potatoes	1 sack	2.40
5	Ham	1 can	6.54
6	Soda	12 pack	5.50

The consumer submits the following SELECT statement that does not contain the primary key column:

SELECT Description, Price FROM Item

The rowset that returns from ICommand::Execute is:

Table 11: Rowset Return from ICommand::Execute
Row Handle	Description	Price
0x1	Peas	1.21
0x2	Carrots	1.30
0x3	Soda	3.00
0x4	Potatoes	2.40
0x5	Ham	6.54
0x6	Soda	5.50

Assume the user wants to increase the price of a six-pack of Soda to $3.25.

The consumer uses IRowset::SetData to update the Price in the row containing Row Handle 0x3 from 3.00 to 3.25. The SQL that OLE DB Provider for Teradata submits to Teradata Database is similar to:

UPDATE Item SET Price = ? WHERE Description = ?

Soda is bound to the Description column in the FROM clause, and 3.25 is bound to the Price column.

The UPDATE statement has the following effect on the Item table:

Table 12: Table after Update
Item No (INTEGER) PK	Description (VARCHAR)	Package Type (VARCHAR)	Price (FLOAT)
1	Peas	1 can	1.21
2	Carrots	1 can	1.30
3	Soda	6 pack	3.25
4	Potatoes	1 sack	2.40
5	Ham	1 can	6.54
6	Soda	12 pack	3.25

The intention was to only update the six-pack price in the row of ItemNo = 3. However, because Soda is bound to the Description column, this SQL statement updates rows with Item No 3 and 6 in the underlying table.

A usual practice is to specify Price in the UPDATE statement WHERE clause, to restrict the update to the six-pack price. However, OLE DB Provider for Teradata did not include Price in the WHERE clause because Price is type FLOAT in the underlying table. OLE DB Provider for Teradata does not use columns of type FLOAT, REAL, and DOUBLE in the WHERE clause. To solve this problem, you should always include the primary key in the SELECT statement that creates the rowset:

SELECT ItemNo, Description, Price from Item

The rowset that returns from ICommand::Execute is:

Table 13: Rowset Return Using Primary Key
Row Handle	ItemNo	Description	Price
0x1	1	Peas	1.21
0x2	2	Carrots	1.30
0x3	3	Soda	3.00
0x4	4	Potatoes	2.40
0x5	5	Ham	6.54
0x6	6	Soda	5.50

The ItemNo primary key column is now available in the rowset. The unique ItemNo can identify the row instead of the non-unique soda description.

The consumer uses IRowsetChange::SetData to update the Price in the row with the Row Handle 0x3. OLE DB Provider for Teradata generates the UPDATE statement:

UPDATE Item SET Price = ? WHERE Description = ? and ItemNo = ?

This statement uniquely identifies the target row. Teradata Database only updates the six-pack Soda price.

Recommendations

When you update rowsets, the following is recommended:

Always include the primary key in the rowset.

Avoid using columns of type FLOAT, REAL or DOUBLE if there is no primary key in the rowset.

For more information on using IRowsetChange and IRowsetUpdate to change data, refer to the Microsoft Developer’s Network (MSDN) documentation on Changing Data.

MSDN Library - See http://msdn.microsoft.com/library/

OLE DB Programmer’s Guide

Part 1: Introduction to OLE DB

Chapter 5: Updating Data in Russets

Changing Data