Connection data source
In this tutorial, we will learn the details of the ODBC APIS.
Because our program does not communicate directly with the ODBC driver, but through the ODBC Manager to define a series of APIs for your program to complete the work, so we need to include ODBC32.INC and ODBC32.LIB files, of course there is Windows .inc.
The connection data source requires the following steps:
Assign an environment handle (Environment Handle). Just do this once when each ODBC task is performed. Once you get your handle, we can modify the environmental properties to fit our needs. You can imagine this imagination to create a Workspace in DB. Confirm the version of ODBC that will be used. You can choose between ODBC 2.x and 3.x. They are different in many ways, so this step is required To make the ODBC Manager, it will use what grammar to communicate with the user program, and how to explain the user program command. Assign a connection handle. This step can be seen as an air connection. We have not specified using the driver, Connect to the database. This information will be written later. Create a connection. You can establish a connection by calling the ODBC function.
When the connection is completed, you must close and destroy it by the following steps:
Disconnect the connection with the data source. Release the connection handle. Release the environment handle (if you need more connections in this environment)
Assign a handle
Before the ODBC 3.x, we need to call a lot of independent functions to assign environment, connection, and statement handles (SQLAllocStmt). In ODBC 3.x, these functions are replaced by SQLallochandle, the syntax is as follows:
SQLRETURN SQLALLOCHANDLE (SQLSMallint Handletype,
SQLHANDLE INPUTHANDLE,
SQLHANDLE * OUTPUTHANDLEPTR
);
It looks quite trouble, simplify look at:
SQLALLOCHANDLE PROTO HANDLETYPE: DWORD, INPUTHANDLE: DWORD, OUTPUTHANDLEPTR: DWORD
SQLRETURN is defined as a sqlsmallint type. SQLSMallint is defined as short integer, such as a word (16 bits). So the return value of this function is in AX, not Eax. This is important. But the parameters of the function under Win32 It is transmitted through a 32-bit stack. Even if this parameter is just a word length (16 bits), it should also be extended to 32 bits. This is why handletype is illustrated as double word (DWORD) instead of words (Word). The entrance to the library ODBC32.LIB, SQLAllochandle is _sqlallochandle @ 12. That is to say, the combination of the parameters of this function is 12 bytes (3 dWords). However, this is not the prototype of the C function. SQLalloChandle will use only Handletype The base word and ignore the high word. Therefore, the C function prototype is functional (functionally) correctly and our assembly function is reflected in the actual.
End the SQL type discussion, let's take a look at the parameters and return values of the function. .
Handletype is a constant defines the type of handle that wants to assign. The possible values are as follows:
SQL_HANDLE_ENV Environment Handle (Environment Handle) SQL_HANDLE SQL_HANDLE_STMT Statement Handle (Statement Handle) SQL_HANDLE_DESC Descriptor Handle (Descriptor Handle)
The descriptor is a data set describes the parameters of a SQL statement or a column number of a result set, depending on the application or driver.
InputHandle is a handle of the father "text". That is, if you want to assign a connection handle, you need to create through an environments because the connection will be established in the text of that environment. If you want to assign an environment handle, this parameter must be SQL_HANDLE_NULL (Note SQL_HANDLE_NULL in Windows.inc version 1.18 and its previous versions are not correct as 0L. You need to delete "L" otherwise the program will not be compiled. This is my fault, because I am responsible for revising Windows.inc SQL / ODBC section.) Because the environment has no parent text. For statements and descriptor handles, we need to connect the connections as this parameter. OutputHandLeptr If the call is successful, it will point to a double word, which contains the assigned handle. Sqlallochandle's possible return value is as follows:
The SQL_SUCCESS function successfully completed. SQL_SUCCESS_WITH_INFO function successfully completed, but brought back a non-fatal error or warning. SQL_ERROR function call failed. SQL_INVALID_HANDLE Transport illegal handle.
Regardless of the success of the function or fail, we can get more information by calling the SqlgetDiagRec or SqlgetDiaGfield function. They are very similar to the getLastError in the Win32 API.
example:
.DATA? HENV DD?
.codeinvoke sqlallochandle, sql_handle_env, sql_handle_null, addr henv.if AX == SQL_SUCCESS || AX == SQL_SUCCESS_WITH_INFO
Select the version of ODBC
After allocating an environment handle, we need to set an environment attribute SQL_ATTR_ODBC_VERSION an appropriate value of the environment property by calling the function SQLSetEnvAttr you might have guessed, there is a similar function as SQLSetConnectAttr and SQLSetStmtAttr SQLSetEnvAttr prototype is as follows...:
Sqlsetenvattr Proto EnvironmentHandle: DWORD, Attribute: DWORD, VALUEPTR: DWORD,
StringLength: DWORD
EnvironmentHandle. Like literally, it contains the environment handle to set the properties. Attribute. This is a constant that represents the properties that the user needs to set. For us, it is SQL_ATTR_ODBC_VERSION. You can view all lists from MSDN. ValuePtr. The meaning of the parameter is determined by the attribute value you want to set. If the property value is 32-bit, this parameter will be considered to be the attribute value you want to set. If the attribute value is a string or binary buffer, it is interpreted as pointing String or buffer pointer. If we specify the attribute to be set to be set to SQL_ATTR_ODBC_VERSION, this parameter we can fill in two possible values of SQL_OV_ODBC3 and SQL_OV_ODBC2, respectively correspond to ODBC 3.x and 2.x. StringLength. Pointed by ValuePTR. The value of the value. If this value is a string or binary buffer, this parameter must be legal. If the attribute you want to set is a double word, this parameter is ignored. Because the SQL_ATTR_ODBC_VERSION attribute contains a double word value, we can Only give it to NULL.
The return value of this function is the same as SQLAllochandle.
example:
.DATA? HENV DD?
.codeinvoke SQLAllocHandle, SQL_HANDLE_ENV, SQL_HANDLE_NULL, addr hEnv.if ax == SQL_SUCCESS || ax == SQL_SUCCESS_WITH_INFOinvoke SQLSetEnvAttr, hEnv, SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, NULL.if ax == SQL_SUCCESS || ax == SQL_SUCCESS_WITH_INFO assigned connection handle
This step is similar to the assignment environment handle, we can do it by calling the SQLALLALLOCHANDLE function and assigning different parameter values.
example:
.DATA? HENV DD? HCONN DD?
.codeinvoke SQLAllocHandle, SQL_HANDLE_ENV, SQL_HANDLE_NULL, addr hEnv.if ax == SQL_SUCCESS || ax == SQL_SUCCESS_WITH_INFOinvoke SQLSetEnvAttr, hEnv, SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, NULL.if ax == SQL_SUCCESS || ax == SQL_SUCCESS_WITH_INFOinvoke SQLAllocHandle, SQL_HANDLE_DBC, hEnv, addr Hconn.if AX == SQL_SUCCESS || AX == SQL_SUCCESS_WITH_INFO
Establish a connection
We now have to connect to the data source through a specific ODBC driver. This goal is achieved through these three ODBC functions. They provide us with a few layers of "option".
SqlConnectCore This is the easiest function. It only requires data source (DSN, Data Source Name) and optional usernames and passwords. It does not provide any GUI options, such as displaying a dialog box to providing more information. If you already have the DSN of the database you need to use, you can use this function. SqldriverConnectCore provides more options than SqlConnect. We can connect a data source that is not defined within system information. If there is no DSN. In addition, we can specify whether this function needs to display a dialog to provide more information. For example, if the user hosted the database's name, it guides the ODBC driver to display a dialog box, let the user choose Want to connect the database. SQLBROWSECONNECTLEVEL 1 This function allows you to enumerate the data source at runtime. More flexible than SqlDriverConnect. Since SQLBROWSECONNECT can be invoked multiple times, each time you provide more dedicated information until the needed connection handle is finally obtained.
I will first check the sqlconnect function. To use SqlConnect, you should first know what is DSN. DSN is an abbreviation for the data source name, is a string that uniquely identifies a data source. A DSN identifies a data structure that contains information about a particular data source. This information includes what ODBC driver to use and which database to be connected. We can connect 32-bit ODBC data sources in the control panel. Create, modify, and delete DSN.
The syntax of SqlConnect is as follows:
SQLConnect proto ConnectionHandle: DWORD pDSN: DWORD, DSNLength: DWORD, pUserName: DWORD, NameLength: DWORD, pPassword: DWORD, PasswordLength: DWORDConnectionHandle connection handle to be used PDSN pointing DSN pointer DSNLength DSN length pusername point...... The user name pointer Namelength. The length PPassword, the username, pointing to the pointer PASSWORDLENGTH. The length of the password.
In a minimum case, SqlConnect needs to connect to the handle, DSN, and DSN. If the data source is not required, the username and password are not required. The return value of the function is the same as the return value of SQLAllochandle.
Suppose there is a DSN called "Sales" in our system and we want to connect this data source. We can do this:
.DATADSN DB "SALES", 0
.code ... invoke sqlconnect, hconn, addr dsn, sizeof dsn, 0,0,0,0
The disadvantage of SqlConnect is that we must create its DSN. SqldriverConnect provides greater flexibility before connecting a data source. Its syntax is as follows:
SQLDRIVERCONNECT Proto ConnectionHandle: DWORD, HWND: DWORD, PINCONNECTSTRING: DWORD, INSTRINGTH: DWORD, PoutConnectString: DWORD, OUTBUFFERSIZE: DWORD, OUTBUFFERSIZE: DWORD, POUTCONNECTSTRINGTH: DWORD,
Drivercompletion: DWORD
ConnectionHandle Connect the handle HWND application window handle. If this parameter is set to null, the driver will not display a dialog box to display more information (if any). PinConnectString points to the pointer to the connection string. This is A ASCIIZ string, format is described by the specific ODBC driver to connect. It describes the driver name, data source, and other additional properties. For details of the connection string, please refer to MSDN, this is no longer detailed. InstringLength connection string The length. PoutconnectString points to the pointer to the buffer that will be filled in the full connection string. This buffer will have at least 1,024 bytes. This sounds confused. In fact, the connection string we provide is incomplete. At this time, the ODBC driver prompts users more information. Next ODBC driver creates a complete connection string according to all possible information and put it into buffer Area. Even if we provide the connection string already work, this buffer will also fill in more attribute values. The purpose of this parameter is to save the full connection string to prepare for the next connection. Outbuffersize is directed by PoutconnectString. PoutconnectStringLength points to a double word pointer to receive the length of the full connection string returned by the ODBC driver. DriverCompletion is used to indicate whether the ODBC Manager / Driver will prompt users more information. However, this flag depends on whether the HWND parameter is transmitted to a window handle when calling this function. If not, even if the flag is set, the ODBC Manager / Driver does not prompt the user. The possible value is as follows: SQL_Driver_promptodbc driver prompts the user to enter information. The driver will use this information to create a connection string. SQL_Driver_completesql_driver_complete_required only When the user provides the connection string not complete, the ODBC driver prompts the user. SQL_Driver_nopromptodbc driver will not prompt users.
example:
.datastrConnect DB "DBQ = C: /Data/test.mdb; driver = {Microsoft Access Driver (* .mdb)};", 0
.DATA? BUFFER DB 1024 DUP (?) OUTSTRINGLENGTH DD?
.code ..... Invoke SqldriverConnect, HCONN, HWND, Addr Strconnect, Sizeof Strconnect, AddR Buffer, Sizeof Buffer, Addr OutbufferLength, SQL_Driver_complete
Disconnect connection with data sources
After the connection is complete, we can query and other operations on the data source. These will be discussed in the next section. Now, we have completed the operation of the data source, you can disconnect with its connection by calling SqldisConnect. This function is very simple (just like the sadness and cold reality: the destruction is more easy to create). It only needs a parameter: connect the handle.
Invoke Sqldisconnect, HCONN
Release connection and environment handle
After the connection is disconnected, we can now call the SQLFreeHandle function to destroy your handle and environment handle. This is a new function provided by ODBC 3.x. It replaces SQLFreeConnect, SQLFreeEnv, and SQLFreeStmt functions. SQLFreeHandle syntax is as follows:
SQLFreeHandle Proto Handletype: DWORD, HANDLE: DWORD
The Handletype identifies the constant to destroy the category of the handle. Possible values are the handle to be destroyed by the same handle in SQLAllochandle.
For example:
