One of the examples of vc6 ODBC

Programming in vc6.0 database-MFC ODBC

In VC, there are two ways to connect to a database by using ODBC. The first method is to use odbc api, and the second is to use mfc odbc. Here I will only talk about MFC

ODBC.
To make database development more convenient, Microsoft encapsulates ODBC APIs so that we can directly use the mfc odbc class during database development:
Cdatabase: establish a connection with the data source
Crecordset: Get Record set
Crecordview: provides a form view that is directly connected to a record set. The dialog box data exchange mechanism (DDX) is used to transmit data between the record set and the form view space.
Cfieldexchange: supports field data exchange (DFX), that is, data exchange between the members of the record set field data and the fields of the corresponding database table
Cdbexception: ODBC exception class
1. In actual development, follow these steps to access the database using the mfc odbc class:
A. Use the cdatabase class method to open the Data source:
Define the cdatabase object m_db; call its openex method to open the data source. The openex prototype is as follows:
Virtual bool openex (maid, DWORD dwoptions = 0 );
Parameter 1 is the connection string, for example, "DNS = memo; uid = sa; Pwd = 123", and DNS is the data source name. If lpszconnectstring is passed as null

The data source dialog box appears, prompting you to select a data source.
Parameter 2 is the open mode. The default value 0 indicates that the database is opened in the Shared Mode with write access. ODBC cursor library DLL is not loaded, and only when there is not enough information

The ODBC connection dialog box is displayed. The optional parameters are as follows:
Cdatabase: openexclusive: This library version is not supported. Always open for a shared data source. If this option is selected, the assertion fails.
Cdatabase: openreadonly: open in read-only mode
Cdatabase: usecursorlib load ODBC cursor library DLL. The cursor masks the basic ODBC driver.ProgramTo effectively prevent the use of dynamic sets (if

Drivers support them ). If a cursor library is loaded, the only supported cursor is a static snapshot and only the forward cursor is supported. The default value is true. If you plan to directly create from crecordset

A record set object without Derivation should not be loaded into the cursor library.
Cdatabase: noodbcdialog: whether or not sufficient connection information is provided, the ODBC connection dialog box is not displayed.
Cdatabase: forceodbcdialog: the ODBC connection dialog box is always displayed.
If you want to use a trusted connection, that is, the user name and password are not required, you should use the open method, but there are restrictions when opening the record set. See the following.
(Note: how to create a data source: Control Panel-> Administrative Tools-> data source (ODBC)-> Add, select the corresponding driver, such as the database under SQL Server, select

SQL Server-> complete the input data name, description, Select Server "(local)", next, select the SQL Server verification that the user enters the login ID and password, enter the login ID and

Password, next, select change default database, next, complete, test .)
B. Define the crecordset object and call its open method to open the record set, as shown in
Crecordset RS (& m_db );
Virtual bool open (uint nopentype = afx_db_use_default_type, lpctstr lpszsql = NULL, DWORD dwoptions = none

);
Parameter 1 is the open type, as shown below:
Afx_db_use_default_type: Default Value
Crecordset: dynaset: a dynamic record set. It supports two-way cursors and maintains synchronization with the connected data source. You can perform a fetch operation to update data.

.
Crecordset: snapshot: a static snapshot. Once a record set is formed, all changes to the data source cannot be reflected in the record set. The application must perform the operation again.

Query to obtain updates to the data. This type of record set also supports bidirectional cursors.
Crecordset: dynamic: Compared with the crecordset: dynaset record set, the crecordset: dynamic record can also synchronize data of other users in the fetch operation.

Most ODBC drivers do not support this record set.
Crecordset: forwardonly: Except reverse cursors, other features are the same as crecordset: snapshot.
Parameter 2 is an SQL query statement, and the query result is saved in the record set.
Parameter 3: common options for creating a record set
Crecordset: None: no option (default). It is mutually exclusive with all other options. records can be updated, deleted, and added.
Crecordset: appendonly: records cannot be modified or deleted, but records can be added.
Crecordset: readonly: the record set is read-only.
View msdn for other options
When using a trusted connection, if you use crecordset: dynaset, ODBC does not support dynamic record set errors, and crecordset: Dynamic opens the record set

ODBC does not support dynamic pointer errors, while crecordset: forwardonly causes invalid cursor position errors.
C. Bind a record set (through rfx)
When you use the Wizard to insert a cmyset class and the crecordset is used as the base class, the variables corresponding to the table fields are automatically generated and automatically match the table in dofieldexchange of cmyset.

Field binding, such as rfx_long (pfx, _ T ("[column name]"), variable name); pfx is the cfieldexchange class pointer. (Note the field type)
After binding, you can use these variables to update the corresponding field. For example, if you add a field, call addnew first, and then call setfieldnull ($ variable name, false) to mark the field.

It is a dirty data (changed), and the variable is assigned a value. Call update to save the new record to the database, requery to refresh the record set, and return the record set pointer to the first record.

Insert. Update is required after addnew and edit. You can modify the current record after calling edit.
D. parameterized record set and query
Crecordset member variable: m_strfilter is used to filter the record set. It stores the condition string of the WHERE clause in the SQL statement.
M_strfilter = "column name = 'value'". After the value is assigned, the record set that calls this object to open or Refresh has been filtered. In parameter 2, only the table name Rs. Open is given.

(Crecordset: snapshot, "book "). M_strsort sorts the record set, m_strsort = "column name". If the where and

order by clause, m_strfilter and m_strsort must be empty.
in addition to direct value assignment, you can also use parameterization:
(1) Declare the parameter variable. The Code is as follows:
cstring strname;
int Nage;
(2) initialize the following parameters in the constructor:
strname = _ T ("");
Nage = 0;
m_nparams = 2;
(3) bind the variable to the corresponding column. The Code is as follows:
pfx-> setfieldtype (cfieldexchange:: Param)
rfx_text (pfx, _ T ("name"), strname);
rfx_single (pfx, _ T ("Age"), Nage );
after completing the preceding steps, you can use the variable to perform conditional queries. The Code is as follows:

M_pmyrs-> m_strfilter = "name =? And age =? ";
M _ pmyrs-> strname = "Feng ";
M _ pmyrs-> Nage = 20;
M _ pmyrs-> open (); // if the record set has been opened, refresh
The value of the Parameter Variable replaces "?" In the query string in the order of binding. Wildcard.
F. bookmarks positioning: You can set bookmarks in the current record. After a series of moves, you can find the records in the bookmarks. You can directly return the bookmarks.
Cdbvarint bookmark;
Rs. getbookmark (bookmark); // obtain the current record and save it to the bookmarks.
Rs. setbookmark (bookmark); // returns the bookmark position.
Before using bookmarks, you can use crecordset: canbookmark to determine whether Bookmarks can be located. If yes, you also need to add it to dwoptions of the open function of the record set.
Crecordset: usebookmark. Call cdatabase: getbookmarkpersistence before calling to check whether setbookmark can be safely called.
Absolute positioning: ROW = 10;
Rs. setabsouteposition (ROW );
Positioning is not supported for record sets that only scroll forward.
Note: If you do not need to obtain records from the database, you do not need to open the record set. After the data source is opened, you can directly call cdatabase: executesql to execute SQL statements, such as inserting

Import, delete, update, and so on. Enable the record set only when you need to obtain records from the database. Use while (! Rs. iseof () determines whether the last record of the record set is exceeded. If

No record set bound. You can use getfieldvalue to retrieve the field value of the current record and move it to the next record with movenext. After a record set is bound, the variable value is

Field value. You can directly operate on variables without using getfieldvalue. I will discuss the conversion between data types in the database and C ++.

.
2. Transaction Processing
call cdatabase: begintrans () to start the transaction, commit the transaction, roll back, and cancel the transaction.
3. Exception Handling: during database programming, the following statements can be used to capture exceptions
try
{......
}< br> catch (cdbexception, ex)
{< br> afxmessagebox (ex-> m_strerror);
afxmessagebox (ex-> m_strstatenativeorigin );
}< br> and_catch (cmemoryexception, PEX)
{< br> pex-> reporterror ();
afxmessagebox ("Memory exception ");
}< br> and_catch (c1_tio N, e)
{< br> tchar szerror [100];
E-> geterrormessage (szerror, 100);
afxmessagebox (szerror );
}< br> end_catch
due to my limited level, I hope you can correct me if you have any questions. Thank you!
one more thing: after creating a project, you need to add the afxdb. h header file, which should be defined as the ODBC class in this file... S