Connecting to a data source

Data | Data source connection Data source
In this tutorial, we'll learn the details of using ODBC APIs.

Because our program does not communicate with the ODBC driver directly, but through the ODBC Manager to define a series of APIs for your program calls to complete the work, so we need to include odbc32.inc and odbc32.lib files, of course, and Windows.inc.

The following steps are required to connect to a data source:
Assigns an environment handle (environment handle). This is done only once for each ODBC task (session). Once the handle is obtained, we can modify the environment properties to suit our needs. You can think of this as creating a workspace in db work. 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 necessary to allow the ODBC Administrator what syntax it will use to communicate with the user program and how to interpret the user program's commands. Assigns a connection handle. This step can be considered as creating an empty connection. We have not yet specified the use of that driver to connect to that database. This information will be written later. Establish a connection. You can establish a connection by calling an ODBC function.
When the connection completes, you must close and destroy it by following these steps:
Disconnect from the data source. Releases the connection handle. Dispose of an environment handle (if you no longer need to make more connections in this environment) assign a handle
Before the ODBC 3.x version, we needed to invoke many separate functions to allocate the environment, the connection, and the statement handles (SQLALLOCENV, SQLAllocConnect, sqlallocstmt). In ODBC 3.x, These functions are replaced by sqlallochandle, and the syntax is as follows:

Sqlreturn Sqlallochandle (Sqlsmallint handletype, Sqlhandle inputhandle, Sqlhandle * outputhandleptr);

It looks like a hassle, simplify it and look at it:

Sqlallochandle Proto Handletype:dword,
Inputhandle:dword,
Outputhandleptr:dword

Sqlreturn is defined as the Sqlsmallint type. and Sqlsmallint is defined as a short integer, such as a word (bits). So the return value of the function is in ax, not eax. This is very important. However, the parameters of the WIN32 function are transmitted through a 32-bit stack. Even if this parameter is only a word length (16 bits), it should be extended to 32 bits. This is why Handletype is described as a double character (DWORD) instead of Word (word). Take a look at the import library The entrance to the Odbc32.lib,sqlallochandle is _sqlallochandle@12. This means that the parameter of this function has a combined length of 12 bytes (3 DWORDs). However, this does not mean that the C function is not a prototype. Sqlallochandle will use only the Handletype word and ignore the high word. So the C function prototype is functionally (functionally) correct and our Assembler function prototype reflects the reality.

To end the discussion of SQL types, let's take a look at the parameters and return values of the functions ...
Handletype is a constant that defines the type of handle that you want to assign. The possible values are as follows: SQL_HANDLE_ENV environment handle (environment HANDLE) SQL_HANDLE_DBC connection handle (Connection HANDLE) SQL_HANDLE_STMT statement handle (Statement HANDLE) sql_handle_desc description Fu (descriptor HANDLE)
A descriptor is a data collection that describes the parameters of an SQL statement or the number of columns of a result set, depending on the application or driver.
Inputhandle is a handle to the parent text. That is, if you want to assign a connection handle, you need to pass an environment handle because the connection will be created 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 is incorrectly defined as 0L in Windows.inc version 1.18 and its previous version. You need to remove "L" otherwise the program will not be compiled. This is my fault because I am responsible for revising the SQL/ODBC section of Windows.inc.) Because the environment has no parent text. For statements and descriptor handles, we need to use the connection handle as this argument. Outputhandleptr If the call succeeds, it will point to a double word containing the assigned handle.
The possible return values for Sqlallochandle are as follows:
The Sql_success function completed successfully. The SQL_SUCCESS_WITH_INFO function completes successfully, but takes back a non-fatal error or warning. The Sql_error function call failed. Sql_invalid_handle the handle to the function is illegal.
Regardless of the success or failure of the call to the function, we can get more information by invoking the SQLGetDiagRec or SQLGetDiagField function. They are similar to the GetLastError in the Win32 API.


Example:

. Data?
HENV DD?

. Code
Invoke Sqlallochandle, Sql_handle_env, sql_handle_null, addr henv
. If Ax==sql_success | | Ax==sql_success_with_info
Select the version of ODBC
After assigning the environment handle, we need to set an ambient property sql_attr_odbc_version to the appropriate value. Setting the ambient property can be sqlsetenvattr by calling the function. You might have guessed that there are similar functions like SQLSetConnectAttr and SQLSetStmtAttr. The SQLSetEnvAttr prototype is as follows:

SQLSetEnvAttr Proto Environmenthandle:dword,
Attribute:dword,
Valueptr:dword, Stringlength:dword
Environmenthandle. As with the literal meaning, it contains the environment handle to set the property. Attribute. This is a constant that represents the property that the user needs to set. For us, it's sql_attr_odbc_version. You can view the entire list from MSDN. Valueptr. The meaning of this parameter is determined by the property value you want to set. If the property value is 32 bits, this parameter is considered to be the property value you want to set. If the property value is a string or binary buffer, it is interpreted as a pointer to a string or buffer. If we specify the property to be set to Sql_attr_ Odbc_version, this parameter we can fill in the SQL_OV_ODBC3 and SQL_OV_ODBC2 these two possible values, respectively, corresponding to the ODBC 3.x and 2.x. Stringlength. The length of the value pointed to by Valueptr. If this value is a string or binary buffer, this argument must be valid. If the property you want to set is a double word, this argument is ignored. Because the Sql_attr_odbc_version property contains a double word value, we can only assign it null.
The return value of this function is the same as Sqlallochandle.

Example:

. Data?
HENV DD?

. Code
Invoke Sqlallochandle, Sql_handle_env, sql_handle_null, addr henv
. If Ax==sql_success | | Ax==sql_success_with_info
Invoke SQLSetEnvAttr, Henv, Sql_attr_odbc_version, SQL_OV_ODBC3, NULL
. If Ax==sql_success | | Ax==sql_success_with_info
Assigning connection handles
This step is similar to the allocation environment handle, which can be done by calling the Sqlallochandle function and assigning a different parameter value.

Example:

. Data?
HENV DD?
Hconn DD?

. Code
Invoke Sqlallochandle, Sql_handle_env, sql_handle_null, addr henv
. If Ax==sql_success | | Ax==sql_success_with_info
Invoke SQLSetEnvAttr, Henv, Sql_attr_odbc_version, SQL_OV_ODBC3, NULL
. If Ax==sql_success | | Ax==sql_success_with_info
Invoke 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 with a specific ODBC driver. These three ODBC functions are used to achieve this goal. They provide us with several layers of "choice".
Sqlconnectcore This is the simplest function. It requires only the data source name (Dsn,data source name) and an optional username and password. It does not provide any GUI options, such as displaying a dialog box to the user to provide more information. You can use this function if you already have a DSN for the database you need to use. Sqldriverconnectcore This function provides a more sqlconnect choice. We can connect to a data source that is not defined within the system information. If there is no DSN. In addition, we can specify whether the function needs to display a dialog box to provide more information to the user. For example, if the user omits the name of the database, it instructs the ODBC driver to display a dialog box that allows the user to select the database to which they want to connect. Sqlbrowseconnectlevel 1 This function allows you to enumerate data sources at run time (RunTime). More flexible than SQLDriverConnect. Because SQLBrowseConnect can be called multiple times, the user is given more specialized information each time until the required connection handle is finally obtained.
I'll check the SQLConnect function first. To use SQLConnect, you should first know what a DSN is. A DSN is an abbreviation of the data source name, which is a string that uniquely identifies a data source. A DSN identifies a data structure that contains information about how to connect to a particular data source. This information includes which ODBC driver to use and which database to connect to. We can create, modify, and delete DSN through the 32-bit ODBC data source in the Control Panel.

The syntax for SQLConnect is as follows:

SQLConnect Proto Connectionhandle:dword
Pdsn:dword,
Dsnlength:dword,
Pusername:dword,
Namelength:dword,
Ppassword:dword,
Passwordlength:dword
ConnectionHandle. The connection handle to use. Pdsn. A pointer to a DSN. Dsnlength. The length of the DSN is pusername. A pointer to the user name Namelength. The length of the user name is Ppassword. A pointer to the password used by the user name Passwordlength. Length of password
At a minimum, SQLConnect requires a connection handle, the length of the DSN and the DSN. If the data source is not needed, the username and password are not required. The return value of the function is the same as the return value of the Sqlallochandle.

Suppose we have a DSN called "Sales" in our system and we want to connect to this data source. We can do this:

. Data
DSN db "Sales", 0

. Code
......
Invoke SQLConnect, Hconn, addr DSN, sizeof dsn,0,0,0,0

The disadvantage of SQLConnect is that before connecting to a data source, we must create its DSN. SQLDriverConnect provides greater flexibility. Its syntax is as follows:

SQLDriverConnect Proto Connectionhandle:dword,
Hwnd:dword,
Pinconnectstring:dword,
Instringlength:dword,
Poutconnectstring:dword,
Outbuffersize:dword,
Poutconnectstringlength:dword, Drivercompletion:dword
ConnectionHandle Connection handle HWND application window handle. If this parameter is set to NULL, the driver will not display a dialog box for the user to display more information (if any). Pinconnectstring A pointer to the connection string. This is a asciiz string that is described in the format by the specific ODBC driver to be connected. It describes the driver name, data source, and other additional properties. The specific description of the connection string is shown in MSDN, which is no longer detailed here. The length of the Instringlength connection string. Poutconnectstring A pointer to the buffer to be filled in with the full connection string. This buffer will be at least 1,024 bytes long. This sounds confusing. In fact, the connection string we provide is incomplete, and the ODBC driver prompts the user for more information. The ODBC driver then creates a complete connection string based on all possible information and puts it into the buffer. Even if the connection string we provide is already working, the buffer will fill in more property values. The purpose of this argument is that we can save the full connection string to prepare for the next connection. Outbuffersize the length of the buffer to which the poutconnectstring points. Poutconnectstringlength points to a two-word pointer that receives the length of the full connection string returned by the ODBC driver. Drivercompletion a flag to indicate whether the ODBC Administrator/driver will prompt the user for more information. However, this flag depends on whether a window handle to the HWND argument is passed when the function is called. If not, the ODBC Administrator/driver will not prompt the user even if the flag is set. The possible values are as follows:

The SQL_DRIVER_PROMPTODBC driver prompts the user to enter information. The driver will use this information to create the connection string. Sql_driver_complete
Sql_driver_complete_required The ODBC driver prompts the user only if the user-supplied connection string is not complete. The SQL_DRIVER_NOPROMPTODBC driver will not prompt the user.
Example:

. Data
StrConnect 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 from the data source
After the connection has been successfully completed, we can query the data source and other operations. These are discussed in the next section. Now, assuming that we have completed the operation of the data source, we can disconnect it by calling SQLDisconnect. This function is very simple (like the sad and grim reality: destruction is always easier than creation). It requires only one parameter: the connection handle.

Invoke SQLDisconnect, Hconn
Releasing connections and environment handles
After a smooth disconnect, we can now call the Sqlfreehandle function to destroy the connection handle and the environment handle. This is a new function provided by ODBC 3.x. It replaces the SQLFreeConnect, sqlfreeenv, and SQLFREESTMT functions. The Sqlfreehandle syntax is as follows:

Sqlfreehandle Proto Handletype:dword, Handle:dword
Handletype A constant that identifies the category of the handle to be destroyed. A handle that is the same as the handle to be destroyed in the Sqlallochandle.
For example:

Invoke Sqlfreehandle, SQL_HANDLE_DBC, Hconn
Invoke Sqlfreehandle, Sql_handle_env, henv