1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
|
.Dd January 24, 2024
.Dt SQLITE3_PREPARE 3
.Os
.Sh NAME
.Nm sqlite3_prepare ,
.Nm sqlite3_prepare_v2 ,
.Nm sqlite3_prepare_v3 ,
.Nm sqlite3_prepare16 ,
.Nm sqlite3_prepare16_v2 ,
.Nm sqlite3_prepare16_v3
.Nd compiling an SQL statement
.Sh SYNOPSIS
.In sqlite3.h
.Ft int
.Fo sqlite3_prepare
.Fa "sqlite3 *db"
.Fa "const char *zSql"
.Fa "int nByte"
.Fa "sqlite3_stmt **ppStmt"
.Fa "const char **pzTail"
.Fc
.Ft int
.Fo sqlite3_prepare_v2
.Fa "sqlite3 *db"
.Fa "const char *zSql"
.Fa "int nByte"
.Fa "sqlite3_stmt **ppStmt"
.Fa "const char **pzTail"
.Fc
.Ft int
.Fo sqlite3_prepare_v3
.Fa "sqlite3 *db"
.Fa "const char *zSql"
.Fa "int nByte"
.Fa "unsigned int prepFlags"
.Fa "sqlite3_stmt **ppStmt"
.Fa "const char **pzTail"
.Fc
.Ft int
.Fo sqlite3_prepare16
.Fa "sqlite3 *db"
.Fa "const void *zSql"
.Fa "int nByte"
.Fa "sqlite3_stmt **ppStmt"
.Fa "const void **pzTail"
.Fc
.Ft int
.Fo sqlite3_prepare16_v2
.Fa "sqlite3 *db"
.Fa "const void *zSql"
.Fa "int nByte"
.Fa "sqlite3_stmt **ppStmt"
.Fa "const void **pzTail"
.Fc
.Ft int
.Fo sqlite3_prepare16_v3
.Fa "sqlite3 *db"
.Fa "const void *zSql"
.Fa "int nByte"
.Fa "unsigned int prepFlags"
.Fa "sqlite3_stmt **ppStmt"
.Fa "const void **pzTail"
.Fc
.Sh DESCRIPTION
To execute an SQL statement, it must first be compiled into a byte-code
program using one of these routines.
Or, in other words, these routines are constructors for the prepared statement
object.
.Pp
The preferred routine to use is
.Fn sqlite3_prepare_v2 .
The
.Fn sqlite3_prepare
interface is legacy and should be avoided.
.Fn sqlite3_prepare_v3
has an extra "prepFlags" option that is used for special purposes.
.Pp
The use of the UTF-8 interfaces is preferred, as SQLite currently does
all parsing using UTF-8.
The UTF-16 interfaces are provided as a convenience.
The UTF-16 interfaces work by converting the input text into UTF-8,
then invoking the corresponding UTF-8 interface.
.Pp
The first argument, "db", is a database connection
obtained from a prior successful call to
.Fn sqlite3_open ,
.Fn sqlite3_open_v2
or
.Fn sqlite3_open16 .
The database connection must not have been closed.
.Pp
The second argument, "zSql", is the statement to be compiled, encoded
as either UTF-8 or UTF-16.
The sqlite3_prepare(), sqlite3_prepare_v2(), and sqlite3_prepare_v3()
interfaces use UTF-8, and sqlite3_prepare16(), sqlite3_prepare16_v2(),
and sqlite3_prepare16_v3() use UTF-16.
.Pp
If the nByte argument is negative, then zSql is read up to the first
zero terminator.
If nByte is positive, then it is the number of bytes read from zSql.
If nByte is zero, then no prepared statement is generated.
If the caller knows that the supplied string is nul-terminated, then
there is a small performance advantage to passing an nByte parameter
that is the number of bytes in the input string \fIincluding\fP the nul-terminator.
.Pp
If pzTail is not NULL then *pzTail is made to point to the first byte
past the end of the first SQL statement in zSql.
These routines only compile the first statement in zSql, so *pzTail
is left pointing to what remains uncompiled.
.Pp
*ppStmt is left pointing to a compiled prepared statement
that can be executed using
.Fn sqlite3_step .
If there is an error, *ppStmt is set to NULL.
If the input text contains no SQL (if the input is an empty string
or a comment) then *ppStmt is set to NULL.
The calling procedure is responsible for deleting the compiled SQL
statement using
.Fn sqlite3_finalize
after it has finished with it.
ppStmt may not be NULL.
.Pp
On success, the sqlite3_prepare() family of routines return SQLITE_OK;
otherwise an error code is returned.
.Pp
The sqlite3_prepare_v2(), sqlite3_prepare_v3(), sqlite3_prepare16_v2(),
and sqlite3_prepare16_v3() interfaces are recommended for all new programs.
The older interfaces (sqlite3_prepare() and sqlite3_prepare16()) are
retained for backwards compatibility, but their use is discouraged.
In the "vX" interfaces, the prepared statement that is returned (the
sqlite3_stmt object) contains a copy of the original SQL
text.
This causes the
.Fn sqlite3_step
interface to behave differently in three ways:
.Bl -enum
.It
If the database schema changes, instead of returning SQLITE_SCHEMA
as it always used to do,
.Fn sqlite3_step
will automatically recompile the SQL statement and try to run it again.
As many as SQLITE_MAX_SCHEMA_RETRY retries will
occur before sqlite3_step() gives up and returns an error.
.It
When an error occurs,
.Fn sqlite3_step
will return one of the detailed error codes or extended error codes.
The legacy behavior was that
.Fn sqlite3_step
would only return a generic SQLITE_ERROR result code and
the application would have to make a second call to
.Fn sqlite3_reset
in order to find the underlying cause of the problem.
With the "v2" prepare interfaces, the underlying reason for the error
is returned immediately.
.It
If the specific value bound to a host parameter in the
WHERE clause might influence the choice of query plan for a statement,
then the statement will be automatically recompiled, as if there had
been a schema change, on the first
.Fn sqlite3_step
call following any change to the bindings of that parameter.
The specific value of a WHERE-clause parameter might influence
the choice of query plan if the parameter is the left-hand side of
a LIKE or GLOB operator or if the parameter is compared to
an indexed column and the SQLITE_ENABLE_STAT4 compile-time
option is enabled.
.El
.Pp
.Pp
sqlite3_prepare_v3() differs from sqlite3_prepare_v2() only in having
the extra prepFlags parameter, which is a bit array consisting of zero
or more of the SQLITE_PREPARE_* flags.
The sqlite3_prepare_v2() interface works exactly the same as sqlite3_prepare_v3()
with a zero prepFlags parameter.
.Sh IMPLEMENTATION NOTES
These declarations were extracted from the
interface documentation at line 4176.
.Bd -literal
SQLITE_API int sqlite3_prepare(
sqlite3 *db, /* Database handle */
const char *zSql, /* SQL statement, UTF-8 encoded */
int nByte, /* Maximum length of zSql in bytes. */
sqlite3_stmt **ppStmt, /* OUT: Statement handle */
const char **pzTail /* OUT: Pointer to unused portion of zSql */
);
SQLITE_API int sqlite3_prepare_v2(
sqlite3 *db, /* Database handle */
const char *zSql, /* SQL statement, UTF-8 encoded */
int nByte, /* Maximum length of zSql in bytes. */
sqlite3_stmt **ppStmt, /* OUT: Statement handle */
const char **pzTail /* OUT: Pointer to unused portion of zSql */
);
SQLITE_API int sqlite3_prepare_v3(
sqlite3 *db, /* Database handle */
const char *zSql, /* SQL statement, UTF-8 encoded */
int nByte, /* Maximum length of zSql in bytes. */
unsigned int prepFlags, /* Zero or more SQLITE_PREPARE_ flags */
sqlite3_stmt **ppStmt, /* OUT: Statement handle */
const char **pzTail /* OUT: Pointer to unused portion of zSql */
);
SQLITE_API int sqlite3_prepare16(
sqlite3 *db, /* Database handle */
const void *zSql, /* SQL statement, UTF-16 encoded */
int nByte, /* Maximum length of zSql in bytes. */
sqlite3_stmt **ppStmt, /* OUT: Statement handle */
const void **pzTail /* OUT: Pointer to unused portion of zSql */
);
SQLITE_API int sqlite3_prepare16_v2(
sqlite3 *db, /* Database handle */
const void *zSql, /* SQL statement, UTF-16 encoded */
int nByte, /* Maximum length of zSql in bytes. */
sqlite3_stmt **ppStmt, /* OUT: Statement handle */
const void **pzTail /* OUT: Pointer to unused portion of zSql */
);
SQLITE_API int sqlite3_prepare16_v3(
sqlite3 *db, /* Database handle */
const void *zSql, /* SQL statement, UTF-16 encoded */
int nByte, /* Maximum length of zSql in bytes. */
unsigned int prepFlags, /* Zero or more SQLITE_PREPARE_ flags */
sqlite3_stmt **ppStmt, /* OUT: Statement handle */
const void **pzTail /* OUT: Pointer to unused portion of zSql */
);
.Ed
.Sh SEE ALSO
.Xr sqlite3 3 ,
.Xr sqlite3_bind_blob 3 ,
.Xr sqlite3_finalize 3 ,
.Xr sqlite3_open 3 ,
.Xr sqlite3_reset 3 ,
.Xr sqlite3_step 3 ,
.Xr sqlite3_stmt 3 ,
.Xr SQLITE_OK 3 ,
.Xr SQLITE_PREPARE_PERSISTENT 3
|
