Obtain the structure information of a heterogeneous database using VC ++

Obtain the structure information of a heterogeneous database using VC ++

Abstract: Based on the introduction of ODBC technology, This paper combines MFC and ODBC APIs to create two custom classes to obtain the structure information of any database with different configuration types.

Keywords: ODBC, MFC, heterogeneous database, record set

I. Question proposal

With the rapid development of database technology in various application fields, the market has launched a variety of database systems. In order to make full use of resources and achieve information sharing, in this way, users can access Heterogeneous Databases transparently (including data query, update, and exchange). The author developed a communication platform for heterogeneous databases. In the development of the platform, obtaining structure information of various heterogeneous databases is a prerequisite for data access. The author uses VC ++ 5.0 as the development language and uses ODBC to implement this key technology.

Ii. ODBC technology introduction

ODBC technology refers to open database connection technology, which allows applications to exchange data with data sources without worrying about the DBMS from which the data source comes. Traditional ODBC programming is implemented by calling ODBC APIs in advanced languages (such as C. The application requires the driver manager and each driver to allocate information storage space for the ODBC environment, each connection, and each SQL statement, and return a handle pointing to each storage area for calling.

The ODBC interface defines three types of handles:

Environment handle: identifies memory storage for the entire process, including valid connection handle and active connection handle. ODBC defines the environment handle as a henv variable. The application uses a single environment handle, which must be requested before connecting to the data source.

Connection handle: identifies memory storage for specific connection information. ODBC defines the connection handle as the hdbc type. The application must request a connection handle before connecting to the data source. Each connection handle is related to the Environment handle. However, the environment handle can have multiple connection handles related to it.

Statement handle: identifies memory storage for SQL statement information. ODBC defines the statement handle as a hstmt variable. The application must request the statement handle before submitting an SQL request. Each statement handle is related to a connection handle. However, each connection handle can have multiple statement handles related to it.

The following uses the C language as an example to illustrate the general process of traditional ODBC programming.

1. Environment application. Allocate the environment handle henv; sqlallocenv (& henv); Description: Allocate an environment handle and support one or more data source connections.

2. Connection Request, allocate the connection handle hdbc; sqlallocconnect (henv, & hdbc); Description: A connection handle corresponds to a data source and can have multiple connection handles.

3. Connect to the data source and connect to the data source sqldriverconnect (hdbc,...) using the connection handle. Description: obtain registration information in the dialog box and connect to the data source.

4. Statement application and statement handle allocation

Sqlallocstmt (hdbc, & hstmt); Description: Obtain the statement handle to execute the SQL statement.

5. Execute SQL statements

Sqlexecdirect (hstmt, sqlstatement,...); Description: Execute an SQL statement using the statement handle.

6. Release all resources

Sqlfreestemt (hsf-st,...); // release statement handle

Sqldisconnect (hdbc); // disconnect

Sqlfreeconnect (hdbc); // release the current database connection handle

Sqlfreeenv (henv); // release the environment handle

Iii. Obtain structure information of a heterogeneous database using VC ++ and ODBC

The traditional ODBC programming process is complicated, and various parameters are difficult to understand, and it is difficult to directly obtain the returned data. The VC ++ 5.0 MFC class library encapsulates ODBC APIs, which simplifies ODBC programming (especially database record set operations ), however, it is still difficult to obtain the structure information of a heterogeneous database simply using the MFC class. Therefore, it is necessary to combine the MFC and traditional odbc api programming. The author used ODBC interface functions to overload some member functions of the crecordset class in MFC and create the ctable and ccolumns classes. Using these two newly created classes, you can easily obtain the structure information of a heterogeneous database. The following is the definition of the ctable and ccolumns classes:

Class ctable: Public crecordset {

Virtual cstring getdefaconnect connect () {return "";}

Virtual cstring getdefasql SQL () {return "";}

Public:

Ctable (cdatabase * pdatabase );

Bool open (maid = NULL, maid );

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 getdefaconnect connect () {return "";}

Virtual cstring getdefasql SQL () {return "";}

Public:

Ccolumns (cdatabase * pdatabase );

Bool open (thinkstr psztablequalifier = NULL, thinkstr psztableowner = NULL, thinkstr psztablename = NULL, thinkstr 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 (maid, maid, maid ){

Retcode nretcode;

Uword bfunctionexists; // checks whether sqlcolumns functions are supported.

Afx_ SQL _sync (: sqlgetfunctions (m_pdatabase-> m_hdbc, SQL _api_sqlcolumns, & bfunctionexists); If (! Check (nretcode) |! Bfunctionexists ){

If (! Bfunctionexists)

Trace (_ T ("sqlcolumns not supported "));

Return false;

}

// Set the buffer status and allocate the statement handle

Setstate (nopentype, null, readonly );

If (! Allochstmt ())

Return false;

Try {

Onsetoptions (m_hstmt );

Allocstatusarrays ();

// Call the sqlcolumns function of ODBC

Partition (: sqlcolumns (m_hstmt, (uchar far *) partition, SQL _cnt, (uchar far *) psztableowner, SQL _cnt, (uchar far *) psztablename, SQL _nt, (uchar far *) pszcolumnname, SQL _cnt ));

If (! Check (nretcode ))

Throwdbexception (nretcode, m_hstmt );

// Allocate memory and enter information

Allocandcachefieldinfo ();

Allocrowset ();

Movenext ();

M_bbof = m_beof;

}/

/Capture exception information

Catch_all (e ){

Close ();

Throw_last ();

} End_catch_all return true ;}

// Obtain 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 (pfx, _ T ("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 (pfx, _ T ("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_strtableowner = _ T ("");

M_strtablename = _ T ("");

M_strtabletype = _ T ("");

M_strremarks = _ T ("");

M_nfields = 5;

}

Bool ctable: open (maid, maid ){

Retcode nretcode;

Uword bfunctionexists;

// Check whether the sqltables function is supported

Afx_ SQL _sync (: sqlgetfunctions (m_pdatabase-> m_hdbc, SQL _api_sqltables, & bfunctionexists ));

If (! Check (nretcode) |! Bfunctionexists ){

If (! Bfunctionexists) trace (_ T ("sqltables not supported "));

Return false;

}

// Set the buffer status and allocate the statement handle

Setstate (nopentype, null, readonly );

If (! Allochstmt () return false;

Try {

Onsetoptions (m_hstmt );

Allocstatusarrays ();

// Call the sqltables function of ODBC

Callback (: sqltables (m_hstmt, (uchar far *) tables, SQL _cnt, (uchar far *) psztableowner, SQL _nt, (uchar far *) psztablename, SQL _nt, (uchar far *) psztabletype, SQL _cnt ));

If (! Check (nretcode ))

Throwdbexception (nretcode, m_hstmt );

// Allocate memory and enter information

Allocandcachefieldinfo ();

Allocrowset ();

Movenext ();

M_bbof = m_beof;

}

// Capture exception information

Catch_all (e ){

Close ();

Throw_last ();

} End_catch_all return true;

}

Void ctable: 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 ("table_type"), m_strtabletype );

Rfx_text (pfx, _ T ("Remarks"), m_strremarks );

}

The preceding two classes overload the open and dofieldexchange functions of crecordset. The application can create a ctable or ccolumns class as needed, and call the open member function to create the corresponding table structure and field structure record set. Next, we can use the following functions to traverse the structure information of a heterogeneous database.

Void crecordset: movefirst (); // move to the first record

Void crecordset: movelast (); // move to the last record

Void crecordset: moveprev (); // move to the previous record

Void crecordset: movenext (); // move to the next record

Bool crecordset: isbof (); // determines whether it has reached the first record

Bool crecordset: iseof (); // determines whether the last record is reached

Iv. Conclusion

With the custom ctable and ccolumns classes, the application can obtain the structure information of any heterogeneous database. You can conveniently perform operations on unknown databases based on the obtained information. If you combine the ctable and ccolumns classes with document classes and visual classes, you can display the structure information in a certain way in the window. Using the above technology, the author successfully obtained the structure information of various heterogeneous database databases on the communication platform.