Connect to the data source

In this tutorial, we will learn the details of using ODBC APIs.

Because our program does not directly communicate with the ODBC driver, but uses the ODBC manager to define a series of APIS for your program calls to complete the work, we need to include the odbc32.inc and odbc32.lib documents, of course there are more windows. inc.

The following steps are required to connect to the Data source:
Assign an environment handle (Environment handle ). you only need to do this once for each ODBC task (session. once a handle is obtained, we can modify the environment attribute to suit our needs. You can imagine creating a workspace in dB work. confirm the version of ODBC to be used. you can. version X and 3. select between versions X. they are different in many ways, so this step is necessary to enable the ODBC manager to communicate with the user program in what syntax, and how to explain the user program commands. allocate a connection handle. this step can be seen as creating an empty connection. we have not specified the driver to connect to that database. this information will be written later. create a connection. you can call the ODBC function to establish a connection.
When the connection is complete, you must follow these steps to close and destroy it:
Disconnect from the data source. Release the connection handle. Release the environment handle (if you do not need to make more connections in this environment ).
In ODBC 3. before Version X, we need to call many separate functions to allocate environment, connection, and statement handle (sqlallocenv, sqlallocconnect, sqlallocstmt ). in ODBC 3. in X, these functions are replaced by sqlallochandle. The syntax is as follows:

Sqlreturn sqlallochandle (sqlsmallint handletype, sqlhandle inputhandle, sqlhandle * outputhandleptr );

It looks quite troublesome. Just take a look at it:

Sqlallochandle proto handletype: DWORD,
Inputhandle: DWORD,
Outputhandleptr: DWORD

Sqlreturn is defined as sqlsmallint type. sqlsmallint is defined as a short integer, for example, a word (16 bits ). so the return value of this function is in ax, not eax. this is important. however, parameters of functions under Win32 are transmitted through a 32-bit stack. even if this parameter is only a character length (16 bits), it should be extended to 32 bits. this is why handletype is described as DWORD rather than word ). let's take a look at importing data to odbc32.lib. The sqlallochandle entry is _ sqlallochandle @ 12. that is to say, the parameter combination length of this function is 12 bytes (3 Dwords ). however, this does not mean that the C function prototype is incorrect. sqlallochandle only uses the base character of handletype and ignores the high character. therefore, the C function prototype is functional (functionally) and our assembler function prototype reflects the actual situation.

After the SQL type discussion, let's take a look at the parameters and return values of the function ..
Handletype is a constant and defines the type of handle to be allocated. possible values: SQL _handle_env environment handle (Environment handle) SQL _handle_dbc connection handle (connection handle) SQL _handle_stmt statement handle (statement handle) SQL _handle_desc descriptor handle (descriptor handle)
A descriptor is a data set that describes the parameters of an SQL statement or the number of columns in a result set, depending on the application or driver.
Inputhandle is the handle pointing to the parent "text. that is to say, if you want to allocate a connection handle, you need to use an environment handle because the connection will be created in the text of that environment. if you want to allocate an environment handle, this parameter must be SQL _handle_null (note that SQL _handle_null is in windows. in Inc 1.18 and earlier versions, it is incorrectly defined as 0l. you need to delete "L" or the program will not be compiled. this is my fault because I am responsible for revising windows. SQL/ODBC section in Inc .) because the environment does not have the parent text. for statements and descriptor handles, we need to use the connection handle as this parameter. If the call is successful, outputhandleptr points to a dual string containing the allocated handle.
Sqlallochandle may return the following values:
The SQL _success function is successfully completed. SQL _success_with_info function is successfully completed, but a non-fatal error or warning is returned. SQL _error function call failed. SQL _invalid_handle the handle sent to the function is invalid.
Whether the function call is successful or failed, we can call the sqlgetdiagrec or sqlgetdiagfield function to obtain more information. They are similar to getlasterror in 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 ODBC version
After the environment handle is allocated, we need to configure an environment attribute SQL _attr_odbc_version with an appropriate value. to configure environment attributes, call the sqlsetenvattr function. you may have guessed that there are more similar functions such as sqlsetconnectattr and sqlsetstmtattr. the sqlsetenvattr prototype is as follows:

