Basic methods for accessing direct odbc api database calls in AppBuilder

Chapter 1: how to configure DSN for your development system:

(1) Open the control panel of Windows. Select the ODBC Data Sources icon and double-click it to open the content. The ODBC Data Source Administrator dialog box is displayed.

(2) Select User DSN in the dialog box, and then you can select the <add> button to add a DSN.

Chapter 2: How to Use ODBC APIs to directly write database-based programs under VC:

ODBC database programming
1. General steps:

Allocate Environment

Before calling any ODBC function, the application system must initialize ODBC and establish an environment.
ODBC uses this environment to monitor the database connections established by the application system. Only one
The environment is necessary, because no matter how many connections there are, they can be established in one environment. Complete this point
The ODBC function SQLAllocEnv in the configuration process is described in the next section.

SQLAllocEnv
SQLAllocEnv allocates memory for the Environment handle and initializes the ODBC call layer interface used by the application system.
The application system must call SQLAllocEnv before calling any other ODBC function.
The following is the syntax of SQLAllocEnv:
RETCODE SQLAllocEnv (phenv)
The parameters of SQLAllocEnv are shown in the following table. The return code is SQL-ERROR. Because SQLError is called
No valid handle, so this function does not have the SQLSTATE return code;
// Allocate the environment handle
SQLRETURN m_retcode;
If (m_henv! = SQL _NULL_HENV)
Return FALSE;
If (SQL _SUCCESS = (m_retcode = SQLAllocEnv (& m_henv )))
{
// Create a new DSN
CreateDSN (IDS_HOME_DSNNAME, IDS_HOME_DBFILENAME );
// Allocate a connection handle
If (SQL _SUCCESS = (m_retcode = SQLAllocConnect (m_henv, & m_hdbc )))
{
// Connect to the data source
If (SQL _SUCCESS = (m_retcode = SQLConnect (m_hdbc, (UCHAR *) (LPCTSTR) m_strDSN), SQL _cnt, NULL, 0, NULL, 0 )))
{
M_bConnected = TRUE;
}
}
}
Allocate connection handle

Just as the application system environment is represented by the Environment handle, the connection handle represents the application system and data source.
. For each data source to be connected by the application system, a connection must be allocated.
Handle. For example, if you want to connect to the data source of dBase and BTrieve at the same time, you must allocate two connections.
Handle. The next section describes the SQLAllocConnect function.

SQLAllocConnect allocates memory for the connection handle in the environment identified by henv. Below is
SQLAllocConnect Syntax:
RETCODE SQLAllocConnect (henv, phdbc)
The SQLAllocConnect parameters are shown in the following table. The return code is:
SQL-SUCCESS
SQL-SUCCESS-WITH-INFO
SQL-ERROR
SQL-INVALID-HANDLE
The return code of SQLSTATE is:
01000 S1000 maid
Connect to the data source using core functions

Although many ODBC functions can establish connections, there is only one type in the core API layer, that is, the function
SQLConnect. It provides a simple and effective way to connect to the data source. All drivers are supported
SQLConnect, so it is the most interoperable solution. The following describes SQLConnect.

SQLConnect loads a database driver and establishes a connection with the data source. This connection
The handle determines the storage location of all connection information (including its status, transaction status, and error information.

SQLConnect Syntax:
RETCODE SQLConnect (hdbc, szDSN, cbDSN, szUID, cbUID, szAuthStr, cbauthrecognition)

The return code of SQLConnectde is:
SQL-SUCCESS
SQL-SUCCESS-WITH-INFO
SQL-ERROR
SQL-INVALID-NUMBER


Disconnected from Data Source

Once the application system completes a data source connection, it should be disconnected. Connection is very expensive
Because many DBMS are charged for every licensed person or user connected at the same time. When
When the connection is complete, it should be returned so that other users can register to enter the system. ODBC described below
Function SQLDisconnect processes this operation.

SQLDisconnect closes the data source connection related to the specified connection handle. SQLDisconnect
Syntax:
RETCODESQLDisconnect (hdbc)
The return code of SQLDisconnect is:
SQL-SUCCESS
SQL-SUCCESS-WITH-INFO
SQL-ERROR
SQL-INVALID-HANDLE

If the data source is successfully connected, continue to the next step;
CString strSQLString;
RETCODE ReturnCode;
Sqlhsf-thstmt;
If (SQL _SUCCESS! = SQLAllocHandle (SQL _HANDLE_STMT, theApp. m_hdbc, & hstmt ))
Return;
StrSQLString. Format (
"SELECT"
"NID ,"
"ARIQI ,"
"NJINE ,"
"ASHUOMING ,"
"ALAIYUAN ,"
"ACUNZHE ,"
"AYONGTU ,"
"AXIAOFEI ,"
"ABEIZHU"
"FROM HHZhiChu"
"Where nid = % u ",
NID );
If (SQL _SUCCESS = (ReturnCode = SQLExecDirect (hstmt, (UCHAR *) (LPCTSTR) strSQLString), SQL _ets )))
{
SQLBindCol (hstmt, 1, SQL _C_SLONG, & dbdata. m_Nid, 0, & cb );
SQLBindCol (hstmt, 2, SQL _C_TIMESTAMP, & dbdata. m_Ariqi, 0, & cb );
SQLBindCol (hstmt, 3, SQL _C_DOUBLE, & dbdata. m_Njine, 0, & cb );
SQLBindCol (hstmt, 4, SQL _C_CHAR, dbdata. m_Ashuoming, HHZhiChu_aShuoMing_SIZE, & cb );
SQLBindCol (hstmt, 5, SQL _C_CHAR, dbdata. m_Alaiyuan, HHZhiChu_aLaiYuan_SIZE, & cb );
SQLBindCol (hstmt, 6, SQL _C_CHAR, dbdata. m_Acunzhe, HHZhiChu_aCunZhe_SIZE, & cb );
SQLBindCol (hstmt, 7, SQL _C_CHAR, dbdata. m_Ayongtu, HHZhiChu_aYongTu_SIZE, & cb );
SQLBindCol (hstmt, 8, SQL _C_CHAR, dbdata. m_Axiaofei, HHZhiChu_aXiaoFei_SIZE, & cb );
SQLBindCol (hstmt, 9, SQL _C_CHAR, dbdata. m_Abeizhu, HHZhiChu_aBeiZhu_SIZE, & cb );
If (SQL _SUCCESS = (ReturnCode = SQLFetch (hstmt )))
{
// The data is successfully read. You can process one pair of data.
}
}
: SQLFreeHandle (SQL _HANDLE_STMT, hstmt );


