HOWTO: Retrieving Calculated Fields from SQL Server 7.0

• Microsoft SQL Server version 7.0
• ActiveX Data Objects (ADO), version 2.1 SP2

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 );
}
 

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

cn.ConnectionString = 
"Provider=SQLOLEDB;Server=rrbman;Database=testdb;uid=sa;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)

) 

REFERENCES

SQL Server 7.0 Books Online; search on: "DBPROP_SERVERDATAONINSERT"

Additional query words: kbado kbGroupMdac

Keywords :
Version : WINDOWS:2.1 SP2; winnt:7.0
Platform : WINDOWS winnt
Issue type : kbhowto