Preparing and using statements

Statement preparation and Use statements
To continue our study, in this chapter, let's look at how to manipulate the data source through ODBC.

In the previous chapter, we have learned the first step, how to connect a data source. A connection defines a data channel for a user and a data source. It is static. If you want to manipulate the data source, we must use the statement (statement). You can think of a statement as a command to send a data source. This command must be written in SQL. By using statements, we can modify the structure of the data source, Execute the query, modify, and delete the data.

The preparation and use of statements can be divided into the following steps:
Assign a statement handle (statement handle) Create an SQL statement EXECUTE statement DESTROY statement assign a statement handle
You can assign a statement handle by calling the function Sqlallochandle. Examples are as follows:

. Data?
HSTMT DD?

. Code
......
Invoke Sqlallochandle, sql_handle_stmt, hconn, addr hstmt
Creating SQL statements
You're on your own. You have to know the syntax of SQL. For example, when you want to create a table, you have to know the CREATE TABLE statement.
EXECUTE statement
There are four methods of executing statements, whether they are compiled by the database Engine (ready) and how they are defined.
Direct Execution (Execution) defines the SQL statement by the user program. The statement is compiled and executed at run time (runtime). Compilation execution (Prepared Execution) is also defined by the User program SQL statement, But compilation and execution are divided into two steps: First, SQL is prepared (compiled), and then executed. In this way, we can compile the SQL statement once but execute the same statement multiple times to save time. Stored procedure (Procedures) SQL statements are compiled and stored in the data source. User programs can call these statements at run time. directory (Catalog) SQL is hard-coded (hardcoded) within the ODBC driver. The purpose of a catalog function is to return a predefined result set, such as all table names in a database. In general, user programs that catalog functions use to obtain information about a data source can call them at run time.
Each of these four methods has advantages and disadvantages. When you run only one SQL statement at a time, direct execution is a good choice; if you want to run a statement frequently, you should prefer compilation execution because the SQL statement is compiled only at the first run, and it will run faster in the next run because it has been compiled The stored procedure is the best choice for speed, because it has been compiled and stored in the data source. The disadvantage is that not all data stores support stored procedures. The directory is primarily used to obtain some information about the structure of the data source.

In this chapter, we'll look at direct execution and compilation execution, as they are done by our program. Writing stored procedures is a matter for the DBMS (database management System). And the catalog will be discussed later.
Direct execution
To execute the SQL statement directly and quickly, call the function SQLExecDirect with the following syntax:

SQLExecDirect Proto Statementhandle:dword,
Pstatementtext:dword,
Textlength:dword
Statementhandle. The statement handle pstatementtext to use. A pointer textlength to the SQL statement to execute. The length of the SQL statement.
The possible return values are as follows:
Sql_success operation Smooth Sql_success_with_info operation Smooth but may produce non-fatal error SQL_ERROR operation failure Sql_invalid_handle use of statement handle illegal sql_need_ Data will get this return value if it does not provide enough parameters before executing the SQL statement. You need to call the SQLParamData or SQLPutData function to submit the arguments. Sql_no_data if the SQL statement does not return a result set, for example, just a query action, This return value will be obtained. Let the user know that the action was successful, but no result set was returned. sql_still_executing if the SQL statement is executed asynchronously, SQLExecDirect returns the value immediately, indicating that the statement is executing. In general, if you are using a multithreaded operating system, asynchronous execution is a good idea. If you want to execute asynchronously, the old can set the statement properties by SQLSetStmtAttr.
Example:

. Data
SQLSTMT 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
Compile execution
The execution of SQL is divided into two steps: The first step is to prepare the * statement by calling the function SQLPrepare. The second step is to execute the statement by calling the SQLExecute function. When using compilation execution, we can invoke SQLExecute multiple times to execute the same statement. Using SQL parameters in conjunction, this method is extremely effective for executing the same statement.

SQLPrepare and SQLExecDirect Use the same three parameters, so the function prototype is no longer written here. SQLExecute Syntax:

SQLExecute Proto Statementhandle:dword

Just this one parameter, I think no longer need to explain;

Example:

. Data
SQLSTMT 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 sqlstmt
Invoke SQLExecute, hstmt

You might think that there is nothing better than direct execution of compile execution. The above example is not obvious. We need to know the parameters of the SQL statement to study it carefully.
Statement parameters (Statement Parameters)
The arguments here refer 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 that we're looking for a phone number for a clerk named "Bob," you can use the following SQL statement:

Select Telephoneno from employee where name= ' Bob '

The SQL statement worked as we had hoped. But what if we want to find another employee's phone number? If you don't use parameters, you'll have to write another SQL statement and then compile and execute it again.

