Simple Guide to Using ole db to read and write SQL Server in Visual C ++

Simple Guide to Using ole db to read and write SQL Server in Visual C ++
 
Huang Yousheng. This article was published by the original author on MSN space and csdn. You can save, use in non-commercial software, and reference part or all of the text in this article in a non-profit article, but please note the author and the original address. To be used for other purposes, contact the author (eien@eyou.com) first ). The author does not guarantee that this article is completely correct and is not responsible for losses caused by theoretical or code defects in this article.

When you need to operate a database, ole db is always considered to be the most efficient but difficult method. However, in my recent use of ole db, the efficiency of ole db is high, but it is not difficult at all. I am afraid it is difficult to say that it is mainly because there are too few Chinese materials for reference. I wrote this article to help my colleagues who need to access OLE DB in the future. This article includes the following content:

1. Write data to the database in ole db;
2. ole db read database;
3. ole db processes binary data (text, ntext, image, etc.

First, let's take a look at the code for writing SQL Server. readers who have a certain VC Foundation should be able to understand it smoothly. It's so easy to write ole db to a database!

Note:
1. The template class eautoreleaseptr <t> used in the following code is similar to ccomptr <t> In ATL. It is a class that automatically calls release during structure analysis. The ccomptr <t> code is defined in atlbase. h.
2. The following code is compiled in the Unicode environment, because the executed SQL statement must be Unicode. To set the project to Unicode, first Delete _ MBCS to write Unicode and _ Unicode in Preprocessor on the properties page of Project> Settings> C/C ++. On the link property page, select output in category and add wwinmaincrtstartup in entry-point symbol.

Eautoreleaseptr <idbinitialize> pidbinitialize;
Hresult = connectdatabase (& pidbinitialize, _ T ("127.0.0.1"), _ T ("sa"), _ T ("password "));
If (failed (hresult ))
{
// Failed, probably because the database is not started or the user name and password are incorrect.
Return;
}

Eautoreleaseptr <iopenrowset> piopenrowset;
Hresult = createsession (pidbinitialize, & piopenrowset );
If (failed (hresult ))
{
// Error
Return;
}

Eautoreleaseptr <icommand> picommand;
Eautoreleaseptr <icommandtext> picommandtext;
Hresult = createcommand (piopenrowset, & picommand, & picommandtext );
If (failed (hresult ))
{
// Error
Return;
}

Hresult = executesql (picommand, picommandtext, _ T ("use pbdata "));
If (failed (hresult ))
{
// If this fails, the SQL statement execution fails. Here, pbdata has not been created.
Return;
}

// Create a table
Executesql (picommand, picommandtext, _ T ("create table 2005_1 (volume real not null, Id int not null identity )"));

// Add record
Executesql (picommand, picommandtext, _ T ("insert into 2005_1 values (100.0 )"));

//...

The code for several functions is as follows:

Hresult connectdatabase (idbinitialize ** ppidbinitialize, maid, maid, pszpassword)
{
Assert (ppidbinitialize! = NULL & pszdatasource! = NULL & pszuserid! = NULL & pszpassword! = NULL );

Uint utimeout = 15u; // connection timeout (seconds)
Tchar szinitstr [1024];
Verify (1023> = wsprintf (szinitstr, _ T ("provider = sqloledb; Data Source = % s; initial catalog = Master; user id = % s; Password = % s; connect timeout = % u "), pszdatasource, pszuserid, pszpassword, utimeout ));
// Initial catalog = Master indicates "use master" after the connection is successful ".

Eautoreleaseptr <idatainitialize> pidatainitialize;
Hresult =: cocreateinstance (clsid_msdainitialize, null, clsctx_inproc_server,
Iid_idatainitialize, (void **) & pidatainitialize );
If (failed (hresult ))
{
Return hresult;
}

Eautoreleaseptr <idbinitialize> pidbinitialize;
Hresult = pidatainitialize-> getdatasource (null, clsctx_inproc_server, (lpcolestr) szinitstr,
Iid_idbinitialize, (iunknown **) & pidbinitialize );
If (failed (hresult ))
{
Return hresult;
}
Hresult = pidbinitialize-> initialize ();
If (failed (hresult ))
{
Return hresult;
}
* Ppidbinitialize = pidbinitialize. Detach ();
Return s_ OK;
}

Hresult createsession (idbinitialize * pidbinitialize, iopenrowset ** ppiopenrowset)
{
Assert (pidbinitialize! = NULL & ppiopenrowset! = NULL );
Eautoreleaseptr <idbcreatesession> psession;
Hresult = pidbinitialize-> QueryInterface (iid_idbcreatesession, (void **) & psession );
If (failed (hresult ))
{
Return hresult;
}
Eautoreleaseptr <iopenrowset> piopenrowset;
Hresult = psession-> createsession (null, iid_iopenrowset, (iunknown **) & piopenrowset );
If (failed (hresult ))
{
Return hresult;
}
* Ppiopenrowset = piopenrowset. Detach ();
Return s_ OK;
}

Hresult createcommand (iopenrowset * piopenrowset, icommand ** ppicommand, icommandtext ** ppicommandtext)
{
Assert (piopenrowset! = NULL & ppicommand! = NULL & ppicommandtext! = NULL );
Hresult;
Eautoreleaseptr <icommand> picommand;
{
Eautoreleaseptr <idbcreatecommand> picreatecommand;
Hresult = piopenrowset-> QueryInterface (iid_idbcreatecommand, (void **) & picreatecommand );
If (failed (hresult ))
{
Return hresult;
}

Hresult = picreatecommand-> createcommand (null, iid_icommand, (iunknown **) & picommand );
If (failed (hresult ))
{
Return hresult;
}
}
Eautoreleaseptr <icommandtext> picommandtext;
Hresult = picommand-> QueryInterface (& picommandtext );
If (failed (hresult ))
{
Return hresult;
}
* Ppicommand = picommand. Detach ();
* Ppicommandtext = picommandtext. Detach ();
Return s_ OK;
}

Hresult executesql (icommand * picommand, icommandtext * picommandtext, lpctstr pszcommand, long * plrowsaffected)
{
Assert (picommand! = NULL & picommandtext! = NULL & pszcommand! = NULL & pszcommand [0]! = 0 );

Hresult = picommandtext-> setcommandtext (dbguid_dbsql, (lpcolestr) pszcommand );
If (failed (hresult ))
{
Return hresult;
}
Long laffected;
Hresult = picommand-> execute (null, iid_null, null, plrowsaffected = NULL? & Laffected: plrowsaffected, (iunknown **) null );
Return hresult;
}

The above is all the code for writing the database, isn't it easy? Read it again.

// Obtain picommand and picommandtext by following the same steps in the above Code. Omitted here

Hresult = picommandtext-> setcommandtext (dbguid_dbsql, (lpcolestr) _ T ("select volume from 2005_1 where id = @ identity ")); // retrieve the record we just added
If (failed (hresult ))
{
Return;
}

Long laffected;
Eautoreleaseptr <irowset> pirowset;
Hresult = picommand-> execute (null, iid_irowset, null, & laffected, (iunknown **) & pirowset );

If (failed (hresult ))
{
Return;
}

Eautoreleaseptr <iaccessor> piaccessor;
Hresult = pirowset-> QueryInterface (iid_iaccessor, (void **) & piaccessor );

If (failed (hresult ))
{
Return;
}

// A structure defined based on the numerical type of each field in the table, used to store the values of the returned Fields
Struct cloadlastfromdb
{
Dbstatus dwdsvolume;
DWORD dwlenvolume;
Float fvolume;
};

// Here we only query one field. To query multiple fields, add the corresponding field definitions in cloadlastfromdb, and expand the following dbbinding. Dbbinding []. iordinal should point to each field respectively. dbbinding []. wtype should assign appropriate values based on the field type.

Dbbinding [1];
Dbbinding [0]. iordinal = 1; // the location of the volume field, starting from 1
Dbbinding [0]. obvalue = offsetof (cloadlastfromdb, fvolume );
Dbbinding [0]. oblength = offsetof (cloadlastfromdb, dwlenvolume );
Dbbinding [0]. obstatus = offsetof (cloadlastfromdb, dwdsvolume );
Dbbinding [0]. ptypeinfo = NULL;
Dbbinding [0]. pobject = NULL;
Dbbinding [0]. pbindext = NULL;
Dbbinding [0]. dwpart = dbpart_value | dbpart_status | dbpart_length;
Dbbinding [0]. dwmemowner = dbmemowner_clientowned;
Dbbinding [0]. eparamio = dbparamio_notparam;
Dbbinding [0]. cbmaxlen = 0;
Dbbinding [0]. dwflags = 0;
Dbbinding [0]. wtype = dbtype_r4; // float is dbtype_r4, and Int Is dbtype_i4. See msdn
Dbbinding [0]. bprecision = 0;
Dbbinding [0]. bscale = 0;

Haccessor = db_null_haccessor;
Dbbindstatus DBS [1];
Hresult = piaccessor-> createaccessor (dbaccessor_rowdata, 1, dbbinding, sizeof (cloadlastdatafromdb), & haccessor, DBS );

If (failed (hresult ))
{
Return;
}

Assert (DBS [0] = dbbindstatus_ OK );
Ulong urowsobtained = 0;
Hrow hrows [1]; // Here we only query the latest record
Hrow * phrows = hrows;
Cloadlastfromdb RMD;
Hresult = pirowset-> getnextrows (null, 0, 1, & urowsobtained, & phrows );
If (succeeded (hresult) & urowsobtained! = 0u)
{
Hresult = pirowset-> getdata (phrows [0], haccessor, & RMD );
If (failed (hresult ))
{
Assert (false );
}
Assert (RMD. dwdsvolume = dbstatus_s_ OK );
// RMD. fvolume is the value we want
}

Pirowset-> releaserows (urowsobtained, phrows, null, null );
Piaccessor-> releaseaccessor (haccessor, null );
Piaccessor. Release ();
Pirowset. Release ();

The read operation is complete, isn't it still very easy? Next let's take a look at the most troublesome binary data reading and writing (text, ntext, image, etc. To read and write BLOB data, we need an auxiliary class, which is defined as follows:

Class csequentialstream: Public isequentialstream // BLOB Data Handler class
{
Public:
Csequentialstream ();
Virtual ~ Csequentialstream ();
Virtual bool seek (ulong uposition );
Virtual bool clear ();
Virtual ulong getlength () {return m_ubufferused ;};
Virtual operator void * const () {return m_pbuffer ;};
Stdmethodimp _ (ulong) addref () {return ++ m_urefcount ;};
Stdmethodimp _ (ulong) release () {assert (m_urefcount! = 0u); -- m_urefcount; If (m_urefcount = 0u) {Delete this;} return m_urefcount ;};
Stdmethodimp QueryInterface (refiid riid, lpvoid * GMM );
Stdmethodimp read (void _ rpc_far * PV, ulong CB, ulong _ rpc_far * pcbread );
Stdmethodimp write (const void _ rpc_far * PV, ulong CB, ulong _ rpc_far * writable written );
Void resetposition () {m_uposition = 0u ;};
Hresult preallocbuffer (ulong usize );

PRIVATE:
Ulong m_urefcount; // reference count
Void * m_pbuffer; // Buffer
Ulong m_ubufferused; // Buffer Used
Ulong m_ubuffersize; // buffer size
Ulong m_uposition; // current index position in the buffer
};

The implementation is as follows:

Csequentialstream: csequentialstream (): m_urefcount (0u), m_pbuffer (null), m_ubufferused (0u), m_ubuffersize (0u), m_uposition (0u)
{
Addref ();
}

Csequentialstream ::~ Csequentialstream ()
{
Clear ();
}

Hresult csequentialstream: QueryInterface (refiid riid, void ** GMM)
{
If (riid = iid_iunknown | riid = iid_isequentialstream)
{
* GMM = this;
(Iunknown *) * GMM)-> addref ();
Return s_ OK;
}
* GMM = NULL;
Return e_nointerface;
}

Bool csequentialstream: Seek (ulong uposition)
{
Assert (uposition <m_ubufferused );
M_uposition = uposition;
Return true;
}

Bool csequentialstream: clear ()
{
M_ubufferused = 0u;
M_ubuffersize = 0u;
M_uposition = 0u;
(M_pbuffer! = NULL? Cotaskmemfree (m_pbuffer): 0 );
M_pbuffer = NULL;
Return true;
}

Hresult csequentialstream: preallocbuffer (ulong usize)
{
If (m_ubuffersize <usize)
{
M_ubuffersize = usize;
M_pbuffer = cotaskmemrealloc (m_pbuffer, m_ubuffersize );
If (m_pbuffer = NULL)
{
Clear ();
Return stg_e_insufficientmemory;
}
}
Return s_ OK;
}

Hresult csequentialstream: Read (void * PV, ulong CB, ulong * pcbread)
{
(Pcbread! = NULL? (* Pcbread = 0u): 0 );
If (Pv = NULL) {return stg_e_invalidpointer ;}
If (cb = 0u) {return s_ OK ;}

Assert (m_uposition <= m_ubufferused );
Ulong ubytesleft = m_ubufferused-m_uposition;

If (ubytesleft = 0u) {return s_false;} // no more bytes

Ulong ubytesread = (CB> ubytesleft? Ubytesleft: CB );
Memcpy (PV, (byte *) m_pbuffer + m_uposition, ubytesread );
M_uposition + = ubytesread;

(Pcbread! = NULL? (* Pcbread = ubytesread): 0 );
Return (CB! = Ubytesread? S_false: s_ OK );
}

Hresult csequentialstream: Write (const void * PV, ulong CB, ulong * wrote written)
{
If (Pv = NULL) {return stg_e_invalidpointer ;}
(Writable written! = NULL? (* Required written = 0u): 0 );
If (cb = 0u) {return s_ OK ;}

Assert (m_uposition <= m_ubufferused );
If (m_ubuffersize <m_uposition + CB)
{
M_ubuffersize = m_uposition + CB;
M_pbuffer = cotaskmemrealloc (m_pbuffer, m_ubuffersize );
If (m_pbuffer = NULL)
{
Clear ();
Return stg_e_insufficientmemory;
}
}
M_ubufferused = m_uposition + CB;
Memcpy (byte *) m_pbuffer + m_uposition, PV, CB );
M_uposition + = CB;
(Writable written! = NULL? (* Required written = CB): 0 );
Return s_ OK;
}

