~ubuntu-branches/ubuntu/wily/sqlite3/wily

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

113

</form>

114

</div>

115

</table>

116

</div></div></div></div>

117

</td></tr></table>

118

119

120

121

<h1 align=center>An Introduction To The SQLite C/C++ Interface</h1>

122

123

<p>

124

This article provides an overview and roadmap to the C/C++ interface

125

to SQLite.

126

</p>

127

128

<p>

129

Early versions of SQLite were very easy to learn since they only

130

supported 5 C/C++ interfaces. But as SQLite has grown in capability,

131

new C/C++ interfaces have been added so that now there

132

are over 185 distinct APIs. This can be overwhelming to a new programmer.

133

Fortunately, most of the C/C++ interfaces in SQLite are very specialized

134

and never need to be used. Despite having so many

135

entry points, the core API is still relatively simple and easy to code to.

136

This article aims to provide all of the background information needed to

137

easily understand how SQLite works.

138

</p>

139

140

<p>

141

A separate document, <a href="c3ref/intro.html">The SQLite C/C++ Interface</a>,

142

provides detailed

143

specifications for all of the various C/C++ APIs for SQLite. Once

144

the reader

145

understands the basic principles of operation for SQLite,

146

<a href="c3ref/intro.html">that document</a> should be used as a reference

147

guide. This article is intended as introduction only and is neither a

148

complete nor authoritative reference for the SQLite API.

149

</p>

150

151

<h2>1.0 Core Objects And Interfaces</h2>

152

153

<p>

154

The principal task of an SQL database engine is to evaluate statements

155

of SQL. In order to accomplish this purpose, the developer needs

156

to know about two objects:

157

</p>

158

159

<p><ul>

160

<li> The <a href="c3ref/sqlite3.html">database connection</a> object: sqlite3 </li>

161

<li> The <a href="c3ref/stmt.html">prepared statement</a> object: sqlite3_stmt </li>

162

</ul></p>

163

164

<p>

165

Strictly speaking, the <a href="c3ref/stmt.html">prepared statement</a> object is not required since

166

the convenience wrapper interfaces, <a href="c3ref/exec.html">sqlite3_exec</a> or

167

<a href="c3ref/free_table.html">sqlite3_get_table</a>, can be used and these convenience wrappers

168

encapsulate and hide the <a href="c3ref/stmt.html">prepared statement</a> object.

169

Nevertheless, an understanding of

170

<a href="c3ref/stmt.html">prepared statements</a> is needed to make full use of SQLite.

171

</p>

172

173

<p>

174

The <a href="c3ref/sqlite3.html">database connection</a> and <a href="c3ref/stmt.html">prepared statement</a> objects are controlled

175

by a small set of C/C++ interface routine listed below.

176

</p>

177

178

<p><ul>

179

<li> <a href="c3ref/open.html">sqlite3_open()</a> </li>

180

<li> <a href="c3ref/prepare.html">sqlite3_prepare()</a> </li>

181

<li> <a href="c3ref/step.html">sqlite3_step()</a> </li>

182

<li> <a href="c3ref/column_blob.html">sqlite3_column()</a> </li>

183

<li> <a href="c3ref/finalize.html">sqlite3_finalize()</a> </li>

184

<li> <a href="c3ref/close.html">sqlite3_close()</a> </li>

185

</ul></p>

186

187

<p>

188

The six C/C++ interface routines and two objects listed above form the core

189

functionality of SQLite. The developer who understands them

190

will have a good foundation for using SQLite.

191

</p>

192

193

<p>

194

Note that the list of routines is conceptual rather than actual.

195

Many of these routines come in multiple versions.

196

For example, the list above shows a single routine

197

named <a href="c3ref/open.html">sqlite3_open()</a> when in fact there are three separate routines

198

that accomplish the same thing in slightly different ways:

199

<a href="c3ref/open.html">sqlite3_open()</a>, <a href="c3ref/open.html">sqlite3_open16()</a> and <a href="c3ref/open.html">sqlite3_open_v2()</a>.

200

The list mentions <a href="c3ref/column_blob.html">sqlite3_column()</a>

201

when in fact no such routine exists.

202

The "sqlite3_column()" shown in the list is place holders for

203

an entire family of routines to be used for extracting column

204

data in various datatypes.

205

</p>

206

207

<p>

208

Here is a summary of what the core interfaces do:

209

</p>

210

211

212

213

<tr><td valign="top" align="right"><a href="c3ref/open.html">sqlite3_open()</a></td>

214

215

This routine

216

opens a connection to an SQLite database file and returns a

217

<a href="c3ref/sqlite3.html">database connection</a> object. This is often the first SQLite API

218

call that an application makes and is a prerequisite for most other

219

