Microsoft KB Archive/219029

= HOWTO: Retrieving Calculated Fields from SQL Server 7.0 =

Article ID: 219029

Article Last Modified on 12/5/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q219029



SUMMARY
Using the DBPROP_SERVERDATAONINSERT property on a rowset retrieves calculated fields and identity values immediately when they are inserted.

DBPROP_SERVERDATAONINSERT makes the retrieval of this data a one step process, and improves the performance of inserts.



MORE INFORMATION
The following code assumes that a connection has been made using the SQL Server native provider, and that Initialize has been called on the IDBInitialze interface.

NOTE: DBPROP_SERVERDATAONINSERT only applies to server side cursors, and will not function with disconnected recordsets.

struct MyBuffer {   int f1; DWORD dwStatus; char f2[21]; };

void MyInsert { IDBCreateCommand * pIDBCreateCommand; ICommandText * pICommandText; ICommandProperties * pICommandProperties; IRowsetChange * pIRowsetChange; IAccessor * pIAccessor1; HACCESSOR hAccessor1; const ULONG nCmdProps = 4; ULONG cCmdPropertySets = 1; DBPROP CmdProperties[ nCmdProps ]; DBPROPSET rgCmdPropSet; MyBuffer Buffer; DBBINDSTATUS DBBindStatus[2]; DBBINDING DBBindings[] = {   {        1,        offsetof( MyBuffer, f1 ), 0,       offsetof( MyBuffer, dwStatus ), NULL, NULL, NULL, DBPART_VALUE | DBPART_STATUS, DBMEMOWNER_CLIENTOWNED, DBPARAMIO_NOTPARAM, 4,       0,        DBTYPE_I4, 0,       0    },    {        2,        offsetof( MyBuffer, f2 ), 0,

0,       NULL, NULL, NULL, DBPART_VALUE, DBMEMOWNER_CLIENTOWNED, DBPARAMIO_NOTPARAM, 21,       0,        DBTYPE_STR, 0,       0    } };

CmdProperties[ 0 ].dwPropertyID = DBPROP_SERVERDATAONINSERT; CmdProperties[ 0 ].dwOptions = DBPROPOPTIONS_REQUIRED; CmdProperties[ 0 ].dwStatus = DBPROPSTATUS_OK; CmdProperties[ 0 ].colid = DB_NULLID; CmdProperties[ 0 ].vValue.vt = VT_BOOL; CmdProperties[ 0 ].vValue.iVal = VARIANT_TRUE;

CmdProperties[ 1 ].dwPropertyID = DBPROP_IRowsetChange; CmdProperties[ 1 ].dwOptions = DBPROPOPTIONS_REQUIRED; CmdProperties[ 1 ].dwStatus = DBPROPSTATUS_OK; CmdProperties[ 1 ].colid = DB_NULLID; CmdProperties[ 1 ].vValue.vt = VT_BOOL; CmdProperties[ 1 ].vValue.boolVal = VARIANT_TRUE;

CmdProperties[ 2 ].dwPropertyID = DBPROP_CANFETCHBACKWARDS; CmdProperties[ 2 ].dwOptions = DBPROPOPTIONS_REQUIRED; CmdProperties[ 2 ].dwStatus = DBPROPSTATUS_OK; CmdProperties[ 2 ].colid = DB_NULLID; CmdProperties[ 2 ].vValue.vt = VT_BOOL; CmdProperties[ 2 ].vValue.boolVal = VARIANT_TRUE;

CmdProperties[ 3 ].dwPropertyID = DBPROP_SERVERCURSOR; CmdProperties[ 3 ].dwOptions = DBPROPOPTIONS_REQUIRED; CmdProperties[ 3 ].dwStatus = DBPROPSTATUS_OK; CmdProperties[ 3 ].colid = DB_NULLID; CmdProperties[ 3 ].vValue.vt = VT_BOOL; CmdProperties[ 3 ].vValue.boolVal = VARIANT_TRUE;

rgCmdPropSet.guidPropertySet = DBPROPSET_ROWSET; rgCmdPropSet.cProperties = nCmdProps; rgCmdPropSet.rgProperties = CmdProperties;

pIDBCreateCommand->CreateCommand( NULL,       IID_ICommandText,        ( IUnknown ** ) & pICommandText );

pICommandText->SetCommandText( DBGUID_DBSQL, wSQLString );

pICommandText->QueryInterface( IID_ICommandProperties,       ( void ** ) & pICommandProperties );

pICommandProperties->SetProperties( cCmdPropertySets, & rgCmdPropSet );

pICommandText->Execute( NULL, IID_IRowset, NULL,       & cRowsAffected, ( IUnknown ** ) & pIRowset );

pIRowset->QueryInterface( IID_IRowsetChange, ( void ** ) & IRowsetChange );

pIRowset->QueryInterface( IID_IAccessor, ( void ** ) & pIAccessor1 );

pIAccessor1->CreateAccessor( DBACCESSOR_ROWDATA,        2,         DBBindings,         sizeof( MyBuffer ),         & hAccessor1,         DBBindStatus );

strcpy( Buffer.f2, "test test test" );

pIRowsetChange->InsertRow( DB_NULL_HCHAPTER,       hAccessor1,        & Buffer,        & hRow );

pIRowset->GetData( hRow, hAccessor1, & Buffer ); }

In Microsoft ActiveX Data Objects (ADO) the code would be as follows:

Note You must change uid= and pwd= to the correct values before you run this code. Make sure that uid has the appropriate permissions to perform this operation on the database. Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset

cn.ConnectionString = "Provider=SQLOLEDB;Server=rrbman;Database=testdb;uid= ;pwd= ;" cn.Open

rs.ActiveConnection = cn rs.Properties("Server Data On Insert").Value = True rs.Open "SELECT * FROM x",, adOpenKeyset, adLockOptimistic rs.AddNew rs.Fields(1) = "Test" rs.Update

Debug.Print rs.Fields(0)

rs.Close cn.Close

The table x is defined as;

create table x ( f1 integer identity primary key, f2 varchar(20)

)