Next, we will add records to a table that contains the ntext field. Suppose the structure of this table (News) is: Id int not null identity, title nchar (80), contents ntext.

// Add the record first, and leave the ntext field blank. We will update ntext later.
Hresult = executesql (picommand, picommandtext, _ T ("insert into news values (''test '','''')"));

Dbprop;
Dbpropset. guidpropertyset = dbpropset_rowset;
Dbpropset. cproperties = 1;
Dbpropset. rgproperties = & dbprop;

Dbpropset;
Dbpropset. rgproperties [0]. dwpropertyid = dbprop_updatability;
Dbpropset. rgproperties [0]. dwoptions = dbpropoptions_required;
Dbpropset. rgproperties [0]. dwstatus = dbpropstatus_ OK;
Dbpropset. rgproperties [0]. colid = db_nullid;
Dbpropset. rgproperties [0]. vvalue. Vt = vt_i4;
V_i4 (& dbpropset. rgproperties [0]. vvalue) = dbpropval_up_change;

Eautoreleaseptr <icommandproperties> picommandproperties;
Hresult = picommandtext-> QueryInterface (iid_icommandproperties, (void **) & picommandproperties );

// Set the rowset attribute to "updatable value of a field"
Hresult = picommandproperties-> setproperties (1, & dbpropset );