SQLite APIs. Many SQLite interfaces require a pointer to

220

the <a href="c3ref/sqlite3.html">database connection</a> object as their first parameter and can

221

be thought of as methods on the <a href="c3ref/sqlite3.html">database connection</a> object.

222

This routine is the constructor for the <a href="c3ref/sqlite3.html">database connection</a> object.

223

</td>

224

225

<tr><td valign="top" align="right"><a href="c3ref/prepare.html">sqlite3_prepare()</a></td>

226

227

This routine

228

converts SQL text into a <a href="c3ref/stmt.html">prepared statement</a> object and returns a pointer

229

to that object. This interface requires a <a href="c3ref/sqlite3.html">database connection</a> pointer

230

created by a prior call to <a href="c3ref/open.html">sqlite3_open()</a> and a text string containing

231

the SQL statement to be prepared. This API does not actually evaluate

232

the SQL statement. It merely prepares the SQL statement for evaluation.

233

234

<p>Note that the use of <a href="c3ref/prepare.html">sqlite3_prepare()</a> is not recommended for new

235

applications. The alternative routine <a href="c3ref/prepare.html">sqlite3_prepare_v2()</a> should

236

be used instead.</p>

237

</td>

238

239

<tr><td valign="top" align="right"><a href="c3ref/step.html">sqlite3_step()</a></td>

240

241

This routine is used to evaluate a <a href="c3ref/stmt.html">prepared statement</a> that has been

242

previously created by the <a href="c3ref/prepare.html">sqlite3_prepare()</a> interface. The statement

243

is evaluated up to the point where the first row of results are available.

244

To advance to the second row of results, invoke <a href="c3ref/step.html">sqlite3_step()</a> again.

245

Continue invoking <a href="c3ref/step.html">sqlite3_step()</a> until the statement is complete.

246

Statements that do not return results (ex: INSERT, UPDATE, or DELETE

247

statements) run to completion on a single call to <a href="c3ref/step.html">sqlite3_step()</a>.

248

</td>

249

250

251

<tr><td valign="top" align="right"><a href="c3ref/column_blob.html">sqlite3_column()</a></td>

252

253

This routine returns a single column from the current row of a result

254

set for a <a href="c3ref/stmt.html">prepared statement</a> that is being evaluated by <a href="c3ref/step.html">sqlite3_step()</a>.

255

Each time <a href="c3ref/step.html">sqlite3_step()</a> stops with a new result set row, this routine

256

can be called multiple times to find the values of all columns in that row.

257

As noted above, there really is no such thing as a "sqlite3_column()"

258

function in the SQLite API. Instead, what we here call "sqlite3_column()"

259

is really a place-holder for an entire family of functions that return

260

a value from the result set in various data types. There are also routines

261

in this family that return the size of the result (if it is a string or

262

BLOB) and the number of columns in the result set.

263

264

<p><ul>

265

<li> <a href="c3ref/column_blob.html">sqlite3_column_blob()</a> </li>

266

<li> <a href="c3ref/column_blob.html">sqlite3_column_bytes()</a> </li>

267

<li> <a href="c3ref/column_blob.html">sqlite3_column_bytes16()</a> </li>

268

<li> <a href="c3ref/column_count.html">sqlite3_column_count()</a> </li>

269

<li> <a href="c3ref/column_blob.html">sqlite3_column_double()</a> </li>

270

<li> <a href="c3ref/column_blob.html">sqlite3_column_int()</a> </li>

271

<li> <a href="c3ref/column_blob.html">sqlite3_column_int64()</a> </li>

272

<li> <a href="c3ref/column_blob.html">sqlite3_column_text()</a> </li>

273

<li> <a href="c3ref/column_blob.html">sqlite3_column_text16()</a> </li>

274

<li> <a href="c3ref/column_blob.html">sqlite3_column_type()</a> </li>

275

<li> <a href="c3ref/column_blob.html">sqlite3_column_value()</a> </li>

276

</ul></p>

277

</td>

278

279

<tr><td valign="top" align="right"><a href="c3ref/finalize.html">sqlite3_finalize()</a></td>

280

281

This routine destroys a <a href="c3ref/stmt.html">prepared statement</a> created by a prior call

282

to <a href="c3ref/prepare.html">sqlite3_prepare()</a>. Every prepared statement must be destroyed using

283

a call to this routine in order to avoid memory leaks.

284

</td>

285

286

<tr><td valign="top" align="right"><a href="c3ref/close.html">sqlite3_close()</a></td>

287

288

This routine closes a <a href="c3ref/sqlite3.html">database connection</a> previously opened by a call

289

to <a href="c3ref/open.html">sqlite3_open()</a>. All <a href="c3ref/stmt.html">prepared statements</a> associated with the

