Access the SQL Server database using ODBC in VC ++

Editor: Qi Rui click to enter the Forum. Keywords: vc odbc Database ODBC (Open Database Connectivity) is a Database access standard defined by Microsoft, it provides a standard database access method to access databases of different database providers. It is essentially a set of database access APIs. although there are many ways to access databases, ODBC is widely used in actual programming because of its relatively simple programming.

VC ++ provides a set of mfc odbc classes that encapsulate ODBC APIs to reduce the amount of program code writing. When you use the mfc odbc class in VC ++ to access the database, you usually first configure or select an existing data source, and then construct a CDatabase class object to open the data source, the Database Class Object and SQL (Structured Query Language) can be used to access the database, and then the CRecordset class or its inherited class object can be constructed, you can use dataset objects and SQL statements to operate tables in the database. In VC ++, using the mfc odbc class to operate SQL Server databases is basically the same idea.

1. Configure the data source

In a program, dynamic configuration of the data source based on the user's choice without calling the ODBC data source Manager is sometimes necessary for application development. After all, the ODBC data source Manager is complex for users who are not familiar with the database, and the interface style of the ODBC data source Manager may be different from that of the entire application, for rigorous application developers, these are intolerable.

When configuring the SQL Server data source, you must have the SQL Server name and the target database name on the Server (otherwise, the configuration data source dialog box or failure will pop up when you open the data source ). This is different from specifying the path of the database file When configuring Access and other database data sources.

You can use the odbc api function SQLBrowseConnect to obtain the database and language information of all local SQL Server servers and servers. The usage is as follows:

① Obtain the server name

SQLBrowseConnect (hdbc, "DRIVER = {SQL Server};", SQL _ets, BrowseResult, sizeof (BrowseResult), & BrowseResultLen );

Hdbc is the link handle obtained by using the SQLAllocHandle function. The second parameter is the input string specifying the link attribute, the third parameter is the length of the input string, and the fourth parameter is the pointer of the output string, the information we get is stored in this string. The fifth parameter specifies the length of the output string, and the sixth parameter indicates the length of the actually returned string. The string returned by the function pointed to by BrowseResult contains the format "SERVER: Server = {Server_name1, Server_name2 ,...}" Server_name1 and Server_name2 are SQL Server names.

② Obtain the Database Name

After you select a server, you can use the SQLBrowseConnect function to obtain the database or language information on the server (if necessary ).

SQLBrowseConnect (hdbc, "SERVER = Server_name1; UID = sa; PWD = 515578;", SQL _cnt, BrowseResult, sizeof (BrowseResult), & BrowseResultLen );

UID and PWD are the usernames and passwords used to access the server. The string returned by the function directed to this BrowseResult contains a string such as DATABASE: Database = {master, model ,...} And LANGUAGE: Language = {Arabic, Brazilian, English ,...} The master and model are the database names on the server.

You can use this information to configure the SQL Server Data source:

SQLConfigDataSource (NULL, ODBC_ADD_DSN, "SQL Server", "DSN = myDSN/0 SERVER = xhm/0 DATABASE = pubs/0/0 "))

The author has the honor to find the CSQLInfoEnumerator class written by Santosh Rao on the Internet, which has three member functions: EnumerateSQLServers, EnumerateDatabase, and handler. It encapsulates the SQLBrowseConnect function and processes the corresponding characters, this greatly simplifies the use of SQLBrowseConnect functions.

2. Establish a connection with the data source

If you use odbc api functions for connection, both SQLConnect, SQLDriverConnect, and SQLBrowseConnect functions can be implemented. The SQLConnect function is used to directly establish a connection with the data source given all parameters. SQLDriverConnect is used to specify some connection parameters and pop up the data source browsing window to interact with users, after obtaining enough parameters, establish a connection with the data source. SQLBrowseConnect gets the connection parameters through iteration and then connects them. The usage is described above.

MFC provides a database-type CDatabase for connecting to the data source, through which we can easily establish a connection with the Data source:

