The header file and lib library you want to use before using the ODBC API. Microsoft SQL Extensions # include "sqltypes.h" // This file defines the types used in ODBC # include "sqlucode.h" // This is the the unicode include for ODBC Core functions # include "odbcss.h" // This is The return value of the return value ODBC API of the ODBC API is defined as: SQLRETURN for the return value of the ODBC API. The return value is: SQL_SUCCESS, SQL_SUCCESS_WITH_INFO; returns an error code when failing. One thing to note is that if the ODBC return value is: SQL_SUCCESS_WITH_INFO does not indicate full success, but it indicates that the execution is successful but has certain error messages. When the error is executed, the ODBC returns a result set of error messages, you need to traverse all rows in the collection of results, this and later query SQL statement execution result set ideas is very similar. SQLAllocHandle create ODBC handles SQLRETURN SQLAllocHandle (SQL SMALLINT HandleType, handle type SQLHANDLE InputHandle // required application, // input handle SQLHANDLE * OutputHandlePtr); // output handle that specifies the need to apply in the first parameter is the handle of the first parameter HandleType The value can be: 1. SQL_HANDLE_ENV 2. SQL_HANDLE_DBC 3. SQL_HANDLE_STMT
SQLConnect connection database SQLRETURN SQLConnect (SQLHDBC ConnectionHandle, // DBC handle, hdbc SQLCHAR * ServerName, // the name of the ODBC DSN SQLSMALLINT NameLength1, // ServerName parameter specifies the total length (optionally SQL_NTS) SQLCHAR * UserName, // database username SQLSMALLINT NameLength2, // UserName parameters specified length (can SQL_NTS) SQLCHAR * Authentication, // user password database SQLSMALLINT NameLength3) // Authentication parameters specified length (may be SQL_NTS) example: SQLConnect (hdbc, (SQLTCHAR *) szDSN , SQL_NTS, (SQLTCHAR *) szUserId, SQL_NTS, (SQLTCHAR *) szPassword, SQL_NTS); SQLExecDirect to execute SQL statements directly SQLRETURN SQLExecDirect (SQLHSTMT StatementHandle, // STMT handle SQLCHAR * StatementText, // SQL statement SQLINTEGER TextLength) // parameter StatementText Length, you can use SQL_NTS if the function is successful, you will get a result set, otherwise the error message will be returned.
Gets the result of the SQL statement for the SQL query statement, the ODBC returns a cursor, and the cursor corresponds to a result set (you can understand as a table). Developers use cursors to browse all results, you can use the ODBC API function to move the cursor and get the value of the column fields of the current cursor pointing. In addition, it is also possible to modify the data of the cursor currently to by the cursor, and the modification will be directly reflected in the database.
SQLFETCH Moves the cursor SQLRETURN SQLFETCH (SQLHSTMT StatementHandle); After you call SQLExECDirect to execute the SQL statement, you need to pay data to get data. StatementHandle is a STMT handle, which must be performed. When the SQLFETCH function is called, the cursor will be moved to the next record, and when the cursor moves to the last one of the record set, the function will return SQL_NO_DATA.
SQLGetData cursor obtained at a column value SQLRETURN SQLGetData (SQLHSTMT StatementHandlem, // STMT handle SQLUSMALLINT ColumnNumber, // column number starts at 1 SQLSMALLINT TargetType, // data buffer (the TargetValuePtr) a C language type SQLPOINTER TargetValuePtr, // data buffer SQLINTEGER BufferLength, // data buffer length (the TargetValuePtr) of SQLINTEGER * StrLen_or_IndPtr); // returns the byte length of the current field obtained SQLBindCol column binding obtaining field data SQLRETURN SQLBindCol (SQLHSTMT StatementHandle through, // STMT statement SQLUSMALLINT ColumnNumber, // List, starting the C language type of SQLSMallint Targettype, // Data buffer (targetvalueptr) C language type SQLPointer TargetValuePtr, // Data buffer SQLINTEGER BUFFERLEENGTH, / / DATA Buffer (TargetValuePtr) byte length SQLINTEGER * Strlen_or_indptr ); // Returns the byte length obtained by the current field to read the field value from the result set, but can utilize the column binding (sqlbindcol) in the speed of speed, allow ODBC to transfer data after each movement cursor In the specified variable
SqlnumResultCols gets the result set column number SQLRETURN SQLNUMRESULTCOLS (SQLHSTMT StatementHandle, // STMT Handle Sqlsmallint * ColumnCountPtr); // Returns the number of columns
SQLROWCOUNT to get the number of row numbers after the SQL statement, SQLRETURN SQLROWCOUNT (SQLHSTMT StatementHandle, // STMT Handle SQLINTEGER * ROWCOUNTPTR); // The number of rows that are affected data You can perform SQL statements with SQLExECD to insert, modify, and delete data, After performing insert, modify, and delete SQL statements, you can get the number of rows that are affected by the SQLRowCount function.
Of columns in the result obtained SQLDescribeCol described SQLRETURN SQLDescribeCol (SQLHSTMT StatementHandle, // STMT handle SQLSMALLINT ColumnNumber, // number of columns need to be calculated from the start SQLCHAR * ColumnName 1, give name // SQLSMALLINT BufferLength column // specified ColumnName The maximum length of the parameter sqlsmallint * namelengththptr, // returns the length of the column name Sqlsmallint * DataTyPEPTR, // Returns the odbc data type of the column, see table SQLUINTEGER * columnsizeptr, // Return the length of the length sqlsmallint * decimaldigitsptr, // is listed as a number after the decimal point when return data type bits SQLSMALLINT * NullablePtr); // indicates whether the column is provided to allow nulls SQLSetStmtAttr ODBC cursor type SQLRETURN SQLSetStmtAttr (SQLHSTMT StatementHandle, // STMT handle SQLINTEGER attribute, // attribute type to be set SQLPOINTER ValuePtr, // Provide parameter value SQLINTEGER STRINGTH); / / Specify the length of the parameter, when the parameter is integer, set to // SQL_IS_INTEGER
Attribute ValuePtr role SQL_ATTR_ASYNC_ENABLE integer value of: SQL_ASYNC_ENABLE_OFF, SQL_ASYNC_ENABLE_ON whether to use asynchronous execution function SQL_ATTR_QUERY_TIMEOUT set timeout in seconds when a valid integer execution of SQL statements, set to 0 means no timeout SQL_ATTR_CURSOR_TYPE integer value of: SQL_CURSOR_FORWARD_ONLY, SQL_CURSOR_STATIC, SQL_CURSOR_DYNAMIC, SQL_CURSOR_KEYSET_DRIVEN Sets the type of cursor
1. Advance the cursor: SQL_CURSOR_FORWARD_ONLY, the cursor is only scrolling forward. 2. Static cursor: SQL_CURSOR_STATIC, the data of the result set is static, which means that the data returned after the query will not change again, even if there is other programs updated the record in the database, the results in the results It does not change. 3. Dynamic cursor: SQL_CURSOR_DYNAMIC, after the cursor is open, when the data value corresponding to the rows in the result set changes, the change can be reflected on the result set corresponding to the cursor, including: Field modification, addition, result The order of concentration line changes. But note that if the line 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 of the dynamic cursor is reconstructed when data changes. For example, assume that the dynamic cursor has been acquired two rows, then another application updated the row in the two rows and deleted another row. If the dynamic curster tried to get those rows, it will not detect the deleted row ( Because the current result is only one line, do not use this method to detect the deleted row, because this situation may not be due to the line's data cannot meet the query conditions), but return the new value that has been updated. . 4. Key Set Cursor: SQL_CURSOR_KEYSET_DRIVEN, and the above dynamic cursor is different, the key set cursor can detect the deletion and modification of the row, but cannot detect the addition of the row and the resulting sequence change. Because the entire result set is created when the cursor is created, the records and sequences in the result collection have been fixed, and this is the same as the static cursor. Therefore, the key set cursor can be said to be a cursor type between the static cursor and the dynamic cursor. Such as: SQLSetStmtAttr (hstmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER) SQL_CURSOR_KEYSET_DRIVEN, 0); SQLFetchScroll Scroll can use query SQLRETURN SQLFetchScroll (SQLHSTMT StatementHandle, // STMT statement SQLSMALLINT FetchOrientation, // cursor scrolling mode, the table below SQLINTEGER FetchOffset); / / The SQLFETCH function described earlier before the cursor scroll can only move the cursor forward, but in many cases we need the cursor to move before and after. We need to use another function SQLFETCHSCROLL, but before this, use SQLSTMTATTR to properly set the cursor type. Fetchorientation Configuration SQL_FETCH_NEXT scrolls to the next line, this time calls fairly with SQLFETCH, parameter fetchoffset will be ignored (0 value) SQL_FETCH_PRIOR scroll to the previous line, the parameter fetchoffset will be ignored (using 0 value) SQL_FETCH_FIRST scroll to the first line, parameter FetchOffset is ignored (with value 0) SQL_FETCH_LAST scroll to the last line, the parameter FetchOffset is ignored (with value 0) SQL_FETCH_ABSOLUTE scroll to the parameter FetchOffset specified absolute row SQL_FETCH_RELATIVE scroll from the current location to the parameter FetchOffset specified relative row, FetchOffset greater than 0 Indicates forward scrolling, Fetchoffset is less than 0 indicates backward scrolling