290

connection should be <a href="c3ref/finalize.html">finalized</a> prior to closing the

291

connection.

292

</td>

293

294

</table>

295

296

<h3>1.1 Typical Usage Of Core Routines And Objects</h3>

297

298

<p>

299

An application that wants to use SQLite will typically use

300

<a href="c3ref/open.html">sqlite3_open()</a> to create a single <a href="c3ref/sqlite3.html">database connection</a>

301

during initialization.

302

Note that <a href="c3ref/open.html">sqlite3_open()</a> can be used to either open existing database

303

files or to create and open new database files.

304

While many applications use only a single <a href="c3ref/sqlite3.html">database connection</a>, there is

305

no reason why an application cannot call <a href="c3ref/open.html">sqlite3_open()</a> multiple times

306

in order to open multiple <a href="c3ref/sqlite3.html">database connections</a> - either to the same

307

database or to different databases. Sometimes a multi-threaded application

308

will create separate <a href="c3ref/sqlite3.html">database connections</a> for each threads.

309

Note too that is not necessary to open separate database connections in

310

order to access two or more databases. A single <a href="c3ref/sqlite3.html">database connection</a>

311

can be made to access two or more databases at one time using the

312

<a href="lang_attach.html">ATTACH</a> SQL command.

313

</p>

314

315

<p>

316

Many applications destroy their <a href="c3ref/sqlite3.html">database connections</a> using calls to

317

<a href="c3ref/close.html">sqlite3_close()</a> at shutdown. Or, for example, an application might

318

open <a href="c3ref/sqlite3.html">database connections</a> in response to a File->Open menu action

319

and then destroy the corresponding <a href="c3ref/sqlite3.html">database connection</a> in response

320

to the File->Close menu.

321

</p>

322

323

<p>

324

To run an SQL statement, the application follows these steps:

325

</p>

326

327

<p><ol>

328

<li> Create a <a href="c3ref/stmt.html">prepared statement</a> using <a href="c3ref/prepare.html">sqlite3_prepare()</a>. </li>

329

<li> Evaluate the <a href="c3ref/stmt.html">prepared statement</a> by calling <a href="c3ref/step.html">sqlite3_step()</a> one

330

or more times. </li>

331

<li> For queries, extract results by calling

332

<a href="c3ref/column_blob.html">sqlite3_column()</a> in between

333

two calls to <a href="c3ref/step.html">sqlite3_step()</a>. </li>

334

<li> Destroy the <a href="c3ref/stmt.html">prepared statement</a> using <a href="c3ref/finalize.html">sqlite3_finalize()</a>. </li>

335

</ol></p>

336

337

<p>

338

The foregoing is all one really needs to know in order to use SQLite

339

effectively. All the rest is just ornamentation and detail.

340

</p>

341

342

<h2>2.0 Convenience Wrappers Around Core Routines</h2>

343

344

<p>

345

The <a href="c3ref/exec.html">sqlite3_exec()</a> interface is a convenience wrapper that carries out

346

all four of the above steps with a single function call. A callback

347

function passed into <a href="c3ref/exec.html">sqlite3_exec()</a> is used to process each row of

348

the result set. The <a href="c3ref/free_table.html">sqlite3_get_table()</a> is another convenience wrapper

349

that does all four of the above steps. The <a href="c3ref/free_table.html">sqlite3_get_table()</a> interface

350

differs from <a href="c3ref/exec.html">sqlite3_exec()</a> in that it stores the results of queries

351

in heap memory rather than invoking a callback.

352

</p>

353

354

<p>

355

It is important to realize that neither <a href="c3ref/exec.html">sqlite3_exec()</a> nor

356

<a href="c3ref/free_table.html">sqlite3_get_table()</a> do anything that cannot be accomplished using

357

the core routines. In fact, these wrappers are implemented purely in

358

terms of the core routines.

359

</p>

360

361

362

<h2>3.0 Binding Parameters and Reusing Prepared Statements</h2>

363

364

<p>

365

In prior discussion, it was assumed that each SQL statement is prepared

366

once, evaluated, then destroyed. However, the SQLite allows the same

367

<a href="c3ref/stmt.html">prepared statement</a> to be evaluated multiple times. This is accomplished

368

using the following routines:

369

</p>

370

371

<p><ul>

372

<li> <a href="c3ref/reset.html">sqlite3_reset()</a> </li>

373

<li> <a href="c3ref/bind_blob.html">sqlite3_bind()</a> </li>

374

</ul></p>

375

376

<p>

377

After a <a href="c3ref/stmt.html">prepared statement</a> has been evaluated by one or more calls to

378

<a href="c3ref/step.html">sqlite3_step()</a>, it can be reset in order to be evaluated again by a

379

