---- Open Database Interconnection (ODBC) has been accessed to a standard for remote data libraries in the Client / Server database application system. Do a strong front-end development tool, MS Visual Basic provides a variety of routes of access to ODBC data sources, such as Jet Database Engine, ODBC API functions, RDO interfaces, etc. Comparison, it is directly difficult to use the ODBC API function, but the performance of the data library obtained is also best. Sincere, the performance of the RDO interface for VB 4.0 Enterprises has been received near the ODBC API, but it is unfortunate that this interface can only be run in the 32-bit Windows environment, while the ODBC API function does not have this limit. ---- ODBC API Function Sound Method ---- With the use of its dynamic library function, before VB is used to use the ODBC API function, the function, constant, and data integration of the use will be made. The ODBC API function resides in the ODBC running dynamic library ODBC.DLL (16-bit) or ODBC32.DLL (32 bits), which is located in the Windows subdirectory system. The usual practice is to use a module file separately in the VB project, then add the ODBC API declaration statement, as shown below, the module file module1.bas used in this instance: Declare Function SQLALLOCENV LIB "ODBC32.DLL" Phenv & AS INTEGER
Declare Function Sqlallocconnect lib "odbc32.dll" (Byval Henv &, PhDBC &) AS Integer
Declare function sqlallocstmt lib "odbc32.dll" (Byval HDBC &, PhSTMT &) AS Integer
Declare function sqlconnect lib "odbc32.dll" (Byval HDBC &, BYVAL SZDSN $, BYVAL CBDSN%, BYVAL SZUID $, BYVAL CBUID%, Byval Szauthstr $, BYVAL CBAUTHSTR%) AS INTEGER
Declare Function SQLColAttributesString Lib "odbc32.dll" Alias "SQLColAttributes" (ByVal hstmt &, ByVal icol%, ByVal fDescType%, ByVal rgbDesc As String, ByVal cbDescMax%, pcbDesc%, pfDesc &) As Integer
Declare Function Sqldisconnect lib "odbc32.dll" (Byval HDBC &) AS INTEGER
Declare function sqlexecdirect lib "odbc32.dll" (Byval Hstmt &, Byval Szsqlstr $, BYVAL CBSQLSTR & AS INTEGERDECLARE FUNC32.DLL "(Byval Hstmt &)
Declare Function SQLFreeConnect lib "odbc32.dll" (Byval HDBC &) AS INTEGER
Declare Function SQLFreeEnv lib "odbc32.dll" (Byval Henv &) AS Integer
Declare Function SQLFREESTMT LIB "ODBC32.DLL" (Byval Hstmt &, Byval FOPTION%) AS Integer
Declare function sqlgetdata lib "odbc32.dll" (Byval Hstmt &, Byval Icol%, Byval FcType%, Byval RGBValue As String, Byval Cbvaluemax &, PCBVALUE &) AS INTEGER
Declare function SQLNUMRESULTCOLS LIB "odbc32.dll" (Byval Hstmt &, PCCOL%) AS Integer
Public const SQL_C_CHAR AS long = 1
Public const SQL_COLUMN_LABEL AS long = 18
Public const SQL_DROP AS long = 1
Public const SQL_ERROR As long = -1
Public const SQL_NO_DATA_FOUND As long = 100
Public const SQL_SUCCESS AS long = 0
Public const SQL_ATTR_MAX_LENGTH AS long = 100
---- It is necessary to say that in the function sounding, the active environment should be selected in the envelopment environment. There are two textual files odbc16.txt and odbc32.txt in the VB subdirectory Samples / Remauto / DB_ODBC, have all 16-bit and 32-bit ODBC API functions, constant, and data structures, which can be copied from the middle. The required statement statement. ---- Use the ODBC API programming method ---- Call the ODBC API function in the VB to access the ODBC database, code preparation is generally performed in accordance with the following procedures: ---- First, initialize ODBC ---- in this process In the application, the application will initially initialize the ODBC interface by calling the SQLALLOENV function to obtain an ODBC environment handle. The ODBC environment handle is the parent handle of its ODBC source handle, so that there is no argument to build a few ODBC connections, this process is only necessary to execute. For example: DIM RC AS INTEGER 'ODBC function return code DIM HENV AS long' odbc Environment Handle RC = SQLALLOCENV (HENV) Get ODBC Environment Handle ---- Second, establish a connection with ODBC data source ---- this process The following two steps consist: ---- 1, call the SQLAlLocconnect function to get the connection handle. For example,: DIM HDBC AS Long 'Connection Handle RC = SQLACCONNECT (HENV, HDBC)' Gets the connection handle ---- 2, establish a connection. This step can be achieved throughout a variety of ways, the simplest direction is to call the SqlConnect function. For example: DIM DSN AS STRING, UID AS STRING, PWD AS STRINGDS = "DatasourceName" 'ODBC Data Source Name UID = "UserID"' User Account PWD = "Password" 'User Password RC = SqlConnect (HDBC, DSN, LEN (DSN ), UID, LEN (UID), PWD, LEN (PWD)) Establishment ---- Third, Acquisition Data ---- User's access to ODBC data sources is implemented by SQL statements. In this process, the application will be used to submit the SQL statement to the ODBC data library to complete the operation of the user. The specific steps are as follows: ---- 1, call the sqlallocstmt function to obtain the statement handle. For example: DIM HSTMT As Longrc = SQLAllocStmt (HDBC, HSTMT) ---- 2, execute SQL statement.
Methods to perform SQL statements, the simplest and clear way is to call SQLAllocStmt functions, for example: DIM SQLSTMT AS STRINGSQLSTMT = "Select * from authors" RC = SQLEXECDirect (HSTMT, SQLSTMT, LEN (SQLSTMT)) ---- Four, Retrieval result set ---- If the SQL statement is submitted smoothly and executed correctly, then a result set is generated. There are many ways to retrieve the confession, the simplest direct method is to call the SQLFETCH and SQLGETDATA functions. The function of the SQLFETCH function is to move the front record pointer of the fruit set to the next record, and the function of the SQLgetData function is a field value for the preceding record in the confession. Tongmong can use a cycle to extract all the field values in the collection set, the cycle is repeatedly executed with the SQLFETCH and SQLGETDATA functions until the SQLFETCH function returns to SQL_NO_DATA_FOUND, which shows the end of the column. Dim ColVal As String * 225ColVal = String (255, 0) Do Until SQLFetch (hstmt) = SQL_NO_DATA_FOUNDrc = SQLGetData (hstmt, i, SQL_C_CHAR, ColVal, Len (ColVal), SQL_NULL_DATA) Loop ---- V. End Applications - --- The system resources used in the program must be released before the application completes the database operation, exiting the run. These system resources include: clause handles, connect handles, and ODBC ring handles. The steps completed into this process are as follows: ---- 1, call the SQLFREESTMT function to release the clause handle and its associated system resources. For example, RC = SQLFREESTMT (HSTMT, SQL_DROP) ---- 2, call the SQLDisconnect function shutdown connection. For example: rc = sqldisconnect (hdbc) ---- 3, call the SQLFreeConnect function to release the connecting handle and its associated system resources. For example, RC = SQLFREECONNECT (HDBC) ---- 4, call the SQLFreeEnv function to release the ring handle and its associated system resources, stop ODBC operation. For example, RC = SQLFREENV (HENV) ---- In addition, there is a question that needs to be gradually considering at the time of the programming program, which is the wrong handling. All ODBC API functions, if there is a mistake between execution, it will return a standard error code SQL_ERROR. Generally speaking, after each call ODBC API function, it should be checked to check the value, determine whether the function is in power, and then determines whether it will continue to continue.
