Prepare and use statements
Continue our study, in this chapter, let's see how to operate the data source through odbc.
In the previous chapter, we have learned the first step, how to connect a data source. A connection defines the data channel of the user and the data source. It is static. If you want to operate the data source, we must use the statement. You can think that the statement is a command to send the data source. This "command" must be used SQL is written. By using the statement, we can modify the structure of the data source, perform queries, modify, and delete data.
Preparation and use of statements can be divided into the following steps:
Assign a statement handle (statement handle) Creating a SQL statement to execute a statement destruction statement assigning a statement handle to assign a statement handle by calling a function sqlallochandle. The examples are as follows: .data? Hstmt dd? .Data? Invoke sqlallochandle, sql_handle_stmt, hconn, addr HSTMT Create SQL statement This section You have to rely on yourself you must know the syntax of SQL. For example, when you want When you create a table, you have to know the CREATE TABLE statement. Execute the statement execution statement has four methods, whether they are compiled by the database engine (ready) and how to define it.
Direct Execution Defines the SQL statement by the user program. Statement is compiled and executed in runtime. Compilation execution is also defined by the user program defines the SQL statement, but compiling and executing is divided into two steps: first SQL is ready (compiled), which is performed next. With this method, we can only compile the SQL statement once, but perform the same statement multiple times to save time. Stored procedure SQL statement is compiled and stored in data Inside the source, the user program can call these statements at runtime. Directory (Catalog) SQL is hard coded in the ODBC driver. The purpose of the directory function is to return a predefined result set, for example, all the table names in the database. Total For the directory function, the user program used to obtain the data source can call them at runtime. These four methods have advantages and disadvantages. When you only run a SQL statement, directly execute is a good choice If you want to run a certain statement, you should prefer to compile execution because the SQL statement is compiled only when the first runtime is running, and it will run faster in the next run, because it has been compiled; stored procedure It is the best choice for focusing because it has been compiled and stored in the data source. The disadvantage is that all data stores support stored procedures. The directory is mainly used to obtain some information of the data source structure. In this chapter, we mainly look at the direct execution and compilation execution because they are done by our program. Writing the stored procedure is a DBMS (Database Management System). The directory will be discussed later. Direct execution to be directly executed And quickly execute the SQL statement, call the function SQLEXECDirect as follows:
SQLEXECDIRECT Proto StatementHandle: DWORD, PStatementText: DWORD, TEXTLENGTH: DWORD
STATEMENTHANDLE. The statement handle PStateText is pointing to the length of the pointer TEXTLENGTH. SQL statement to the SQL statement. The possible return value is as follows:
SQL_SUCCESS operation Success SQL_SUCCESS_WITH_INFO operation is smooth, but may generate a non-ended error SQL_ERROR operation failed SQL_INVALID_HANDLE use of the statement handle illegal SQL_NEED_DATA If there is no sufficient parameter before executing the SQL statement, this return value will be obtained. At this time, you need to call SQLParamData or SqlputData functions. Parameters. SQL_NO_DATA If the SQL statement does not return a result set, for example, this return value will be obtained. The user knows that the action is successful, but no result set is returned. SQL_STILL_EXECUTING If the SQL statement is executed asynchronously, SQLEXECDire will return this value immediately, indicating that the statement is being executed. In general, if you use a multi-threaded operating system, asynchronous execution is a good idea. If you want to execute asynchronously, you can set the statement properties through SQLSETSTMTATTR. Examples: .dataSQLStmt db "select * from Sales", 0 .data hStmt dd .code ..... invoke SQLAllocHandle, SQL_HANDLE_STMT, hConn, addr hStmt.if ax == SQL_SUCCESS || ax == SQL_SUCCESS_WITH_INFO invoke SQLExecDirect,?? HSTMT, AddR SQLSTMT, SIZEOF SQLSTMT Compilation Perform SQL's execution is divided into two steps: The first step must be prepared by calling the function sqlpreprePrePrePreprePrePrePrePrePrePrePrePrePrePrePrePrePrePare. Step 2, by calling the SQLExecute function. When using compilation execution, we can call SQLEXECUTE multiple times to perform the same statement. Combine the SQL parameter, this method is extremely valid for the same statement. SqlpRepare is using the same three parameters as SQLExecdirect, so the function prototype is no longer written here. SQLEXECUTE Syntax: Sqlexecute Proto StatementHandle: DWORD
I only need this parameter, I don't want to explain it;) example: .datasqlstmt DB "SELECT * from sales", 0 .data? Hstmt dd? .Code ..... INVOKE SQLALLOCHANDLE, SQL_HANDLE_STMT, HCONN, AddR HSTMT .IF AX == SQL_Success || AX == SQL_SUCCESS_WITH_INFO INVOKE SQLPREPARE, HSTMT, ADDR SQLSTMT, SIZEOF SQLSTMTINVOKE SQLEXECUTE, HSTMT You may think that there is no strength to execute directly for direct execution. The above example is still not obvious. We need to know the parameters of the SQL statement to carefully study it. Statement parameters Here the parameters here refers to the variables used by the SQL statement. For example, we have a table called "Employee", which has three fields: "name", "Surname" and "Telephoneno". Now we have to find a telephone number called "Bob", you can use the following SQL statement:
SELECT Telephoneno from Employee Where Name = 'Bob'
This SQL statement is working like the hope. But what if we want to find another staff member? If you don't use the parameters, then write a SQL statement, then compile it again, execute it. Now we will not allow this inefficient behavior. We can use parameters to achieve your goals. In the above example, we must replace the string / value to '' '' '(called Parameter Marker). SQL statement will turn this: SELECT TELEPHONEENO from Employee Where name =?
Now think about this question: How do the ODBC driver know what value is used to replace the parameter flag '?'? The answer is: We must provide the value you need. This method is called parameter binding. Simple point It is the process of establishing a connection with variables in the user program with variables in the user program. In the above example, we need to create a buffer to tell the ODBC driver, which will provide from us when it requires a specific value of a parameter The string buffer is obtained. Once a parameter is binded to a variable, it will remain bound until it is bound to another variable, or until all parameters are released by the function SQLFreeStmt (function) parameter SQL_RESET_PARAMS, or until the statement is released. One Parameter binding to a variable is implemented by calling a function SQLBINDPARAMETER, the syntax is as follows:
SQLBindParameter proto StatementHandle: DWORD, ParameterNumber: DWORD, InputOutputType: DWORD, ValueType: DWORD, ParameterType: DWORD, ColumnSize: DWORD, DecimalDigits: DWORD, ParameterValuePtr: DWORD, BufferLength: DWORD, pStrLenOrIndPtr: ParameterNumber number of parameters DWORD StatementHandle statement handle by 1 start. This is the method of odbc used to determine the parameter descriptor. If there are three parameters, the leftmost is the first parameter, the rightmost side is the third parameter. INPUTOUTPUTTYPE indicates that the parameter is used to input or output a logo. Here, the ODBC driver will use the value obtained in the parameter, and the output means that the ODBC driver will put the result in the parameter at the end of the operation. Most cases We use parameters as input. The output parameters are often related to the stored procedure. The two possible values of this parameter are: SQL_PARAM_INPUT, SQL_PARAM_INPUT_OUTPUT and SQL_PARAM_OUTPUT (Translator: It should be three parameters, but the description) ValueType indicates the value of the user program to bind to the parameter or The type of buffer is buffer. Possible types are a set of constants, starting with SQL_C_. The SQL type of ParameterType parameter. For example, if the SQL parameter is a text field, we fill in the value SQL_CHAR here. View the ODBC programmers guide in the MSDN to get a full list (ODBC Programmer's Reference). The length of the columnsize parameter. In other words, it is considered to be the length of the column (field) connected to the parameter marker. In our example, our parameter flags use the standard value for the column "name". If the column is defined 20 words Slim, we should fill in 20. DecimalDigits and the number of columns connected to the parameter descriptor. ParameterValuePTR points to the pointer to the buffer containing the parameter data. BufferLength is pointed to by ParameterValuePtr. Pstrlenorindptr pointing to a double The word pointer contains one of the following:
The length of the parameter contained in the buffer pointing by ParameterValuePtr. Unless the type of parameters is a string or binary value, this value is ignored. Don't confuse it with BufferLength, look at this example will understand: assuming this parameter is a character String, the column has 20 bytes wide. So we allocate a 21-byte long buffer and transfer its address to ParameterValuePtr. Before calling the function SQLEXECUTE, we put the string "bob" in the buffer. This string has 3 bytes long, so we need to put this 3 value in the double word pointing to PSTRlenorindptr. SQL_NTS. This parameter is One 0 end string (NULL-TERMINATED STRING). SQL_NULL_DATA. The parameter value is null. SQL_DEFAULT_PARAM. The stored procedure will use the default value of the parameter, not the value obtained from the user program. It only applies to default parameter values The stored procedure. The data of the parameter will be transmitted by SQLPutData. Since the data may not be placed in memory (such as the entire file), we can tell the ODBC driver we will replace with SQLPutdata. Mustbe you will say that PSTRLENORINDPTR is too parameter More, but usually, we will only use the first or third options.
Example: .datasqlstring DB "SELECT TELEPHONEENO from EMPLOYEE WHERE NAME =?", 0Sample1 DB "Bob", 0Sample2 DB "Mary", 0 .data? Buffer DB 21 DUP (?) Strlen Dd? Code ...... ..invoke SQLPrepare, hStmt, addr SQLString, sizeof SQLString invoke SQLBindParameter, hStmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 20, 0, addr buffer, sizeof buffer, addr StrLen; ============ =============================== ========================= ===================================================================================================================================== =====================================; second run; ========= ==========================================================================================================================================00 We only bind only the parameters and the buffer once, when we modify the contents of the buffer and call SQLExecute many times. Do not need to call the sqlprepare.odbc driver know to find the parameters it needs because of the need to tell the SQLBINDPARAMETER function It is. But now we can't get the results of the query. Access and Applicable Results is the content of the next chapter.
Suppose we have completed a lot of SQL statements and perform a new statement, then it is not necessary to redistribute a statement handle. Just use SQL_UNBIND and SQL_RESET_PARAMS parameters to invoke the SQLFreeStmt function to release the binding of the parameters. Next Then use the original statement handle to execute the SQL statement. Release statement is implemented by calling the SQLFreeHandle function. Translation: Original title is preplaying and using statements, because the ODBC driver has a wide range, interpretation, compilation, compilation, but prepaaring not only And there is also a pre-compiled meaning. Also: About the MSDN mentioned by the author, its ODBC section can be found in Visual Studio and its components, such as C: / Program Files / DevStudio / Sharedide / Help / ODBC.HLP.
