ADO database connection

ADO database connection

I have been learning about ADO database connections these days, and I feel complicated. So I made some notes while learning and using them, the following are some of the things I have summarized about the connection to the ADO database. Because it is a note, it is not necessarily very orderly. The post can be used as a reference, I also hope that you can work together to improve the meaning of the errors and shortcomings. Of course, I hope you can notify me in a timely manner. When I post them, I have an idea, because I always encounter some unknown errors in the process of using them, I think you may also encounter them. So I gave a summary of the errors, of course, we need to complete it here. If you have any thoughts and opinions, I hope to leave a message in the comments, I will accept your comments and add them in time (Of course, with your permission)

Example of the connection string used to connect to the Sybase Database:
Provider = Sybase. aseoledbprovider.2; initial catalog = examsysdb; user id = sa; Data Source = exam; persist Security info = false

1. import warehouse receiving File

Before using ADO, you must use the import symbol # import to introduce the ADO library file at the end of the stdafx. h file of the project so that the compiler can compile it correctly. The Code is as follows:
# Import "C: \ Program Files \ common files \ System \ ADO \ msado15.dll" no_namespace Rename ("EOF", "endoffile") Rename ("Bof", "firstoffile ")

Ado class is defined as a kind of resource stored in ado dll (msado15.dll), which is internally called a Type Library. The Type Library describes the autonomous interface and the com vtable interface used by C ++. When using the # import command, Visual C ++ needs to read this type of library from ado dll at runtime and create a set of C ++ header files. These header files have the. TLI and. tlh extensions, which can be found in the project directory. The ADO class called in the C ++ program code should be defined in these files.
The third line of the program indicates that the ADO object does not use namespaces. In some applications, namespace is necessary because the objects in the application may conflict with those in ADO. If you want to use a namespace, you can change the third-line program to rename_namespace ("adons "). The fourth line of code changes the EOF In ADO to adoeof to avoid conflicts with other libraries that define their own EOF.

2. initialize the com Environment
(1): coinitialize (null); // initialize the OLE/COM library Environment
: Couninitialize (); // Since the environment is initialized, it is necessary to release it.
(2) You can also call the global function of MFC.
Afxoleinit ();

3. Define three objects and create instances

(1) _ connectionptr pconnection ("ADODB. Connection ");
_ Recordsetptr precordset ("ADODB. recordset ");
_ Commandptr pcommand ("adodn. Command ");

(2) _ connectionptr pconnection;
_ Recordsetptr precordset;
_ Commandptr pcommand;

Pconnection. createinstance (_ uuidof (connection ));
Precordset. createinstance (_ uuidof (recordset ));
Pcommand. createinstance (_ uuidof (command ));

(3) _ connectionptr pconnection;
_ Recordsetptr precordset;
_ Commandptr pcommand;

Pconnection. createinstance ("ADODB. Connection ");
Precordset. createinstance ("ADODB. recordset ");
Pcommand. createinstance ("ADODB. Command ");

4. Open a connection
Pconnection-> open (connectionstring, "", "", admodeunknown); // connect to the database

The above connection string connectionstring corresponds to different writing methods based on different data sources.
1) Access 2000
"Provider = Microsoft. Jet. oledb.4.0; Data Source = databasename; user id = username; Password = userpassword"

2) access ODBC Data
"Provider = madasql; DSN = dsnname; uid = username; Pwd = userpassword ;"

3) accessing the Oracle database
"Provider = msdaora; Data sourse = servername; user id = username; Password = userpassword ;"

3) access the ms SQL database
"Provider = sqloledb, data source = servername; initial catalog = databasename; user id = username; Password = userpassword ;"

4. Execute SQL commands
There are many SQL commands, but I will not consider the details. Here I will only explain the general method.
Cstring strsql; // defines an SQL command string to save SQL statements.

Strsql. Format ("SQL statement ");

Then, use the strsql. allocsysstring () method in each method that uses the SQL command string for type conversion.

5. Dedicated Data Types of COM

Variant, BSTR, safearray

Variant variables have many scopes and are managed using _ variant_t.
BSTR is a string variable that is managed using _ bstr_t.

6. Close the connection

If (m_pconnection-> state) // you cannot close the connection multiple times. Otherwise, an error occurs.
M_pconnection-> close ();

7. structured exception handling
Ado encapsulates the COM interface, so it requires error processing.
For example:
Hresult hr;
Try
{
HR = m_pconnection.createinstance ("ADODB. Connection"); // create a connection object
If (succeeded (HR ))
{
HR = m_pconnection-> open ("provider = Microsoft. Jet. oledb.4.0; Data Source = test. mdb", "", ", admodeunknown); // connect to the database
/// In the above sentence, the provider in the connection string is for the Access2000 environment. For Access97, you need to change it to provider = Microsoft. Jet. oledb.3.51 ;}
}
Catch (_ com_error e) // catch an exception
{
Cstring errormessage;
Errormessage. Format ("failed to connect to the database! \ R \ n error message: % s ", E. errormessage ());
Afxmessagebox (errormessage); // displays the error message.
}

8. Error Cause Analysis
(1) the interface is not supported. It may be that null values cannot be inserted.

 

Well, I hope you can complete this summary with me!

Trackback: http://tb.blog.csdn.net/TrackBack.aspx? Postid = 18452
From: http://blog.csdn.net/eliner/archive/2004/04/22/18452.aspx

Introduction to using ADO to access the database-VC
Keyword ADO database access
Original Author name: xiangshengchang

Introduction
VC uses ADO to access the database. It introduces the various objects and methods used by VC to access the database. It is classic and practical and worth noting.

Reader rating 10 rating 3

Body
I. Ado Overview
ADO is an easy-to-use application layer interface designed by Microsoft for the latest and most powerful data access example ole db. Ado enables you to write applications to access and operate data on database servers through the OLE. DB Provider. The main advantages of ADO are ease of use, fast speed, low memory consumption, and small disk relics. Ado uses the least network traffic in key application solutions, and uses the least number of layers between the front end and the data source. All these are designed to provide lightweight, high-performance interfaces. ADO is a familiar metaphor for OLE Automation interface.

Ole db is a set of "Component Object Model" (COM) interfaces. It is a new low-level database interface that encapsulates ODBC functions, access data stored in different information sources in a unified manner. Ole db is the technical basis for Microsoft UDA (Universal Data Access) policies. Ole db provides high-performance access to any data source, including relational and non-relational databases, e-mail and file systems, text and graphics, and custom business objects. That is to say, ole db is not limited to isam, Jet, or even relational data sources. It can process any type of data without considering their format and storage methods. In practical applications, this diversity means that you can access data that resides in an Excel spreadsheet, text file, email/directory service, or even a mail server, such as Microsoft Exchange. However, the ole db Application Programming Interface is designed to provide the best functions for various applications and does not meet the simplification requirements. The API you need should be a bridge between applications and ole db, Which is ActiveX Data Objects (ADO ).

2. Use ADO in VC :)

1. Introduce the ADO Library File

Before using ADO, you must use the direct import symbol # import in the stdafx. h header file of the project to introduce the ADO library file so that the compiler can compile it correctly. The Code is as follows:

Import ADO library files with # Import

# Import "C: \ Program Files \ common files \ System \ ADO \ msado15.dll" no_namespaces Rename ("EOF" adoeof ")

This statement declares that ADO is used in the project, but the namespace of ADO is not used. To avoid constant conflicts, change the constant EOF to adoeof. Now you can use the ADO interface without adding another header file.

2. initialize the OLE/COM library Environment
Note that the ADO library is a group of COM dynamic libraries, which means that the application must initialize the OLE/COM library environment before calling ADO. In an MFC application, a better method is to initialize the OLE/COM library environment in the initinstance member function of the main class of the application.

Bool cmyadotestapp: initinstance ()
{
If (! Afxoleinit () // This is the initialization com Library
{
Afxmessagebox ("OLE initialization error !");
Return false;
}

......

}

3. Introduction to ADO Interfaces

The ADO library contains three basic interfaces: _ connectionptr, _ commandptr, and _ recordsetptr.
_ Connectionptr interface returns a record set or a null pointer. It is usually used to create a data connection or execute an SQL statement that does not return any results, such as a stored procedure. Using the _ connectionptr interface to return a record set is not a good method. _ Recordserptr is usually used for operations to return records. When you use the _ connectionptr operation, you need to retrieve the number of records to traverse all records, but you do not need to use the _ recordserptr operation.

The _ commandptr interface returns a record set. It provides a simple method to execute stored procedures and SQL statements that return record sets. When using the _ commandptr interface, you can use the global _ connectionptr interface, or you can directly use the connection string in the _ commandptr interface. If you only perform one or several data access operations, the latter is a good choice. However, if you want to frequently access the database and return many record sets, you should use the global _ connectionptr interface to create a data connection, use the _ commandptr interface to execute stored procedures and SQL statements.

_ Recordsetptr is a record set object. Compared with the above two types of objects, it provides more control functions for the record set, such as record lock and cursor control. Like the _ commandptr interface, it does not have to use a created data connection. You can use a connection string to replace the connection pointer with the connection Member variable assigned to _ recordsetptr, create a data connection by yourself. If you want to use multiple record sets, the best way is to use the global _ connectionptr interface that has created a data connection like the command object.
And then use _ recordsetptr to execute stored procedures and SQL statements.

4. Use the _ connectionptr Interface
_ Connectionptr is mainly a connection interface that gets a connection to the database. Its connection string can be directly written by itself or directed to an odbc dsn.

_ Connectionptr pconn;
If (failed (pconn. createinstance ("ADODB. Connection ")))
{
Afxmessagebox ("create instance failed! ");
Return;
}

Cstring strsrc;
Strsrc = "driver = SQL Server; server = ";
Strsrc = "suppersoft ";
Strsrc = "; database = ";
Strsrc = "mydb ";
Strsrc = "; uid = sa; Pwd = ";

Cstring strsql = "insert into student (No, name, sex, address) values (3, 'aaa', 'male', 'beijing ')";

_ Variant_t varsrc (strsrc );
_ Variant_t varsql (strsql );
_ Bstr_t bstrsrc (strsrc );

If (failed (pconn-> open (bstrsrc, "", "",-1 )))
{
Afxmessagebox ("can not open database! ");
Pconn. Release ();
Return;
}

Colevariant vtoptional (long) disp_e_paramnotfound, vt_error );

Pconn-> execute (_ bstr_t (strsql), & vtoptional,-1 );

Pconn. Release ();

Afxmessagebox ("OK! ");

5. Use the _ recordsetptr interface (taking connecting to SQL Server as an example)
_ Recordsetptr pptr;
If (failed (pptr. createinstance ("ADODB. recordset ")))
{
Afxmessagebox ("create instance failed! ");
Return false;
}

Cstring strsrc;
Strsrc = "driver = SQL Server; server = ";
Strsrc = "210.46.141.145 ";
Strsrc = "; database = ";
Strsrc = "mydb ";
Strsrc = "; uid = sa; Pwd = ";
Strsrc = "sa ";

Cstring strsql = "select ID, name, gender, address from personal ";

_ Variant_t varsrc (strsrc );
_ Variant_t varsql (strsql );

If (failed (pptr-> open (varsql, varsrc, adopenstatic, adlockoptimistic, adshorttext )))
{
Afxmessagebox ("Open Table failed! ");
Pptr. Release ();
Return false;
}

While (! Pptr-> getadoeof ())
{
_ Variant_t varno;
_ Variant_t varname;
_ Variant_t varsex;
_ Variant_t varaddress;

Varno = pptr-> getcollect ("ID ");
Varname = pptr-> getcollect ("name ");
Varsex = pptr-> getcollect ("gender ");
Varaddress = pptr-> getcollect ("Address ");

Cstring strno = (char *) _ bstr_t (varno );
Cstring strname = (char *) _ bstr_t (varname );
Cstring strsex = (char *) _ bstr_t (varsex );
Cstring straddress = (char *) _ bstr_t (varaddress );

Strno. trimright ();
Strname. trimright ();
Strsex. trimright ();
Straddress. trimright ();

Int ncount = m_list.getitemcount ();
Int nitem = m_list.insertitem (ncount, _ T (""));
M_list.setitemtext (nitem, 0, strno );
M_list.setitemtext (nitem, 1, strname );
M_list.setitemtext (nitem, 2, strsex );
M_list.setitemtext (nitem, 3, straddress );

Pptr-> movenext ();
}

Pptr-> close ();
Pptr. Release ();

6. Use the _ commandptr Interface
The _ commandptr interface returns A recordset object and provides more record set control functions. The following code example shows how to use the _ commandptr interface:

Code 11: Use the _ commandptr interface to obtain data

_ Commandptr pcommand;
_ Recordsetptr PRS;
Pcommand. createinstance (_ uuidof (command ));
Pcommand-> activeconnection = pconn;
Pcommand-> commandtext = "select * from student ";
Pcommand-> commandtype = ad1_text;
Pcommand-> parameters-> refresh ();
PRS = pcommand-> execute (null, null, adcmdunknown );
_ Variant_t varvalue = PRS-> getcollect ("name ");
Cstring strvalue = (char *) _ bstr_t (varvalue );

6. For data type conversion, because the COM object is cross-platform, it uses a common method to process various types of data,
Therefore, the cstring class is incompatible with the COM Object. We need a set of APIs to convert data of the COM Object and C type. _ Vatiant_t and _ bstr_t are two types of objects. They provide common methods to convert data of the COM Object and C type.
This article from: Book tribe-e-book download (www.shubulo.com) detailed source reference: http://www.shubulo.com/thread-13572-1-1.html