Writing Strings with OLE (CCommand)
This last weekend I was writing a COM object for submitting/editing entries into a SQL Server 2005 database we have for collecting project metrics. To do so, I was using the templated OLE DB functions: CCommand<CDynamicAccessor>, and things were progressing well until I needed to write a string value into a varchar field.
The SetValue Problem
The documentation for CDynamicAccessor's SetValue API was helpful enough to tell me that "special handling" is required when trying to store string data, but is a little vague on the specifics. After some searching around I came across a KB article (KB201390) which goes into a recommended solution which involves the horribly evil practice of blindly copying data into a buffer you don’t own. I am actually very surprised that this is the best they could come up with at the time, as this is a very good way to corrupt memory or quite possibly introduce a security vulnerability.
The Solution
After a little more poking around, I ran across the CDynamicStringAccessor, which has a nice SetString method. This let me avoid the evil hack suggested in the KB article, and did so with very little churn to my code. So now I was able to edit the strings in the database, and everything was happy with my testing. At least until the point where it didn't work...
The NULL Problem
I have a little test script for trying out my COM object which consists of a couple of lines, one of which calls my method for modifying an entry in the database, and one to read it back and display it. When I ran this test on a specific record, the update didn't stick: no matter what I tried to write, it always came back to my script as an empty string. And the utterly evil part was that no errors were generated. It turns out that if the field contained an actual NULL value (not an empty string) then the update would silently fail.
The Workaround
Since I own the database I'm trying to write to, I have the flexibility to go in and disallow NULL values on the fields I want to modify from OLE. When setting up this constraint, I also needed to provide an empty string as a default value (using two single quotes: '').
The Code
For the people (like myself) that prefer to see working samples, here is the code I used.
The SQL statement to create the table I will be writing too, notice the NOT NULL and DEFAULT values.
CREATE TABLE [Metrics](
[MetricID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Metrics_MetricID] DEFAULT (newid()),
[Name] [varchar](64) NOT NULL CONSTRAINT [DF_Metrics_Name] DEFAULT (''),
[Contact] [varchar](32) NOT NULL CONSTRAINT [DF_Metrics_Contact] DEFAULT (''),
[Units] [varchar](16) NOT NULL CONSTRAINT [DF_Metrics_Units] DEFAULT (''),
[Description] [text] NOT NULL CONSTRAINT [DF_Metrics_Description] DEFAULT (''),
[CustomURL] [varchar](256) NOT NULL CONSTRAINT [DF_Metrics_CustomURL] DEFAULT (''),
CONSTRAINT [PK_Metrics] PRIMARY KEY CLUSTERED
(
[MetricID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
The code to open the database:
lastError = dbSource.OpenFromInitializationString(L"DRIVER={SQL Server};SERVER=myserver;"
L"DATABASE=mydatabase;UID=username;PWD=password;");
if(FAILED(lastError))
{
return lastError;
}
lastError = dbSession.Open(dbSource);
if(FAILED(lastError))
{
dbSource.Close();
return lastError;
}
Here is the code I used to open and edit the record.
CCommand<CDynamicStringAccessorW> recordset;
CDBPropSet propset(DBPROPSET_ROWSET);
propset.AddProperty(DBPROP_IRowsetChange, true);
propset.AddProperty(DBPROP_IRowsetUpdate, true);
propset.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE |
DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE);
// the MetricID is hard coded here for sample purposes
char* sql = L"SELECT [Contact] FROM Metrics WHERE [MetricID] = "
L"'{00000000-0000-0000-0000-000000000000}'";
lastError = recordset.Open(dbSession, sql, &propset);
if(FAILED(lastError))
{
goto cleanup;
}
lastError = recordset.MoveFirst();
if(FAILED(lastError))
goto cleanup;
if(lastError == DB_S_ENDOFROWSET) // row not found
{
lastError = 0x80000000 | DB_S_NORESULT;
goto cleanup;
}
lastError = recordset.SetString(1, newVal);
if(FAILED(lastError))
goto cleanup;
lastError = recordset.SetData();
if(FAILED(lastError))
goto cleanup;
lastError = recordset.Update();
if(FAILED(lastError))
goto cleanup;
cleanup:
recordset.Close();
return lastError;