Problem With Simple Microsoft.SQLSERVER.CE.OLEDB.3.5 Update Query Using Native Code / ATL

Question

   I am trying to write a "simple" UPDATE query using the following native code that I have cobbled together from examples. The code compiles and runs without error and the results indicate that several rows were updated but the changes were not written to the DB. What in the #$%^#@% amd I doing wrong ?? Thanks for any help. TB

#define ROUNDUP(size) ROUNDUP_(size, 8)
void MyExample()
{
	HRESULT hr = NOERROR; 
	IDBCreateCommand * pIDBCrtCmd = NULL;
	ICommandText * pICmdText = NULL;
	ICommandPrepare * pICmdPrepare = NULL;
	ICommandWithParameters * pICmdWParams = NULL;
	ULONG ulNumCols;
	IAccessor * pIAccessor = NULL;
	ULONG cParams;
	DBPARAMINFO * rgParamInfo = NULL;
	WCHAR* pNamesBuffer = NULL;
	BYTE * pData = NULL; 	
	ULONG cbRowSize;
	DBPARAMS params;
	HACCESSOR hAccessor;

	hr = CoInitialize(0);

	DBBINDING* rgBindings = (DBBINDING*)CoTaskMemAlloc( sizeof(DBBINDING) * 2 );

	memset( rgBindings, 0, sizeof(DBBINDING) * 2 );
	
    CComPtr<IDBInitialize> spIDBInitialize;
	hr = spIDBInitialize.CoCreateInstance(OLESTR("Microsoft.SQLSERVER.CE.OLEDB.3.5"));

    CComPtr<IDBProperties> spIDBProperties;
    hr = spIDBInitialize->QueryInterface(IID_IDBProperties, (void**) &spIDBProperties);

	CComVariant varDataSource(OLESTR("MySQLCEDB.sdf"));

	CComVariant varPassword(OLESTR("MyPword"));

	DBPROP sscedbprop = { DBPROP_SSCE_DBPASSWORD, DBPROPOPTIONS_REQUIRED, 0, DB_NULLID, varPassword };
	DBPROP dbprop = { DBPROP_INIT_DATASOURCE, DBPROPOPTIONS_REQUIRED, 0, DB_NULLID, varDataSource };
	
	DBPROPSET dbpropset[2];

	// Initialize property set.
	dbpropset[0].guidPropertySet = DBPROPSET_DBINIT;
	dbpropset[0].rgProperties = &dbprop;
	dbpropset[0].cProperties = 1;

	dbpropset[1].guidPropertySet = DBPROPSET_SSCE_DBINIT;
	dbpropset[1].rgProperties = &sscedbprop;
	dbpropset[1].cProperties = 1;

	hr = spIDBProperties->SetProperties(2, dbpropset);
 
    hr = spIDBInitialize->Initialize();

    CComPtr<IDBCreateSession> spIDBCreateSession;
    hr = spIDBInitialize->QueryInterface(IID_IDBCreateSession, (void**) &spIDBCreateSession);

	hr = spIDBCreateSession->CreateSession(NULL,IID_IDBCreateCommand,(IUnknown**)&pIDBCrtCmd);
	
	hr = pIDBCrtCmd->CreateCommand(NULL,IID_ICommandWithParameters, (IUnknown**)&pICmdWParams); 
	
	hr = pICmdWParams->QueryInterface( IID_ICommandText,(void**)&pICmdText);
	
	hr = pICmdWParams->QueryInterface( IID_ICommandPrepare,(void**)&pICmdPrepare);
	
	LPCTSTR wSQLString = L"UPDATE MyTable SET Name = ? WHERE Name = ?";
	hr = pICmdText->SetCommandText( DBGUID_DBSQL, wSQLString );

	hr = pICmdPrepare->Prepare(1);
	
	hr = pICmdWParams->GetParameterInfo(&cParams, &rgParamInfo, &pNamesBuffer);
	
	hr = pICmdText->QueryInterface( IID_IAccessor, (void**)&pIAccessor);

	
	DWORD dwOffset = 0;

	rgBindings[0].iOrdinal = 1;
	rgBindings[0].obStatus = dwOffset;
	rgBindings[0].obLength = dwOffset+sizeof(DBSTATUS);
	rgBindings[0].obValue = dwOffset+sizeof(DBSTATUS); + sizeof(ULONG);
	rgBindings[0].pTypeInfo = NULL;
	rgBindings[0].pObject = NULL;
	rgBindings[0].pBindExt = NULL;
	rgBindings[0].dwPart = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;
	rgBindings[0].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
	rgBindings[0].eParamIO = DBPARAMTYPE_INPUTOUTPUT;
	rgBindings[0].cbMaxLen = sizeof(WCHAR) * 100;
	rgBindings[0].dwFlags = 0;
	rgBindings[0].wType = DBTYPE_WSTR;
	rgBindings[0].bPrecision = 0;
	rgBindings[0].bScale = 0;

	dwOffset = rgBindings[0].cbMaxLen + rgBindings[0].obValue;
	dwOffset = ROUNDUP( dwOffset );
	
	rgBindings[1].iOrdinal = 2;
	rgBindings[1].obStatus = dwOffset;
	rgBindings[1].obLength = dwOffset + sizeof(DBSTATUS);
	rgBindings[1].obValue = dwOffset + sizeof(DBSTATUS) + sizeof(ULONG);
	rgBindings[1].pTypeInfo = NULL;
	rgBindings[1].pObject = NULL;
	rgBindings[1].pBindExt = NULL;
	rgBindings[1].dwPart = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;
	rgBindings[1].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
	rgBindings[1].eParamIO = DBPARAMTYPE_INPUTOUTPUT;
	rgBindings[1].cbMaxLen = sizeof(WCHAR) * 100;
	rgBindings[1].dwFlags = 0;
	rgBindings[1].wType = DBTYPE_WSTR;
	rgBindings[1].bPrecision = 0;
	rgBindings[1].bScale = 0;
	
	cbRowSize = rgBindings[1].obValue + rgBindings[1].cbMaxLen;
	
	hr = pIAccessor->CreateAccessor( DBACCESSOR_PARAMETERDATA, 2, rgBindings, cbRowSize, &hAccessor, NULL );

	pData = (BYTE*) malloc( cbRowSize );
	
	memset( pData, 0, cbRowSize );

	WCHAR* new_value =  L"NewName";
	WCHAR* old_val = L"OldName";
	
	*(DBSTATUS*)(pData + rgBindings[0].obStatus) = DBSTATUS_S_OK;
	wcscpy( (WCHAR*)(pData + rgBindings[0].obValue), new_value );	
	*(ULONG*)(pData + rgBindings[0].obLength) = sizeof(WCHAR) * wcslen( new_value );	
	*(DBSTATUS*)(pData + rgBindings[1].obStatus) = DBSTATUS_S_OK;	
	wcscpy( (WCHAR*)(pData + rgBindings[1].obValue), old_val );	
	*(ULONG*)(pData + rgBindings[1].obLength) = sizeof(WCHAR) * wcslen( old_val );	
	
	params.pData = pData;
	params.cParamSets = 2;
	params.hAccessor = hAccessor;
	
	DBROWCOUNT row_count;
	
	hr = pICmdText->Execute( NULL, IID_NULL, &params, &row_count, NULL );
	// when I run this, row_count is set to 5 indicating 5 rows were affected but the changes
	// do not appear in the DB file.
	// What am I doing wrong ?????

}

