Connect to the MySQL database using ODBC in VC ++ 6.0

Is the final effect of the program, including data display, add, modify, and delete common functions of these database operations.

My debugging environment is XP and MySQL version is mysql4.0.23

1. Install the ODBC driver of MySQL

Download driver from http://www.mysql.com

Address: http://dev.mysql.com/downloads/connector/odbc/3.51.html#win32

I downloaded windows zip/setup. EXE.

Download the setup.exe file from the zip package and double-click it to install it. There is nothing to say. It is a silly installation.

2. Set ODBC Data sources

There are two ways to configure the ODBC data source for MySQL:

First: manually set

It is troublesome, but it is indeed a safer method.

The procedure is as follows:

Start> Control Panel> Administrative Tools> data source (ODBC). After you double-click the data source (ODBC), the following interface is displayed:

Click "add" in the upper-right corner to display the following interface:

We can see that the driver we just installed has already appeared in the list, select it, and click Finish. The following interface is displayed.

Enter the information required for each connection as shown in the following figure. Click "test" to test the connection. If the sucessful is displayed, the connection is successful.

I will explain the entered information: Data Source Name: the abbreviation is DSN. The Chinese translation is the data source name, which is to give a name to the data source. For the sake of security, take the English name, saving the trouble.

Description, it cannot be saved. I fill in localhost. If you have a remote host, try to fill in the IP address of the remote host. I have not tried it. It is unclear that user: User Name, my local database uses root. If you have other options, enter password: password. If you have not set the password, enter the password. If not, leave the database blank, this is a ListBox. You can enter it by yourself or select it from the drop-down list. If the server, user, and password in front of you are correct, the optional database will be displayed in the drop-down list, this is the database resource we want to connect.

The explanation is complete. After clicking OK, we can complete the settings.

The second method is to dynamically set the data source only when the program is executed. sqlconfigdatasource is the method used, check msdn to check its usage. The first parameter is generally set to null, and the second parameter is odbc_add_dsn, indicating that the third parameter is the driver name for the new data source, when copied from the data source (ODBC), the fourth parameter is the connection string. Multiple parameters are separated by commas (,). DSN indicates the data source name, uid indicates the user name, And PWD indicates the password, server is the host name, database is the database name, and ends with two.

You only need to add this line in the program. When the program runs on it, it will add the data source you set in the data source, you can also find it in Control Panel> Administrative Tools> data source (ODBC. As shown in figure 2, The odbctest and odbctestqqqq are dynamically created, and the latter is manually created.

Sqlconfigdatasource (null, odbc_add_dsn, "MySQL ODBC 3.51 driver", "DSN = odbctest uid = root Pwd = Server = localhost database = ODBC ");

3. I used vc6.0 to write the Connection Program. VC is good, that is, it is encapsulated too much, for example, the first person to come here will be a dog biting a tortoise at half past one. Below I will say in my way that there must be a better way for experts. I may comment on this article, or let me open my eyes.

(1 ). Create a project based on the dialog box. In VC, click File> new. In the projects drop-down list, select MFC Appwizard (exe ); enter the name of a project in the input box in the upper-right corner, named "odbctest". Select a storage directory where E: codbctest exists. Click OK to go to the next step and select "based on" dialog box, click Finish to complete the settings.

(2 ). Switch to file view, and find stdafx In header files. h. This is the first header file to be included in MFC. We Add the following two lines to introduce the header files required for ODBC and database operations to the project. Errors may occur when these two header files are not compiled.

......

# Include <odbcinst. h> # include "afxdb. H" // Replace the quotation marks with Angle brackets. The Editor automatically converts the quotation marks to non-source code.

// {Afx_insert_location }}......

(3 ). Draw subject dialog box

The body dialog box is used to display data in the list and place interfaces for other operation portals. In this program, a list control and five button controls are placed in the body dialog box.

Select Resources in the dialog box in the resource view, and then draw the dialog box as shown in Figure

Describe the controls on the graph

1 ). List, which uses list control. Note that the view in styles should be set to report, as shown in

2 ). Three buttons are added in sequence: add, modify, and delete.

I will explain it here. I usually read online tutorials or books. The process of writing the above programs is based on steps and there is no additional process, because the authors have already re-designed the layout and try to be concise. But the actual programming is indeed different. It often needs to be modified, rearranged, and optimized many times. So here I plan to explain it based on the actual programming process, rather than the step-by-step description, strive to restore the entire process of programming. As a beginner of VC, I believe that many new users like me will encounter the same difficulties. It doesn't matter. Everything is difficult at the beginning.

As mentioned in the general tutorial, we may prepare the resources to be used later, and then program the system. I don't want to do this here, but return to the programming of the main window to implement a function.

1 ). When the data list is displayed dizzy, many people will be dizzy. This is not a nonsense. There is nothing in our database (Database Name: ODBC). Why.

It doesn't matter. Fortunately, I have been using MySQL for several years. I don't know much about other MySQL management tools, such as EMS, phpMyAdmin, dbtools ......

I used phpMyAdmin for simple purposes. This tool must be installed locally with PHP and MySQL for use. If PHP is not installed locally, it won't work, but it doesn't matter, downloading an EMS is also good. It has powerful tools, gorgeous interfaces, and rich functions. If you have used SQL Server, you may be more accustomed to using dbtools manager. This is also a powerful tool and is worth a try.

Ermao said that there will be a bunch of tools on the machine before learning VC. I did not believe it at first. Now I believe it. No, just a few days later, I ran VC, msdn, and a bunch of entry-level e-books, readers, and video playback software ...... Since there are already so many, it doesn't matter if there are more than a few.

I ran the question. I first braked it back, and then talked about the data display. Step 1: Create a table list in the ODBC database, all of which blame me. This database name is a little misleading to the audience, here again, the ODBC here is a MySQL database I have built, not the damn abbreviation. The following table is created.

ID: An auto-increment, non-negative 10-digit integer field used to store the user ID

Name: a 40-bit variable-length string used to store user names

Age: it is a three-digit small integer field. Because the non-negative setting is not enabled, the maximum value is 127. This field stores the user's age.

This is the structure of the List table. Then we insert several initial data records into the table to display the data.

I will not talk about phpMyAdmin. This is a tool that is not included in the scope of this article.

After adding the data, we can browse them in phpMyAdmin, such:

2 ). Show data list by programming

Return to VC. In the Class View, locate codbctestdlg and expand it. Find the oninitdialog () method in it. This method is the initialization method of the dialog box, my initial idea was to complete database connection, query, and output data to the list. So I wrote the following code:

  CDatabase db;
db.Open(NULL,FALSE,FALSE,"ODBC;DSN=odbctest;UID=root;PWD=");
CRecordset rs( &db );
rs.Open( CRecordset::forwardOnly, _T("SELECT * FROM list order by id Asc"));
  short nFields = rs.GetODBCFieldCount();
while(!rs.IsEOF())
{
  CString varID;
  rs.GetFieldValue("id", varID);
  m_list.InsertItem(0,varID);
  CString varName;
  rs.GetFieldValue("name", varName);
  m_list.SetItemText(0, 1, varName);
  CString varAge;
  rs.GetFieldValue("age", varAge);
  m_list.SetItemText(0, 2, varAge);
  rs.MoveNext();
}
rs.Close();
db.Close();

But later I found that there was a problem, because after each record is added, the list needs to be updated and output again, so I need to write the same code as above, isn't that a waste of time. So I put the above code into a method of the class. The procedure is as follows:

1 ). In the Class View, select codbctestdlg, click Create on the right, select Add function, and create a void getrecord () method, as shown in

2 ). Put the above Code into the method. The final code is as follows:

Void codbctestdlg: getrecord ()
{
M_list.deleteallitems ();
Cdatabase dB;
DB. Open (null, false, false, "ODBC; DSN = odbctest; uid = root; Pwd = ");
Crecordset RS (& dB );
Rs. Open (crecordset: forwardonly, _ T ("select * From list order by id asc "));
// Short nfields = Rs. getodbcfieldcount (); // This row was originally used to traverse fields in the table and is not used now
While (! Rs. iseof ())
{
Cstring varid;
Rs. getfieldvalue ("ID", varid );
M_list.insertitem (0, varid );
Cstring varname;
Rs. getfieldvalue ("name", varname );
M_list.setitemtext (0, 1, varname );
Cstring varage;
Rs. getfieldvalue ("Age", varage );
M_list.setitemtext (0, 2, varage );
Rs. movenext ();
}
Rs. Close ();
DB. Close ();
M_list.adjustcolumnwidth (); // added a cmylistctrl class, which is a newly added method for adaptive data width.
}

In this way, you can call this method when you need to retrieve the list data again. This is called reuse.

 

ODBC connects to the database. There are two classes that need to be concerned. The first is cdatabase, and the other is crecordset.

The former is used to establish database connections, and the latter is used to acquire data sets.

Establish a connection using the following code

Cdatabase dB; // declare an object

DB. Open (null, false, false, "ODBC; DSN = odbctest; uid = root; Pwd ="); // connect to the data source

Check msdn for the reason.

Data collection

Crecordset RS (& dB); // bind a data source

Rs. Open (crecordset: forwardonly, _ T ("select * From list order by id asc"); // query data

To execute SQL statements, I do not use the methods provided by ODBC, but use a more direct method.

Cstring SQL;

SQL. Format ("update list set name = '% s', age =' % s' where id = % d", str_name, str_age, item_id );

DB. executesql (SQL );

The advantage of this method is that you do not need to inherit new classes from crecordset, and it is more intuitive for me to learn about some SQL statements.