Now we are not going to allow this inefficient behavior. We can use parameters to achieve our goals. In the above example, we have to replace the string/value with '? ' (called a parameter marker (parameter marker)). The SQL statement becomes this:

Select Telephoneno from employee where name=?

Now consider this question: How does an ODBC driver know what value to replace the parameter marker '? '? The answer is: we have to provide the value we need. This method is called a parameter binding (parameter binding). In a nutshell, it's the process of connecting a parameter marker to a variable in a user program. In the example above, we need to create a buffer to tell the ODBC driver When it requires a specific value for a parameter, it is obtained from the string buffer we provide. Once a parameter is bound to a variable, it remains 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.

Binding a parameter to a variable is implemented by calling the 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:dword
Statementhandle statement handle Parameternumber number of arguments, starting from 1. This is the method that ODBC uses to determine the parameter descriptor. If you have three parameters, the leftmost is the first argument, and the rightmost is the third argument. Inputoutputtype Indicates whether the parameter is a flag to enter or output. The input here means that 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 argument at the end of the operation. In most cases, we use parameters as input. Output parameters are often related to stored procedures. The two possible values for this parameter are: Sql_param_input, Sql_param_input_output and Sql_param_output (translator: should be three parameters, but the original text) valuetype Indicates the value or type of buffer that the user program will bind to the parameter. The possible type is a set of constants, beginning with Sql_c_. The SQL type of the ParameterType parameter. For example, if the SQL parameter is a text field, we'll fill in the value sql_char here. View the ODBC Programmer's Guide in MSDN for a complete list (ODBC Programmer ' s Reference). The length of the ColumnSize parameter. In other words, you can think of the length of the column (field) that is connected to the parameter marker. In our example, our parameter markers use standard values for column "name". If the column is defined as 20 bytes long, we should fill in the ColumnSize 20. Decimaldigits the number of columns that are connected to the parameter descriptor. Parametervalueptr A pointer to the buffer that contains the parameter data. Bufferlength the length of the buffer to which the parametervalueptr points. Pstrlenorindptr A pointer to a double word that contains one of the following: the length of the parameter contained in the buffer pointed to by PARAMETERVALUEPTR. Unless the type of the argument is a string or binary value, Otherwise, this value is ignored. Don't confuse it with bufferlength, and take a look at this example to see that the argument is a string that has 20 bytes wide. So we allocated a 21-byte long buffer and sent its address to parametervalueptr. Before calling the function SQLExecute, we put the string "Bob" in the buffer. This string has a 3-byte length, so we need to put the value 3 in the pstrlenorindptr point. Sql_nts. This argument is a 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 instead of the value obtained from the user program. It applies only to stored procedures that have defined default parameter values. Sql_data_at_exec. The data for the parameter will be transmitted by SQLPutData. Since the data may be too large to fit into memory (such as the entire file's data), we can tell the ODBC driver that we will replace it with SQLPutData.

You may say that there are too many pstrlenorindptr parameters, but usually we only use the first or third option.
Example:

. Data
SqlString db "Select Telephoneno from Employee where name=?", 0
Sample1 db "Bob", 0
Sample2 db "Mary", 0

. Data?
Buffer db DUP (?)
StrLen DD?

. Code
........
Invoke SQLPrepare, hstmt, addr sqlstring,sizeof sqlstring
Invoke SQLBindParameter, hstmt, 1, Sql_param_input, Sql_c_char, Sql_char, 0, addr buffer, sizeof buffer, addr StrLen
;===========================================
; The I Run
;===========================================
Invoke lstrcpy, addr buffer, addr Sample1
mov StrLen, sizeof Sample1
Invoke SQLExecute, hstmt
;===========================================
; Second Run
;===========================================
Invoke lstrcpy, addr buffer, addr Sample2
mov StrLen, sizeof Sample2
Invoke SQLExecute, hstmt


Note that we only bind the parameter to the buffer only once, when we modify the contents of the buffer and call the SQLExecute A lot of times. You don't have to call the Sqlprepare.odbc driver to know where to look for the arguments it needs because it's been told by the SQLBindParameter function.

But now we are not able to get the results of the query. Access and application result sets are the contents of the next chapter.

Assuming that we have completed a lot of SQL statements and are going to execute a new statement, there is no need to reassign a statement handle. Just use the sql_unbind and Sql_reset_params arguments to call the SQLFREESTMT function to unbind the arguments. Next you can then use the original statement handle to execute the SQL statement.
Release statement
Implemented by calling the Sqlfreehandle function.





The original title is preparing and Using statements, because the ODBC driver is numerous, has the explanation, compiles the execution the cent, therefore preparing not only refers to prepares, also has the precompilation meaning. Another: With respect to MSDN mentioned by the author, its ODBC part can be found in Visual Studio and its parts, such as
C:\program Files\devstudio Haredide\help\odbc.hlp.