Close the database after the program is completed,
// Disconnect
If (m_bConnected)
{
SQLDisconnect (m_hdbc );
SQLFreeHandle (SQL _HANDLE_DBC, m_hdbc );
M_hdbc = NULL;
}
// Delete the DSN
If (FoundDSNName (IDS_HOME_DSNNAME ))
DeleteDSN (IDS_HOME_DSNNAME );
// Delete the environment handle
If (m_henv)
{
SQLFreeHandle (SQL _HANDLE_ENV, m_henv );
M_henv = NULL;
}

2. Database Operations
The following methods are only valid for ACCESS databases,
1. Generate a database
CString strFileName = "c: 1.mdb ";
CString strDriver;
Char szFileName [100 + _ MAX_PATH];
StrDriver = "Microsoft Access Driver (*. mdb )";
Sprintf (szFileName, "CREATE_DB = % s General", strFileName );
SQLConfigDataSource (NULL, ODBC_ADD_DSN, strDriver, szFileName );
2. compress the database
BOOL SuperDatabase: Compaction (CString strSourName, CString strDestName)
{
If (strSourName. IsEmpty ())
Return FALSE;
If (strDestName. IsEmpty ())
StrDestName = strSourName;
Char szCommand [100 + _ MAX_PATH];
Int j;
CString strDriver;
StrDriver = "Microsoft Access Driver (*. mdb )";
J = sprintf (szCommand, "COMPACT_DB = % s General", strSourName, strDestName );
Return SQLConfigDataSource (NULL, ODBC_ADD_DSN, strDriver, szCommand );
}
3. Obtain the Database Name:
If a data source has been opened, you can use the data source to obtain the name of the current database;
CString SuperDatabase: GetDatabaseName ()
{
ASSERT (m_hdbc! = SQL _NULL_HDBC );
Char szName [MAX_TNAME_LEN];
SWORD nResult;
SQLGetInfo (m_hdbc, SQL _DATABASE_NAME,
SzName, MAX_TNAME_LEN, & nResult );
Return szName;
}

3. Operations on data sources:
1. Add a data source
BOOL SuperDatabase: CreateDSN (CString strDriver, CString strFileName, CString strDSN, CString strUserID, CString strPWD)
{
Char szAttr [100 + _ MAX_PATH];
Int j;
If (strDriver. IsEmpty ())
StrDriver = "Microsoft Access Driver (*. mdb )";
J = sprintf (szAttr, "DSN = % s", strDSN );
J ++;
J = sprintf (szAttr + j, "DBQ = % s", strFileName );
Return SQLConfigDataSource (NULL, ODBC_ADD_DSN, strDriver, szAttr );
}
2. delete a data source
Void SuperDatabase: RemoveDSN (CString strDSN)
{
Char szDSN [255];
Sprintf (szDSN, "DSN = % s", strDSN );
BOOL bIsSuccess = SQLConfigDataSource (NULL, ODBC_REMOVE_DSN, "Microsoft Access Driver (*. mdb)", szDSN );
}
3. Obtain an existing DSN from the system.
SWORD nsf-cenamelength; // DSN str length
SWORD nSourceDescriptionLength; // Driver Description str length
Char szSourceDescription [MAXBUFLEN + 1]; // Driver Description string
SQLRETURN nResult; // Return Code
SWORD nDirection = SQL _FETCH_FIRST;
If ((