📄 odbc3.html
字号:
<html>
<head>
<title>ODBC Programming Tutorial: Preparing and Using Statements</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#000000" text="#FFFFFF" link="#FFFFCC" vlink="#FFCCCC" alink="#CCFFCC">
<h1 align="center"><font face="Tahoma" color="#FFFFCC">Preparing and Using Statements</font></h1>
<p><font face="Tahoma" size="-1">In this tutorial, we will further our study of
ODBC programming. Specifically, we will learn how to interact with the data
source via ODBC.</font></p>
<p><font face="Tahoma" size="-1">In the previous tutorial, you know how to make
a connection to a data source. It's the first step. A connection defines a data
path between you and the data source. It's passive. In order to interact with
the data source, you need to use statement. You can think of a statement as
a command you send to a data source. The "command" must be written
in SQL. With statement, you can modify the structure of the data source, query
it for data, update data and delete data.</font></p>
<p><font face="Tahoma" size="-1">The steps in preparing and using statement are
as follows:</font></p>
<ol>
<li><font face="Tahoma" size="-1">Allocate a statement handle</font></li>
<li><font face="Tahoma" size="-1">Create the SQL statement</font></li>
<li><font face="Tahoma" size="-1">Execute the statement</font></li>
<li><font face="Tahoma" size="-1">Destroy the statement</font></li>
</ol>
<h4><font face="Tahoma" color="#FFFFCC">Allocate a statement handle</font></h4>
<p><font face="Tahoma" size="-1">You allocate a statement handle by calling<font color="#FFFFCC"><b>
SQLAllocHandle</b></font>, passing it the appropriate arguments. For example,</font></p>
<p><font face="Tahoma" size="-1"><b><font color="#33CCCC">.data?<br>
hStmt dd ?</font></b></font></p>
<p><font face="Tahoma" size="-1" color="#33CCCC"><b>.code<br>
......<br>
invoke SQLAllocHandle, SQL_HANDLE_STMT, hConn,
addr hStmt</b></font></p>
<h4><font face="Tahoma" color="#FFFFCC">Create the SQL statement</font></h4>
<p><font face="Tahoma" size="-1">This part, you have to help yourself. You must
learn about SQL grammar. For example, if you want to create a table, you need
to understand <font color="#CCFFCC"><b>CREATE TABLE</b></font>. </font></p>
<h4><font face="Tahoma" color="#FFFFCC">Execute the statement</font></h4>
<p><font face="Tahoma" size="-1">There are four ways of executing a statement,
depending on when they are compiled (prepared) by the database engine and who
defines them.</font></p>
<table border="1" cellspacing="1" cellpadding="3" align="center">
<tr>
<td bgcolor="#000099"><b><font face="Tahoma" size="-1">Direct Execution</font></b></td>
<td bgcolor="#003333"><font face="Tahoma" size="-1">Your program defines the
SQL statement. The statement is compiled and executed at runtime in one
step.</font></td>
</tr>
<tr>
<td bgcolor="#000099"><b><font face="Tahoma" size="-1">Prepared Execution</font></b></td>
<td bgcolor="#003333"><font face="Tahoma" size="-1">Your program also defines
the SQL statement. However, the preparation and execution are divided into
two steps: first the SQL statement is prepared (compiled) and then it is
executed. With this method, you can compile the SQL statement once and then
execute the same SQL statement multiple times. It saves time.</font></td>
</tr>
<tr>
<td bgcolor="#000099"><b><font face="Tahoma" size="-1">Procedures</font></b></td>
<td bgcolor="#003333"><font face="Tahoma" size="-1">The SQL statements are
compiled and stored at the data source. Your program calls those statements
at run time.</font></td>
</tr>
<tr>
<td bgcolor="#000099"><font face="Tahoma" size="-1"><b>Catalog</b></font></td>
<td bgcolor="#003333"><font face="Tahoma" size="-1">The SQL statements are
hardcoded into the ODBC driver. The purpose of catalog functions is to return
predefined result sets such as the names of the tables in the database.
All in all, catalog functions are used to obtain information about the data
source. Your program calls them at run time.</font></td>
</tr>
</table>
<p><font face="Tahoma" size="-1">These four methods have their pros and cons.
Direct execution is good when you run a particular SQL statement only once.
If you need to run a particular statement several times in succession, you'd
better use prepared execution because the SQL statement will be compiled the
first time it runs. On subsequent runs, it will execute faster because the statement
is already compiled. Procedures are the best choices if you want speed. Because
the procedures are already compiled and stored at the data source, they run
fast. The downside is that not all data stores support procedures. Catalog is
for obtaining information about the structure of the data source.</font></p>
<p><font face="Tahoma" size="-1">In this tutorial, we will focus on direct execution
and prepared execution because they are done on our application's side. Writing
procedures (stored procedures) are DBMS-specific. We will make use of catalog
functions in the future.</font></p>
<h4><font face="Tahoma" color="#FFFFCC">Direct Execution</font></h4>
<p><font face="Tahoma" size="-1">To execute your SQL statement directly and instantly,
call SQLExecDirect which has the following syntax:</font></p>
<blockquote>
<pre><font face="Tahoma"><b><font color="#33CCCC">SQLExecDirect proto StatementHandle:DWORD,<br> pStatementText:DWORD,<br> TextLength:DWORD</font></b></font></pre>
</blockquote>
<ul>
<li><font color="#FFCCFF" face="Tahoma"><b><font size="-1">StatementHandle</font></b></font><font face="Tahoma" size="-1">.
The handle to the statement you want to use</font></li>
<li><font color="#FFCCFF" face="Tahoma"><b><font size="-1">pStatementText</font></b></font><font face="Tahoma" size="-1">.
The pointer to the SQL string you want to execute</font></li>
<li><font color="#FFCCFF" face="Tahoma"><b><font size="-1">TextLength</font></b></font><font face="Tahoma" size="-1">.
The length of the SQL string.</font></li>
</ul>
<p><font face="Tahoma" size="-1">The possible return values are:</font></p>
<table border="1" cellspacing="1" cellpadding="3" align="center">
<tr>
<td bgcolor="#003399"><b><font face="Tahoma" size="-1">SQL_SUCCESS</font></b></td>
<td bgcolor="#333300"><font face="Tahoma" size="-1">The operation is successful.</font></td>
</tr>
<tr>
<td bgcolor="#003399"><b><font face="Tahoma" size="-1">SQL_SUCCESS_WITH_INFO</font></b></td>
<td bgcolor="#333300"><font face="Tahoma" size="-1">The operation is successful
and it may encounter a non-fatal error.</font></td>
</tr>
<tr>
<td bgcolor="#003399"><b><font face="Tahoma" size="-1">SQL_ERROR</font></b></td>
<td bgcolor="#333300"><font face="Tahoma" size="-1">The operation failed</font></td>
</tr>
<tr>
<td bgcolor="#003399"><b><font face="Tahoma" size="-1">SQL_INVALID_HANDLE</font></b></td>
<td bgcolor="#333300"><font face="Tahoma" size="-1">The statement handle you
gave to the function was invalid</font></td>
</tr>
<tr>
<td bgcolor="#003399"><b><font face="Tahoma" size="-1">SQL_NEED_DATA</font></b></td>
<td bgcolor="#333300"><font face="Tahoma" size="-1">If the SQL statement included
one or more parameters and you failed to supply them before execution, you'll
get this return value. You then need to submit the parameters via <font color="#FFFFCC"><b>SQLParamData</b></font>
or <font color="#FFFFCC"><b>SQLPutData</b></font>.</font></td>
</tr>
<tr>
<td bgcolor="#003399"><b><font face="Tahoma" size="-1">SQL_NO_DATA</font></b></td>
<td bgcolor="#333300"><font face="Tahoma" size="-1">If your SQL statement
doesn't return a result set, ie. it's just an action query, you get this
value so you know that the action completed successfully but no result set
is returned.</font></td>
</tr>
<tr>
<td bgcolor="#003399"><b><font face="Tahoma" size="-1">SQL_STILL_EXECUTING</font></b></td>
<td bgcolor="#333300"><font face="Tahoma" size="-1">If you execute the statement
asynchronously, <font color="#FFFFCC"> <b>SQLExecDirect</b></font> returns
immediately with this value, indicating that the statement is being processed.
By default, ODBC drivers operate in synchronous mode which is good if you
use a multithreading OS. If you want to execute asynchronously, you can
set the statement attribute with <font color="#FFFFCC"><b>SQLSetStmtAttr</b></font>.</font></td>
</tr>
</table>
<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>
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
SQLExecDirect, hStmt, addr SQLStmt, sizeof SQLStmt</font></b></font></font></p>
<h4><font face="Tahoma" color="#FFFFCC">Prepared Execution</font></h4>
<p><font face="Tahoma" size="-1">The process of executing an SQL statement is
divided into two distince phases. In the first phase, you must *prepare* the
statement by calling <font color="#FFFFCC"><b>SQLPrepare</b></font>. Next, you
call <font color="#FFFFCC"><b>SQLExecute</b></font> to actually run the statement.
With prepared execution, you can call <font color="#FFFFCC"><b>SQLExecute</b></font>
on the same SQL statement any number of times. Combined with using SQL parameters,
this method is very effective for running the same SQL statement over and over
again.</font></p>
<p><font face="Tahoma" size="-1"><b><font color="#FFFFCC">SQLPrepare</font></b>
takes the same three parameters as <font color="#FFFFCC"><b>SQLExecDirect</b></font>
so I won't show its definition here. <font color="#FFFFCC"><b>SQLExecute</b></font>
has the following syntax:</font></p>
<blockquote>
<pre><font face="Tahoma"><b><font color="#33CCCC">SQLExecute proto StatementHandle:DWORD</font></b></font></pre>
</blockquote>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -