ODBC programming in Visual C ++

ODBC (Open Database Connectivity) is a standard application interface (API) used to access data in related or unrelated database management systems (DBMS ). This document describes the methods and techniques for ODBC programming with Visual C ++ in Windows 95.

---- Keywords: ODBC, Visual C ++, and Windows programming.

---- 1. Overview

---- ODBC is a programming interface that uses SQL. ODBC allows application writers to avoid the complexity of connecting to the data source. This technology has been widely supported by most DBMS vendors.

---- Microsoft developer Studio provides 32-bit ODBC drivers for most standard database formats. These standard data formats include SQL Server, access, paradox, dBase, Foxpro, Excel, Oracle, and Microsoft text. If you want to use another data format, you need the corresponding ODBC drive and DBMS.

---- After you use your DBMS database management function to generate a new database mode, you can use ODBC to log on to the data source. For your applications, you can register many different databases by installing drivers. For more information about how to log on to the database, see ODBC online help.

---- II. ODBC database class provided by MFC

---- The MFC base class library of Visual C ++ defines several database classes. When using ODBC programming, cdatabase, crecordset, and crecordview are often used ). Where:

---- The cdatabase object provides a connection to the data source, through which you can operate on the data source.

---- The crecordset Class Object provides a set of Records extracted from the data source. The crecordset object is usually used in two forms: dynamic row set (dynasets) and snapshot set (snapshots ). Dynamic datasets can be synchronized with changes made by other users. A snapshot set is a static view of data. Each form provides a set of records when the record set is opened. The difference is that when you scroll to a record in a dynamic row set, changes made to this record by other users or other record sets in your application are displayed accordingly.

---- The crecordview class object can display database records in a controlled manner. This view is directly connected to the table view of a crecordset object.

---- 3. Application ODBC programming

---- Appwizard with Visual C ++ can automatically generate an ODBC application framework. The method is: Open the new option in the File menu, select projects, enter the project name, select MFC Appwizard (exe), and then follow the prompts of Appwizard. If you want to read or write a database when Appwizard asks whether the database is supported, select database view with file support; however, it is more appropriate for our mother to choose "-atabase view without file support. After database support is selected, the database Source button is activated. Select it to call the data Options dialog box. The database Options dialog box displays the database resources that have been registered with ODBC. Select the database you want to operate on, for example, super_es. Click OK and the select database tables dialog box appears, lists all the tables in the database you selected. Select the table you want to operate on and click OK. After the database and data table are selected, you can continue the Appwizard operation as usual.

---- Note that the view class of the generated application framework (for example, csuper_esview) contains a pointer m_pset pointing to the csuper_esset object, which is created by Appwizard, the purpose is to establish a connection between the view form and the record set so that the query results in the record set can be easily displayed on the view form. For more information about m_pset usage, see visual c ++ online book.

---- The Program establishes a connection with the Data Language and uses the cdatebase: openex () or cdatabase: open () function for initialization. The database object must be initialized before you use it to construct a record set object.

---- The following is an example of ODBC programming skills in the Visual C ++ environment:

---- 1. query records

---- Use the crecordset: open () and crecordset: requery () member functions to query records. Before using the crecordset class object, you must use the crecordset: open () function to obtain a valid record set. Once you have used the crecordset: open () function, you can apply the crecordset: requery () function when querying again. When calling the crecordset: open () function, if you have passed an opened cdatabase object pointer to the m_pdatabase member variable of the crecordset Class Object, use the database object to establish an ODBC connection; otherwise, if m_pdatabase is a null pointer, create a cdatabase Class Object and connect it to the default data source. Then, initialize the crecordset class object. The default data source is obtained by the getdefaconnect connect () function. You can also provide the required SQL statement and use it to call the crecordset: open () function, for example:

Super_esset.open (afx_database_use_default, strsql );
---- If no parameter is specified, the program uses the default SQL statement to operate the SQL statement specified in the getdefasql SQL () function:

Cstring csuper_esset: getdefasql SQL ()
{Return _ T ("[basicdata], [mainsize]");}
---- For the table name returned by the getdefasql SQL () function, the default operation is the SELECT statement, that is:

Select * From basicdata, mainsize
---- You can also use the member variables m_strfilter and m_strsort of the crecordset to execute conditional query and result sorting during the Query Process. M_strfilter is a filter string that stores the where condition strings in SQL statements. m_strsort is a Sort string that stores the order by character strings in SQL statements. For example:

Super_esset.m_strfilter = "type = motor ";
Super_esset.m_strsort = "voltage ";
Super_esset.requery ();
The corresponding SQL statement is:
Select * From basicdata, mainsize
Where type = Motor
Order by voltage
---- In addition to directly assigning values to m_strfilter, parameterization can also be used. Parameterization makes conditional query tasks more intuitive and convenient. To use parameterization, follow these steps:

---- (1). Declare the variable:

Cstring P1;
Float P2;
---- (2). initialize the parameters in the constructor.

P1 = _ T ("");
P2 = 0.0f;
M_nparams = 2;
---- (3). Bind the variable to the corresponding column

Pfx-> setfieldtype (cfieldexchange: Param)
Rfx_text (pfx, _ T ("p1"), P1 );
Rfx_single (pfx, _ T ("p2"), P2 );
---- After completing the preceding steps, you can use the variable to perform conditional queries:

M_pset-> m_strfilter = "type =? And voltage =? ";
M_pset-> P1 = "Motor ";
M_pset-> P2 = 60.0;
M_pset-> requery ();
---- Replace the value of the variable with "?" In the query string in the order of binding "? "Adapter.

---- If the query result is multiple records, you can use the crecordset functions to move (), movenext (), moveprev (), movefirst (), and movelast () to move the cursor.

---- 2. Add records

---- Add a record using the addnew () function, requiring the database to open in the allowed way:

M_pset-> addnew (); // Add a new record to the end of the table
M_pset-> setfieldnull (& (m_pset-> m_type), false );
M_pset-> m_type = "Motor ";
... // Enter a new field value
M_pset-> Update (); // Save the new record to the database
M_pset-> requery (); // refresh the record set
---- 3. delete records

---- Directly use the delete () function, and do not call the update () function after calling the delete () function:

M_pset-> Delete ();
If (! M_pset-> iseof ())
M_pset-> movenext ();
Else
M_pset-> movelast ();
---- 4. Modify records

---- Use the Edit () function to modify records:

M_pset-> edit (); // modify the current record
M_pset-> m_type = "generator ";
// Modify the field value of the current record
...
M_pset-> Update (); // Save the Modification result to the database
M_pset-> requery ();
---- 5. Undo the operation

---- If you want to discard the current operation after adding or modifying a record, you can call the update () function:

Crecordset: Move (afx_move_refresh );
---- To undo the Add or modify mode and restore the current record before the Add or modify mode. The value of afx_move_refresh is zero.

---- 6. Reuse of database connections

---- A member variable m_pdatabase is defined in the crecordset class:

Cdatabase * m_pdatabase;
---- It is a pointer to the object database class. If you pass an opened cdatabase Class Object Pointer to m_pdatabase before the crecordset Class Object calls the open () function, you can share the same cdatabase class object. For example:

Cdatabase m_db;
Crecordset m_set1, m_set2;
M_db.open (_ T ("super_es"); // establish an ODBC connection
M_set1.m_pdatabase = & m_db;
// M_set1 reuse m_db object
M_set2.m_pdatabse = & m_db;
// M_set2 reuse m_db object
---- 7. Direct Execution of SQL statements

---- Although using the crecordset class, we can complete most query operations, and also provide SQL statements in the crecordset: open () function, but sometimes we want to perform other operations, for example, to create a new table, delete a table, and create new fields, you need to use the cdatabase class to directly execute SQL statements. You can call the cdatabase: executesql () function to directly execute SQL statements:

Bool CDB: executesqlandreportfailure (const cstring & strsql)
{
Try
{
M_pdb-> executesql (strsql); // directly execute the SQL statement
}
Catch (cdbexception, E)
{
Cstring strmsg;
Strmsg. loadstring (ids_execute_ SQL _failed );
Strmsg + = strsql;
Return false;
}
End_catch
Return true;
}
---- It should be noted that, because the data operation statements provided by different DBMS are different, direct execution of SQL statements may damage the DBMS independence of the software. Therefore, such operations should be used with caution in applications.

---- 8. Dynamic join table

---- The dynamic table connection can be achieved by specifying an SQL statement when calling the crecordset: open () function. The same record set object can only access tables with the same structure. Otherwise, the query results cannot correspond to variables.

Void CDB: changetable ()
{
If (m_pset-> isopen () m_pset-> close ();
Switch (m_id)
{
Case 0:
M_pset-> open (afx_db_use_default_type,
"Select * From slot0"); // connection table slot0
M_id = 1;
Break;
Case 1:
M_pset-> open (afx_db_use_default_type,
"Select * From slot1"); // connect table slot1
M_id = 0;
Break;
}
}
---- 9. dynamically connect to the database

---- Because the connection to the database is implemented through the cdatabase class object, we can dynamically connect to the database by assigning the crecordset class object parameter m_pdatabase to connect the cdatabase Object Pointer of different databases.

Void CDB: changeconnect ()
{
Cdatabase * PDB = m_pset-> m_pdatabase;
PDB-> close ();

Switch (m_id)
{
Case 0:
If (! PDB-> open (_ T ("super_es ")))
// Connect to the data source super_es
{
Afxmessagebox ("Data Source super_es failed to open ,"
"Check the corresponding ODBC connection", mb_ OK | mb_iconwarning );
Exit (0 );
}
M_id = 1;
Break;
Case 1:
If (! PDB-> open (_ T ("Motor ")))
// Connect the data source Motor
{
Afxmessagebox ("failed to open the motor data source ,"
"Check the corresponding ODBC connection", mb_ OK | mb_iconwarning );
Exit (0 );
}
M_id = 0;
Break;
}
}
---- IV. Summary

---- The ODBC class library in Visual C ++ can help programmers complete the vast majority of database operations. ODBC technology is used to free programmers from specific DBMS, which greatly reduces the workload of software development, shortens the development cycle, and improves the efficiency and software reliability. This article summarizes some of my experiences in software development and hopes to help ODBC developers.