call to <a href="c3ref/reset.html">sqlite3_reset()</a>.

380

Using <a href="c3ref/reset.html">sqlite3_reset()</a> on an existing <a href="c3ref/stmt.html">prepared statement</a> rather than

381

creating a new <a href="c3ref/stmt.html">prepared statement</a> avoids unnecessary calls to

382

<a href="c3ref/prepare.html">sqlite3_prepare()</a>.

383

In many SQL statements, the time needed

384

to run <a href="c3ref/prepare.html">sqlite3_prepare()</a> equals or exceeds the time needed by

385

<a href="c3ref/step.html">sqlite3_step()</a>. So avoiding calls to <a href="c3ref/prepare.html">sqlite3_prepare()</a> can result

386

in a significant performance improvement.

387

</p>

388

389

<p>

390

Usually, though, it is not useful to evaluate exactly the same SQL

391

statement more than once. More often, one wants to evaluate similar

392

statements. For example, you might want to evaluate an INSERT statement

393

multiple times though with different values to insert. To accommodate

394

this kind of flexibility, SQLite allows SQL statements to contain

395

<a href="lang_expr.html#varparam">parameters</a>

396

which are "bound" to values prior to being evaluated. These values can

397

later be changed and the same <a href="c3ref/stmt.html">prepared statement</a> can be evaluated

398

a second time using the new values.

399

</p>

400

401

<p>

402

In SQLite, wherever it is valid to include a string literal, one can use

403

a <a href="lang_expr.html#varparam">parameter</a> in one of the following forms:

404

</p>

405

406

<p><ul>

407

408

409

410

411

412

</ul></p>

413

414

<p>

415

In the examples above, <i>NNN</i> is an integer value and

416

<i>AAA</i> is an identifier.

417

A parameter initially has a value of NULL.

418

Prior to calling <a href="c3ref/step.html">sqlite3_step()</a> for the first time or immediately

419

after <a href="c3ref/reset.html">sqlite3_reset()</a>, the application can invoke one of the

420

<a href="c3ref/bind_blob.html">sqlite3_bind()</a> interfaces to attach values

421

to the parameters. Each call to <a href="c3ref/bind_blob.html">sqlite3_bind()</a>

422

overrides prior bindings on the same parameter.

423

</p>

424

425

<p>

426

An application is allowed to prepare multiple SQL statements in advance

427

and evaluate them as needed.

428

There is no arbitrary limit to the number of outstanding

429

<a href="c3ref/stmt.html">prepared statements</a>.

430

</p>

431

432

<h2>4.0 Extending SQLite</h2>

433

434

<p>

435

SQLite includes interfaces that can be used to extend its functionality.

436

Such routines include:

437

</p>

438

439

<p><ul>

440

<li> <a href="c3ref/create_collation.html">sqlite3_create_collation()</a> </li>

441

<li> <a href="c3ref/create_function.html">sqlite3_create_function()</a> </li>

442

<li> <a href="c3ref/create_module.html">sqlite3_create_module()</a> </li>

443

</ul></p>

444

445

<p>

446

The <a href="c3ref/create_collation.html">sqlite3_create_collation()</a> interface is used to create new

447

collating sequences for sorting text.

448

The <a href="c3ref/create_module.html">sqlite3_create_module()</a> interface is used to register new

449

virtual table implementations.

450

</p>

451

452

<p>

453

The <a href="c3ref/create_function.html">sqlite3_create_function()</a> interface creates new SQL functions -

454

either scalar or aggregate. The new function implementation typically

455

makes use of the following additional interfaces:

456

</p>

457

458

<p><ul>

459

<li> <a href="c3ref/aggregate_context.html">sqlite3_aggregate_context()</a> </li>

460

<li> <a href="c3ref/result_blob.html">sqlite3_result()</a> </li>

461

<li> <a href="c3ref/user_data.html">sqlite3_user_data()</a> </li>

462

<li> <a href="c3ref/value_blob.html">sqlite3_value()</a> </li>

463

</ul></p>

464

465

<p>

466

All of the built-in SQL functions of SQLite are created using exactly

467

these same interfaces. Refer to the SQLite source code, and in particular

468

the <b>date.c</b> and <b>func.c</b> source files for examples.

469

</p>

470

471

<h2>5.0 Other Interfaces</h2>

472

473

<p>

474

This article only mentions the foundational SQLite interfaces.

475

The SQLite library includes many other APIs implementing useful

476

features that are not described here.

477

A <a href="c3ref/funclist.html">complete list of functions</a> that form the SQLite

478

application programming interface is found at the

479

<a href="c3ref/intro.html">C/C++ Interface Specification</a>.

480

Refer to that document for complete and authoritative information about

481

all SQLite interfaces.

482

</p>

483