Sqlsetenvattr proto environmenthandle: DWORD,
Attribute: DWORD,
Valueptr: DWORD, stringlength: DWORD
Environmenthandle. similar to the word surface, it contains the environment handle for configuring attributes. attribute. this is a constant, indicating the attributes to be configured. for us, it is SQL _attr_odbc_version. all lists can be viewed from msdn. valueptr. the meaning of this parameter is determined by the attribute value to be configured. if the attribute value is 32 bits, this parameter is considered as the attribute value to be configured. if the attribute 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 configured as SQL _attr_odbc_version, we can fill in the two possible values SQL _ov_odbc3 and SQL _ov_odbc2, which correspond to ODBC 3 respectively. X and 2. x. stringlength. the length of the value pointed by valueptr. if the value is a string or binary buffer, this parameter must be valid. if the attribute to be configured is a double character, this parameter is ignored. because the SQL _attr_odbc_version attribute contains a double-character value, we can only assign it null.
The return value of this function is the same as that of 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
Allocate connection handle
This step is similar to allocating environment handles. We can call the sqlallochandle function and assign different parameter values.

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
Create a connection
Now we need to use a specific ODBC driver to connect to the data source. These three ODBC functions are used to achieve this goal. They provide us with several layers of "choice ".
Sqlconnectcore is the simplest function. He only needs the data source name (DSN, data source name) and optional username and password. He does not provide any GUI options, such as displaying a dialog box to users to provide more information. If you already have the DSN of the database you want to use, you can use this function. the sqldriverconnectcore function provides more options than sqlconnect. we can connect to a data source that is not defined in the system information. If no DSN exists. in addition, we can specify whether this function needs to display a dialog box to provide more information for users. for example, if the user misses the database name, he will instruct the ODBC driver to display a dialog box for the user to select the database to connect. sqlbrowseconnectlevel 1: This function allows you to enumerate data sources at runtime. it is more flexible than sqldriverconnect. Because sqlbrowseconnect can be called multiple times in sequence, more special information is provided to the user each time until the required connection handle is obtained.
I will first check the sqlconnect function. To use sqlconnect, you should first know what is DSN. DSN is the 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 specific data source. this information includes the ODBC driver to be used and the database to be connected. we can use the 32-bit ODBC data source in the control panel to create, modify, and delete DSN.

Sqlconnect Syntax:

Sqlconnect proto connectionhandle: DWORD
Pdsn: DWORD,
Dsnlength: DWORD,
Pusername: DWORD,
Namelength: DWORD,
Ppassword: DWORD,
Passwordlength: DWORD
Connectionhandle. the connection handle to be used. pdsn. pointer to DSN. dsnlength. the length of the DSN pusername. the pointer to the user name namelength. the username length is ppassword. pointer to the password used by the user name passwordlength. password Length
In the minimum case, sqlconnect requires a connection handle, the length of DSN and DSN. If the data source is not required, the user name and password are not required. The return value of the function is the same as that of sqlallochandle.

Suppose there is a DSN named "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

The disadvantage of sqlconnect is that before connecting to a data source, we must create its DSN. sqldriverconnect to provide 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 more information (if any) in a dialog box ). pinconnectstring pointer to the connection string. this is an asciiz string in the format described by the ODBC driver to be connected. it describes the driver name, data source, and other additional attributes. for more information about the connection string, see msdn. the length of the instringlength connection string. poutconnectstring refers to the pointer to the buffer to be filled with the complete connection string. this buffer will be at least 1,024 bytes long. this sounds confusing. In fact, the connection string we provide will be incomplete. In this case, the ODBC driver will prompt you for more information. next, the ODBC driver creates a complete connection string based on any possible information and puts it into the buffer zone. Even if the provided connection string is ready for operation, this buffer will be filled with more attribute values. The purpose of this parameter is to save the complete connection string to prepare for the next connection. The length of the buffer that outbuffersize points to by poutconnectstring. poutconnectstringlength points to a dual-character pointer to receive the length of the complete connection string returned by the ODBC driver. The drivercompletion flag is used to indicate whether the ODBC Manager/driver will prompt the user for more information. However, this flag depends on whether a window handle is transmitted when the hwnd parameter is called. If no, the ODBC Manager/driver will not prompt the user even if the flag is configured. 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 only when the user-provided connection string is incomplete, the ODBC driver will prompt the user. 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 successful connection, we can query the data source and perform other operations. these will be discussed in the next section. now, if we have completed the operations on the data source, we can call sqldisconnect to disconnect it. this function is very simple (like the sad and cold reality: destruction is always easier than creation ). it only needs one parameter: Connection handle.

Invoke sqldisconnect, hconn
Release connection and environment handle
After the connection is successfully disconnected, we can now call the sqlfreehandle function to destroy the connection handle and environment handle. this is done by ODBC 3. X provides new functions. it replaces sqlfreeconnect, sqlfreeenv, and sqlfreestmt functions. sqlfreehandle Syntax:

Sqlfreehandle proto handletype: DWORD, handle: DWORD
Handletype identifies the constant of the type of the handle to be destroyed. The possible value is the same as that of the handle to be destroyed in sqlallochandle.
For example:

Invoke sqlfreehandle, SQL _handle_dbc, hconn
Invoke sqlfreehandle, SQL _handle_env, henv