📄 odbc3.html
字号:
<p><font face="Tahoma" size="-1">With only one parameter, I don't think it needs
explanation ;)</font></p>
<p><font face="Tahoma" size="-1" color="#33CCCC"><b>Example:</b></font></p>
<p><font color="#33CCCC" face="Tahoma"><b><font size="-1">.data<br>
SQLStmt db "select * from Sales",0</font></b></font></p>
<p><font face="Tahoma" size="-1"><b><font color="#33CCCC">.data?<br>
hStmt dd ?</font></b></font></p>
<p><font color="#33CCCC" face="Tahoma"><b><font size="-1">.code<br>
.....<br>
</font></b></font><font face="Tahoma" size="-1" color="#33CCCC"><b> invoke
SQLAllocHandle, SQL_HANDLE_STMT, hConn, addr hStmt</b></font><font face="Tahoma"><br>
<font size="-1"><b><font color="#33CCCC"> .if
ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO </font></b></font><br>
<font color="#33CCCC"><b><font size="-1"> invoke
SQLPrepare, hStmt, addr SQLStmt, sizeof SQLStmt<br>
invoke SQLExecute, hStmt</font></b></font></font></p>
<p><font face="Tahoma" size="-1">You may wonder what the advantage of prepared
execution over direct execution is. From the above examples, it's not apparent.
We need to know about statement parameters to be able to appreciate it fully.</font></p>
<h4><font face="Tahoma" color="#FFFFCC">Statement Parameters</font></h4>
<p><font face="Tahoma" size="-1">Parameters, as mentioned here, are variables
that are used by SQL statements. For example, if we have a table named "employee"
that has three fields, "name", "surname", and "TelephoneNo"
and we need to find the telephone no. of an employee named "Bob",
we can use the following SQL statement:</font></p>
<blockquote>
<pre><font face="Tahoma"><b><font color="#CCFFCC">select telephoneNo from employee where name='Bob'</font></b></font></pre>
</blockquote>
<p><font face="Tahoma" size="-1">This SQL statement works the way we want but
what if you want to find the telephone no. of another employee? If you don't
use a parameter, you don't have any choice but to construct a new SQL string
and compile/run it again.</font></p>
<p><font face="Tahoma" size="-1">Now let's say we can't tolerate this inefficiency
anymore. We can use a parameter to our advantage. In our example above, we must
replace the string/value with ? (called parameter marker). The SQL string will
be:</font></p>
<blockquote>
<pre><font face="Tahoma"><b><font color="#CCFFCC">select telephoneNo from employee where name=?</font></b></font></pre>
</blockquote>
<p><font face="Tahoma" size="-1">Think a bit about this: How can the ODBC driver
know what value it should put in place of the parameter marker? The answer:
we must supply it with the desired value. The method is called <font color="#CCFFCC"><b>parameter
binding</b></font>. Simply, it's the process of associating a parameter marker
to a variable in your application. In the above example, we need to create a
string buffer and then tell the ODBC driver that when it needs the actual value
of a parameter, it should obtain the value from the string buffer we provided.
Once a parameter is bound to a variable, it remains bound to that variable until
it's bound to a different variable, or until all parameters are unbound by calling
<font color="#FFFFCC"> <b>SQLFreeStmt</b></font> with<font color="#CCFFCC"><b>
SQL_RESET_PARAMS</b></font> or until the statement is released.</font></p>
<p><font face="Tahoma" size="-1">You bind a parameter to a variable by calling
<font color="#FFFFCC"><b>SQLBindParameter</b></font> which has the following
syntax:</font></p>
<ul>
<li>
<pre><font face="Tahoma"><b><font color="#CCFFCC">SQLBindParameter proto StatementHandle:DWORD,<br> ParameterNumber:DWORD,<br> InputOutputType:DWORD,<br> ValueType:DWORD,<br> ParameterType:DWORD,<br> ColumnSize:DWORD,<br> DecimalDigits:DWORD,<br> ParameterValuePtr:DWORD,<br> BufferLength:DWORD,<br> pStrLenOrIndPtr:DWORD</font></b></font></pre>
</li>
<li><font color="#FFCCFF" face="Tahoma" size="-1"><b>StatementHandle</b></font><font face="Tahoma" size="-1">
The handle of the statement.</font></li>
<li><font color="#FFCCFF" face="Tahoma" size="-1"><b>ParameterNumber </b></font><font face="Tahoma" size="-1">The
parameter number, starting from 1. This is the way ODBC uses to identify the
parameter markers. If there are three parameters, the leftmost one is parameter
no.1 and the rightmost is parameter no.3</font></li>
<li><font color="#FFCCFF" face="Tahoma" size="-1"><b>InputOutputType </b></font><font face="Tahoma" size="-1">A
flag to specify whether this parameter is for input or output. Input here
means the ODBC driver grabs the value in the parameter for its own use while
Output means the ODBC driver will place the result in the parameter when the
operation was done. Most of the time, we use parameter for input. Output parameter
is usually associated with stored procedures. Two possible values are: <font color="#CCFFCC"><b>SQL_PARAM_INPUT</b></font>,
<font color="#CCFFCC"> <b>SQL_PARAM_INPUT_OUTPUT</b></font> and <font color="#CCFFCC"><b>SQL_PARAM_OUTPUT</b></font></font></li>
<li><font color="#FFCCFF" face="Tahoma" size="-1"><b>ValueType</b></font><font face="Tahoma" size="-1">
Specify the type of the variable or buffer in your application, that you want
to bind to the parameter. There is a list of constants for the available types.
Their names begin with <font color="#CCFFCC"><b>SQL_C_</b></font></font></li>
<li><font color="#FFCCFF" face="Tahoma" size="-1"><b>ParameterType</b></font><font face="Tahoma" size="-1">
Specify the SQL type of the parameter. For example, if the SQL parameter is
a text field, you must put the value <font color="#CCFFCC"><b>SQL_CHAR</b></font>
here. You need to look up the complete list from ODBC Programmer's reference
on MSDN.</font></li>
<li><font color="#FFCCFF" face="Tahoma" size="-1"><b>ColumnSize</b></font><font face="Tahoma" size="-1">
The size of the parameter or you can think of this field as the size of the
column (field) associated with the parameter marker. In our example, our parameter
marker is used as a criteria on the column "name". If that column
is defined as 20 bytes in size, you should pass 20 in <font color="#FFCCFF"><b>ColumnSize</b></font>.</font></li>
<li><font color="#FFCCFF" face="Tahoma" size="-1"><b>DecimalDigits </b></font><font face="Tahoma" size="-1">The
number of decimal places of the column associated with the parameter marker.</font></li>
<li><font color="#FFCCFF" face="Tahoma" size="-1"><b>ParameterValuePtr</b></font><font face="Tahoma" size="-1">
A pointer to a buffer for the parameter's data. </font></li>
<li><font color="#FFCCFF" face="Tahoma" size="-1"><b>BufferLength</b></font><font face="Tahoma" size="-1">
The size of the buffer pointed to by <font color="#FFCCFF"><b>ParameterValuePtr</b></font>.</font></li>
<li><font size="-1" color="#FFCCFF" face="Tahoma"><b>pStrLenOrIndPtr</b></font><font face="Tahoma" size="-1">
This is a pointer to a dword variable that contains one of the following values:</font>
<ul>
<li><font face="Tahoma" size="-1" color="#FFFFCC">The length of the parameter
value stored in the buffer pointed to by ParameterValuePtr</font><font face="Tahoma" size="-1">.
This value is ignored unless the parameter type is a text string or binary.
Don't confuse this with <font color="#FFCCFF"><b>BufferLength</b></font>.
An example would make it clear. Suppose the parameter is a text string.
The column is 20 bytes wide. So you allocate a 21-byte buffer and pass
its address to <font color="#FFCCFF"><b>ParameterValuePtr</b></font>.
Just before calling <font color="#FFFFCC"><b>SQLExecute</b></font>, you
put the string "Bob" into the buffer. This string is 3 bytes
long thus you need to put the value 3 in the variable pointed to by <font color="#FFCCFF"><b>pStrLenOrIndPtr</b></font>.</font></li>
<li><font face="Tahoma" size="-1" color="#FFFFCC">SQL_NTS</font><font face="Tahoma" size="-1">.
The parameter value is a null-terminated string. </font></li>
<li><font face="Tahoma" size="-1" color="#FFFFCC">SQL_NULL_DATA</font><font face="Tahoma" size="-1">.
The parameter value is NULL. </font></li>
<li><font face="Tahoma" size="-1" color="#FFFFCC">SQL_DEFAULT_PARAM</font><font face="Tahoma" size="-1">.
A procedure is to use the default value of a parameter, rather than a
value retrieved from the application. This is applicable only to stored
prcoedures which have default parameter values defined.</font></li>
<li><font face="Tahoma" size="-1" color="#FFFFCC">SQL_DATA_AT_EXEC</font><font face="Tahoma" size="-1">.
The data for the parameter will be sent with <font color="#FFFFCC"><b>SQLPutData</b></font>.
Since the data may be too large to hold in memory such as the data in
a whole file, you tell the ODBC driver that you will send the data to
it via <font color="#FFFFCC"><b>SQLPutData</b></font> instead.<br>
<br>
So many possible values for<font color="#FFCCFF"><b> pStrLenOrIndPtr</b></font>,
you would surely say. But mostly, we will use only the first or third
option</font></li>
</ul>
</li>
</ul>
<p><font face="Tahoma" size="-1"><b><font color="#33CCCC">Example:</font></b></font></p>
<p><font color="#33CCCC" face="Tahoma"><b><font size="-1">.data<br>
SQLString db "select telephoneNo from employee where name=?",0<br>
Sample1 db "Bob",0<br>
Sample2 db "Mary",0</font></b></font></p>
<p><font color="#33CCCC" face="Tahoma"><b><font size="-1">.data?<br>
buffer db 21 dup(?)<br>
StrLen dd ?</font></b></font></p>
<p><font color="#33CCCC" face="Tahoma"><b><font size="-1">.code<br>
........<br>
invoke SQLPrepare, hStmt, addr SQLString,sizeof
SQLString <br>
invoke SQLBindParameter, hStmt, 1, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_CHAR, 20, 0, addr buffer, sizeof buffer, addr StrLen<br>
;===========================================<br>
; First run<br>
;=========================================== <br>
invoke lstrcpy, addr buffer, addr Sample1<br>
mov StrLen, sizeof Sample1<br>
invoke SQLExecute, hStmt<br>
;===========================================<br>
; Second run<br>
;=========================================== <br>
invoke lstrcpy, addr buffer, addr Sample2<br>
mov StrLen, sizeof Sample2<br>
invoke SQLExecute, hStmt<br>
</font></b></font></p>
<p><font face="Tahoma" size="-1">Note that we bind the parameter to a buffer only
once and then we modify the content of the buffer and call <font color="#FFFFCC"><b>SQLExecute
</b></font>several times. No need to call <font color="#FFFFCC"><b>SQLPrepare</b></font>
again. The ODBC driver knows where to find the parameter value it needs because
we "told" it by calling <font color="#FFFFCC"><b>SQLBindParameter</b></font>.</font></p>
<p><font face="Tahoma" size="-1">We will ignore the records returned by the query
for now. Accessing and using the result set is the subject of the future tutorials.</font></p>
<p><font face="Tahoma" size="-1">Supposed that you're done with a particular SQL
statement and you want to execute a new statement, you need not allocate a new
statement handle. You should unbind the parameters (if any) by calling <font color="#FFFFCC"><b>SQLFreeStmt</b></font>
with <font color="#CCFFCC"><b>SQL_UNBIND</b></font> and <font color="#CCFFCC"><b>SQL_RESET_PARAMS</b></font>.
Then you can "reuse" the statement handle for the next SQL statement.</font>
</p>
<h4><font face="Tahoma" color="#FFFFCC">Freeing the statement</font> </h4>
<p><font face="Tahoma" size="-1">It's done by calling <font color="#FFFFCC"><b>SQLFreeHandle</b></font>.</font></p>
<hr>
<p align="center"><font face="Tahoma" size="-1"><b>[<a href="http://win32asm.cjb.net">Iczelion's
Win32 Assembly Homepage</a>]</b></font></p>
<p> </p>
</body>
</html>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -