Using VC to obtain a heterogeneous database library structure information Air Force Telecommunications Engineering School Computer Room Wan Yixiao Xiao Summary: Based on the introduction ODBC technology, combine the MFC and ODBC API created two custom classes, and realize anything Acquisition of heterogeneous database library structure information. Keywords: ODBC, MFC, heterogeneous database, recordset one. Problem proposing with the rapid development of database technology in various application sectors, the market has launched a variety of database systems, in order to make full use of resources, realize information sharing, so that Users can implement transparent access to the heterogeneous database (including data query, update, and exchange features), the author develops a heterogeneous database communication platform. During the development of the platform, the structural information for obtaining various isomeric databases is the premise of data access. The author uses ODBC to implement this key technology with VC 5.0. II. ODBC technology introduction ODBC technology refers to an open database connection technology that enables applications to use DBMS from which DBMs from the data source is taken, and use its standard interface to implement data exchange between data sources. Traditional ODBC programming is to use advanced languages (such as C language) to call ODBC's API. The application requires that the driver manager and each driver are ODBC environments, each connection, and each SQL statement allocated information storage space, and returns to the handle of each storage area for their call. The ODBC interface defines three handle types: Environment handle: to identify memory storage for full information, including valid connection handles and current active connection handles. ODBC defines the environment handle as a HENV type variable. The application uses a single environment handle that must request the handle before connecting to the data source. Connection handle: Identify memory storage for a particular connection. ODBC defines the connecting handle as an HDBC type. The application must request the connection handle before connecting to the data source. Each connection handle is related to the environment handle. However, the environment handle can have multiple connecting handles associated therewith. Statement handle: Identify memory storage for SQL statement information. ODBC defines the statement handle as an HSTMT type variable. The application must request a statement handle before submitting the SQL request. Each statement handle is related to a connection handle. However, each connecting handle can have multiple statement handles related to it. The general process of traditional ODBC programming is described below with C language. 1. Environmental application, allocate environment handle Henv Henv; SQLallocenv (& HENV); Description: Assign an environment handle to support one or more data source connections. 2, connection application, allocate connection handle HDBC HDBC; SQLAllocConnect (HENV, & HDBC); Description: A connection handle corresponds to a data source, there can be multiple connection handles. 3, connect the data source, connect to the data source SQLDRIVERCONNECT (HDBC, ...) with a connection handle; Description: Get registration information in a dialog box and connect the data source. 4, statement application, assignment statement handle SQLAllocStmt (HDBC, & HSTMT); Description: Get a statement handle to perform SQL statements. 5. Execute SQL statements SQLEXECDirect (hstmt, sqlstatement, ..); Description: Use the statement handle to execute the SQL statement.
6, release all resources SQLFREESTEMT (HSTMST, ...); // Release the clause handle SQLDisconnect (HDBC); // Disconnect SQLFreeConnect (HDBC); // Release the current database connection handle SQLFreeEnv (HENV); // Release the environment Handle 3. Using VC and ODBC technology to obtain isomeric database structure information The traditional ODBC programming process is more complicated, and various parameters are not easy to understand, and the returned data is more difficult. The MFC class library of VC 5.0 encapsulates the ODBC's API, and some simplify ODBC programming (especially for database record sets), but simply uses the structural information of the MFC class to obtain the isomeric database. Therefore, it is necessary to put MFC Combine with the traditional ODBC API programming. The author uses the ODBC interface function to overload some member functions of the CRecordset class in the MFC, create a CTABLE and CCOLUMNS classes. Using these two newly created classes, it can easily obtain the isomeric database structure information.
Below is the definition of CTable and Ccolumns classes: class CTable: public CRecordset {virtual CString GetDefaultConnect () {return "";} virtual CString GetDefaultSQL () {return "";} public: CTable (CDatabase * pDatabase); BOOL Open ( LPCSTR pszTableQualifier = NULL, LPCSTR pszTableOwner = NULL, LPCSTR pszTableName = NULL, LPCSTR pszTableType = NULL, UINT nOpenType = forwardOnly); CString m_strTableQualifier; CString m_strTableOwner; CString m_strTableName; CString m_strTableType; CString m_strRemarks; virtual void DoFieldExchange (CFieldExchange *);} ; class CColumns: public CRecordset {virtual CString GetDefaultConnect () {return "";} virtual CString GetDefaultSQL () {return "";} public: CColumns (CDatabase * pDatabase); BOOL Open (LPCSTR pszTableQualifier = NULL, LPCSTR pszTableOwner = NULL, LPCSTR pszTableName = NULL, LPCSTR pszColumnName = NULL, UINT nOpenType = forwardOnly); CString m_strTableQualifier; CString m_strTableOwner; CString m_strTableName; CString m_strColumnName; int m_nDataType; CString m_strTypeName; long m_nPrecision; long m_nLength; int m_nScale; int m_nRadix ; Int m_fnullable; cstring m_strremarks;
virtual void DoFieldExchange (CFieldExchange *);}; BOOL CColumns :: Open (LPCSTR pszTableQualifier, LPCSTR pszTableOwner, LPCSTR pszTableName, LPCSTR pszColumnName, UINT nOpenType) {RETCODE nRetCode; UWORD bFunctionExists; // check whether the function supports SQLColumns AFX_SQL_SYNC (:: SQLGetFunctions (m_pdatabase-> m_hdbc, sql_api_sqlcolumns, & bfunctionexists); if (! check (nretcode) ||! bFunctionexists) {if (! bFunctionexists) Trace (_t ("SQLColumns does not support / n"); return false;} // set buffer status, distribution statement handle SetState (nOpenType, NULL, readOnly); if (! AllocHstmt ()) return FALSE; TRY {OnSetOptions (m_hstmt); AllocStatusArrays (); // call ODBC SQLColumns function AFX_ODBC_CALL (:: SQLColumns (m_hstmt, (UCHAR FAR *) pszTableQualifier, SQL_NTS, (UCHAR FAR *) pszTableOwner, SQL_NTS, (UCHAR FAR *) pszTableName, SQL_NTS, (UCHAR FAR *) pszColumnName, SQL_NTS)); if (Check (nRetCode!)) ThrowDBException ( NRETCODE, M_HSTMT; // Allocate memory, fill in information allocandcachefieldInfo (); allocrowset (); MoveNext (); m_bbof = m_beof;} // Abnormal information capture catch_all (E) {close (); throw_last ();} end_catch_all return TRUE;} // get record set information void CColumns :: DoFieldExchange (CFieldExchange * pFX) {pFX-> SetFieldType (CFieldExchange :: outputColumn); RFX_Text (pFX, _T ( "TABLE_QUALIFIER"), m_strTableQualifier); RFX_Text (pFX, _T ( "TABLE_OWNER"), m_strTableOwner); RFX_Text (pFX, _T ( "TABLE_NAME"), m_strTableName); RFX_Text (pFX, _T ( "COLUMN_NAME"), m_strColumnName); RFX_Int (pFX, _T ( "DATA_TYPE"), m_nDataType ); RFX_Text (PFX, _T ("type_name"
), m_strtypename); RFX_long ("precision"), m_nprecision; RFX_long (PFX, _T ("Length"), m_nlength); RFX_INT (PFX, _T ("scale"), m_nscale); RFX_INT (PFX) , _T ("radix"), m_nradix; RFX_INT ("nullable"), m_fnullable; RFX_Text (PFX, _T ("Remarks"), m_strremarks);} ccolumns :: ccolumns (cdatabase * pdatabase): CRecordset (pDatabase) {m_strTableQualifier = _T ( ""); m_strTableOwner = _T ( ""); m_strTableName = _T ( ""); m_strColumnName = _T ( ""); m_nDataType = 0; m_strTypeName = _T ( ""); m_nPrecision = 0; m_nLength = 0; m_nScale = 0; m_nRadix = 0; m_fNullable = 0; m_strRemarks = _T ( ""); m_nFields = 12;} CTable :: CTable (CDatabase * pDatabase): CRecordset (pDatabase) {m_strTableQualifier = _T (""); M_strtableownload = _t ("); m_strtablename = _t ("); m_strtableType = _t ("); m_strremarks = _t ("); m_nfields = 5;} B OOL CTable :: Open (LPCSTR pszTableQualifier, LPCSTR pszTableOwner, LPCSTR pszTableName, LPCSTR pszTableType, UINT nOpenType) {RETCODE nRetCode; UWORD bFunctionExists; // check whether to support the SQLTables function AFX_SQL_SYNC (:: SQLGetFunctions (m_pDatabase-> m_hdbc, SQL_API_SQLTABLES, & bFunctionExists) ); If (! Check (nretcode) ||! BFunctionexists) TRACE (_t ("SQLTables does not support / n"); Return False;} // Set buffer status, assign a statement handle setState NopENTYPE, NULL, READONLY;