ODBC (Open Database Connectivity, an open database connection) is a standard application interface (API) for disappearing data in a related or unrelated database management system (DBMS). This article gives the specific method and techniques of ODBC programming with Visual C in a Windows 95 environment.
---- Keywords: ODBC, Visual C , Windows programming.
---- One. Overview
---- ODBC is a program design interface using SQL. Use ODBC to allow applications of applications to avoid complexity associated with data sources. This technology has been widely supported by most DBMS manufacturers.
---- Microsoft Developer Studio provides 32-bit ODBC drives for most standard database formats. These standard data formats include: SQL Server, Access, Paradox, DBASE, FoxPro, Excel, Oracle, and Microsoft Text. If the user wants to use other data formats, the user needs the corresponding ODBC driver and DBMS.
---- User You can use ODBC to log in to the data source after generating new database modes using your own DBMS database management feature. For users' applications, you can register a lot of different databases as long as the driver is installed. See the specific operations of the login database See online help for ODBC.
---- II. ODBC database class provided by MFC
---- Visual C MFC base library defines several database classes. When programming with ODBC, you are often used to use CDATABASE, CRecordSet (Record Class), and CRecordView. among them:
---- CDatabase class object provides a connection to the data source, by which you can operate on the data source.
---- CRecordset class object provides a recordset that is extracted from the data source. CRecordset objects are usually used in two forms: dynamic rows (Dynastes) and snapshots. Dynamic row can keep synchronization with changes made with other users. The snapshot set is a static view of the data. Each form provides a set of records when the recording set is opened, and the difference is that when you scroll to a record in a dynamic row, other users or other records in your application are The changes made by the record will be displayed accordingly.
---- CRecordView class object can display database records in the form of control. This view is a table view that is directly connected to a CRecordset object.
---- three. Apply ODBC programming
---- Application Visual C AppWizard can automatically generate an ODBC application framework. The method is: Open the new option of the File menu, select Projects, fill in the engineering name, select the MFC AppWizard (EXE), and then press the AppWizard prompt. When AppWizard is included in the database support, if you want to read and write the database, select the Database View with File Support; and 阆敕 阆敕 适 菘 男 男 男 ⒍ ⒍ 牖匦 牖匦 牖匦 牖匦 牖匦 牖匦 牖匦 牖匦 牖匦 菘 牖匦 菘"The -ATABASE View WITHOUT FILE Support option is relatively appropriate. After selecting the database support, the Database Source button is activated and select it to call the Data Options dialog. Database resources registered with ODBC are displayed in the Database Options dialog, select the database you want to operate, such as: super_ES, click OK, will appear Select Database Tables dialog, which lists the included databases. All tables, after selecting the table you want to operate, click OK. After selecting the database and data table, you can continue the appWizard operation as a result. ---- Special needs to be pointed out: in the generated application framework View class (such as: CSUPER_ESVIEW) contains a pointer M_Pset pointing to the CSUPER_ESSET object, the pointer is established by AppWizard, the purpose is to establish between the viewing form and the record set. Contact, make the query results in the record set can be easily displayed on the view form. See Visual C Online Book for details on M_PSET.
---- Program is connected to the data language to initialize using the cdatebase :: OpenEx () or CDATABASE :: Open () function. Database objects must be initialized before you use it to construct a recordset object.
---- The following example shows the programming skills of ODBC in the Visual C environment:
---- 1 . Search record
---- Query Record Using CRecordset :: Open () and CRecordset :: Requery () member functions. Before using the CRecordset class object, you must use the CRecordset :: Open () function to get a valid recordset. Once you have used the CRecordset :: Open () function, you can apply the CRecordset :: Requery () function when you query. When calling the CRecordset :: Open () function, if you have passed a M_PDatabase member variable that has been passed to the CRecordset class object, use the database object to create an ODBC connection; otherwise, if m_pdatabase is an empty pointer, you will create a new one. The CDATABASE class object is connected to the default data source and then performs the initialization of CRecordset class objects. The default data source is obtained by a getDefaultconnect () function. You can also provide the SQL statement you need, and call CRecordset :: open () functions, for example:
Super_eset.open (AFX_DATABASE_USE_DEFAULT, STRSQL);
---- If there is no specified parameters, the program uses the default SQL statement, which is to operate on the SQL statement specified in the getDefaultsql () function:
CSTRING CSUPER_ESSET :: getDefaultsql ()
{Return_T ("[BasicData], [MAINSIZE]");} ---- For the table name returned for the getDefaultsql () function, the corresponding default operation is a SELECT statement, namely:
Select * from BasicData, MAINSIZE
---- Inquiries can also use CRecordset member variables M_Strfilter and M_STRSORT to perform conditional query and result sorting. M_Strfilter is a filter string, stores the condition string after WHERE in the SQL statement; m_strsort is the sort string, stores the string after the ORDER BY in the SQL statement. Such as:
Super_eset.m_strfilter = "Type = motor";
Super_eset.m_strsort = "Voltage";
Super_esset.Requery ();
The corresponding SQL statement is:
Select * from BasicData, MAINSIZE
Where type = electric motor
ORDER by Voltage
---- In addition to the direct assignment to m_strfilter, parameterization can be used. Using parameterization can be more intuitive and more convenient to complete the conditional query task. The steps to use parameterization are as follows:
---- (1) . Disclaimer the parameters:
CSTRING P1;
Float p2;
---- (2) . Initialize the amount of parameters in the constructor
P1 = _t ("");
P2 = 0.0F;
m_nparams = 2;
---- (3). Bind the parameters with the corresponding column
PFX-> setfieldType (cfieldexchange :: param)
RFX_Text (PFX, _T ("P1"), P1);
RFX_SINGLE (PFX, _T ("p2"), p2);
---- After completing the above steps, you can use the parameters to check:
m_pset-> m_strfilter = "Type =? and Voltage =?"
m_pset-> p1 = "electric motor";
m_pset-> p2 = 60.0;
m_pset-> Requory ();
---- The value of the parameter is replaced in the "?" Adaptation in the query string in order in the order.
---- If the result of the query is a plurality of records, you can move the cursor with the CRecordset class function Move (), MoveNext (), Movesev (), MoveFirst (), and MoveLast ().
---- 2 . Increasing record
---- Add record using the addnew () function, requires the database to be opened in a way that is allowed:
m_pset-> addnew (); // Add a new record at the end of the table
m_pset-> setfieldnull (& (M_Pset-> M_Type), False);
m_pset-> m_type = "electric motor";
... // Enter a new field value
m_pset-> Update (); // Deposit a new record into the database
m_pset-> Requory (); // Reconstruction record set
---- 3. Delete Record
---- Use the delete () function directly, and do not call the Update () function after calling the delete () function:
m_pset-> delete ();
IF (! m_pset-> ISEOF ())
M_pset-> moandenext (); ELSE
m_pset-> movelast ();
---- 4. Modify record
---- Modify Record Using Edit () functions:
m_pset-> edit (); // Modify the current record
m_pset-> m_type = "Generator";
/ / Modify the current record field value
...
m_pset-> update (); // Deposit the modification into the database
m_pset-> Requory ();
---- 5. Revoke operation
---- If the user chooses to give up or modify the record, you want to discard the current operation, you can call before calling the Update () function:
CRecordset :: Move (AFX_MOVE_REFRESH);
---- To undo increase or modify the mode and restore the current record before adding or modifying the mode. The value of the parameter AFX_MOVE_REFRESH is zero.
---- 6. Database connection
---- Define a member variable m_pdatabase in the CRecordset class:
CDATABASE * m_PDATABASE;
---- It is a pointer to the object database class. If you pass a open CDatabase class object norm to M_PDatabase, you can share the same CDatabase class object before the CRecordset class object calls the Open () function. Such as:
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 multiplex m_db object
m_set2.m_pdatabse = & m_db;
// m_set2 multiplex m_db object
---- 7. Direct execution of the SQL statement
---- Although we can do most query operations through the CRecordset class, and you can also provide SQL statements in the CRecordset :: Open () function, but sometimes we want to do some other operations, such as establishing a new table. , Delete the table, create a new field, etc., then you need to use the mechanism of direct SQL statements to the CDATABASE class. Direct execution of the SQL statement by calling the cDatabase :: executesql () function:
Bool CDB :: EXECUTESQLANDREPORTFAILURURE (Const Cstring & Strsql)
{
Try
{
m_pdb-> executesql (strsql); // Execute SQL statement directly
}
Catch (CDBEXCEPTION, E)
{
CString strmsg;
STRMSG.LOADSTRING (IDS_EXECUTE_SQL_FAILD);
STRMSG = strsql;
Return False;
}
END_CATCH
Return True;
}
---- It should be pointed out that since the data operation statement provided by DBMS is not the same, the SQL statement directly executes the SQL statement may destroy the software's DBMS independence, so such operations should be used with caution in the application.
---- 8 . Dynamic connection table
---- The dynamic connection of the table can be implemented using the SQL statement when calling the CRecordset :: Open () function. The same record set object can only access tables with the same structure, otherwise the query result will not correspond to the variable.
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"); // Connection table Slot1
m_id = 0;
Break;
}
}
---- 9 . Dynamic connection database
---- Since the connection to the database is implemented by the CDatabase class object, we can dynamically connect the database through the CDATABASE of the CRecordset class object parameter m_pdatabase to connect to the CDatabase object pointer to 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 the data source Super_ES
{
AfxMessageBox ("Data Source Super_ES Opened Failed,"
"Please 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 ("Data Source Motor Opening Failed,"
"Please check the corresponding ODBC connection", MB_OK | MB_ICONWARNING);
exit (0);
}
m_id = 0;
Break;
}
}
---- Four. to sum up
---- The ODBC class library in Visual C can help programmers complete the vast majority of database operations. Using ODBC technology, the programmer explained from the specific DBMS, thereby greatly reduced the workload of software development, shortening the development cycle, and improving efficiency and software reliability. Some of this article summarize some experience in software development hopes to help workers engaged in ODBC development.
