ODBC connection 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 to call to complete the work, we need to include the odbc32.inc and odbc32.lib files, windows. inc.

The following steps are required to connect to the Data source:

1. 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.
2. Confirm the version of ODBC to be used. you can go to ODBC 2. 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.
3. 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.
4. Establish 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:

1. Disconnect from the data source.
2. Release the connection handle.
3. Release the environment handle (if you no longer need to make more connections in this environment)

Allocate a handle

In ODBC 3. before Version X, we need to call many independent 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 that defines the type of handle to be allocated. The possible values are as follows:

SQL _handle_env	Environment handle (Environment handle)
SQL _handle_dbc	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.
• Outputhandleptr if the call is successful, it will point to a dual word, which contains the allocated handle.

Sqlallochandle may return the following values:

SQL _success	The function is successfully completed.
SQL _success_with_info	The 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.

No matter whether the function is successfully called or fails, 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 set an environment attribute SQL _attr_odbc_version to an appropriate value. you can call sqlsetenvattr to set environment properties. you may have guessed that there are 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 literal meaning, it contains the environment handle for setting attributes.
• Attribute. This is a constant that indicates the attribute to be set. For us, it is SQL _attr_odbc_version. You can view all the lists from msdn.
• Valueptr. the meaning of this parameter is determined by the attribute value you want to set. if the attribute value is 32-bit, this parameter is considered as the attribute value to be 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 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 you want to set 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 connect to the data source through a specific ODBC driver. These three ODBC functions are used to achieve this goal. They provide us with several layers of "choice ".

Sqlconnect	Core	This is the simplest function. It only requires the data source name (DSN, data source name) and optional username and password. It 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.
Sqldriverconnect	Core	This 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, it will instruct the ODBC driver to display a dialog box for the user to select the database to connect.
Sqlbrowseconnect	Level 1	This function allows you to enumerate data sources at runtime, which 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. you can create, modify, and delete a DSN using the 32-bit ODBC data source in the control panel.

Sqlconnect Syntax:

SQLConnect proto ConnectionHandle:DWORD
pDSN:DWORD,
DSNLength:DWORD,
pUserName:DWORD,
NameLength:DWORD,
pPassword:DWORD,
PasswordLength:DWORD

• Connectionhandle. Connection handle to be used.
• Pdsn. pointer to DSN.
• Dsnlength. DSN Length
• Pusername. pointer to user name
• Namelength. Length of the user name
• 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) for the user ).
• Pinconnectstring pointer to the connection string. this is an asciiz string in the format described by the specific 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 points to the pointer to the buffer that will 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 all 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 property values. The purpose of this parameter is that we can save the complete connection string to prepare for the next connection.
• The length of the buffer that outbuffersize points to by poutconnectstring.
• Poutconnectstringlength is a two-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 set. The possible values are as follows:
  

  SQL _driver_prompt	The ODBC driver prompts the user to enter information. The driver will use this information to create a connection string.
  SQL _driver_complete SQL _driver_complete_required	The ODBC driver will prompt you only when the user-provided connection string is incomplete.
  SQL _driver_noprompt	The ODBC 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 is complete, 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 value may be the same as that in sqlallochandle.
• Handle: the handle to be destroyed.

For example:

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