Answer 1

Hi Tom,

Please debug your codes and print the T-SQL commands out, please check the whether the final T-SQL commands are correct. Additionally, you can copy the final T-SQL command here for analysis.

---

Allen Li
TechNet Community Support

Answer 2

Do you have more copies of the sdf file - look in your bin/debug folder, perhaps.

---

Please mark as answer, if this was it. Visit my SQL Server Compact blog

Answer 3

Hi Tom,

Please debug your codes and print the T-SQL commands out, please check the whether the final T-SQL commands are correct. Additionally, you can copy the final T-SQL command here for analysis.

---

Allen Li
TechNet Community Support

Allen, thanks for the reply. I am not sure what you are asking for. Could you explain further?

Tom

Answer 4

Hi Tom,

Please debug your codes and print the T-SQL commands out, please check the whether the final T-SQL commands are correct. Additionally, you can copy the final T-SQL command here for analysis.

---

Allen Li
TechNet Community Support

   If I understand what you are suggesting(http://msdn.microsoft.com/en-us/library/ms241871(v=vs.80).aspx), unfortunately I am using VC++2010/Express which does not have this capability ;-(((

The DB file was created by a VC# 2010 Express vesrion app and works fine there(.NET4.0).

Tom Fisher

Answer 5

Hi, with C++ codes, we execute a T-SQL command to update data in SQL Server. Since the command didn’t take effect, I suggest printing the command out rather than executing it for debugging, in this way, we can try to execute the command directly with SQL Server Management Studio to check whether the command is correct. If the final command is wrong, we need to check our codes to determine which available value is wrong. If the final T-SQL command is correct, we will need to check the connection between the application with SQL Server.

---

Allen Li
TechNet Community Support

Answer 6

  Thanks Allen. Sorry I don't understand what you are asking me to do. What do you mean by "printing the command out" ? The command is shown in the code above as the following. Other than running it in the VC++ Express debugger, I don't know what you are asking me to do. Please explain. 

LPCTSTR wSQLString = L"UPDATE MyTable SET Name = ? WHERE Name = ?";
hr = pICmdText->SetCommandText( DBGUID_DBSQL, wSQLString );

TB