Hresult = picommandtext-> setcommandtext (dbguid_dbsql, (lpcolestr) l "select contents from news where id = @ identity ");

Long laffected;
Eautoreleaseptr <irowsetchange> pirowsetchange;
Hresult = picommand-> execute (null, iid_irowsetchange, null, & laffected, (iunknown **) & pirowsetchange );

Eautoreleaseptr <iaccessor> piaccessor;
Hresult = pirowsetchange-> QueryInterface (iid_iaccessor, (void **) & piaccessor );

Struct blobdata
{
Dbstatus dwstatus;
DWORD dwlength;
Isequentialstream * piseqstream;
};

// For dbobject and dbbinding settings, it is recommended that you refer to msdn for ease of understanding.
Dbobject dbobj;
Dbobj. dwflags = stgm_read;
Dbobj. IID = iid_isequentialstream;

Dbbinding;
Dbbinding. iordinal = 1; // position of the Blob field, starting from 1
Dbbinding. obvalue = offsetof (blobdata, piseqstream );
Dbbinding. oblength = offsetof (blobdata, dwlength );
Dbbinding. obstatus = offsetof (blobdata, dwstatus );
Dbbinding. ptypeinfo = NULL;
Dbbinding. pobject = & dbobj;
Dbbinding. pbindext = NULL;
Dbbinding. dwpart = dbpart_value | dbpart_status | dbpart_length;
Dbbinding. dwmemowner = dbmemowner_clientowned;
Dbbinding. eparamio = dbparamio_notparam;
Dbbinding. cbmaxlen = 0;
Dbbinding. dwflags = 0;
Dbbinding. wtype = dbtype_iunknown;
Dbbinding. bprecision = 0;
Dbbinding. bscale = 0;

