📄 ado.html
字号:
<!-- 12302003 Mark Jundo P. Documento javelinexxx@yahoo.com -->
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>ActiveX Data Objects Tutorial</title>
<link rel="stylesheet" href="tutorial.css">
</head>
<body>
<h1>ActiveX Data Objects Database Programming in C++</h1>
<h2>Introduction</h2>
<p>Although I intended this tutorial to be as straightforward as possible, I still can't dispense
with the following
information that I think is necessary to understand the motive behind making
this tutorial. Programming database applications in Visual C++ can be a pain
because the tutorials that abound, especially those provided by Microsoft
usually deal with the MFC database classes, CRecordset and CDaoRecordset.
While these classes are fine for most applications, we can't help but notice the
trend towards OLE DB programming. So what exactly is OLE DB?</p>
<h4>OLE DB and ADO</h4>
<p>According to Microsoft, OLE DB is the data access 'in' thing of the future.
This is because OLE DB is intended to provide a simple way of getting data in
and out of databases, wherever they may be located. Through OLE DB it is
possible to access data contained in files, RDBMS, email, and anything that
could possibly have data. But programming OLE DB stuff is not for the
faint-hearted since having the ability to access data from virtually any source
adds to the complexity of the technology. What we need is something
that's made specifically for database access. Enter ActiveX Data Objects
or ADO. It would be right to say that ADO is a layer spread on top of OLE
DB so that the complexity of OLE DB is hidden from the casual programmer.
Among its many intended functions, ADO was designed to create controls that
could provide data access and control in web pages. So to be easily usable in
web scripting languages like VBScript and programming environments such as
Visual Basic, ADO has a minimal number of objects, namely:</p>
<ul>
<li>Connection - used for establishing and maintaining a connection with a
database.</li>
<li>Error - checked for database error information.</li>
<li>Command - used to execute commands in the database.</li>
<li>Parameter - used for passing variables and for calling parameterized
queries or stored procedures.</li>
<li>Recordset - contains a set of records from the database.</li>
<li>Field - represents a single column in the record set.</li>
</ul>
<h2>Using ADO</h2>
<p>There are two ways to use ADO in Visual C++. The first is by using ADO
controls like the ADO Data Control and the ADO Data-bound Grid. I won't be
discussing this method here since using controls does not provide us with much
flexibility. The second would be to use ADO programmatically. That
is, using the ADO classes directly.</p>
<h3>Connecting to a Database and Data Retrieval</h3>
<h4>Importing the ADO DLL</h4>
<p>To be able to use ADO we must first import the ADO DLL since ADO is not part
of the MFC class hierarchy. We can use the #import directive to import
ActiveX DLLs, in this case, the msado15.dll. The directive tells the
compiler to extract the object information from this DLL and place the
information in a couple of header files (.tlh and .tli) that are automatically
included in our project. The following lines, usually placed in "stdafx.h" should do the trick:</p>
<pre>#define INITGUID // This constant is needed by ADO.
#import "C:\Program Files\Common Files\System\ADO\msado15.dll"\
rename_namespace("ADOCG") rename("BOF", "BOFILE") rename("EOF", "EOFILE") // Rename some elements.
using namespace ADOCG;
#include "icrsint.h" // Contains definition of some macros used to write ADO application.</pre>
<p>In line no. 3, we renamed some elements in order to avoid potential conflict
with other elements. For example, we renamed EOF to avoid conflict with
the EOFdefined in <stdio.h>. Next we will try to establish a connection
with a database.</p>
<h4>Connecting to a Database</h4>
<p>Before we can use ADO, we must first initialize the COM environment for our
application with the following:</p>
<pre>::CoInitialize(NULL);</pre>
<p>This enables as to interact with ActiveX objects and so we are home-free to
make calls to ADO. After we are done with ADO we need to shut down COM by
calling CoUnintialize() as follows:</p>
<pre>::CoUninitialize();</pre>
<p>After we initialize COM, we need to establish a connection to the database by
using a Connection object pointer, the _ConnectionPtr. This is done by
declaring a _ConnectionPtr variable then initialize it with an instance of a
Connection object. Passing the UUID of the Connection object to the
CreateInstance function of the Connection pointer does the trick:</p>
<pre>_ConnectionPtr pCon; // _ConnectionPtr has only one underscore
pCon.CreateInstance(__uuidof(Connection)); // __uuidof has two underscores</pre>
<p>After creating the Connection instance, we will open the actual connection to
the database by using the member function Open():</p>
<pre><a name="Open()"></a>pCon->Open(L"DSN=pcs;DESC=MySQL ODBC 3.51 Driver DSN;DATABASE=pcs;SERVER=localhost;PORT=3306;OPTION=35;STMT=",
L"pcs", L"cygnus", <a href="#CursorTypeEnum">adOpenUnspecified</a>);</pre>
<p>The Open() function takes four parameters. The first parameter is the
connection string which defines the OLE DB data source for the database.
For an idea of how a connection string like this is formed (MySQL ODBC DSN's
only), see the following
<a href="odbc_dsn.html">supplement</a>. The second parameter is the user
ID. The third is the password, and the fourth is the type of cursor to be
used. These cursor types are defined in the msado15.tlh that are created
with the import directive:</p>
<pre><a name="CursorTypeEnum"></a>enum CursorTypeEnum
{
adOpenUnspecified = -1,
adOpenForwardOnly = 0,
adOpenKeyset = 1,
adOpenDynamic = 2,
adOpenStatic = 3
};</pre>
<h4>Executing Commands and Retrieving Records</h4>
<p>After establishing a connection to the database, we can pass SQL commands to
the database for execution by using a Command object. Creating a Command
object instance entails almost the same process as creating a Connection:</p>
<pre>_CommandPtr pCmd; // Declare a CommandPtr variable
pCmd.CreateInstance(__uuidof(Command)); // Create an instance of the Command object</pre>
<p>After creating the Command object instance, we must specify which database
connection we're going to use:</p>
<pre>pCmd->ActiveConnection = pCon;</pre>
<p>Next, we'll specify the (SQL) command we will be executing:</p>
<pre>pCmd->CommandText = "select * from item";</pre>
<p>Since the SQL command above returns records, we have two options on how to proceed. We can directly use the
Execute() member of the Command object. Execute() has the following
syntax:</p>
<pre><a name="Execute()"></a>_RecordsetPtr Execute(VARIANT *RecordsAffected, VARIANT *Parameters, long <a href="#CommandTypeEnum">Options</a>);</pre>
<p>The Options parameter of Execute() may be any of the following:</p>
<pre><a name="CommandTypeEnum"></a>enum CommandTypeEnum
{
adCmdUnspecified = -1,
adCmdUnknown = 8,
adCmdText = 1,
adCmdTable = 2,
adCmdStoredProc = 4,
adCmdFile = 256,
adCmdTableDirect = 512
};</pre>
<p>The RecordsetPtr returned by Execute is valid only for commands that return
record sets like the SQL SELECT statement.</p>
<pre>_RecordsetPtr pRset;
pRset = pCmd->Execute(NULL, NULL, adCmdText);</pre>
<p>A second option would be to specify the Command object as the record source
for a Recordset:</p>
<pre>_RecordsetPtr pRset;
pRset.CreateInstance(__uuidof(Recordset));
pRset->PutRefSource(pCmd);</pre>
<p>Afterwards we will need to call on the Open() member of the Recordset object,
which has the following syntax:</p>
<pre>HRESULT Open(const _variant_t &Source, // the record source (an SQL command)
const _variant_t &ActiveConnection, // the connection
enum <a href="#CursorTypeEnum">CursorTypeEnum</a> CursorType, // cursor type
enum <a href="#LockTypeEnum">LockTypeEnum</a> LockType, // locking type
long <a href="#CommandTypeEnum">Options</a>); // kind of command</pre>
<p>Since we already have a record source and a connection, we need to pass on a
Null for the first two parameters. The second parameter is the cursor type
exactly like the values passed to the <a href="#Open()">Open()</a> member of the
Connection object. The third parameter specifies the locking mechanism to
be used on the resulting record set, while the fourth would be exactly like the
values passed in the <a href="#Execute()">Execute()</a> member of the Command
object.</p>
<pre><a name="LockTypeEnum"></a>enum LockTypeEnum
{
adLockUnspecified = -1,
adLockReadOnly = 1,
adLockPessimistic = 2,
adLockOptimistic = 3,
adLockBatchOptimistic = 4
};</pre>
<p>The code for executing the command and retrieving the record would then be:</p>
<pre>_variant_t vtNull;
vtNull.vt = VT_ERROR;
vtNull.scode = DISP_E_PARAMNOTFOUND;
pRS->Open(vtNull, vtNull, adOpenDynamic, adLockUnspecified, adCmdText);</pre>
<p>There is a more direct way of retrieving records that allows us to dispense
with the Connection and Command objects altogether:</p>
<pre>_RecordsetPtr pRset;
pRset.CreateInstance(__uuidof(Recordset));
pRS->Open(L"select * from item",
L"DSN=pcs;DESC=MySQL ODBC 3.51 Driver DSN;DATABASE=pcs;SERVER=localhost;UID=pcs;PASSWORD=cygnus;PORT=3306;OPTION=35;STMT=",
adOpenDynamic, adLockUnspecified, adCmdText);</pre>
<p>Take note though that it is still best to open a single connection to
the database and use it for access instead of repeatedly specifying a connection
string for opening record sets.</p>
<h3>Navigating the Recordset</h3>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -