ODBC programming ODBC (Open Database Connectivity, Open Database Connections) in Visual C is a standard application interface (API) for data for data in associated or unrelated database management systems (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. The ODBC database class provided by the 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, through 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. Applying ODBC programming ---- AppWizard Apply Visual C 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 programming techniques for ODBC in the Visual C environment: ---- 1. Query 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 the CRecordset :: open () function, for example: super_esset.open (AFX_DATABASE_USE_DEFAULT, STRSQL); ---- If there is no specified parameter, the program uses the default SQL statement, the SQL statement specified in the getDefaultsql () function: cstring csuper_esset :: getDefaultsql () {return_t ("[BasicData], [mainsize]");} ---- For getDefaultsql () functions The returned table name, the corresponding default operation is the SELECT statement, namely: select * from BasicData, the mainsize ---- The query can also take advantage of 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_estet.m_strfilter = "type = motor"; super_esset.m_strsort = "Voltage"; super_esset.reQuery (); corresponding SQL statement is: Select * from BasicData, MAINSIZE WHERE TYPE = Motor ORDER BY VOLTAGE ---- In addition to Direct assignment to m_strfilter, you can use parameterization. Using parameterization can be more intuitive and more convenient to complete the conditional query task. The steps to use parameterization are as follows: ---- (1). Declaration of parameters: cstring p1; float p2; ---- (2). Initializing the parameter amount P1 = _t ("); p2 = 0.0f; m_nparams = 2; ---- (3). Bind the parameter 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 parameter 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 replaces the "?" Adaptifier in the query string according to the order of the binding. ---- 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 . Add a record ---- Add record uses the addNew () function, require the database to open: m_pset-> addnew (); // Add new record m_pset-> setfieldnull at the end of the table Add a new record m_pset-> setfieldnull (m_pset- > m_type), false); m_pset-> m_type = "electric machine"; ... // Enter new field value m_pset-> update (); // store new records into database m_pset-> Requory (// Reconstruction record set ---- 3. Delete Record ---- Use the delete () function directly, and do not need to call the Update () function after calling the delete () function: m_pset-> delete (); if (! M_pset-> ISEOF ()) m_pset-> MoveNext (); elsem_pset-> movelast (); ---- 4. Modify the 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-> ReQuery (); ---- 5. Undo operation ---- If the user wants 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 mode.
The value of the parameter AFX_MOVE_REFRESH is zero. ---- 6. Database connection multiplexing ---- defines a member variable m_pdatabase: cdatabase * m_pdatabase: --- it is a pointer to the object database class in the CRecordset 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_pdatabase = & m_db; // m_set2 complex Use m_db objects ---- 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. For example, create a new table, delete the table, create a new field, etc., then the mechanism of direct SQL statements that use the CDATABASE class is required. To complete the SQL statement by calling CDatabase :: ExecuteSQL () function directly execute: BOOL CDB :: ExecuteSQLAndReportFailure (const CString & strSQL) {TRY {m_pdb-> ExecuteSQL (strSQL); // execute SQL statements directly} CATCH (CDBException, e ) {CString strmsg; strmsg.loadstring (ids_execute_sql_failed); strmsg = strsql; return false;} end_catch returse;} ---- should point out that due to the same data operation statement provided by different DBMS, directly execute SQL statement You may damage the software's DBMS independence, so this type should be used with caution in your 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 slam slot0"); // Connection Table slot0m_id = 1; Break; Case 1: m_pset-> Open (AFX_DB_USE_DEFAULT_TYPE, "Select * from slot1"); // Connect table slot1m_id = 0; Break;}} ---- 9. Dynamically Connect 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.