Haccessor = db_null_haccessor;
Dbbindstatus dBs;
Hresult = piaccessor-> createaccessor (dbaccessor_rowdata, 1, & dbbinding, sizeof (blobdata), & haccessor, & DBS );

Eautoreleaseptr <irowset> pirowset;
Hresult = pirowsetchange-> QueryInterface (iid_irowset, (void **) & pirowset );

Ulong urowsobtained = 0;
Hrow * phrows = NULL;
Hresult = pirowset-> getnextrows (null, 0, 1, & urowsobtained, & phrows );

Csequentialstream * PSS = new csequentialstream;
Pss-> preallocbuffer (1024); // pre-allocated memory and read data
Pss-> write (pszsomebuffer, 512, null); // pss-> write can be called continuously
Pss-> write (pszsomebuffer + 512,512, null );
Pss-> resetposition ();

Blobdata BD;
BD. piseqstream = (isequentialstream *) PSS;
BD. dwstatus = dbstatus_s_ OK;
BD. dwlength = pss-> getlength ();

// Write BLOB data to the database
Hresult = pirowsetchange-> setdata (phrows [0], haccessor, & BD );

Piaccessor-> releaseaccessor (haccessor, null );
Pirowset-> releaserows (urowsobtained, phrows, null, null );

// PSS was released by pirowsetchange-> setdata.

In this way, a record is added. The code for reading BLOB fields is exactly the same as above.
Hresult = pirowset-> getnextrows (null, 0, 1, & urowsobtained, & phrows );
You can change the code below.

Blobdata BD;
Hresult = pirowset-> getdata (phrows [0], haccessor, & BD );
If (BD. dwstatus = dbstatus_s_isnull)
{
// This field is empty
}
Else if (BD. dwstatus! = Dbstatus_s_ OK | Bd. piseqstream = NULL)
{
// Failed
}
Else
{
// Read BLOB data from the isequentialstream interface allocated by the System
Byte szreadbuffer [1024];
For (ulong uread = 0u ;;)
{
If (failed (BD. piseqstream-> Read (szreadbuffer, 1024, & uread )))
{
Break;
}
// Szreadbuffer contains blob field data
If (uread! = 1024)
{
Break;
}
}
BD. piseqstream-> release ();
}
Piaccessor-> releaseaccessor (haccessor, null );
Pirowset-> releaserows (urowsobtained, phrows, null, null );

At this point, I have finished all the lectures, hoping to help you. The Code posted in this article can be copied and used, but in some places, the return value judgment and error handling code must be added.