// M_db is the CDatabase object

// M_szUserId and m_szPassword are CString objects. They are the user name and password used to access the data source.

CString str;

Str = _ T ("DSN = xhmtest; UID =") + m_szUserId + _ T ("; PWD =") + m_szPassword;

If (! M_db.OpenEx (str, CDatabase: noOdbcDialog )){

AfxMessageBox ("failed to open the data source ");

}

 

In addition, if you want to operate the data source, you can use the opened cdatabase object to execute SQL statements. Create a new table:

STR = "create table tabledemo (column1text, column2 number )";

M_db.executesql (ssql );

3. Obtain the structure information of the database.

In VC ++, you can use the odbc api alone to obtain the structure information of the database. However, the method is very complex and various parameters are not easy to understand, and the returned results are not easy to obtain. In MFC, no separate classes are provided for application developers to obtain database structure information. However, we can still obtain the structure information of the database through some existing methods.

3.1 obtain the table in the database

The odbc api function sqltables can be used to obtain table information in the database, but it is not easy to use.

There is a catalog routine in the msdn sample, which demonstrates that if you get the table information of a database and the field information in the table, there are two very useful classes in this program: ctables and ccolumns. ctables inherits the crecordset class, encapsulates the sqltables function in a clever way, and stores the result set in the crecordset class for easy retrieval.

In the program, we can apply the following:

// M_db is an opened cdatabse object,

// M_strtableowner is a ccombobox object

Ctables RS (& m_db );

Rs. Open (null, "table ");

Cstring strtableref;

M_combtable.resetcontent ();

While (! Rs. iseof ())

{

Strtableref = _ T ("[");

If (! Rs. m_strTableOwner.IsEmpty ())

StrTableRef + = rs. m_strTableOwner + _ T ("]. [");

StrTableRef + = rs. m_strTableName + _ T ("]");

M_combTable.AddString (strTableRef );

Rs. MoveNext ();

}

Rs. Close ();

 

In the CTables object Open method, specify the fourth number of energy. "TABLE" indicates the TABLE in the returned database. If this parameter is set to "system table", the system table is returned. If this parameter is set to "system table, returns all tables.

3.2 obtain the field structure in the table.

Three methods are available to structure fields in the table:

① Using the odbc api function SQLColumn directly, its implementation is also complicated.

② Use the CColumns class that encapsulates the SQLColumns function mentioned above. Its usage is similar to that of CTables, and its member variable m_strColumnName is the field name.

③ Use the member functions of the CRecordset class. MFC provides a method to obtain table structure information in the CRecordset class, which is very convenient to use. (But it is strange that the CDatabase class does not have a class method to obtain the database structure information ?)

Let's take CRecodrset as an example to briefly explain how to get the table structure. Assume that you want to get all the field names of the table selected by the user in the CComboBox object, and export it to the column name of a CListCtrl object (whose View attribute is set to Report:

// M_strTableOwner is the same as the preceding one. It is a CComboBox object.

// M_listData is the ClistCtrl object.

CRecordset rs

CODBCFieldInfo info;

CString strSQL;

M_combTable.GetLBText (m_combTable.GetCurSel (), strSQL );

StrSQL = _ T ("SELECT * FROM") + strSQL;

Rs. Open (CRecordset: snapshot, strSQL, CRecordset: readOnly );

Int nColumns = rs. GetODBCFieldCount ();

For (int nNum = 0; nNum <nColumns; nNum ++)

{

Prs-> GetODBCFieldInfo (nNum, info );

M_listData.InsertColumn (nNum, info. m_strName, LVCFMT_LEFT, 80)

}

 

In addition, we can use the CRecordSet: GetFieldValue function to obtain the value of the record indicated by the cursor in the record set by specifying the column name or column sequence number, using CRecordset: AddNew, CRecordset :: delete, CRecordset: Edit, CRecordset: Update, and other function operations.

From http://soft6.com/html/tech/13/131401.shtml