1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
3
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
4
<title>Compiling An SQL Statement</title>
5
<style type="text/css">
8
font-family: Verdana, sans-serif;
13
a:visited { color: #734559 }
15
.logo { position:absolute; margin:3px; }
31
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
32
.toolbar a:visited { color: white; }
33
.toolbar a:hover { color: #044a64; background: white; }
35
.content { margin: 5%; }
36
.content dt { font-weight:bold; }
37
.content dd { margin-bottom: 25px; margin-left:20%; }
38
.content ul { padding:0px; padding-left: 15px; margin:0px; }
41
.se { background: url(../images/se.gif) 100% 100% no-repeat #044a64}
42
.sw { background: url(../images/sw.gif) 0% 100% no-repeat }
43
.ne { background: url(../images/ne.gif) 100% 0% no-repeat }
44
.nw { background: url(../images/nw.gif) 0% 0% no-repeat }
46
/* Things for "fancyformat" documents start here. */
47
.fancy img+p {font-style:italic}
48
.fancy .codeblock i { color: darkblue; }
49
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
50
.fancy h2 { margin-left: 10px }
51
.fancy h3 { margin-left: 20px }
52
.fancy h4 { margin-left: 30px }
53
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
54
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
55
.fancy #toc a { color: darkblue ; text-decoration: none }
56
.fancy .todo { color: #AA3333 ; font-style : italic }
57
.fancy .todo:before { content: 'TODO:' }
58
.fancy p.todo { border: solid #AA3333 1px; padding: 1ex }
59
.fancy img { display:block; }
60
.fancy :link:hover, .fancy :visited:hover { background: wheat }
61
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
62
.fancy li p { margin: 1em 0 }
63
/* End of "fancyformat" specific rules. */
69
<div><!-- container div to satisfy validator -->
71
<a href="../index.html">
72
<img class="logo" src="../images/sqlite370_banner.gif" alt="SQLite Logo"
74
<div><!-- IE hack to prevent disappearing logo--></div>
75
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
77
<table width=100% style="clear:both"><tr><td>
78
<div class="se"><div class="sw"><div class="ne"><div class="nw">
79
<table width=100% style="padding:0;margin:0;cell-spacing:0"><tr>
82
<a href="../about.html">About</a>
83
<a href="../sitemap.html">Sitemap</a>
84
<a href="../docs.html">Documentation</a>
85
<a href="../download.html">Download</a>
86
<a href="../copyright.html">License</a>
87
<a href="../news.html">News</a>
88
<a href="../support.html">Support</a>
91
gMsg = "Search SQLite Docs..."
92
function entersearch() {
93
var q = document.getElementById("q");
94
if( q.value == gMsg ) { q.value = "" }
95
q.style.color = "black"
96
q.style.fontStyle = "normal"
98
function leavesearch() {
99
var q = document.getElementById("q");
100
if( q.value == "" ) {
102
q.style.color = "#044a64"
103
q.style.fontStyle = "italic"
108
<div style="padding:0 1em 0px 0;white-space:nowrap">
109
<form name=f method="GET" action="http://www.sqlite.org/search">
110
<input id=q name=q type=text
111
onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
112
<input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
116
</div></div></div></div>
118
<div class=startsearch></div>
120
<a href="intro.html"><h2>SQLite C Interface</h2></a><h2>Compiling An SQL Statement</h2><blockquote><pre>int sqlite3_prepare(
121
sqlite3 *db, /* Database handle */
122
const char *zSql, /* SQL statement, UTF-8 encoded */
123
int nByte, /* Maximum length of zSql in bytes. */
124
sqlite3_stmt **ppStmt, /* OUT: Statement handle */
125
const char **pzTail /* OUT: Pointer to unused portion of zSql */
127
int sqlite3_prepare_v2(
128
sqlite3 *db, /* Database handle */
129
const char *zSql, /* SQL statement, UTF-8 encoded */
130
int nByte, /* Maximum length of zSql in bytes. */
131
sqlite3_stmt **ppStmt, /* OUT: Statement handle */
132
const char **pzTail /* OUT: Pointer to unused portion of zSql */
134
int sqlite3_prepare16(
135
sqlite3 *db, /* Database handle */
136
const void *zSql, /* SQL statement, UTF-16 encoded */
137
int nByte, /* Maximum length of zSql in bytes. */
138
sqlite3_stmt **ppStmt, /* OUT: Statement handle */
139
const void **pzTail /* OUT: Pointer to unused portion of zSql */
141
int sqlite3_prepare16_v2(
142
sqlite3 *db, /* Database handle */
143
const void *zSql, /* SQL statement, UTF-16 encoded */
144
int nByte, /* Maximum length of zSql in bytes. */
145
sqlite3_stmt **ppStmt, /* OUT: Statement handle */
146
const void **pzTail /* OUT: Pointer to unused portion of zSql */
148
</pre></blockquote><p>
149
To execute an SQL query, it must first be compiled into a byte-code
150
program using one of these routines.</p>
152
<p>The first argument, "db", is a <a href="../c3ref/sqlite3.html">database connection</a> obtained from a
153
prior successful call to <a href="../c3ref/open.html">sqlite3_open()</a>, <a href="../c3ref/open.html">sqlite3_open_v2()</a> or
154
<a href="../c3ref/open.html">sqlite3_open16()</a>. The database connection must not have been closed.</p>
156
<p>The second argument, "zSql", is the statement to be compiled, encoded
157
as either UTF-8 or UTF-16. The sqlite3_prepare() and sqlite3_prepare_v2()
158
interfaces use UTF-8, and sqlite3_prepare16() and sqlite3_prepare16_v2()
161
<p>If the nByte argument is less than zero, then zSql is read up to the
162
first zero terminator. If nByte is non-negative, then it is the maximum
163
number of bytes read from zSql. When nByte is non-negative, the
164
zSql string ends at either the first '\000' or '\u0000' character or
165
the nByte-th byte, whichever comes first. If the caller knows
166
that the supplied string is nul-terminated, then there is a small
167
performance advantage to be gained by passing an nByte parameter that
168
is equal to the number of bytes in the input string <i>including</i>
169
the nul-terminator bytes as this saves SQLite from having to
170
make a copy of the input string.</p>
172
<p>If pzTail is not NULL then *pzTail is made to point to the first byte
173
past the end of the first SQL statement in zSql. These routines only
174
compile the first statement in zSql, so *pzTail is left pointing to
175
what remains uncompiled.</p>
177
<p>*ppStmt is left pointing to a compiled <a href="../c3ref/stmt.html">prepared statement</a> that can be
178
executed using <a href="../c3ref/step.html">sqlite3_step()</a>. If there is an error, *ppStmt is set
179
to NULL. If the input text contains no SQL (if the input is an empty
180
string or a comment) then *ppStmt is set to NULL.
181
The calling procedure is responsible for deleting the compiled
182
SQL statement using <a href="../c3ref/finalize.html">sqlite3_finalize()</a> after it has finished with it.
183
ppStmt may not be NULL.</p>
185
<p>On success, the sqlite3_prepare() family of routines return <a href="../c3ref/c_abort.html">SQLITE_OK</a>;
186
otherwise an <a href="../c3ref/c_abort.html">error code</a> is returned.</p>
188
<p>The sqlite3_prepare_v2() and sqlite3_prepare16_v2() interfaces are
189
recommended for all new programs. The two older interfaces are retained
190
for backwards compatibility, but their use is discouraged.
191
In the "v2" interfaces, the prepared statement
192
that is returned (the <a href="../c3ref/stmt.html">sqlite3_stmt</a> object) contains a copy of the
193
original SQL text. This causes the <a href="../c3ref/step.html">sqlite3_step()</a> interface to
194
behave differently in three ways:</p>
198
If the database schema changes, instead of returning <a href="../c3ref/c_abort.html">SQLITE_SCHEMA</a> as it
199
always used to do, <a href="../c3ref/step.html">sqlite3_step()</a> will automatically recompile the SQL
200
statement and try to run it again.
204
When an error occurs, <a href="../c3ref/step.html">sqlite3_step()</a> will return one of the detailed
205
<a href="../c3ref/c_abort.html">error codes</a> or <a href="../c3ref/c_abort_rollback.html">extended error codes</a>. The legacy behavior was that
206
<a href="../c3ref/step.html">sqlite3_step()</a> would only return a generic <a href="../c3ref/c_abort.html">SQLITE_ERROR</a> result code
207
and the application would have to make a second call to <a href="../c3ref/reset.html">sqlite3_reset()</a>
208
in order to find the underlying cause of the problem. With the "v2" prepare
209
interfaces, the underlying reason for the error is returned immediately.
213
If the specific value bound to <a href="../lang_expr.html#varparam">host parameter</a> in the
214
WHERE clause might influence the choice of query plan for a statement,
215
then the statement will be automatically recompiled, as if there had been
216
a schema change, on the first <a href="../c3ref/step.html">sqlite3_step()</a> call following any change
217
to the <a href="../c3ref/bind_blob.html">bindings</a> of that <a href="../lang_expr.html#varparam">parameter</a>.
218
The specific value of WHERE-clause <a href="../lang_expr.html#varparam">parameter</a> might influence the
219
choice of query plan if the parameter is the left-hand side of a <a href="../lang_expr.html#like">LIKE</a>
220
or <a href="../lang_expr.html#glob">GLOB</a> operator or if the parameter is compared to an indexed column
221
and the <a href="../compile.html#enable_stat3">SQLITE_ENABLE_STAT3</a> compile-time option is enabled.
225
</p><p>See also lists of
226
<a href="objlist.html">Objects</a>,
227
<a href="constlist.html">Constants</a>, and
228
<a href="funclist.html">Functions</a>.</p>