Friday, April 15, 2016

Object Linking and Embedding Database

Recently, I've been messing with a database API I've never used directly before, even though I've been using it all the time. I'm talking about OLE DB, the interface behind ActiveX Data Objects (ADO).

The job is rather simple. Native C++ application, MS SQL Server database, performance is a priority, so no managed code. Massive inserts into the database, so I want to use table-valued parameters (TVPs), the most friendly option for those. The ADO interface doesn't support TVPs, while OLE DB does. So OLE DB it is.

Turns out, while the OLE DB API per se is not badly documented, the body of samples and discussion on the 'Net is rather poor, so I thought I'd share some pitfalls I've spent time in along the way.

When you're calling a stored procedure that returns both recordsets and output parameters, the latter only appear in the bound variables when the recordsets have been scrolled through and released. In fact, it's the Release() call on the recordset that triggers output parameter filling.

When you want to provide an empty table as a TVP, bind both parameter value and status via the DBBINDING structure, and provide the value DBSTATUS_S_DEFAULT in the bound status variable, as opposed to DBSTATUS_S_ISNULL. If you provide the latter, the stored procedure call will fail, and the value of the status variable on completion will be DBSTATUS_E_BADSTATUS.

A useful pattern is generating an accessor handle for a procedure, and calling it multiple times with the same accessor. When doing so, watch for parameter status values changing after the ICommand::Execute() call. In my case, a variable was too big to fit into a parameter, and the status was reset to DBSTATUS_S_TRUNCATED. There was no error on the procedure call - the Execute() method returned S_OK. However, the next call to Execute() did fail, since DBSTATUS_S_TRUNCATED is not a valid status value upon input.

No comments:

Post a Comment