Use ODBC to access the database

Header files and LIB libraries used before using ODBC APIs
# Include "SQL. h" // This is the main include for ODBC Core functions.
# Include "sqlext. h" // This is the include for applications using the Microsoft SQL Extensions
# Include "sqltypes. h" // This file defines the types used in ODBC
# Include "sqlucode. h" // This is the unicode include for ODBC Core functions
# Include "odbcss. h" // This is the application include file for the SQL Server driver specific defines.
# Pragma coment (lib, "odbc32.lib ")

Odbc api Return Value
The return value of odbc api is defined as SQLRETURN. The returned values are SQL _SUCCESS, SQL _SUCCESS_WITH_INFO, and the error code is returned when the operation fails. Note that if the ODBC return value is SQL _SUCCESS_WITH_INFO, it does not indicate that the execution is completely successful, but it indicates that the execution is successful but contains a certain error message. When an error occurs, ODBC returns a result set of the error message. You need to traverse all rows in the result set. This is similar to the query of the SQL statement execution result set described later.

SQLAllocHandle create ODBC handle
SQLRETURN SQLAllocHandle (
SQL SMALLINT HandleType, // handle type to be applied
SQLHANDLE InputHandle, // input handle
Sqlhandle * outputhandleptr); // output handle, that is, specify the handle to be applied in the first parameter
The value of handletype can be:
1. SQL _handle_env
2. SQL _handle_dbc
3. SQL _handle_stmt

Connect to the database using sqlconnect
Sqlreturn sqlconnect (
Sqlhdbc connectionhandle, // DBC handle, hdbc
Sqlchar * servername, // The odbc dsn name
Sqlsmallint namelength1, // specify the length of the servername parameter (SQL _cnt can be used)
Sqlchar * username, // database username
Sqlsmallint nameleng22. // specify the length of the username parameter (SQL _cnt can be used)
Sqlchar * authentication, // Database User Password
Sqlsmallint namelength3) // specify the length of the authentication parameter (you can use SQL _nt)
For example:
Sqlconnect (
Hdbc,
(Sqltchar *) szdsn, SQL _cnt,
(Sqltchar *) szuserid, SQL _cnt,
(SQLTCHAR *) szPassword, SQL _cnt );

SQLExecDirect directly executes SQL statements
SQLRETURN SQLExecDirect (
SQLHSTMT StatementHandle, // STMT handle
SQLCHAR * StatementText, // SQL statement
SQLINTEGER TextLength) // specifies the length of the StatementText parameter.
If the function is successfully executed, you will get a result set. Otherwise, an error message will be returned.

Obtain the SQL statement execution result
For SQL query statements, ODBC returns a cursor, which corresponds to a result set (which can be understood as a table ). Developers can use the cursor to view all the results. You can use the odbc api function to move the cursor and obtain the value of the column field pointed to by the current cursor. In addition, you can use the cursor to modify the data pointed to by the cursor, which is directly reflected in the database.

SQLFetch move cursor
SQLRETURN SQLFetch (SQLHSTMT StatementHandle );
After you call SQLExecDirect to execute an SQL statement, you need to traverse the result set to obtain data. StatementHandle is the STMT handle, which must have been executed. When the SQLFetch function is called, The cursor will be moved to the next record. When the cursor moves to the last record set, the function will return SQL _NO_DATA.

SQLGetData: obtain the value of a column at the cursor.
SQLRETURN SQLGetData (
SQLHSTMT StatementHandlem, // STMT handle
SQLUSMALLINT ColumnNumber, // column number, starting with 1
SQLSMALLINT TargetType, // the C language type of the data buffer (TargetValuePtr)
SQLPOINTER TargetValuePtr, // data buffer
SQLINTEGER BufferLength, // The length of the data buffer (TargetValuePtr)
SQLINTEGER * StrLen_or_IndPtr); // returns the byte length of the current field.

SQLBindCol obtains field data through Column Binding
SQLRETURN SQLBindCol (
SQLHSTMT StatementHandle, // STMT statement
SQLUSMALLINT ColumnNumber, // column number, starting with 1
SQLSMALLINT TargetType, // the C language type of the data buffer (TargetValuePtr)
SQLPOINTER TargetValuePtr, // data buffer
SQLINTEGER BufferLength, // The length of the data buffer (TargetValuePtr) in bytes
SQLINTEGER * StrLen_or_IndPtr); // returns the byte length of the current field.
SQLGetData can be used to read field values from the result set. However, for speed purposes, you can use the Column Binding (SQLBindCol) method to send data to the specified variable after each cursor movement.

SQLNumResultCols
SQLRETURN SQLNumResultCols (
SQLHSTMT StatementHandle, // STMT handle
SQLSMALLINT * ColumnCountPtr); // The number of returned Columns

SQLRowCount: number of rows affected after SQL statement execution
SQLRETURN SQLRowCount (
SQLHSTMT StatementHandle, // STMT handle
SQLINTEGER * RowCountPtr); // Number of affected data rows
You can use SQLExecDirect to execute SQL statements to insert, modify, and delete data. After executing the insert, modify, and delete SQL statements, you can use the SQLRowCount function to obtain the number of affected data rows.

SQLDescribeCol
SQLRETURN SQLDescribeCol (
SQLHSTMT StatementHandle, // STMT handle
SQLSMALLINT ColumnNumber, // the sequence number of the expected column, which is calculated from 1
SQLCHAR * ColumnName, // get the column name
SQLSMALLINT BufferLength, // specify the maximum length of the ColumnName Parameter
SQLSMALLINT * NameLengthPtr, // returns the length of the column name
SQLSMALLINT * DataTypePtr, // ODBC Data Type of the returned column, see table
SQLUINTEGER * ColumnSizePtr, // returns the length of the column
SQLSMALLINT * DecimalDigitsPtr, // The number of digits after the decimal point returned when the column is of the numeric type
SQLSMALLINT * NullablePtr); // specifies whether the column allows null values.

SQLSetStmtAttr: Set ODBC cursor type
SQLRETURN SQLSetStmtAttr (
SQLHSTMT StatementHandle, // STMT handle
SQLINTEGER Attribute, // specify the Attribute type to be set
SQLPOINTER ValuePtr, // provided parameter value
SQLINTEGER StringLength); // specify the length of the parameter. If the parameter is an integer, set it
// SQL _IS_INTEGER. When the parameter is a string
// It is the string length or SQL _cnt
The SQLSetStmtAttr function allows us to use different cursor types in ODBC.

Attribute	ValuePtr	Function
SQL _ATTR_ASYNC_ENABLE	Integer. value: SQL _ASYNC_ENABLE_OFF, SQL _ASYNC_ENABLE_ON	Whether asynchronous execution is used
SQL _ATTR_QUERY_TIMEOUT	Set a valid integer	Time-out seconds for SQL statement execution. If it is set to 0, no time-out occurs.
SQL _ATTR_CURSOR_TYPE	Integer. value: SQL _CURSOR_FORWARD_ONLY, SQL _CURSOR_STATIC, SQL _CURSOR_DYNAMIC, SQL _CURSOR_KEYSET_DRIVEN	Set the cursor type

1. Forward cursor: SQL _CURSOR_FORWARD_ONLY. The cursor only scrolls forward.
2. static cursor: SQL _CURSOR_STATIC. The data in the result set is static. This means that the data in the returned result set will not change after the query is executed, even if other programs update the records in the database, the records in the result set will not change.
3. dynamic Cursor: SQL _CURSOR_DYNAMIC. After the cursor is opened, when the data value of the row in the result set changes, the changes can be reflected in the result set corresponding to the cursor. These changes include: modify, add, and change the order of rows in the result set. However, note that if a row is deleted, it cannot be reflected in the current result set because the deleted row no longer appears in the current result set. The result set corresponding to the dynamic cursor is rebuilt when the data changes. For example, assume that the dynamic cursor has obtained two rows, and then the other application updates one row in the two rows and deletes the other row. If the Dynamic Cursor tries to get those rows, it cannot detect deleted rows (because there is only one row in the current result set, but do not use this method to detect deleted rows, this may also occur because the row data cannot meet the query conditions after being changed), but returns the new value of the updated row.
4. key set cursor: SQL _CURSOR_KEYSET_DRIVEN. Unlike the above dynamic cursor, the key set cursor can detect row deletion and modification, but cannot detect row addition and result set order changes. Because the entire result set is created when the cursor is created, the records and sequence in the result set have been fixed, which is the same as the static cursor. Therefore, the keyset cursor is a type between a static cursor and a dynamic cursor.
For example, SQLSetStmtAttr (hstmt, SQL _ATTR_CURSOR_TYPE, (SQLPOINTER) SQL _CURSOR_KEYSET_DRIVEN, 0 );

SQLFetchScroll queries with a scroll cursor
SQLRETURN SQLFetchScroll (
SQLHSTMT StatementHandle, // STMT statement
SQLSMALLINT FetchOrientation, // The method of cursor scrolling, as shown in the table below
SQLINTEGER FetchOffset); // cursor scroll position
The SQLFetch function described above only allows the cursor to move forward, but in many cases we need the cursor to move forward and backward. We need to use another function SQLFetchScroll, but before that, please use SQLSetStmtAttr to correctly set the cursor type.

FetchOrientation	Meaning
SQL _FETCH_NEXT	Scroll to the next line. At this time, the call is equivalent to SQLFetch. The FetchOffset parameter will be ignored (0 value)
SQL _FETCH_PRIOR	Scroll to the previous row. The FetchOffset parameter is ignored (0 value)
SQL _FETCH_FIRST	Scroll to the first line. The FetchOffset parameter is ignored (0 value)
SQL _FETCH_LAST	Scroll to the last row. The FetchOffset parameter is ignored (0 value)
SQL _FETCH_ABSOLUTE	Scroll to the absolute line specified by the FetchOffset parameter.
SQL _FETCH_RELATIVE	From the current position to the relative row specified by the FetchOffset parameter, FetchOffset greater than 0 indicates rolling forward, FetchOffset less than 0 indicates rolling backward